Mysql transaction concurrency problem solution

I encountered such a problem during development

A video watching record, when updated to 100, means it has been watched, and it will not be updated if there are subsequent requests.

turn out:

As a result, many data inside are problematic.

It is speculated that the following circumstances will lead to

The first request transaction is in execution and has not been committed (because it is sometimes difficult to reproduce locally, so I manually sleep for a few seconds in the program when processing the first record to achieve this effect)

The second request transaction has started to execute. At this time, the historical maximum value found is not 100, so it will be updated.

I looked up the solution online:

Pessimistic Lock

Direct lock row record

I tested this locally and it really works. One transaction starts but does not end, and the second transaction waits. However, it will cause a blocking state because of system concurrency. I dare not consider it, so I just record this method.

Manual simulation:

Execute the first transaction:

-- Video 100 BEGIN;

SELECT * FROM `biz_coursestudyhistory` WHERE sid = 5777166;

UPDATE biz_coursestudyhistory set studyStatus = 100,versionNO=versionNO+1 WHERE sid = 1 AND versionNO = 0;

-- commit ; ​​Do not execute first, annotate first, and only execute the above 

Then execute the second transaction:

BEGIN;
 
UPDATE biz_coursestudyhistory set studyStatus = 90,versionNO=versionNO+1 WHERE sid = 1 AND versionNO = 0;
 
SELECT * FROM `biz_coursestudyhistory` WHERE sid = 1 FOR UPDATE;
 
COMMIT;

You will find that you cannot succeed and are always in a waiting state.

View Locks

It is indeed locked. Here, as long as the commit of the first transaction is executed, the second transaction will be executed.

From this we can see that row locks can directly achieve the ideal data unification state. If one transaction is modified, other operations cannot be performed. I feel that this is more suitable for security projects such as banks.

Optimistic locking:

This is simpler and does not cause blocking

The way is to add the version number

var maxver = select max(version) from table

To update, use

update table set studystatus = xxx,version = version +1 where id =1 and version = maxver

Written words

INSERT into table (contentStudyID,courseWareID,studyStatus,studyTime,endTime)
SELECT 27047358,3163,100,333,NOW() FROM dual WHERE NOT EXISTS (SELECT 1 FROM table WHERE contentStudyID =27047358 AND
courseWareID = 3163
 )

In this way, when updating or writing, you can directly determine whether the data in the library exists. If it does not exist, it is used by other threads.

After changing to this writing style, using jmeter for multi-threaded testing, the initial multiple record updates were successful, but now only one record is successful, and the rest fail.

From inserting multiple records at the beginning, to only inserting one data later

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.