I would like to ask you a question about the operation of MySQL database under concurrency.
tools
mysql (engine innoDB, isolation level REPEATABLE-READ)
scenario (there are two operations in a transaction)
- Update the total score value in the user table;
- insert the change log of the credit record (key field: current change integral score, total score of the user after change)
Database table structure
user table
1 :
2 :
:
1:
2:
png]
Analysis (regardless of the correctness of the data)
method 1 thinks that one thread opens the transaction, and when the user information is updated (not yet committed), the row is locked, and if the transaction has not been committed (the method has not been completed), the other thread can only wait. However, if you update different users (user id is different), multiple users will not be blocked
method 2 actually reports a deadlock exception except for the first operation, which rolls back the transaction, so only one log is successfully inserted. Personally, I think that this situation is that after each thread enters the method, it first performs the log insertion operation (does not affect each other). After the thread sleep x seconds, multiple threads need to get the write lock to update the user at the same time, but only one thread can get it. The deadlock caused by other threads waiting for each other.
I don"t know whether my analysis is correct or not, and I don"t quite understand the cause of the deadlock in method 2. I still hope that some great gods can give me some advice. I can"t thank you enough
.
Code address: https://gitee.com/gegepy/basi.
Branch: dev-mysql
Test Class: ScoreChangeLogServiceTest
for method 1, the order of execution is
update user where id = 1 ------------------------- (1)
insert log (id,xxx) select x where user id = 1 ------(2)
suppose there are two things An and B, and their order of execution for operations 1 and 2 is as follows:
------------A------------------------------------------B------------------
----user.id=1X-----------------------------------------------------
------------------------------user.id=1XB----
---X------------------------------------------------
-------------------------------user.id=1X-------------
because everything blocks waiting at operation (1), 10 things are executed in series, and there is no deadlock.
for method 2, the deadlock analysis is as follows:
first analyze the deadlock log
-sharp
show engine innodb status;
=====================================
2018-10-31 10:36:02 7faef1990700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 59 srv_active, 0 srv_shutdown, 3933403 srv_idle
srv_master_thread log flush and writes: 3933462
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 123
OS WAIT ARRAY INFO: signal count 106
Mutex spin waits 110, rounds 1151, OS waits 25
RW-shared spins 94, rounds 2817, OS waits 93
RW-excl spins 6, rounds 166, OS waits 5
Spin rounds per wait: 10.46 mutex, 29.97 RW-shared, 27.67 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-10-31 10:26:48 7faef190e700
*** (1) TRANSACTION:
TRANSACTION 24058981, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 358, OS thread handle 0x7faef0298700, query id 76122 192.168.2.127 root updating
update t_user
set score = score + 6
where id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9890 page no 3 n bits 72 index `PRIMARY` of table `ahamp-cms`.`t_user` trx id 24058981 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000016f1c5d; asc o ];;
2: len 7; hex 75000001481517; asc u H ;;
3: len 4; hex 74657374; asc test;;
4: len 4; hex 80000037; asc 7;;
*** (2) TRANSACTION:
TRANSACTION 24058980, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 357, OS thread handle 0x7faef190e700, query id 76125 192.168.2.127 root updating
update t_user
set score = score + 10
where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9890 page no 3 n bits 72 index `PRIMARY` of table `ahamp-cms`.`t_user` trx id 24058980 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000016f1c5d; asc o ];;
2: len 7; hex 75000001481517; asc u H ;;
3: len 4; hex 74657374; asc test;;
4: len 4; hex 80000037; asc 7;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9890 page no 3 n bits 72 index `PRIMARY` of table `ahamp-cms`.`t_user` trx id 24058980 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000016f1c5d; asc o ];;
2: len 7; hex 75000001481517; asc u H ;;
3: len 4; hex 74657374; asc test;;
4: len 4; hex 80000037; asc 7;;
*** WE ROLL BACK TRANSACTION (2)
as you can see from the log above, thing 1 waits for lock_mode X locks rec but not gap waiting , that is, X lock, and thing 2 holds lock mode S locks rec but not gap , that is, S lock, waiting for X lock.
so how do these two things form a deadlock? First understand what S-lock and X-lock are.
- S lock: a read lock (shared lock) is a lock created by a read operation. Other users can read data concurrently, but no transaction can modify the data (acquire exclusive locks on the data) until all shared locks have been released. If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that are allowed to share locks can only read data and cannot modify it.
- X lock: write lock (exclusive lock) if transaction T adds an exclusive lock to data A, other transactions can no longer add any kind of lock to A. A transaction that is granted an exclusive lock can both read and modify the data.
the order in which locks are acquired in the method under analysis:
insert log (id,xxx) select x where user id = 1 ------ (1)
update user where id = 1 -----------------------------(2)
operation (1) acquires user.id=1 S lock, operation (2) acquires user.id=1 X lock transaction An and transaction B acquire locks in the following order:
------------A------------------------------------------B------------------
----user.id=1S-----------------------------------------------------
--------------------------------------SBuser.id=1S------
--XA-------------------------------------------------
--user.id=1S------------------------------------------------
----------------------------------user.id=1X
-----------------------------------------------------------------
---X-----------------------------------------------------
this is the whole process, and in the end, only part of the transaction commits normally. The title of
can follow aneasystone's blog . His series of blogs are written in more detail.
mysql read / write locks and transactions
