Mysql transaction concurrency problem solution

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.

You may also be interested in:
  • Example of using MySQL transaction features to implement concurrent and safe auto-increment ID
  • Solution to PHP+MySQL high-concurrency locked transaction processing problem
  • Can MySQL's repeatable read level solve phantom reads?
  • Detailed explanation of how MySQL solves phantom reads
  • Detailed explanation of MySQL phantom reads and how to eliminate them
  • MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
  • How to solve the phantom read problem in MySQL
  • mysql+mybatis implements stored procedure + transaction + multi-concurrent serial number acquisition
  • Detailed explanation of concurrent dirty read + non-repeatable read + phantom read in Mysql transactions

<<:  Example code for converting http to https using nginx

>>:  Using vue3 to implement counting function component encapsulation example

Recommend

How to configure user role permissions in Jenkins

Jenkins configuration of user role permissions re...

Detailed explanation of JavaScript animation function encapsulation

Table of contents 1. Principle of animation funct...

Summary of commonly used commands for docker competition submission

Log in to your account export DOCKER_REGISTRY=reg...

Detailed explanation of efficient MySQL paging

Preface Usually, a "paging" strategy is...

WeChat applet scroll-view realizes left and right linkage

This article shares the specific code for WeChat ...

How to start multiple MySQL databases on a Linux host

Today, let’s talk about how to start four MySQL d...

What is em? Introduction and conversion method of em and px

What is em? em refers to the font height, and the ...

Detailed explanation of Vue's live broadcast function

Recently, the company happened to be doing live b...

Ubuntu 16.04 mysql5.7.17 open remote port 3306

Enable remote access to MySQL By default, MySQL u...

Installation tutorial of mysql 8.0.11 compressed version under win10

This article shares the installation tutorial of ...

Class in front-end JavaScript

Table of contents 1. Class 1.1 constructor() 1.2 ...

JavaScript to display hidden form text

This article shares the specific code of JavaScri...

Solve the problem of resetting the Mysql root user account password

Problem description: The following error message ...

Installation and configuration of MySQL 5.7.17 free installation version

MYSQL version: MySQL Community Server 5.7.17, ins...