死锁

本文介绍在业务正真实的一段是锁记录，并根据死锁的记录，复现出死锁的产生过程。

死锁的出现

死锁是指两个或多个进程在执行过程中,在产生资源争夺的时候造成的一种锁互相等待的现象,若无任何干涉，它们都将无法继续执行。此时涉及的线程将处于死锁状态或产生了死锁，表级锁不会产生死锁.死锁问题主要还是针对于InnoDB存储引擎。InnoDB存储引擎虎自爱检测到死锁之后，随机的选择死锁会话中的某一个会话进行回滚操作，解开死锁，并且在InnoDB的监控器中打印一段关于死锁的记录，并且仅保留最近的一次死锁记录。

在业务中，show engine innodb status的记录中，有如下一段死锁的记录：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-11 13:56:34 0x7f471b9a6700
*** (1) TRANSACTION:  
###############事务1
TRANSACTION 415818092, ACTIVE 27 sec inserting
mysql tables in use 1, locked 1 
LOCK WAIT 7 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3
MySQL thread id 17681370, OS thread handle 139944139474688, query id 884795633 10.10.10.1 writing update 
##############对应的线程号，系统线程，查询线程，客户端ip地址，数据库名writing，操作类型update
insert into factory
                 ( CARRFID,..., UPDATETIME ) 
                values  ( 'x9d2T8RRTZW1'...'2020-12-11 13:56:08.016' )
################对应的SQL语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
################事务1等待授予的锁类型
RECORD LOCKS space id 15308 page no 35410 n bits 232 index PRIMARY of table `writing`.`factory` trx id 415818092 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 112 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 22; hex 5839644631636e71546953307231636e356950626e51; asc X9dF1cnqTiS0r1cn5iPbnQ;;
 1: len 6; hex 000016f15fc1; asc     _ ;;
 2: len 7; hex f0000035e00170; asc    5  p;;
 3: len 22; hex 5f3934674d50683451444b5744616f444c35565f5a41; asc _94gMPh4QDKWDaoDL5V_ZA;;
 4: len 2; hex 3939; asc 99;;
 5: len 6; hex e585b6e4bb96; asc       ;;
 6: SQL NULL;
 7: len 1; hex 31; asc 1;;
 8: len 5; hex 99a77ab7f0; asc   z  ;;
 9: len 5; hex 99a77ab7f0; asc   z  ;;
 10: SQL NULL;

*** (2) TRANSACTION: #事务2
TRANSACTION 415818103, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3
MySQL thread id 17677221, OS thread handle 139943382509312, query id 884795717 10.10.10.1  writing update


insert into factory
                 ( CARRFID,..... UPDATETIME ) 
                values  ( 'UVhLaPA'...'2020-12-11 13:56:08.513' )
*** (2) HOLDS THE LOCK(S):
#############事务2持有的锁
RECORD LOCKS space id 15308 page no 35410 n bits 232 index PRIMARY of table `writing`.`factory` trx id 415818103 lock mode S locks gap before rec
Record lock, heap no 112 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 22; hex 5839644631636e71546953307231636e356950626e51; asc X9dF1cnqTiS0r1cn5iPbnQ;;
 1: len 6; hex 000016f15fc1; asc     _ ;;
 2: len 7; hex f0000035e00170; asc    5  p;;
 3: len 22; hex 5f3934674d50683451444b5744616f444c35565f5a41; asc _94gMPh4QDKWDaoDL5V_ZA;;
 4: len 2; hex 3939; asc 99;;
 5: len 6; hex e585b6e4bb96; asc       ;;
 6: SQL NULL;
 7: len 1; hex 31; asc 1;;
 8: len 5; hex 99a77ab7f0; asc   z  ;;
 9: len 5; hex 99a77ab7f0; asc   z  ;;
 10: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15308 page no 35410 n bits 232 index PRIMARY of table `writing`.`factory` trx id 415818103 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 112 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 22; hex 5839644631636e71546953307231636e356950626e51; asc X9dF1cnqTiS0r1cn5iPbnQ;;
 1: len 6; hex 000016f15fc1; asc     _ ;;
 2: len 7; hex f0000035e00170; asc    5  p;;
 3: len 22; hex 5f3934674d50683451444b5744616f444c35565f5a41; asc _94gMPh4QDKWDaoDL5V_ZA;;
 4: len 2; hex 3939; asc 99;;
 5: len 6; hex e585b6e4bb96; asc       ;;
 6: SQL NULL;
 7: len 1; hex 31; asc 1;;
 8: len 5; hex 99a77ab7f0; asc   z  ;;
 9: len 5; hex 99a77ab7f0; asc   z  ;;
 10: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
复制

准备表和语句：

##########建表
CREATE TABLE `t` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#############插入数据
insert into t(a,b) values(1,1),(6,6),(12,12),(17,17),(24,24)

select * from t;
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  6 |    6 |
| 12 |   12 |
| 17 |   17 |
| 24 |   24 |
+----+------+


应用语句：
事务一：

begin;
select * from t LOCK IN SHARE MODE;
 insert into t values(19,19);

事务2:

begin;
select * from t LOCK IN SHARE MODE;
 insert into t values(16,16);
复制

死锁的复现过程：

事务1和事务2单独执行都是可以的，但是出现并发的时候，可能发生同时操作同一张表的同一行数据情况，顺序如下：

事务一：
set  autocommit=off;
begin;
select * from t LOCK IN SHARE MODE;

事务2:
set  autocommit=off;
begin;
select * from t LOCK IN SHARE MODE;
 insert into t values(16,16);

事务一：
 insert into t values(19,19);
即出现死锁
复制

总结

死锁的发生往往是因为在高并发的情况下，不同的线程操作了同一条数据，即不同的会话在操作同一条数据或同一个表的数据，造成死循环的锁等待，产生死锁，虽然MySQL中的死锁不需要人为干涉即可随机的通过回滚操作完成死锁的解锁，但是死锁的产生并没有从根本上解决，要解决死锁的问题，需要开飞配合理清楚业务逻辑，对同一条数据的操作，尽量放在一个会话中完成，避免死锁的出现。