解决一次mysql死锁问题

解决一次mysql死锁问题

背景

多线程开启事务处理。每个事务有多个update操作和一个insert操作（都在同一张表）。

DDL（删除了一些不必要的细节）

默认隔离级别：Repeatable Read

CREATE TABLE `list_rate` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) NOT NULL,
  `rate_date` date NOT NULL,
  `room_type_id` bigint(20) NOT NULL,
  `rate` decimal(20,2) NOT NULL DEFAULT '0.00',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `operator_id` int(10) NOT NULL DEFAULT '0',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `hotel_date_idx` (`hotel_id`,`rate_date`) USING BTREE
) ENGINE=InnoDB;

数据库现有数据

只有hotel_id=2和hotel_id=11111的数据

事务执行的sql（每个事务的区别是hotel_id不同，从10001到10010）

逻辑删除原有数据

UPDATE list_rate 
SET is_deleted = 1, update_time = now(), operator_id = 1 
WHERE hotel_id = 10007 AND room_type_id = 1 AND is_deleted = 0 AND rate_date IN ( '2018-11-10' , '2018-11-11' ) 

插入新的数据

INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted) 
VALUES (10007,'2018-11-10',1,144, now(),now(),1,0) , (10007,'2018-11-11',1,148, now(),now(),1,0) 

根据现有数据情况，update的时候没有数据被更新

实际运行结果

报了非常多一样的错

Exception in thread "UpdateListRate-33" org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.oyo.price.mapper.ListRateMapper.presetRate-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted)         VALUES                        (10005,'2018-11-10',1,148,             now(),now(),1,0)          ,              (10005,'2018-11-11',1,123,             now(),now(),1,0)
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; ]; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)
    at com.sun.proxy.$Proxy100.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:254)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy109.presetRate(Unknown Source)
    at com.oyo.price.repository.impl.ListRateRepositoryImpl.presetRate(ListRateRepositoryImpl.java:36)
    at com.oyo.price.repository.impl.ListRateRepositoryImpl$$FastClassBySpringCGLIB$$6a37e66d.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.oyo.price.repository.impl.ListRateRepositoryImpl$$EnhancerBySpringCGLIB$$ccfbbc66.presetRate(<generated>)
    at com.oyo.price.service.impl.MultithreadServiceImpl.updateListRate(MultithreadServiceImpl.java:60)
    at com.oyo.price.service.impl.MultithreadServiceImpl$$FastClassBySpringCGLIB$$e04a972c.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
    at com.oyo.price.service.impl.MultithreadServiceImpl$$EnhancerBySpringCGLIB$$f81880f4.updateListRate(<generated>)
    at com.oyo.price.service.impl.ListRateServiceImpl$UpdateListRateThread.run(ListRateServiceImpl.java:559)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
    at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy152.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
    at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy113.update(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
    at com.oyo.price.mybatis.CatMybatisPlugin.intercept(CatMybatisPlugin.java:84)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy113.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157)
    at sun.reflect.GeneratedMethodAccessor119.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:408)
    ... 28 more

发现居然有死锁。
根据常识考虑，我每个线程（事务）更新的数据都不冲突，为什么会产生死锁？
带着这个问题，打印mysql最近一次的死锁信息
show engine innodb status
显示如下

=====================================
2018-12-26 11:58:07 0x4994 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 35631 srv_active, 0 srv_shutdown, 478534 srv_idle
srv_master_thread log flush and writes: 514165
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 73218
OS WAIT ARRAY INFO: signal count 76026
RW-shared spins 0, rounds 149557, OS waits 72069
RW-excl spins 0, rounds 30643, OS waits 72
RW-sx spins 3137, rounds 22537, OS waits 108
Spin rounds per wait: 149557.00 RW-shared, 30643.00 RW-excl, 7.18 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-12-26 11:31:01 0x4e84
*** (1) TRANSACTION:
TRANSACTION 4348374, ACTIVE 0 sec inserting
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 142, OS thread handle 16456, query id 279197 localhost 127.0.0.1 root update
INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted)
        VALUES
          
            (10007,'2018-11-10',1,139,
            now(),now(),1,0)
         , 
            (10007,'2018-11-11',1,105,
            now(),now(),1,0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348374 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 457 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000002b67; asc       +g;;
 1: len 3; hex 8fc56b; asc   k;;
 2: len 8; hex 8000000000000164; asc        d;;

*** (2) TRANSACTION:
TRANSACTION 4348375, ACTIVE 0 sec inserting
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 146, OS thread handle 20100, query id 279199 localhost 127.0.0.1 root update
INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted)
        VALUES
          
            (10009,'2018-11-10',1,129,
            now(),now(),1,0)
         , 
            (10009,'2018-11-11',1,88,
            now(),now(),1,0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348375 lock_mode X locks gap before rec
Record lock, heap no 457 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000002b67; asc       +g;;
 1: len 3; hex 8fc56b; asc   k;;
 2: len 8; hex 8000000000000164; asc        d;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348375 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 457 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000002b67; asc       +g;;
 1: len 3; hex 8fc56b; asc   k;;
 2: len 8; hex 8000000000000164; asc        d;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 4348700
Purge done for trx's n:o < 4348700 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283745059479304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059478432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059477560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059476688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059475816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059474944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059474072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283745059473200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
3705 OS file reads, 393906 OS file writes, 287002 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.86 writes/s, 0.76 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 789, seg size 791, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.24 hash searches/s, 0.10 non-hash searches/s
---
LOG
---
Log sequence number 1153015126
Log flushed up to   1153015126
Pages flushed up to 1153015126
Last checkpoint at  1153015117
0 pending log flushes, 0 pending chkp writes
180065 log i/o's done, 0.48 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 164031
Buffer pool size   8192
Free buffers       3424
Database pages     4762
Old database pages 1737
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3615, created 1147, written 177183
0.00 reads/s, 0.00 creates/s, 0.29 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4762, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3504, Main thread ID=4272, state: sleeping
Number of rows inserted 15970, updated 46294, deleted 15313, read 1720295
0.00 inserts/s, 0.10 updates/s, 0.00 deletes/s, 0.24 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

发现事务1在等待一个锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348374 lock_mode X locks gap before rec insert intention waiting

事务2也在等待一个锁

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348375 lock_mode X locks gap before rec insert intention waiting

而且事物2持有了事物1需要的锁

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 65 page no 5656 n bits 736 index hotel_date_idx of table `price`.`list_rate` trx id 4348375 lock_mode X locks gap before rec

关于锁的描述，出现了lock_mode，gap before rec，insert intention等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。
通过搜索引擎，

获取到如下知识：

InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）

InnoDB还将锁细分为如下几种子类型：

• record lock（RK）
  锁直接加在索引记录上面，锁住的是key
• gap lock（GK）
  间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
• next key lock（NK）
  RK+GK
• insert intention lock（IK）
  如果插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞。

锁的持有兼容程度如下表

请求锁\持有锁	gap lock	insert intention lock	record lock	next key lock
gap lock	兼容	兼容	兼容	兼容
insert intention lock	冲突	兼容	兼容	冲突
record lock	兼容	兼容	冲突	冲突
next key lock	兼容	兼容	冲突	冲突

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁
事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道，gap lock和insert intention lock是不兼容的
那么就可以推断出：事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。
那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？
让我回顾一下gap lock的定义：
间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。
调查后发现，当当前索引是一个普通索引的时候，会加一个gap lock来防止幻读，此gap lock 会锁住一个左开右闭的区间。假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。
ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料
MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

UPDATE list_rate 
SET is_deleted = 1, update_time = now(), operator_id = 1 
WHERE hotel_id = 10007 AND room_type_id = 1 AND is_deleted = 0 AND rate_date IN ( '2018-11-10' , '2018-11-11' ) 

因为普通索引
KEYhotel_date_idx(hotel_id,rate_date)
的关系 这段sql会获取一个gap lock，范围(2,11111]

INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted) 
VALUES (10007,'2018-11-10',1,144, now(),now(),1,0) , (10007,'2018-11-11',1,148, now(),now(),1,0) 

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

UPDATE list_rate 
SET is_deleted = 1, update_time = now(), operator_id = 1 
WHERE hotel_id = 10009 AND room_type_id = 1 AND is_deleted = 0 AND rate_date IN ( '2018-11-10' , '2018-11-11' ) 

因为普通索引
KEYhotel_date_idx(hotel_id,rate_date)
的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted) 
VALUES (10009,'2018-11-10',1,144, now(),now(),1,0) , (10009,'2018-11-11',1,148, now(),now(),1,0) 

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。
重新梳理一下：

transaction1	transaction2
update (10007,'2018-11-11') gap lock (2,11111]
	update (10009,'2018-11-11') gap lock (2,11111]
insert wait lock
	insert wait lock
	dead lock roll back
done

所以从结果来看，一堆事务被回滚，只有10007数据被更新成功

结论

gap lock 导致了并发处理的死锁

处理

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

知识库levelup

共享锁、排他锁、意向共享、意向排他
record lock、gap lock、next key lock、insert intention lock
show engine innodb status