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

快来打我* 2024-04-18 13:44 64阅读 0赞

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

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

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

@Transactional
    public class test(){
        // 往A表中插入数据
        public void A(){
    
        }
    
        // 往B表中插入数据
        public void B(){
    
        }
    }

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

@Service
    public class TestAnother{
    
        @Autowired
        private Test test;
        
        public void C(){
            try{
                test.A();
            }catch(Exception e){
                e.printStackTrace();
            }
            try{
                test.B();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    
    }

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

在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方法迟迟拿不到锁，最后导致了获取锁超时。下面是通过数据库查看的最近一次死锁的信息：

=====================================
    2019-09-07 06:28:38 7fe01c931700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 24 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 8912 srv_active, 0 srv_shutdown, 516445 srv_idle
    srv_master_thread log flush and writes: 524528
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 24855
    OS WAIT ARRAY INFO: signal count 25085
    Mutex spin waits 14574, rounds 408115, OS waits 13345
    RW-shared spins 10346, rounds 338033, OS waits 11257
    RW-excl spins 216, rounds 7866, OS waits 240
    Spin rounds per wait: 28.00 mutex, 32.67 RW-shared, 36.42 RW-excl
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 690061
    Purge done for trx's n:o < 690050 undo n:o < 0 state: running but idle
    History list length 1343
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 18225, OS thread handle 0x7fe01c931700, query id 686481 172.17.0.1 root init
    show engine innodb status
    ---TRANSACTION 690050, not started
    MySQL thread id 18223, OS thread handle 0x7fdf6331b700, query id 686305 172.17.0.1 root
    ---TRANSACTION 690060, not started
    MySQL thread id 18203, OS thread handle 0x7fe01cabd700, query id 686456 172.17.0.1 root
    ---TRANSACTION 690058, ACTIVE 32 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
    MySQL thread id 18202, OS thread handle 0x7fe01c1b7700, query id 686341 172.17.0.1 root update
    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)
    ------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:
    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
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    ------------------
    ---TRANSACTION 690056, ACTIVE 36 sec
    67 lock struct(s), heap size 13864, 8195 row lock(s), undo log entries 23
    MySQL thread id 18224, OS thread handle 0x7fdf63bdf700, query id 686331 172.17.0.1 root
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    4606 OS file reads, 96239 OS file writes, 65171 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 22 merges
    merged operations:
     insert 29, delete mark 421, delete 364
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 276671, node heap has 26 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 668395471
    Log flushed up to   668395471
    Pages flushed up to 668395471
    Last checkpoint at  668395471
    0 pending log writes, 0 pending chkp writes
    33363 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 137363456; in additional pool allocated 0
    Dictionary memory allocated 959373
    Buffer pool size   8191
    Free buffers       1028
    Database pages     7137
    Old database pages 2614
    Modified db pages  0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 3270, not young 25362
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 3915, created 13555, written 48527
    0.00 reads/s, 0.00 creates/s, 0.00 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: 7137, unzip_LRU len: 0
    I/O sum[16]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 1, id 140600574822144, state: sleeping
    Number of rows inserted 385622, updated 20256, deleted 79, read 13788081
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.50 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

而此时查数据库发现，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的了解并没有那么深入，错误欢迎指出。