In Spring framework, we often use @Transactional annotation to do transactions, but transactions do not guarantee validity;
The following are the problems I have encountered, which are not necessarily correct, and can be used as a reference.
Mark @Transactional on a class so that all methods under that class run as default transactions.
@Transactional
public class test(){
// Insert data into table A
public void A(){
}
// Insert data into table B
public void B(){
}
}Call this method separately in one method: try catch exceptions are made to this method separately to prevent all data from being rolled back because of exceptions
@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();
}
}
}Normally, this method is not a problem, but on-line, due to the large number of requests, which is often referred to as the high concurrency environment:
In method B, suppose we have an SQL: delete from users where status = test;
Adding an index to status in the users table.
The question arises:
In general, because it's serial logic, it doesn't matter.
But in the case of high concurrency, because we need delete statements, we need row-level locks, because status is a non-aggregated index, so we need to make use of next-key locks to lock up the scope of data. (RR implemented by InnoDB avoids hallucination through next-key lock mechanism. I'm not particularly sure about this part either), but in concurrent environments, because the lock of the previous method is not released, the next method comes in again.
For example, when the first thread comes in, it needs to delete the data of 0-10. At this time, the lock is added to the fifth, while the second thread comes in, such as adding other locks randomly. At this time, it also needs to take the lock of 5, but it does not get it. It needs to wait for thread 1 to release the lock, while the first thread may just need it. The random lock of the second thread causes two threads to wait for each other to get the lock, which leads to deadlock.
In other words, what happens if @Transactional encounters a deadlock?
I simulated deadlock conditions locally, and the local SQL executed a start Transactional, but never submitted.
A request was made online with POSTMAN. Although the execution of method A was completed in the online request, the lock was stuck in method B, which resulted in the acquisition of the lock timeout. The following is the latest deadlock information viewed through the database:
=====================================
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
============================
By looking up the database, we found that the transactions performed by method A were also rolled back.
The reason is: because the current thread is locked by the database deadlock in the acquisition lock, the current request can not be completely terminated, resulting in the transaction of method A can not be submitted. Although the last exception thrown is method B, the transaction of method A can not be submitted correctly because the whole method fails to end correctly, and MYSQL transaction can not be submitted correctly. The default timeout time is 50s.
You can use this command to show variables like'innodb_lock_wait_timeout';
That is to say, if 50s fails to commit the transaction, the current transaction will be rolled back automatically.
This also leads to why method A did not report an exception.
After all, the reason that method A did not have any exceptions but rolled back was because the service was out of time.
Solution:
1. The default of database transaction is automatic submission. We can set it to manual submission manually.
2. Method splitting, so that it is not in a thread, so method A will not roll back because method B timeout.
3.update or insert or delete statements use the primary key index to avoid the next-key lock causing it to generate range locks. This will not result in exclusive locks and deadlocks between threads.
Because the understanding of MYSQL is not so deep, you are welcome to point out the mistakes.