mysql 如何回滚删除的表 mysql回滚数据

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

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

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

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

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

@Servicepublic class TestAnother{@Autowiredprivate 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_idlesrv_master_thread log flush and writes: 524528----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 24855OS WAIT ARRAY INFO: signal count 25085Mutex spin waits 14574, rounds 408115, OS waits 13345RW-shared spins 10346, rounds 338033, OS waits 11257RW-excl spins 216, rounds 7866, OS waits 240Spin rounds per wait: 28.00 mutex, 32.67 RW-shared, 36.42 RW-excl------------TRANSACTIONS------------Trx id counter 690061Purge done for trx's n:o < 690050 undo n:o < 0 state: running but idleHistory list length 1343LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 18225, OS thread handle 0x7fe01c931700, query id 686481 172.17.0.1 root initshow engine innodb status---TRANSACTION 690050, not startedMySQL thread id 18223, OS thread handle 0x7fdf6331b700, query id 686305 172.17.0.1 root---TRANSACTION 690060, not startedMySQL thread id 18203, OS thread handle 0x7fe01cabd700, query id 686456 172.17.0.1 root---TRANSACTION 690058, ACTIVE 32 sec insertingmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2MySQL thread id 18202, OS thread handle 0x7fe01c1b7700, query id 686341 172.17.0.1 root updateINSERT 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 waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;---------------------TRANSACTION 690056, ACTIVE 36 sec67 lock struct(s), heap size 13864, 8195 row lock(s), undo log entries 23MySQL 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: 0Pending flushes (fsync) log: 0; buffer pool: 04606 OS file reads, 96239 OS file writes, 65171 OS fsyncs0.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 mergesmerged operations:insert 29, delete mark 421, delete 364discarded operations:insert 0, delete mark 0, delete 0Hash table size 276671, node heap has 26 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 668395471Log flushed up to   668395471Pages flushed up to 668395471Last checkpoint at  6683954710 pending log writes, 0 pending chkp writes33363 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 959373Buffer pool size   8191Free buffers       1028Database pages     7137Old database pages 2614Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 3270, not young 253620.00 youngs/s, 0.00 non-youngs/sPages read 3915, created 13555, written 485270.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 7137, unzip_LRU len: 0I/O sum[16]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 1, id 140600574822144, state: sleepingNumber of rows inserted 385622, updated 20256, deleted 79, read 137880810.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 使其产生范围锁。这样就不会产生排他锁而导致线程之间死锁。