Mysql 死锁引发的@Transactional 数据回滚

Spring框架中我们经常使用 @Transactional 注解来做事务，但是事务并不能保证有效性；

以下是我遇到的问题，不一定完全正确，可以做个参考：

在一个类上标记了 @Transactional，使得该类下的所有方法都以默认的事务方式运行。

1. @Transactional
2. public class test(){
3. // 往A表中插入数据
4. public void A(){
5. }
6. // 往B表中插入数据
7. public void B(){
8. }
9. }

在一个方法中分别调用这个方法：分别对这个方法进行try catch异常，防止因为异常回滚所有数据

1. @Service
2. public class TestAnother{
3. @Autowired
4. private Test test;
5. public void C(){
6. try{
7. test.A();
8. }catch(Exception e){
9. e.printStackTrace();
10. }
11. try{
12. test.B();
13. }catch(Exception e){
14. e.printStackTrace();
15. }
16. }
17. }

在正常情况下，这个方法是没有问题的，但是在线上的时候，由于请求量较大，也就是我们常说的高并发环境：

在B方法中，假如我们有一句SQL：delete from users where status = 'test’;

在users表中给status加了一个索引。

问题来了：

在一般情况下，由于是串行逻辑，所以不会有影响。

但是在高并发情况下，由于我们需要delete语句，需要行级锁，因为status是一个非聚集索引，所以需要给范围性的数据上行级锁，也就是利用了 next-key lock。(InnoDB实现的RR通过next-key lock机制避免了幻读现象。这部分我也不是特别确定)，而在并发环境下，由于上一个方法的锁未释放，下一个方法又进来了。

比如： 第一个线程进来的时候需要删除0-10的数据，这时候加锁加到了第5个，而第二个线程这个时候也进来了，比如随机加了其他的锁，这时候也需要拿5的锁，但是没有拿到，需要等待线程1释放锁，而第一个线程可能刚好需要第二个线程的随机锁，导致两个线程互相等待拿锁，从而导致死锁。

 

话说回来，如果 @Transactional 遇到死锁会怎么样呢？

我在本地模拟了死锁的条件，本地SQL执行了一个start Transactional，但是一直不提交。

用POSTMAN在线上发了一个请求，线上的请求中虽然A方法执行完成了，但是卡在了B方法迟迟拿不到锁，最后导致了获取锁超时。下面是通过数据库查看的最近一次死锁的信息：

1. =====================================
2. 2019-09-07 06:28:38 7fe01c931700 INNODB MONITOR OUTPUT
3. =====================================
4. Per second averages calculated from the last 24 seconds
5. -----------------
6. BACKGROUND THREAD
7. -----------------
8. srv_master_thread loops: 8912 srv_active, 0 srv_shutdown, 516445 srv_idle
9. srv_master_thread log flush and writes: 524528
10. ----------
11. SEMAPHORES
12. ----------
13. OS WAIT ARRAY INFO: reservation count 24855
14. OS WAIT ARRAY INFO: signal count 25085
15. Mutex spin waits 14574, rounds 408115, OS waits 13345
16. RW-shared spins 10346, rounds 338033, OS waits 11257
17. RW-excl spins 216, rounds 7866, OS waits 240
18. Spin rounds per wait: 28.00 mutex, 32.67 RW-shared, 36.42 RW-excl
19. ------------
20. TRANSACTIONS
21. ------------
22. Trx id counter 690061
23. Purge done for trx's n:o < 690050 undo n:o < 0 state: running but idle
24. History list length 1343
25. LIST OF TRANSACTIONS FOR EACH SESSION:
26. ---TRANSACTION 0, not started
27. MySQL thread id 18225, OS thread handle 0x7fe01c931700, query id 686481 172.17.0.1 root init
28. show engine innodb status
29. ---TRANSACTION 690050, not started
30. MySQL thread id 18223, OS thread handle 0x7fdf6331b700, query id 686305 172.17.0.1 root
31. ---TRANSACTION 690060, not started
32. MySQL thread id 18203, OS thread handle 0x7fe01cabd700, query id 686456 172.17.0.1 root
33. ---TRANSACTION 690058, ACTIVE 32 sec inserting
34. mysql tables in use 1, locked 1
35. LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
36. MySQL thread id 18202, OS thread handle 0x7fe01c1b7700, query id 686341 172.17.0.1 root update
37. INSERT INTO spot_account_flows (flowType, refType, refId, fromUserId, fromAccountId, toUserId, toAccountId, currency, amount, description, createdAt) VALUES ('TRADE_CLEAR', 'CLEARING', 0, 100000, 102950, 100000, 108015, 'BTC', 1, '', 1567837686558)
38. ------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:
39. RECORD LOCKS space id 643 page no 97 n bits 144 index `PRIMARY` of table `ex`.`spot_account_flows` trx id 690058 lock_mode X insert intention waiting
40. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
41. 0: len 8; hex 73757072656d756d; asc supremum;;
42. ------------------
43. ---TRANSACTION 690056, ACTIVE 36 sec
44. 67 lock struct(s), heap size 13864, 8195 row lock(s), undo log entries 23
45. MySQL thread id 18224, OS thread handle 0x7fdf63bdf700, query id 686331 172.17.0.1 root
46. --------
47. FILE I/O
48. --------
49. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
50. I/O thread 1 state: waiting for completed aio requests (log thread)
51. I/O thread 2 state: waiting for completed aio requests (read thread)
52. I/O thread 3 state: waiting for completed aio requests (read thread)
53. I/O thread 4 state: waiting for completed aio requests (read thread)
54. I/O thread 5 state: waiting for completed aio requests (read thread)
55. I/O thread 6 state: waiting for completed aio requests (write thread)
56. I/O thread 7 state: waiting for completed aio requests (write thread)
57. I/O thread 8 state: waiting for completed aio requests (write thread)
58. I/O thread 9 state: waiting for completed aio requests (write thread)
59. Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
60. ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
61. Pending flushes (fsync) log: 0; buffer pool: 0
62. 4606 OS file reads, 96239 OS file writes, 65171 OS fsyncs
63. 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
64. -------------------------------------
65. INSERT BUFFER AND ADAPTIVE HASH INDEX
66. -------------------------------------
67. Ibuf: size 1, free list len 0, seg size 2, 22 merges
68. merged operations:
69. insert 29, delete mark 421, delete 364
70. discarded operations:
71. insert 0, delete mark 0, delete 0
72. Hash table size 276671, node heap has 26 buffer(s)
73. 0.00 hash searches/s, 0.00 non-hash searches/s
74. ---
75. LOG
76. ---
77. Log sequence number 668395471
78. Log flushed up to 668395471
79. Pages flushed up to 668395471
80. Last checkpoint at 668395471
81. 0 pending log writes, 0 pending chkp writes
82. 33363 log i/o's done, 0.00 log i/o's/second
83. ----------------------
84. BUFFER POOL AND MEMORY
85. ----------------------
86. Total memory allocated 137363456; in additional pool allocated 0
87. Dictionary memory allocated 959373
88. Buffer pool size 8191
89. Free buffers 1028
90. Database pages 7137
91. Old database pages 2614
92. Modified db pages 0
93. Pending reads 0
94. Pending writes: LRU 0, flush list 0, single page 0
95. Pages made young 3270, not young 25362
96. 0.00 youngs/s, 0.00 non-youngs/s
97. Pages read 3915, created 13555, written 48527
98. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
99. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
100. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
101. LRU len: 7137, unzip_LRU len: 0
102. I/O sum[16]:cur[0], unzip sum[0]:cur[0]
103. --------------
104. ROW OPERATIONS
105. --------------
106. 0 queries inside InnoDB, 0 queries in queue
107. 0 read views open inside InnoDB
108. Main thread process no. 1, id 140600574822144, state: sleeping
109. Number of rows inserted 385622, updated 20256, deleted 79, read 13788081
110. 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.50 reads/s
111. ----------------------------
112. END OF INNODB MONITOR OUTPUT
113. ============================

而此时查数据库发现，A方法执行的事务也被回滚了。

原因就是：因为当前线程被数据库死锁卡在了获取锁的情况下，当前请求不能完全结束，导致 A 方法的事务不能提交，最后抛出的异常虽然是B方法的，但是A方法由于整个方法未能正确结束，所以事务未能正确提交，而MYSQL事务的默认超时时间是50s。

可以通过此命令 show variables like 'innodb_lock_wait_timeout';

也就是说如果50s未能commit事务，那么当前事务将被自动回滚。

这也就导致了为什么A方法并没有报异常。

说到底导致了A方法没有异常却回滚了是因为服务超时了。

解决方案：

1.数据库事务默认为自动提交，我们可以手动设置为手动提交。

2.方法拆分，使其不在一个线程内即可，这样A方法就不会因为B方法超时而回滚。

3.update或者insert或者delete语句使用主键索引，这样可以避免 next-key lock 使其产生范围锁。这样就不会产生排他锁而导致线程之间死锁。

 

因为对MYSQL的了解并没有那么深入，错误欢迎指出。