Innodb 死锁案例解析

 先贴上案例信息吧：


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    
*** (1) TRANSACTION:
TRANSACTION 52EDC5761, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 34815573, OS thread handle 0x7f1e42a6a700, query id 9442822687 192.168.1.216 pns update
INSERT IGNORE INTO user_device_app (uid,aid) VALUES(22504356,219843041)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 527 page no 7603 n bits 584 index `PRIMARY` of table `pns`.`user_device_app` trx id 52EDC5761 lock_mode X lock
s rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 52EDC5762, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 34823701, OS thread handle 0x7f1e58309700, query id 9442822688 192.168.1.86 pns update
INSERT IGNORE INTO user_device_app (uid,aid) VALUES(22504356,219843041)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 527 page no 7603 n bits 584 index `PRIMARY` of table `pns`.`user_device_app` trx id 52EDC5762 lock mode S lock
s rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 527 page no 7603 n bits 584 index `PRIMARY` of table `pns`.`user_device_app` trx id 52EDC5762 lock_mode X lock
s rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

解释说明下： uid,aid 做为主键，且程序中未开启事务；对于user_device_app 表也只有 delete... ; insert ignore ...., select 三个操作；

死锁是由两个完全相同的语句： insert ignore into user_device_app 造成的；

个人在测试环境，怎么也不能重现死锁的现象

后经请教： insert ignore 是先获得S锁（共享锁），然后在升级为X锁;（所以死锁信息中事务2在后的S锁后，又开始获得X锁）

由于两个事务同时获得了S锁，都升级为X锁的时候，都在互相等待，所以出现死锁现象；

小结：

数据库端：若要避免使用ignore 这样的关键字陷入死锁中：

要禁止同一行并行执行INSERT…ON DUPLICATE KEY特别是INSERT…ON DUPLICATE KEY UPDATE ，以及INSERT IGNORE语句。实在不行就改为（select + insert 来处理）

程序端

要有捕捉死锁回滚信息的代码；

对于防止用户点击多次，造成重复记录的，可以在前端代码中做出限制