MySQL的一种数据库表死锁场景分析及规避方案

2023-01-08 15:58:19

在我们的软件开发过程中，MySQL的使用算是非常普遍的，利用数据库进行数据点CRUD操作，数据记录会涉及到一些锁的操作，有读锁，有写锁，最典型的就是S（共享锁）和X（排它锁），S和X锁，在MySQL的InnoDB引擎下，才会存在，在MyISAM引擎下是不存在的，因为S和X是针对行锁的，MyISAM是表锁，不存在行锁；另外，还有间隙锁，这些和锁相关的概念，这里不多讲。本博文记录X锁在MySQL的InnoDB引擎下导致数据库死锁的问题。

 

按说，就算X锁是行锁，在InnoDB下，其实也不是什么不好的存在，关键是X锁在事物（Transaction）处理中，就容易出现死锁问题，不存在事物的情况下，X锁也是不会导致问题的。但是呢，关系型数据库的使用，事物操作，是非常基础且非常有价值的。

 

什么是死锁呢？一个典型的解释是：张三拥有资源A，希望得到李四的资源B，然后有助于自己完成工作；然而，李四拥有资源B，希望得到张三的资源A，然后有助于自己完成工作。张三不愿意释放自己的资源A，李四也不愿意释放自己的资源B，但是都希望获取对方的资源，于是，两个人都无法完成各自的工作，僵持下去，死耗。。。。

 

那事物这么有价值，怎么还会在X锁的时候，出现数据库死锁的危险结果呢？

想想，X锁，就是排他锁，在数据库的每一行记录的update和delete的时候，都会出现X锁，在对某行记录进行更新和删除操作时，需要先获得X锁，否则是无法对该行记录进行操作的。

事物操作，就是为了保证数据操作的原子性，即，所操作的一系列行为，要么全成功，要么全失败。

在X锁和事物同时存在于一批（至少2条）数据的操作时，就有可能出现死锁现象。这里只是说的是有可能，结合上面的张三和李四的案例，你或许会发现，参与事物的两方，获取到的资源顺序是错位的，然后，这两个事物近乎是同时发生的，于是就会出现张三等李四的资源B，李四等张三的资源A（张三只有等到李四的资源B，才能完成自己的工作，结束事物；同理，李四只有等到张三的资源A，才能完成自己的工作，结束事物。这里要划重点，只有结束了事物，在事物里面获取的X锁，才会逐次释放）

 

下面就拿MySQL数据库的命令行操作，进行案例演示。两个事物，分别在两个shell的窗口下进行，操作数据库里面的两行记录，按照不同的顺序进行（顺序非常重要，重点关注顺序的差异）。

数据表结构：

CREATE TABLE `deadlock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tValue` varchar(255) DEFAULT NULL,
  `tKey` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

 

事物A的操作的内容：

--transcation A
START TRANSACTION;
update deadlock_test set tValue = "trans1" WHERE id = 1;
update deadlock_test set tValue = "trans2" where id = 2;
commit;

事物B的操作的内容：

--transcation B
START TRANSACTION;
update deadlock_test set tValue = "trans3" WHERE id = 2;
update deadlock_test set tValue = "trans4" where id = 1;
commit;

 

下面以慢镜头的方式，分别记录上述两个事物的指令执行的顺序：

事物A的窗口：

a.开始事物A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update deadlock_test set tValue = "trans1" WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
c. (这边执行后，会出现等待。。。)
mysql> update deadlock_test set tValue = "trans2" where id = 2;
Query OK, 1 row affected (10.93 sec)
Rows matched: 1  Changed: 1  Warnings: 0
f.提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事物B的窗口：

b.开始事物B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update deadlock_test set tValue = "trans3" WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
d.(这句执行后，里面出现报错)
mysql> update deadlock_test set tValue = "trans4" where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
e.执行了这句后，出现等待，直到f步骤完毕才ok
mysql> update deadlock_test set tValue = "trans4" where id = 1;
Query OK, 1 row affected (9.92 sec)
Rows matched: 1  Changed: 1  Warnings: 0
g.提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

上面的两个事物窗口中，所有的指令按照a,b,c,d,e,f,g的顺序进行操作，相应的步骤的操作的对应解说，用高亮字体标注出来了，高度关注执行的顺序，一直在强调注意顺序，InnoDB的表记录操作死锁，就是在错乱的顺序下不经意间出现的。数据库里面也会有一些帮助信息，有助于分析大型应用系统中出现类似死锁的根因。

mysql> show engine innodb status;

=====================================
2020-12-31 08:43:43 0x7f32fecb3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 56 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 101350 srv_active, 0 srv_shutdown, 39795010 srv_idle
srv_master_thread log flush and writes: 39895271
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 935661
OS WAIT ARRAY INFO: signal count 1015792
RW-shared spins 0, rounds 515841, OS waits 147862
RW-excl spins 0, rounds 200123827, OS waits 110891
RW-sx spins 57537, rounds 619294, OS waits 5906
Spin rounds per wait: 515841.00 RW-shared, 200123827.00 RW-excl, 10.76 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-12-04 16:52:01 0x7f330c348700 Error in foreign key constraint of table iotscc/#sql-d23_62cc:
FOREIGN KEY (`userId`) REFERENCES `scc_mqtt_user_2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-31 08:42:28 0x7f32fecb3700
*** (1) TRANSACTION:
TRANSACTION 2073046, ACTIVE 79 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 323428, OS thread handle 139857014466304, query id 222414190 10.95.200.17 mysql updating
update deadlock_test set tValue = "trans2" where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2064 page no 3 n bits 72 index PRIMARY of table `myhrm`.`deadlock_test` trx id 2073046 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000001fa1d7; asc       ;;
 2: len 7; hex 54000003500110; asc T   P  ;;
 3: len 6; hex 7472616e7333; asc trans3;;
 4: len 4; hex 676f6f64; asc good;;

*** (2) TRANSACTION:
TRANSACTION 2073047, ACTIVE 27 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 323430, OS thread handle 139856999823104, query id 222414198 10.95.200.17 mysql updating
update deadlock_test set tValue = "trans4" where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2064 page no 3 n bits 72 index PRIMARY of table `myhrm`.`deadlock_test` trx id 2073047 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000001fa1d7; asc       ;;
 2: len 7; hex 54000003500110; asc T   P  ;;
 3: len 6; hex 7472616e7333; asc trans3;;
 4: len 4; hex 676f6f64; asc good;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2064 page no 3 n bits 72 index PRIMARY of table `myhrm`.`deadlock_test` trx id 2073047 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000001fa1d6; asc       ;;
 2: len 7; hex 530000024522cc; asc S   E" ;;
 3: len 6; hex 7472616e7331; asc trans1;;
 4: len 5; hex 68656c6c6f; asc hello;;

*** WE ROLL BACK TRANSACTION (2)

 

从show engine innodb status的输出指令，其实比较清楚的看出一些重要信息，即在(1)TRANSACTION中操作SQL：update deadlock_test set tValue = "trans2" where id = 2的时候等待锁被授权，在(2)TRANSACTION中操作SQL：update deadlock_test set tValue = "trans4" where id = 1的时候等待锁被授权。但是，我们的SQL操作，事物1所需的锁，被事物2的第一个行所占有，事物2所操作的数据的行锁，被事物1的第一个行所占有。

 

总结：

1）InnoDB才会出现行锁X，表锁引擎MyISAM是不会出现死锁的问题。

2）行锁X所授权的行记录顺序非常重要，事物范围内，多个事物操作至少2条记录的Update或者delete时，才有可能出现死锁

3）数据库操作，针对广泛使用的InnoDB，事物操作时，必须考虑操作同一批数据的不同事物，要么在时间上错开，不要出现并发（将并发的操作，调整成时间轴上的顺序操作）

4）......

 

欢迎参与评论

上一篇：MYSQL 死锁


下一篇：搬砖 - js 排序es6