Detailed explanation of shared locks and exclusive locks in mysql

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

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

Exclusive lock is also called write lock, referred to as Other locks include shared locks and exclusive locks, but the transaction that acquires the exclusive lock can read and modify the data.

Everyone may have a good understanding of shared locks, that is, multiple transactions can only read data but not modify data. However, everyone may have a different understanding of exclusive locks. I made a mistake at the beginning, thinking that exclusive locks After a row of data is stored, other transactions cannot read and modify the row of data. 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 to it. Mysql InnoDB engine's default data modification statement, 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. To add a shared lock, you can use the select ... lock in share mode statement. Therefore, the data rows with exclusive locks cannot be modified in other transactions, and the data cannot be queried through for update and lock in share mode locks, but the data can be queried directly through select...from..., because Ordinary queries do not have any locking mechanism.

Having said so much, let’s take a look at the following simple example:

We have the following test data

Now we have an exclusive query for the data row with id=1. Here we will use begin to open the transaction, and we will not see me closing the transaction. This is 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 queried. Now open another query window and use exclusive query and shared lock query for the same data. A way to query

Exclusive query

Shared query

We see that the exclusive lock is turned on Both query and shared lock query will be in a blocking 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 directly use the following query

#We see that the data can be queried.

Let’s take another look at a transaction that acquires a shared lock. In other queries, we can only add shared locks or not.

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

Finally, we verify the problem of automatically adding exclusive locks to the update, delete, and insert statements in the mysql InnoDb engine mentioned above.

At this time, the shared query is in Blocking, waiting for the release of the exclusive lock, but the data can be found using ordinary queries, because the locking mechanism is not mutually exclusive with the exclusive lock, but the data found is the old data before the data is modified.

Then we submit the data, release the exclusive lock and look at the modified data. At this time, exclusive query, shared query and ordinary query can be used, because the row data is released after the transaction is submitted. Exclusive lock, only ordinary queries are displayed below, other students can verify by themselves.

You can see that the results are as expected.

Recommended tutorial: "Mysql Tutorial"

The above is the detailed content of Detailed explanation of shared locks and exclusive locks in mysql. For more information, please follow other related articles on the PHP Chinese website!