MySQL lock control concurrency method

Preface

Locks can generally be divided into optimistic locks and pessimistic locks. Simply put, optimistic locks are controlled by version numbers, and pessimistic locks are controlled by locks.

The following is the data to be used for testing

# Add a user table CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(255) NOT NULL COMMENT 'Name',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#Insert 3 records INSERT INTO `users` (`id`, `name`)
VALUES
 (1, 'Snow Mountain Flying Pig'),
 (2, 'chenqionghe'),
 (3, 'cqh');

The query results are as follows:

1. Optimistic Locking

The core principle is to add a version field for control.
For example, when we want to update a single row concurrently, only one process will update successfully, as follows

UPDATE users SET name="雪山飞猪" WHERE id=3
UPDATE users SET name="chenqionghe" WHERE id=3

The above two SQL statements will eventually be updated successfully, and the last update result will be the main one.

The solution is to add a version field

Add the version field

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

The solution is to add a version field, add it to the where condition for each update, and also update it

UPDATE users SET name="雪山飞猪",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, the update will only be successful once, and whoever grabs the record first will be the master, because the version number has changed after the current process successfully updates, and the second process cannot find this record.
This is the simplest CAS mechanism.

2. Pessimistic Lock

In fact, it is similar to the Mutex and RwMutex read locks in the Go language

Read lock

Also called a shared lock or S lock, when a shared lock is added to a data table, the table becomes read-only mode.
We can lock the entire table, or lock the entire table or some rows, as follows

Full table lock (LOCK TABLE table READ)

The syntax is as follows

LOCK TABLE table READ
UNLOCK TABLE;

Let's test one, the first process executes

LOCK TABLE users READ; 

The second process performs a normal read

SELECT * FROM users WHERE id=1; 

You can query normally. Let’s perform the update again

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

There was a wait.

We unlock the first process

Looking at the second process, it has been updated successfully

Row locks (SELECT ... LOCK IN SHARE MODE)

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

Must be used with transactions. After BEIN starts, locked rows can only be queried externally but not updated.

Let's test it. The first process executes

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

Here, the rows with id 1 and 2 are locked. Our second process performs the update

UPDATE users SET name="雪山飞猪" WHERE id=1

Once again, there was a wait.
OK, now we commit the transaction of the first process

COMMIT; 

The second process update was successful, as follows

Write lock

Exclusive lock, exclusive lock, understood as reading and writing are not possible, the syntax is as follows

Full table lock (LOCK TABLE table WRITE)

LOCK TABLE users WRITE;

At this time, the entire table has been locked. Let's use another process to query the data with id 1.

SELECT * FROM users WHERE id=1 

As you can see, the query has already waited.
Let's unlock the first process.

UNLOCK TABLE 

At this time, the second process immediately succeeds in querying

Row locks (SELECT ... FOR UPDATE)

When we update 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 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

We first 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 has occurred, indicating that it has been locked.
OK, let's submit the transaction of the first process

COMMIT;

Look at the second process again, it has been updated successfully

Simply put, optimistic locking uses version control, pessimistic table locks are generally not needed, row read locks use LOCK IN SHARE MODE, and write locks use FRO UPDATE. It's that simple!

The above is the details of the method of MySQL locking and controlling concurrency. For more information about MySQL locking and controlling concurrency, please pay attention to other related articles on 123WORDPRESS.COM!