1. Second kill code (most low implementation):
//Open transaction
@Transactional(rollbackFor = Exception.class)
public BaseResponse<?> startSeckillWithLock(Long id, Long userId) {
try {
return seckill(id, userId);
} catch (Exception e) {
throw e;
} finally {
}
}
private BaseResponse<?> seckill(Long id, Long userId) {
//The sql statement to get the inventory number is: SELECT number FROM seckill WHERE id = #{id}
Integer number = seckillMapper.getNumberById(id);
if (number != null && number > 0) {
log.info("Current thread:{},user:{},Current inventory{}", Thread.currentThread().getId(), userId, number);
//Deduct inventory. The sql is: update seckill set number = number-1 where id = #{id}
seckillMapper.deductNumberById(id);
//Create order
SuccessKilledModel killed = new SuccessKilledModel();
killed.setSeckillId(id);
killed.setUserId(userId);
killed.setState((short)0);
successKilledMapper.insert(killed);
return BaseResponse.valueOfSuccess();
} else {
return BaseResponse.valueOfError(10010, "Insufficient inventory");
}
}
This multi thread execution will definitely oversold: then, the first solution is to add ReenTranlock program lock, as follows:
2. Second kill and program lock implementation (oversold will occur)
@Override
//Open transaction
@Transactional(rollbackFor = Exception.class)
public BaseResponse<?> startSeckillWithLock(Long id, Long userId) {
lock.lock();
try {
return seckill(id, userId);
} catch (Exception e) {
throw e;
} finally {
lock.unlock();
}
}
private BaseResponse<?> seckill(Long id, Long userId) {
//The sql statement to get the inventory number is: SELECT number FROM seckill WHERE id = #{id}
Integer number = seckillMapper.getNumberById(id);
if (number != null && number > 0) {
log.info("Current thread:{},user:{},Current inventory{}", Thread.currentThread().getId(), userId, number);
//Deduct inventory. The sql is: update seckill set number = number-1 where id = #{id}
seckillMapper.deductNumberById(id);
//Create order
SuccessKilledModel killed = new SuccessKilledModel();
killed.setSeckillId(id);
killed.setUserId(userId);
killed.setState((short)0);
successKilledMapper.insert(killed);
return BaseResponse.valueOfSuccess();
} else {
return BaseResponse.valueOfError(10010, "Insufficient inventory");
}
}
Start 1000 threads and the inventory is 100. The final result is a wonderful phenomenon:
The inventory is actually 1, and then the log in the program is as follows:
Then there is a question: is it still impossible to guarantee the atomicity of data after locking?
The default isolation mechanism of mysql and the underlying implementation of @ Transaction annotation can be read repeatedly. The original root cause is as follows:
In our code,lock.unlock The operation of is written in the method finally Declarative,Maybe we unlocked it at this time,@Transaction The marked method transaction has not been committed,This causes another thread to execute select Statement reads the data before it is updated,As for the following inventory number, why is it decreasing in turn:That's because the transaction has been committed,undo log Snapshot inside version It has been updated,What I read later is the snapshot updated earlier, which must be decreasing in turn, so there will be an oversold phenomenon
Under the isolation mechanism of mysql repeatable read (RR) and read commit (RC), the select statement uses snapshot read, and update, insert and delete use the current read by default
3.mysql current read and snapshot read
-
Current read:
select... lock in share mode
select... For update
Update, delete, insert (exclusive lock)Currently, the latest version is read, and the read records are locked to block other transactions and change the same records to avoid security problems.
For example, suppose you want to update a record, but another transaction has delete d the data and commit ted. If you don't lock it, there will be a conflict. Therefore, when updating, it must be the current read, get the latest information and lock the corresponding records
-
Snapshot read:
The simple select operation does not include the above select... lock in share mode, select... for update
-
Read Committed isolation level: each select generates a read snapshot.
-
Read Repeatable isolation level: the first select statement is where the snapshot is read after the transaction is started, rather than the snapshot is read as soon as the transaction is started
-
-
Implementation of snapshot read, undo and multi version concurrency control
The green on the right side of the figure below is data: a row of data records, the primary key ID is 10, name = 'Jack', age=10, and the update d set is name = 'Tom', age=23.
The transaction will first use the "exclusive lock" to lock the changed line, copy the current value of the line to the undo log, then really modify the value of the current line, and finally fill in the transaction DB_TRX_ID, using rollback pointer DB_ROLL_PTR points to the row dB in undo log before modification_ ROW_ ID
DB_TRX_ID: 6-byte DB_ TRX_ The ID field represents the last updated transaction id(update,delete,insert). In addition, deletion is treated internally as an update, where the special bit in the row is set to mark it as soft deleted.
DB_ ROLL_ PTR: a 7-byte rollback pointer that points to the undo record of the previous version and forms an undo linked list. If a row is updated, the undo log record contains the information needed to recreate the contents of the row before updating the row.
DB_ROW_ID: 6-byte DB_ ROW_ The ID field contains a row ID that increases monotonically with the insertion of a new row. When InnoDB automatically generates a clustered index, the clustered index will include the value of this row ID, otherwise this row ID will not appear in any index. If there is no primary key or appropriate unique index in the table, that is, the clustered index cannot be generated, InnoDB will help us automatically generate the clustered index, and the clustered index will use DB_ROW_ID as the primary key; If the table has a primary key or an appropriate unique index, the clustered index will not contain a DB_ROW_ID's gone.
Others: the insert undo log can be deleted only when the transaction is rolled back and the transaction is committed. update undo log includes update and delete. Rollback and snapshot reading are required
4. Shared lock and exclusive lock (LOCK IN SHARE MODE and FOR UPDATE)
Shared locks allow other transactions to read with shared locks. However, other transactions are not allowed to modify or add exclusive locks. The exclusive lock is more strict. Other transactions are not allowed to add shared locks or exclusive locks, and other transactions are not allowed to modify the locked rows
5. Solution 1: the select statement forces the current read mode (LOCK IN SHARE MODE)
@Override
//Open transaction
@Transactional(rollbackFor = Exception.class)
public BaseResponse<?> startSeckillWithLock(Long id, Long userId) {
lock.lock();
try {
return seckill(id, userId);
} catch (Exception e) {
throw e;
} finally {
lock.unlock();
}
}
private BaseResponse<?> seckill(Long id, Long userId) {
//The sql statement to get the inventory number is: SELECT number FROM seckill WHERE id = #{id} LOCK IN SHARE MODE
Integer number = seckillMapper.getNumberById(id);
if (number != null && number > 0) {
log.info("Current thread:{},user:{},Current inventory{}", Thread.currentThread().getId(), userId, number);
//Deduct inventory
seckillMapper.deductNumberById(id);
//Create order
SuccessKilledModel killed = new SuccessKilledModel();
killed.setSeckillId(id);
killed.setUserId(userId);
killed.setState((short)0);
successKilledMapper.insert(killed);
return BaseResponse.valueOfSuccess();
} else {
return BaseResponse.valueOfError(10010, "Insufficient inventory");
}
}
Principle of this method:
Program lock is added to the method ReeTranlock，That is, only one thread is allowed to run before all the code is executed(But there is no guarantee here unlock Commit transaction before execution)，In transaction select Statement plus LOCK IN SHARE MODE This line of code has a shared lock,Then the following is executed update Statement at this time, an exclusive lock is added to the statement,The function of exclusive lock is to ensure that other transactions can neither read nor write this row of data,So the next transaction comes in SELECT number FROM seckill WHERE id = #{id} LOCK IN SHARE MODE is to wait, which ensures that the acquired data is the latest data
-
Then we test a deadlock phenomenon. We remove the program lock. The program is as follows:
@Override //Open transaction @Transactional(rollbackFor = Exception.class) public BaseResponse<?> startSeckillWithLock(Long id, Long userId) { //lock.lock(); try { return seckill(id, userId); } catch (Exception e) { throw e; } finally { //lock.unlock(); } } private BaseResponse<?> seckill(Long id, Long userId) { //The sql statement to get the inventory number is: SELECT number FROM seckill WHERE id = #{id} LOCK IN SHARE MODE Integer number = seckillMapper.getNumberById(id); if (number != null && number > 0) { log.info("Current thread:{},user:{},Current inventory{}", Thread.currentThread().getId(), userId, number); //Deduct inventory seckillMapper.deductNumberById(id); //Create order SuccessKilledModel killed = new SuccessKilledModel(); killed.setSeckillId(id); killed.setUserId(userId); killed.setState((short)0); successKilledMapper.insert(killed); return BaseResponse.valueOfSuccess(); } else { return BaseResponse.valueOfError(10010, "Insufficient inventory"); } }The result will be Deadlock:
Let's take an example to analyze the cause of deadlock. Using lock in share mode has high risk. See the following case:
session 1:
set autocommit = 0; select * from tb_test where id = 1 lock in share mode;
open session2:
set autocommit = 0; select * from tb_test where id = 1 lock in share mode;
At this time, two sessions hold the shared lock of the row of data with id = 1 at the same time. At this time, we execute the update operation in session 1:
session 1:
update tb_test set col1 = 'AAA' where id = 1;
The result is stuck: at this time, session1 must wait for session2 to exit the transaction or wait until the lock times out:
Lock timeout: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Then we execute it in session2
session2:
update tb_test set col1 = 'BBB' where id = 1;
Result direct error reporting:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
At this time, mysql detects that a deadlock will occur, which will interrupt the execution of the current transaction and restart a new transaction (it should be equivalent to session2 exiting the transaction first and then starting a transaction).
-
Summary:
Lock in share mode Improper use can easily cause deadlock,That is, there are two transactions at the same time select and update Statement,First transaction select Or shared lock,The second transaction starts execution select Shared locks are also obtained,The first transaction is then executed update Obtaining an exclusive lock waits for the second transaction to end,Shared locks allow other transactions to be read with shared locks. However, other transactions are not allowed to modify or add exclusive locks. Then, the second transaction adds an exclusive lock and waits for the first transaction to unlock,It becomes a phenomenon of waiting for each other
6. Solution 2: the select statement forces the current read (exclusive lock mode for update) (optimal solution)
-
code:
@Override //Open transaction @Transactional(rollbackFor = Exception.class) public BaseResponse<?> startSeckillWithLock(Long id, Long userId) { //lock.lock(); try { return seckill(id, userId); } catch (Exception e) { throw e; } finally { //lock.unlock(); } } private BaseResponse<?> seckill(Long id, Long userId) { //The sql statement to get the inventory number is: SELECT number FROM seckill WHERE id = #{id} FOR UPDATE Integer number = seckillMapper.getNumberById(id); if (number != null && number > 0) { log.info("Current thread:{},user:{},Current inventory{}", Thread.currentThread().getId(), userId, number); //Deduct inventory seckillMapper.deductNumberById(id); //Create order SuccessKilledModel killed = new SuccessKilledModel(); killed.setSeckillId(id); killed.setUserId(userId); killed.setState((short)0); successKilledMapper.insert(killed); return BaseResponse.valueOfSuccess(); } else { return BaseResponse.valueOfError(10010, "Insufficient inventory"); } }This method does not add program lock, and it is done with a FOR UPDATE
Principle:
for update Can use exclusive locks,Exclusive locks do not allow other transactions to add shared locks or exclusive locks, nor do other transactions modify locked rows,So combine our code,select Statement with exclusive lock,Then other transactions will be executed SELECT number FROM seckill WHERE id = #{id} LOCK IN SHARE MODE is to wait until the transaction is committed after the first transaction is executed uodate. At this time, the select statement of the next transaction starts to execute. At this time, the row data in the snapshot is already up-to-date