解決一次mysql死鎖問(wèn)題

背景

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

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

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

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

事務(wù)執(zhí)行的sql（每個(gè)事務(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í)候沒(méi)有數(shù)據(jù)被更新

實(shí)際運(yùn)行結(jié)果

報(bào)了非常多一樣的錯(cuò)

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ù)常識(shí)考慮，我每個(gè)線程（事務(wù)）更新的數(shù)據(jù)都不沖突，為什么會(huì)產(chǎn)生死鎖？
帶著這個(gè)問(wè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在等待一個(gè)鎖

*** (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也在等待一個(gè)鎖

*** (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等字眼，看不懂說(shuō)明了什么？說(shuō)明我關(guān)于mysql的鎖相關(guān)的知識(shí)儲(chǔ)備還不夠。那就開始調(diào)查mysql的鎖相關(guān)知識(shí)。
通過(guò)搜索引擎，

獲取到如下知識(shí)：

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

InnoDB還將鎖細(xì)分為如下幾種子類型：

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

鎖的持有兼容程度如下表

請(qǐng)求鎖\持有鎖 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èn)題就來(lái)了，事務(wù)1的intention lock 為什么會(huì)和事務(wù)2的gap lock 有交集，或者說(shuō)，事務(wù)1要插入的數(shù)據(jù)的位置為什么會(huì)被事務(wù)2給鎖??？
讓我回顧一下gap lock的定義：
間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發(fā)現(xiàn)自己相關(guān)的知識(shí)儲(chǔ)備還不夠。那就開始調(diào)查。
調(diào)查后發(fā)現(xiàn)，當(dāng)當(dāng)前索引是一個(gè)普通索引的時(shí)候，會(huì)加一個(gè)gap lock來(lái)防止幻讀，此gap lock 會(huì)鎖住一個(gè)左開右閉的區(qū)間。假設(shè)索引為xx_idx（xx_id），數(shù)據(jù)分布為1,4,6,8,12，當(dāng)更新xx_id=9的時(shí)候，這個(gè)時(shí)候gap lock的鎖定記錄區(qū)間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數(shù)據(jù)，當(dāng)有其他事務(wù)要插入xxid in (9,10,11,12)的數(shù)據(jù)時(shí)，就會(huì)處于等待獲取鎖的狀態(tài)。
ps：當(dāng)前索引不是普通索引，而且是唯一索引等其他情況，請(qǐng)參考下面資料
MySQL 加鎖處理分析

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

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

因?yàn)槠胀ㄋ饕?br> KEYhotel_date_idx(hotel_id,rate_date)
的關(guān)系 這段sql會(huì)獲取一個(gè)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會(huì)獲取一個(gè)insert intention lock （waiting）

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

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

因?yàn)槠胀ㄋ饕?br> KEYhotel_date_idx(hotel_id,rate_date)
的關(guān)系 這段sql也會(huì)獲取一個(gè)gap lock，范圍也是(2,11111]（根據(jù)前面的知識(shí)，gap lock之間會(huì)互相兼容，可以一起持有鎖的）

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也會(huì)獲取一個(gè)insert intention lock （waiting）

看到這里，基本也就破案了。因?yàn)槠胀ㄋ饕年P(guān)系，事務(wù)1和事務(wù)2的gap lock的覆蓋范圍太廣，導(dǎo)致其他事務(wù)無(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é)果來(lái)看，一堆事務(wù)被回滾，只有10007數(shù)據(jù)被更新成功

結(jié)論

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

處理

在mysql默認(rèn)的事務(wù)隔離級(jí)別（repeatable read）下，無(wú)法避免這種情況。只能把并發(fā)處理改成同步處理?；蛘邚臉I(yè)務(wù)層面做處理。

知識(shí)庫(kù)levelup

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

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

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

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

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