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     ;;