mysql 插入导致的死锁问题

 

1、问题现象，通过产生的日志可以看到，两个insert事务产生死锁，这个问题在oracle数据库中没有发生过，mysql是什么原因导致的此问题?

mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 3217010, OS thread handle 47641961760512, query id 649860665 10.xx.xx.xx
INSERT INTO table (columns) VALUES (values);               <------- Insert statement
[Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id xxxx 170 page no xxxx n bits 168 index PRIMARY of table "database_name"."table_name" trx id xxxx lock_mode X insert intention waiting

2018-11-19T20:59:38.997530Z 3217009 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 184047644, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
MySQL thread id 3217009, OS thread handle 47641962026752, query id 649860365 10.xx.xx.xx APP_USER
INSERT INTO table (columns) VALUES (values);               <---- Similar Insert statement as above and same table are used
2018-11-19T20:59:38.997556Z 3217009 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 17170 page no 1172 n bits 168 index PRIMARY of table "database_name"."table_name"  trx id 184047644 lock_mode X <---waiting on the primary key
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex xxxxxxx; asc supremum;;

2018-11-19T20:59:38.997612Z 3217009 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 17170 page no 1172 n bits 168 index PRIMARY of table "database_name"."table_name" trx id 184047644 lock_mode X insert intention waiting <---- waiting on same primary key
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex xxxxxxxxxxx; asc supremum;;

2018-11-19T20:59:38.997671Z 3217009 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

2018-11-19T21:07:50.532127Z 3217290 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2018-11-19T21:07:50.532157Z 3217290 [Note] InnoDB:

　　

2、通过查阅官方文档，可以看到是由于以下原因导致的，但是文档并没有给出过多的解释：

CHANGES
 Transaction isolation level is REPEATABLE-READ.

　　

3、先来了解下在这两种事务隔离方式的区别：

RR：即repeatable-read，

RC：即repeatable-commited，此事务模式是oracle默认的事务隔离级别模式。

 

 

4、为什么在mysql中高并发的插入会导致deadlock，而gap lock又是什么？

CAUSE
When Transaction isolation level is REPEATABLE-READ, Innodb will use Gap lock to prevent other transactions from inserting to the gap.

　　UPDATE和DELETE时（注意这里没有insert，经过对insert测试，没有出现此类情况。），除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁即锁住其扫描的范围。

5、如何避免此类问题？

 

SOLUTION
Change tx_isolation = READ COMMITTED. With READ COMMITTED, it will reduce the gap locks and may help to your deadlocks here.