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

Two ways to completely delete users under Linux

Linux Operation Experimental environment: Centos7...

The meaning and calculation method of QPS and TPS of MySQL database

When doing DB benchmark testing, qps and tps are ...

Docker custom network container interconnection

Table of contents Preface –link Custom Network As...

Sample code for nginx to achieve dynamic and static separation

1. Simple configuration of nginx's dynamic an...

How to compile the Linux kernel

1. Download the required kernel version 2. Upload...

Installing the ping tool in a container built by Docker

Because the Base images pulled by Docker, such as...

Seven ways to implement array deduplication in JS

Table of contents 1. Using Set()+Array.from() 2. ...

VMWare15 installs Mac OS system (graphic tutorial)

Installation Environment WIN10 VMware Workstation...

Installing Win10 system on VMware workstation 14 pro

This article introduces how to install the system...

How to build DockerHub yourself

The Docker Hub we used earlier is provided by Doc...

How to use docker to deploy spring boot and connect to skywalking

Table of contents 1. Overview 1. Introduction to ...

How to view and modify the time zone in MySQL

Today I found that a program inserted an incorrec...

Examples of vertical grid and progressive line spacing

New Questions Come and go in a hurry. It has been...