Example analysis of mysql shared lock and exclusive lock usage

This article uses examples to illustrate the usage of MySQL shared locks and exclusive locks. Share with you for your reference, the details are as follows:

The MySQL lock mechanism is divided into table-level locks and row-level locks. This article will share with you my sharing and communication on shared locks and exclusive locks in row-level locks in MySQL.

A shared lock is also called a read lock, or S lock for short. As the name suggests, a shared lock means that multiple transactions can share a lock for the same data, and can access the data, but can only read and not modify it.

An exclusive lock is also called a write lock, or X lock for short. As the name implies, an exclusive lock cannot coexist with other locks. For example, if a transaction acquires an exclusive lock for a data row, other transactions cannot acquire other locks for the row, including shared locks and exclusive locks. However, the transaction that acquires the exclusive lock can read and modify the data.

Everyone may have a good understanding of shared locks, which means that multiple transactions can only read data but not modify it. However, everyone may have a different understanding of exclusive locks. I made a mistake at the beginning, thinking that after an exclusive lock locks a row of data, other transactions cannot read or modify the row of data. In fact, this is not the case. An exclusive lock means that after a transaction adds an exclusive lock to a row of data, other transactions cannot add other locks on it. The default data modification statements of the MySQL InnoDB engine, update, delete, and insert, will automatically add exclusive locks to the data involved. The select statement will not add any lock type by default. If you want to add an exclusive lock, you can use the select ... for update statement. If you want to add a shared lock, you can use the select ... lock in share mode statement. Therefore, the data row with an exclusive lock cannot be modified in other transactions, nor can it be queried through for update and lock in share mode. However, the data can be queried directly through select ...from... because ordinary queries do not have any locking mechanism.

Having said so much, let's look at the following simple example:

We have the following test data

Now we query exclusively the data row with id=1. Here we use begin to open the transaction, and you will not see me close the transaction. This is done for testing, because committing the transaction or rolling back the transaction will release the lock.

Open a query window

A piece of data will be found. Now open another query window and query the same data using exclusive query and shared lock query.

Exclusive Check

Shared Query

We can see that both exclusive lock queries and shared lock queries are in a blocked state, because the data with id=1 has been locked with an exclusive lock, and the blocking here is waiting for the exclusive lock to be released.

What if we use the following query directly?

We can see that the data can be queried.

Let's take a look at a transaction that acquires a shared lock. In other queries, only shared locks or no locks can be added.

We can see that the data can be queried, but it cannot be queried with an exclusive lock, because exclusive locks and shared locks cannot exist on the same data.

Finally, let's verify the problem of automatic exclusive locks for update, delete, and insert statements in the MySQL InnoDb engine mentioned above.

At this time, the shared query is blocked, waiting for the exclusive lock to be released, but the data can be found using ordinary queries because there is no locking mechanism that is mutually exclusive with the exclusive lock. However, the data found is the old data before the data was modified.

Then we commit the data, release the exclusive lock and look at the modified data. At this time, exclusive query, shared query and normal query can be used. Because the exclusive lock of the row of data is released after the transaction is committed, only normal query is displayed below. Other students can verify it by themselves.

You can see that the results are as expected.

The above is my personal understanding of shared locks and exclusive locks in MySQL. If there are any inaccuracies, I hope you can correct me.

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Database Lock-Related Skills", "Summary of MySQL Stored Procedure Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills" and "Summary of MySQL Transaction Operation Skills".

I hope this article will be helpful to everyone's MySQL database design.