MySQL死锁最简单易懂实例解析

MySQL死锁最简单易懂实例解析

网上的很多教程，关于死锁的分析，各种意向锁X锁等，太乱，太复杂，关于死锁，本人认为重点在于分析原因和解决问题，所以我们用最简单易懂的话语来解释，下面具体举例分析。

一个具体的例子：

表结构和数据如下：

create table dl(
id int auto_increment primary key,
c1 int not null ,
c2 int not null,
key idx_c1(c1));

insert into dl(c1,c2) values (3,1),(3,2),(3,2),(3,3),(4,4),(5,5);

死锁日志如下：通过在mysql执行命令show ENGINE innodb status可查看最近的一次死锁的日志信息。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-03 16:43:59 140261132850944
*** (1) TRANSACTION:
TRANSACTION 1416764, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 15, OS thread handle 140261086668544, query id 283 localhost msandbox updating
update dl set c2=10 where c1=5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416764 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416764 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 1416759, ACTIVE 23 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140261085611776, query id 286 localhost msandbox updating
delete from dl where id=6

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

解析：
关于死锁的分析，各种意向锁X锁等，太乱，太复杂，网上很多这方面的教程，对于死锁重点在于分析原因和解决问题，所以我们用最简单易懂的话语来解释：
从上面死锁日志可以看出：
事务一：update dl set c2=10 where c1=5
事务二：delete from dl where id=6
原因：
事务一：事务一持有索引c1的锁，申请主键id的锁（为什么update修改的是c2，却需要主键的锁呢，因为update是先select出来，并且是通过索引找到主键然后通过主键找到这条数据，然后在update。一个update语句是分步骤的）
事务二：事务二持有主键id的锁，由于需要删除这条数据，包含c1=5，申请索引c1的锁。
死锁出现：事务二申请c1的锁，发现事务一持有c1的锁，等待事务一解除c1的锁，而事务一又等待事务二解除主键的锁，因而造成相互等待解锁，造成死锁。
（这就是典型的加锁顺序不同导致的死锁，同时数据上可以看出id=6这条数据的c1=5）

如何解决：
对于加锁顺序导致的死锁，解决办法就是把索引作为条件的update修改为以主键作为条件：update dl set c2=10 where id=6 。但是实际上在程序中，这种情况发生死锁的几率是极小的。一般c1没有索引，c2是索引的情况下造成死锁的几率就会很大。