linux mysql 查看死锁_MySQL死锁发生原理及最大化规避

InnoDB死锁原理：

死锁的情况发在不同的的事务相互之间拥有对需要的锁，导致相互直限等待

死锁可能发在不同的事务都会对多个相同的表和相同的上施加锁，但事务对表的操作顺序不相同

为了减少死锁的发，要避免使lock table语句，要尽量让修改数据的范围尽可能的和快速；当不同的事务要修改多个表或者量数据时，尽可能的保证修改的顺序在事务之间要致

默认情况下InnoDB下的死锁动侦测功能是开启的，当InnoDB发现死锁时，会将其中的个事务作为牺牲品回滚。

通过innodb_deadlock_detect参数配置动侦测功能是否开启，如果关闭的话，InnoDB就会使innodb_lock_wait_timeout参数来动回滚等待够时间的事务

可以通过show engine innodb status语句查看最后次发死锁的情况

查看是否开启自动侦测死锁：

mysql> show variables like '%innodb_deadlock%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| innodb_deadlock_detect | ON    |

+------------------------+-------+

1 row in set (0.00 sec)

InnoDB死锁操作：

# 两个会话窗口 1 2 全部 set autocommit=0;

# 会话 1 窗口执行  ( 获得 sid=1 的数据行排它锁 )

mysql> update students set sname='aaa' where sid=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

# 会话 2 窗口执行  ( 获得 sid=2 的数据行排它锁 )

mysql> update students set sname='ccc' where sid=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

# 会话 1 窗口执行 等待会话 2 窗口锁释放中

mysql> update students set sname='bbb' where sid=2;

# 会话 2 窗口执行 MySQL发现死锁，直接全部回滚会话 2 窗口的当前事务所有操作

mysql> update students set sname='ddd' where sid=1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

InnoDB死锁检测和回滚：

默认情况下死锁检测功能是开启的，当死锁发⽣时InnoDB会动检测到并牺牲(回滚)其中的个或者个事务，以便让其他的事务继续执下去。

InnoDB选择牺牲的事务往往是代价较的事务，其代价计算是根据事务insert,update, delete的数据规模决定。

如果事务中的某个语句因为错误回滚，则这个语句上的锁可能还会保留，是因为InnoDB仅会存储锁信息，不会存储锁是由事务中的哪个语句产的。

如果在个事务中，select语句调了函数，函数中的某个语句执失败，则那个语句会回滚，如果在整个事务结束时执rollback，则整个事务回滚。

可以通过innodb_deadlock_detect参数关闭死锁检测功能，仅仅innodb_lock_wait_timeout的功能来释放锁等待

减少死锁发的方法：

在事务性数据库中，死锁是个经典的问题，但只要发⽣的频率不⾼则死锁问题不需要太过担⼼

查看死锁的方法有两种：

通过show engine innodb status命令可以查看最后个死锁的情况。

通过innodb_print_all_deadlocks参数配置可以将所有死锁的信息都打印到MySQL的错误志中。

减少死锁发的方法：

尽可能的保持事务型化，减少事务执⾏的时间可以减少发影响的概率。

及时执commit或者rollback，来尽快的释放锁。

可以选较低的隔离级别，如如果要使⽤select... for update和select...lock in share mode语句时可以使读取提交数据隔离级别。

当要访问多个表数据或者要访问相同表的不同集合时，尽可能的保证每次访问的顺序是相同的。如可以将多个语句封装在存储过程中，通过调同个存储过程的法可以减少死锁的发。

增加合适的索引以便语句执所扫描的数据范围够。

尽可能的少使锁，如如果可以承担幻读的情况，则直接使select语句，不要使select...for update语句。

如果没有其他更好的选择，则可以通过施加表级锁将事务执串化，最限度的限制死锁发

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES;

死锁日志记录设置：

# 查看死锁日志记录是否打开

mysql> show variables like '%innodb_print_all_deadlocks%';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_print_all_deadlocks | OFF   |

+----------------------------+-------+

1 row in set (0.01 sec)

# 临时打开死锁记录日志

mysql> set global innodb_print_all_deadlocks=1;

Query OK, 0 rows affected (0.00 sec)

# 再次模拟死锁发生后查看MySQL Error日志

[root@olda-study ~]# tail -f /usr/local/mysql-8.0.16/logs/mysql.log

TRANSACTION 14360, ACTIVE 16 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 12, OS thread handle 139818970310400, query id 104 localhost root updating

update students set sname='bbb' where sid=2

RECORD LOCKS space id 53 page no 4 n bits 80 index PRIMARY of table `course`.`students` trx id 14360 lock_mode X locks rec but not gap waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000002; asc     ;;

1: len 6; hex 000000003819; asc     8 ;;

2: len 7; hex 020000011c07cb; asc        ;;

3: len 3; hex 636363; asc ccc;;

4: len 1; hex 30; asc 0;;

5: len 4; hex 80000001; asc     ;;

TRANSACTION 14361, ACTIVE 11 sec starting index read

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 13, OS thread handle 139818970605312, query id 105 localhost root updating

update students set sname='ddd' where sid=1

RECORD LOCKS space id 53 page no 4 n bits 80 index PRIMARY of table `course`.`students` trx id 14361 lock_mode X locks rec but not gap

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000002; asc     ;;

1: len 6; hex 000000003819; asc     8 ;;

2: len 7; hex 020000011c07cb; asc        ;;

3: len 3; hex 636363; asc ccc;;

4: len 1; hex 30; asc 0;;

5: len 4; hex 80000001; asc     ;;

RECORD LOCKS space id 53 page no 4 n bits 80 index PRIMARY of table `course`.`students` trx id 14361 lock_mode X locks rec but not gap waiting

Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000001; asc     ;;

1: len 6; hex 000000003818; asc     8 ;;

2: len 7; hex 010000011f0f03; asc        ;;

3: len 3; hex 616161; asc aaa;;

4: len 1; hex 31; asc 1;;

5: len 4; hex 80000001; asc     ;;