现象：

并发插入的时候，发现数据库主键断层并且丢失数据

查看死锁日志：

show engine innodb status

解决

看日志很清楚问题出在哪
RECORD LOCKS space id 1373 page no 33 n bits 592 index idx_materialPersonalId of table sgcc_material.material_personal_transcode trx id 7351540 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

死锁的索引为material_personal_transcode表的idx_materialPersonalId。加锁范围是hex（行号）73757072656d756d 到 asc supremum（正无穷）

所以删除索引即可

原因：

该表维护了个人素材id的索引——materialPersonalId，并且mysql为rr级别，那么会存在间隙锁；并发的过程中，insert首先会锁定间隙锁，然后插入之前加上插入意向锁；t1和t2的间隙锁是几乎重合的，此时t1的插入意向锁被t2的间隙锁阻塞了，t2的插入意向锁也被t1阻塞了，t2启动死锁检测，自动回滚数据，导致数据丢失。

日志

  2. =====================================
  3. 2021-03-31 04:02:05 0x7f00a1b9a700 INNODB MONITOR OUTPUT
  4. =====================================
  5. Per second averages calculated from the last 2 seconds
  6. -----------------
  7. BACKGROUND THREAD
  8. -----------------
  9. srv_master_thread loops: 4180 srv_active, 0 srv_shutdown, 502342 srv_idle
  10. srv_master_thread log flush and writes: 506522
  11. ----------
  12. SEMAPHORES
  13. ----------
  14. OS WAIT ARRAY INFO: reservation count 6644
  15. OS WAIT ARRAY INFO: signal count 9731
  16. RW-shared spins 0, rounds 12025, OS waits 4148
  17. RW-excl spins 0, rounds 58722, OS waits 257
  18. RW-sx spins 1044, rounds 21215, OS waits 283
  19. Spin rounds per wait: 12025.00 RW-shared, 58722.00 RW-excl, 20.32 RW-sx
  20. ------------------------
  21. LATEST DETECTED DEADLOCK
  22. ------------------------
  23. 2021-03-31 03:50:51 0x7f00a1738700
  24. *** (1) TRANSACTION:
  25. TRANSACTION 7351540, ACTIVE 0 sec inserting
  26. mysql tables in use 1, locked 1
  27. LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
  28. MySQL thread id 8087, OS thread handle 139640693270272, query id 2319676 nacos.xycmsgcc.com 10.0.0.61 sgcc update
  29. insert into `material_personal_transcode`(`material_personal_id`, `transcode_task_id`, `status`, `url`, `full_url`, `material_size`, `quality`, `used`, `deleted`, `source_type`, `subscribe_id`, `tenant_id`, `create_time`, `modify_time`)
  30.         values
  32.             (45873, '5947b3ec3ee7436b8eb149deca455d4a', 2, 'material/transcode/video/2021/03/31/hjkkXK4H_1617177051505.mp4', '', 0, 3, 0, 0, 1, 0, 1, '2021-03-31 15:50:51.633', '2021-03-31 15:50:51.633')
  33. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  34. RECORD LOCKS space id 1373 page no 33 n bits 592 index idx_materialPersonalId of table `sgcc_material`.`material_personal_transcode` trx id 7351540 lock_mode X insert intention waiting
  35. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  36.  0: len 8; hex 73757072656d756d; asc supremum;;
  37. *** (2) TRANSACTION:
  38. TRANSACTION 7351538, ACTIVE 0 sec inserting
  39. mysql tables in use 1, locked 1
  40. 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
  41. MySQL thread id 8082, OS thread handle 139640685430528, query id 2319682 nacos.xycmsgcc.com 10.0.0.61 sgcc update
  42. insert into `material_personal_transcode`(`material_personal_id`, `transcode_task_id`, `status`, `url`, `full_url`, `material_size`, `quality`, `used`, `deleted`, `source_type`, `subscribe_id`, `tenant_id`, `create_time`, `modify_time`)
  43.         values
  45.             (45872, '5879910ba32f460485211d7e8ff7e119', 2, 'material/transcode/video/2021/03/31/Gllys1Xy_1617177051435.mp4', '', 0, 3, 0, 0, 1, 0, 1, '2021-03-31 15:50:51.709', '2021-03-31 15:50:51.709')
  46. *** (2) HOLDS THE LOCK(S):
  47. RECORD LOCKS space id 1373 page no 33 n bits 592 index idx_materialPersonalId of table `sgcc_material`.`material_personal_transcode` trx id 7351538 lock_mode X
  48. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  49.  0: len 8; hex 73757072656d756d; asc supremum;;
  50. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  51. RECORD LOCKS space id 1373 page no 33 n bits 592 index idx_materialPersonalId of table `sgcc_material`.`material_personal_transcode` trx id 7351538 lock_mode X insert intention waiting
  52. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  53.  0: len 8; hex 73757072656d756d; asc supremum;;
  54. *** WE ROLL BACK TRANSACTION (2)
  55. ------------
  56. TRANSACTIONS
  57. ------------
  58. Trx id counter 7351561
  59. Purge done for trx's n:o < 7351560 undo n:o < 0 state: running but idle
  60. History list length 6
  61. LIST OF TRANSACTIONS FOR EACH SESSION:
  62. ---TRANSACTION 421116431257696, not started
  63. 0 lock struct(s), heap size 1136, 0 row lock(s)
  64. ---TRANSACTION 421116431255872, not started
  65. 0 lock struct(s), heap size 1136, 0 row lock(s)
  66. ---TRANSACTION 421116431272288, not started
  67. 0 lock struct(s), heap size 1136, 0 row lock(s)
  68. ---TRANSACTION 421116431270464, not started
  69. 0 lock struct(s), heap size 1136, 0 row lock(s)
  70. ---TRANSACTION 421116431268640, not started
  71. 0 lock struct(s), heap size 1136, 0 row lock(s)
  72. ---TRANSACTION 421116431242192, not started
  73. 0 lock struct(s), heap size 1136, 0 row lock(s)
  74. ---TRANSACTION 421116431267728, not started
  75. 0 lock struct(s), heap size 1136, 0 row lock(s)
  76. ---TRANSACTION 421116431262256, not started
  77. 0 lock struct(s), heap size 1136, 0 row lock(s)
  78. ---TRANSACTION 421116431260432, not started
  79. 0 lock struct(s), heap size 1136, 0 row lock(s)
  80. ---TRANSACTION 421116431258608, not started
  81. 0 lock struct(s), heap size 1136, 0 row lock(s)
  82. ---TRANSACTION 421116431250400, not started
  83. 0 lock struct(s), heap size 1136, 0 row lock(s)
  84. ---TRANSACTION 421116431247664, not started
  85. 0 lock struct(s), heap size 1136, 0 row lock(s)
  86. ---TRANSACTION 421116431246752, not started
  87. 0 lock struct(s), heap size 1136, 0 row lock(s)
  88. ---TRANSACTION 421116431241280, not started
  89. 0 lock struct(s), heap size 1136, 0 row lock(s)
  90. ---TRANSACTION 421116431243104, not started
  91. 0 lock struct(s), heap size 1136, 0 row lock(s)
  92. ---TRANSACTION 421116431239456, not started
  93. 0 lock struct(s), heap size 1136, 0 row lock(s)
  94. ---TRANSACTION 421116431271376, not started
  95. 0 lock struct(s), heap size 1136, 0 row lock(s)
  96. ---TRANSACTION 421116431237632, not started
  97. 0 lock struct(s), heap size 1136, 0 row lock(s)
  98. ---TRANSACTION 421116431235808, not started
  99. 0 lock struct(s), heap size 1136, 0 row lock(s)
  100. ---TRANSACTION 421116431256784, not started
  101. 0 lock struct(s), heap size 1136, 0 row lock(s)
  102. ---TRANSACTION 421116431266816, not started
  103. 0 lock struct(s), heap size 1136, 0 row lock(s)
  104. ---TRANSACTION 421116431269552, not started
  105. 0 lock struct(s), heap size 1136, 0 row lock(s)
  106. ---TRANSACTION 421116431263168, not started
  107. 0 lock struct(s), heap size 1136, 0 row lock(s)
  108. ---TRANSACTION 421116431264992, not started
  109. 0 lock struct(s), heap size 1136, 0 row lock(s)
  110. ---TRANSACTION 421116431253136, not started
  111. 0 lock struct(s), heap size 1136, 0 row lock(s)
  112. ---TRANSACTION 421116431236720, not started
  113. 0 lock struct(s), heap size 1136, 0 row lock(s)
  114. ---TRANSACTION 421116431240368, not started
  115. 0 lock struct(s), heap size 1136, 0 row lock(s)
  116. ---TRANSACTION 421116431265904, not started
  117. 0 lock struct(s), heap size 1136, 0 row lock(s)
  118. ---TRANSACTION 421116431264080, not started
  119. 0 lock struct(s), heap size 1136, 0 row lock(s)
  120. ---TRANSACTION 421116431259520, not started
  121. 0 lock struct(s), heap size 1136, 0 row lock(s)
  122. ---TRANSACTION 421116431261344, not started
  123. 0 lock struct(s), heap size 1136, 0 row lock(s)
  124. ---TRANSACTION 421116431254048, not started
  125. 0 lock struct(s), heap size 1136, 0 row lock(s)
  126. ---TRANSACTION 421116431251312, not started
  127. 0 lock struct(s), heap size 1136, 0 row lock(s)
  128. ---TRANSACTION 421116431248576, not started
  129. 0 lock struct(s), heap size 1136, 0 row lock(s)
  130. ---TRANSACTION 421116431245840, not started
  131. 0 lock struct(s), heap size 1136, 0 row lock(s)
  132. ---TRANSACTION 421116431254960, not started
  133. 0 lock struct(s), heap size 1136, 0 row lock(s)
  134. ---TRANSACTION 421116431244016, not started
  135. 0 lock struct(s), heap size 1136, 0 row lock(s)
  136. ---TRANSACTION 421116431238544, not started
  137. 0 lock struct(s), heap size 1136, 0 row lock(s)
  138. ---TRANSACTION 421116431244928, not started
  139. 0 lock struct(s), heap size 1136, 0 row lock(s)
  140. ---TRANSACTION 421116431252224, not started
  141. 0 lock struct(s), heap size 1136, 0 row lock(s)
  142. ---TRANSACTION 421116431249488, not started
  143. 0 lock struct(s), heap size 1136, 0 row lock(s)
  144. ---TRANSACTION 421116431234896, not started
  145. 0 lock struct(s), heap size 1136, 0 row lock(s)
  146. --------
  147. FILE I/O
  148. --------
  149. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  150. I/O thread 1 state: waiting for completed aio requests (log thread)
  151. I/O thread 2 state: waiting for completed aio requests (read thread)
  152. I/O thread 3 state: waiting for completed aio requests (read thread)
  153. I/O thread 4 state: waiting for completed aio requests (read thread)
  154. I/O thread 5 state: waiting for completed aio requests (read thread)
  155. I/O thread 6 state: waiting for completed aio requests (write thread)
  156. I/O thread 7 state: waiting for completed aio requests (write thread)
  157. I/O thread 8 state: waiting for completed aio requests (write thread)
  158. I/O thread 9 state: waiting for completed aio requests (write thread)
  159. Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  160.  ibuf aio reads:, log i/o's:, sync i/o's:
  161. Pending flushes (fsync) log: 0; buffer pool: 0
  162. 5720 OS file reads, 71589 OS file writes, 33717 OS fsyncs
  163. 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
  164. -------------------------------------
  165. INSERT BUFFER AND ADAPTIVE HASH INDEX
  166. -------------------------------------
  167. Ibuf: size 1, free list len 0, seg size 2, 31 merges
  168. merged operations:
  169.  insert 33, delete mark 1, delete 0
  170. discarded operations:
  171.  insert 0, delete mark 0, delete 0
  172. Hash table size 34679, node heap has 32 buffer(s)
  173. Hash table size 34679, node heap has 4 buffer(s)
  174. Hash table size 34679, node heap has 16 buffer(s)
  175. Hash table size 34679, node heap has 19 buffer(s)
  176. Hash table size 34679, node heap has 11 buffer(s)
  177. Hash table size 34679, node heap has 23 buffer(s)
  178. Hash table size 34679, node heap has 72 buffer(s)
  179. Hash table size 34679, node heap has 40 buffer(s)
  180. 0.00 hash searches/s, 0.00 non-hash searches/s
  181. ---
  182. LOG
  183. ---
  184. Log sequence number 5125706096
  185. Log flushed up to   5125706096
  186. Pages flushed up to 5125706096
  187. Last checkpoint at  5125706087
  188. 0 pending log flushes, 0 pending chkp writes
  189. 24010 log i/o's done, 0.00 log i/o's/second
  190. ----------------------
  191. BUFFER POOL AND MEMORY
  192. ----------------------
  193. Total large memory allocated 137428992
  194. Dictionary memory allocated 3632821
  195. Buffer pool size   8192
  196. Free buffers       1496
  197. Database pages     6479
  198. Old database pages 2379
  199. Modified db pages  3
  200. Pending reads      0
  201. Pending writes: LRU 0, flush list 0, single page 0
  202. Pages made young 22, not young 0
  203. 0.00 youngs/s, 0.00 non-youngs/s
  204. Pages read 5175, created 1304, written 44850
  205. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  206. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
  207. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  208. LRU len: 6479, unzip_LRU len: 0
  209. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  210. --------------
  211. ROW OPERATIONS
  212. --------------
  213. 0 queries inside InnoDB, 0 queries in queue
  214. 0 read views open inside InnoDB
  215. Process ID=14923, Main thread ID=139641154496256, state: sleeping
  216. Number of rows inserted 1011045, updated 23161, deleted 100224, read 5308941008
  217. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22133.93 reads/s
  218. ----------------------------
  219. END OF INNODB MONITOR OUTPUT
  220. ============================

[1]. Mysql死锁如何排查：insert on duplicate死锁一次排查分析过程. [OL]https://www.cnblogs.com/jay-huaxiao/p/11456921.html