MySQL lock control concurrency method

Time:2022-5-10

preface

Locks can be divided into optimistic locks and pessimistic locks. In short, optimistic locks are controlled by version number and pessimistic locks are controlled by lock.

Here are the data to be tested later

#Add a user table
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `Name ` varchar (255) not null comment ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#Insert 3 pieces of data
INSERT INTO `users` (`id`, `name`)
VALUES
 (1, 'snow mountain flying pig'),
 (2, 'chenqionghe'),
 (3, 'cqh');

The query results are as follows:

1、 Optimistic lock

The core principle is to add a version field to control.
For example, when we want to update single line records concurrently, only one process is updated successfully, as shown below

Update users set name = "snow mountain flying pig" where id = 3
UPDATE users SET name="chenqionghe" WHERE id=3

The above two SQL will be updated successfully in the end, and the final update results will prevail.

The solution is to add a version field

Add version field


ALTER TABLE users ADD `version` INT NOT NULL DEFAULT '0'

The solution is to add a version field, which is added to the where condition at each update, and it is also updated

Update users set name = "snow mountain flying pig", version = version + 1 where id = 3 and version = 0
UPDATE users SET name="chenqionghe",version=version+1 WHERE id=3 AND version=0

This time, it will only be updated successfully once. Who will grab this record first depends on who, because the version number of the current process has changed after the update is successful, and the second process can’t find this record.
This is the simplest CAS mechanism.

2、 Pessimistic lock

In fact, it is similar to mutex and rwmutex read lock in go language

Read lock

It is also called shared lock or s lock. When a shared lock is added to a data table, the table becomes a read-only mode.
We can lock all tables, or all tables or some rows, as follows

Lock table read

The syntax is as follows

Lock table read
UNLOCK TABLE;

Let’s test one, the first process execution


LOCK TABLE users READ;

The second process performs normal reading


SELECT * FROM users WHERE id=1;

It can be queried normally. Let’s do the update again


UPDATE users SET name="chenqionghe" WHERE id=1

There was a wait.

We unlock the first process

Look at the second process. It has been updated successfully

Row lock (select… Lock in share mode)


BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE
COMMIT;

It must be used in conjunction with transactions. After bein starts, the locked rows can only be queried externally and cannot be updated

Let’s test the execution of the first process


BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE

The record lines with IDS 1 and 2 are locked here. Our second process performs the update

Update users set name = "snow mountain flying pig" where id = 1

Wait again.
OK, at this time, we will commit the transaction of the first process


COMMIT;

The second process is updated successfully, as follows

Write lock

Exclusive locks and exclusive locks are also used. It is understood that reading and writing are not enough. The syntax is as follows

Lock table write


LOCK TABLE users WRITE;

At this time, the whole table has been locked. We will use another process to query the data with ID 1


SELECT * FROM users WHERE id=1

You can see that the query has been waiting.
Let’s unlock the first process again


UNLOCK TABLE

At this time, the second process immediately queries successfully

Row lock (select… For update)

When we update the data (insert, delete, update), the database will automatically use an exclusive lock to prevent other transactions from operating the data


BEGIN;
SELECT * FROM users WHERE id IN (1,2) LOCK IN SHARE MODE
COMMIT;

Let’s test again. The first process locks the records with ID 1 and 2


BEGIN;
SELECT * FROM users WHERE id IN (1,2) FOR UPDATE

Note: the transaction is not committed at this time

Let’s use the second process to update the record with ID 3 (not locked)


UPDATE users SET name="chenqionghe" WHERE id=3

The execution was successful.
Let’s update a record with ID 1


UPDATE users SET name="chenqionghe" WHERE id=1

Waiting occurs, indicating that it has been locked.
OK, let’s submit the transaction of the first process


COMMIT;

Let’s look at the second process. It has been updated successfully

To put it simply, optimistic locks are controlled by version, while pessimistic table locks are generally unnecessary. Lock in share mode is used for row read locks, and fro update is used for write locks. That’s it!

The above is the details of the method of MySQL locking to control concurrency. For more information about MySQL locking to control concurrency, please pay attention to other relevant articles of developeppaer!

Recommended Today

QT custom control to realize disc progress bar

This example shares the specific code of QT disc progress bar for your reference. The specific contents are as follows Custom control 2: disc progress bar Upper rendering: Main idea: use qpainter to draw circles and arcs according to the graphic requirements, draw pointers (polygons, specify coordinates), and rotate the coordinate system according to the […]