背景：

①版本

②事务隔离级别

死锁日志(SHOW ENGINE INNODB STATUS\G):

①DEAD LOCK INFO

点击(此处)折叠或打开

------------------------
LATEST DETECTED DEADLOCK
------------------------
161029 16:26:13
*** (1) TRANSACTION:
TRANSACTION 0 1919221023, ACTIVE 0 sec, process no 1229, OS thread id 1158142288 setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 1216
MySQL thread id 507208978, query id 2823365896 192.168.1.15 mcardmgr executing
INSERT INTO t_consum_20(mole_id, mcard_serial, consum_value, consum_date,consum_ip, consum_usage, paygate_trans_id) SELECT 472796134, 207714719, 1000, NOW(), 'XXX.XXX.XXX.XXX', 1, 109931029 FROM DUAL WHERE NOT EXISTS(SELECT * FROM t_consum_20 WHERE paygate_trans_id=109931029 AND consum_usage=1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `DB_MCARD_0/t_consum_20` trx id 0 1919221023 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 1919221021, ACTIVE 0 sec, process no 1229, OS thread id 1160350032 inserting, thread declared inside InnoDB 500
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 507208988, query id 2823365893 192.168.1.15 mcardmgr executing
INSERT INTO t_consum_20(mole_id, mcard_serial, consum_value, consum_date,consum_ip, consum_usage, paygate_trans_id) SELECT 641861380, 207852758, 1000, NOW(), 'XXX.XXX.XXX.XXX', 1, 109931028 FROM DUAL WHERE NOT EXISTS(SELECT * FROM t_consum_20 WHERE paygate_trans_id=109931028 AND consum_usage=1)
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `DB_MCARD_0/t_consum_20` trx id 0 1919221021 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 10657724 n bits 864 index `paygate_trans_id` of table `DB_MCARD_0/t_consum_20` trx id 0 1919221021 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 564f0ed9; asc VO ;; 1: len 4; hex 00512075; asc Q u;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS

②表结构

点击(此处)折叠或打开

mysql> desc t_consum_20;
+------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+----------------+
| consum_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| mole_id | int(10) unsigned | NO | | NULL | |
| mcard_serial | int(10) unsigned | NO | MUL | NULL | |
| consum_value | smallint(5) unsigned | NO | | NULL | |
| consum_date | datetime | NO | | NULL | |
| consum_ip | char(16) | NO | | 0.0.0.0 | |
| consum_usage | smallint(6) | NO | | NULL | |
| paygate_trans_id | int(10) unsigned | NO | MUL | 0 | |
+

概述：

①关于版本，5.0版本默认的innodb_autoinc_lock_mode=0，并且不能改变(其实就是没有这个变量)，从而在高并发的插入下极其容易到达性能瓶颈。
②关于事务隔离级别，RR隔离级别是较为严格的隔离级别，通过gap lock 解决了RC隔离级别下的phontam problem(幻读现象)，但也是由于gap lock的原因导致了此次的dead lock

分析：

①此次的死锁类型，同一张表，两个并发插入(带有子查询)的事务

②更具表结构以及死锁sql 简化
表结构

当前数据

③模拟事务sql

点击(此处)折叠或打开

sql1 = insert into dead_test select NULL,25,180 from DUAL where not exists (select * from dead_test where pid=180,age=25);
sql2 = insert into dead_test select NULL,35,170 from DUAL where not exists (select * from dead_test where pid=170,age=35);

④该类型sql加锁过程
1> 子查询(select * from dead_test where pid=180,age=25)获取对应范围pid索引的S GAP LOCK(此处pid 111~222加锁)，我们都知道innodb支持MVCC，即非锁定读，读不加锁，但是在子查询中的读是要加S锁的，事务提交释放。
2> 确定要插入操作，获取AUTO_INC LOCK
3> 获取pid索引对应范围的 X GAP LOCK，为了更改数据

⑤此次死锁产生是由于sql1进行到了第一步(然后申请该表的AUTO_INC LOCK，等待T2释放)，sql2进行到了第二步(然后申请对应pid索引范围的X GAP LOCK，得等T1的S GAP LOCK释放)。从而产生死锁。

另：
此次并发插入的数据比较密集，很容易因为gap lock产生所等待。
AUTO_INC LOCK 是在插入语句结束后就会释放，并不需要等待事务提交才释放。
其他的锁释放都是在事务提交之后。

关于innodb的表锁，行锁(RECORD LOCK、NEXT-KEY LOCK 、GAP LOCK)，参考《MySQL技术内幕：INNODB存储引擎》

推荐一篇关于InnoDB锁相关的博客 : MySQL加锁处理分析

ITPub博客

DEAD LOCK分析

最新文章