解决一次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

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,590评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,808评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,151评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,779评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,773评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,656评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,022评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,678评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,038评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,756评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,411评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,005评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,973评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,053评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,495评论 2赞 343

推荐阅读更多精彩内容

  • 吃透Mysql
    Mysql概述 数据库是一个易于访问和修改的信息集合。它允许使用事务来确保数据的安全性和一致性，并能快速处理百万条...
    彦帧阅读 13,654评论 10赞 461
  • mysql锁详解
    1. mysql锁知多少 我们进行insert，update，delete，select会加锁吗，如果加锁，加锁步...
    liwsh阅读 4,964评论 0赞 4
  • 从死锁认识MySQL锁
    前言 作为后端开发人员，几乎每天都与数据库打交道。对着变化莫测的需求，紧张的工期，很多程序员日复一日写着CRUD代...
    蕉仔伟阅读 1,200评论 0赞 2
  • mysql锁分析
    当一个系统访问量上来的时候，不只是数据库性能瓶颈问题了，数据库数据安全也会浮现，这时候合理使用数据库锁机制就显得异...
    初来的雨天阅读 3,550评论 0赞 22
  • 声声慢·读橘颂有感
    当年明月，长发可梳，年年豆蔻开时。 四月流岚怜眷，辗转青丝。 相逢从来天命，孰料今、南北东西。 怎肯老、待江枯沙铄...
    婉兮清漾阅读 602评论 2赞 10