解決一次mysql死鎖問題

背景

多線程開啟事務(wù)處理。每個事務(wù)有多個update操作和一個insert操作（都在同一張表）。

DDL（刪除了一些不必要的細節(jié)）

默認隔離級別：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;
數(shù)據(jù)庫現(xiàn)有數(shù)據(jù)

只有hotel_id=2和hotel_id=11111的數(shù)據(jù)

事務(wù)執(zhí)行的sql（每個事務(wù)的區(qū)別是hotel_id不同，從10001到10010）

邏輯刪除原有數(shù)據(jù)

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' )

插入新的數(shù)據(jù)

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)

根據(jù)現(xiàn)有數(shù)據(jù)情況，update的時候沒有數(shù)據(jù)被更新

實際運行結(jié)果

報了非常多一樣的錯

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

發(fā)現(xiàn)居然有死鎖。
根據(jù)常識考慮，我每個線程（事務(wù)）更新的數(shù)據(jù)都不沖突，為什么會產(chǎn)生死鎖？
帶著這個問題，打印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
============================

發(fā)現(xiàn)事務(wù)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

事務(wù)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

關(guān)于鎖的描述，出現(xiàn)了lock_mode，gap before rec，insert intention等字眼，看不懂說明了什么？說明我關(guān)于mysql的鎖相關(guān)的知識儲備還不夠。那就開始調(diào)查mysql的鎖相關(guān)知識。
通過搜索引擎，

獲取到如下知識：

InnoDB是一個支持行鎖的存儲引擎，鎖的類型有：共享鎖（S）、排他鎖（X）、意向共享（IS）、意向排他（IX）

InnoDB還將鎖細分為如下幾種子類型：

  • record lock（RK）
    鎖直接加在索引記錄上面，鎖住的是key
  • gap lock（GK）
    間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
  • next key lock（NK）
    RK+GK
  • insert intention lock（IK）
    如果插入前，該間隙已經(jīng)由gap鎖，那么Insert會申請插入意向鎖。因為了避免幻讀，當(dāng)其他事務(wù)持有該間隙的間隔鎖，插入意向鎖就會被阻塞。

鎖的持有兼容程度如下表

請求鎖\持有鎖 gap lock insert intention lock record lock next key lock
gap lock 兼容 兼容 兼容 兼容
insert intention lock 沖突 兼容 兼容 沖突
record lock 兼容 兼容 沖突 沖突
next key lock 兼容 兼容 沖突 沖突

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

事務(wù)1正在獲取插入意向鎖
事務(wù)2正在獲取插入意向鎖，持有排他gap鎖

再看我們上面的鎖兼容表格，可以知道，gap lock和insert intention lock是不兼容的
那么就可以推斷出：事務(wù)1持有g(shù)ap lock，等待事務(wù)2的insert intention lock釋放；事務(wù)2持有g(shù)ap lock，等待事務(wù)1的insert intention lock釋放，從而導(dǎo)致死鎖。
那么新的問題就來了，事務(wù)1的intention lock 為什么會和事務(wù)2的gap lock 有交集，或者說，事務(wù)1要插入的數(shù)據(jù)的位置為什么會被事務(wù)2給鎖住？
讓我回顧一下gap lock的定義：
間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發(fā)現(xiàn)自己相關(guān)的知識儲備還不夠。那就開始調(diào)查。
調(diào)查后發(fā)現(xiàn)，當(dāng)當(dāng)前索引是一個普通索引的時候，會加一個gap lock來防止幻讀，此gap lock 會鎖住一個左開右閉的區(qū)間。假設(shè)索引為xx_idx（xx_id），數(shù)據(jù)分布為1,4,6,8,12，當(dāng)更新xx_id=9的時候，這個時候gap lock的鎖定記錄區(qū)間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數(shù)據(jù)，當(dāng)有其他事務(wù)要插入xxid in (9,10,11,12)的數(shù)據(jù)時，就會處于等待獲取鎖的狀態(tài)。
ps：當(dāng)前索引不是普通索引，而且是唯一索引等其他情況，請參考下面資料
MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務(wù)1的執(zhí)行過程：

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)
的關(guān)系 這段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）

再看事務(wù)2的執(zhí)行過程

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)
的關(guān)系 這段sql也會獲取一個gap lock，范圍也是(2,11111]（根據(jù)前面的知識，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）

看到這里，基本也就破案了。因為普通索引的關(guān)系，事務(wù)1和事務(wù)2的gap lock的覆蓋范圍太廣，導(dǎo)致其他事務(wù)無法插入數(shù)據(jù)。
重新梳理一下：

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

所以從結(jié)果來看，一堆事務(wù)被回滾，只有10007數(shù)據(jù)被更新成功

結(jié)論

gap lock 導(dǎo)致了并發(fā)處理的死鎖

處理

在mysql默認的事務(wù)隔離級別（repeatable read）下，無法避免這種情況。只能把并發(fā)處理改成同步處理。或者從業(yè)務(wù)層面做處理。

知識庫levelup

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • Mysql概述 數(shù)據(jù)庫是一個易于訪問和修改的信息集合。它允許使用事務(wù)來確保數(shù)據(jù)的安全性和一致性，并能快速處理百萬條...
    彥幀閱讀 13,906評論 10贊 460
  • 1. mysql鎖知多少 我們進行insert，update，delete，select會加鎖嗎，如果加鎖，加鎖步...
    liwsh閱讀 5,204評論 0贊 4
  • 前言 作為后端開發(fā)人員，幾乎每天都與數(shù)據(jù)庫打交道。對著變化莫測的需求，緊張的工期，很多程序員日復(fù)一日寫著CRUD代...
    蕉仔偉閱讀 1,309評論 0贊 2
  • 當(dāng)一個系統(tǒng)訪問量上來的時候，不只是數(shù)據(jù)庫性能瓶頸問題了，數(shù)據(jù)庫數(shù)據(jù)安全也會浮現(xiàn)，這時候合理使用數(shù)據(jù)庫鎖機制就顯得異...
    初來的雨天閱讀 3,687評論 0贊 22
  • 當(dāng)年明月，長發(fā)可梳，年年豆蔻開時。 四月流嵐憐眷，輾轉(zhuǎn)青絲。 相逢從來天命，孰料今、南北東西。 怎肯老、待江枯沙鑠...
    婉兮清漾閱讀 679評論 2贊 10

友情鏈接更多精彩內(nèi)容