Shulou(Shulou.com)06/01 Report--

This paper illustrates the usage of mysql shared lock and exclusive lock. Share with you for your reference, the details are as follows:

Mysql lock mechanism is divided into table-level lock and row-level lock. This article will share with you about the shared lock and exclusive lock in mysql.

Shared lock is also known as read lock, referred to as S lock, as the name implies, shared lock means that multiple transactions can share a lock for the same data, all can access the data, but can only be read and cannot be modified.

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

For shared locks, it may be easy for you to understand that multiple transactions can only read data and cannot change data. There may be some differences in understanding of exclusive locks. I made a mistake at the beginning, thinking that after exclusive locks lock a row of data, other transactions cannot read and modify that row of data, but this is not the case. An exclusive lock means that after a transaction adds an exclusive lock to a row of data, no other transaction can add another lock to it. When mysql InnoDB engine modifies data statements by default, update,delete,insert automatically adds exclusive locks to the data involved. Select statements do not add any locks by default. If you add exclusive locks, you can use select... for update statements, and shared locks can use select. Lock in share mode statement. Therefore, data rows with exclusive locks cannot be modified in other transactions, nor can they be queried through for update and lock in share mode locks, but directly through select. From... Query data because a normal query does not have any locking mechanism.

Having said that, let's take a look at the following simple examples:

We have the following test data

Now that we have an exclusive query on the data rows of id=1, we will use begin to open the transaction without seeing me close the transaction, which is for testing, because committing or rolling back the transaction releases the lock.

Open a query window

A piece of data will be queried, and now another query window will be opened to query the same data using exclusive query and shared lock query, respectively.

Exclusive check

Shared search

We see that both the exclusive lock query and the shared lock query are blocked because the data of the id=1 has been added an exclusive lock, and here the blocking is waiting for the exclusive lock to be released.

What if we directly use the following query?

We can see that the data can be queried.

Let's take another look at a transaction that acquires a shared lock, which can only be shared or unlocked in other queries.

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

Finally, we verify the problem of automatically adding exclusive locks to update,delete,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 with an ordinary query, because the lock mechanism is not used and the exclusive lock is not mutually exclusive, but the data found is the old data before the data was modified.

Then we submit the data and release the exclusive lock to take a look at the modified data. Exclusive check, shared check and normal query can be used at this time, because the data of this line releases the exclusive lock after the transaction is committed, and only the ordinary query is shown below. The other students will verify it themselves.

You can see that the result is as expected.

The above is my personal understanding of mysql shared lock and exclusive lock. I hope you will correct any inaccuracies.

More readers who are interested in MySQL-related content can check out this site's special topic: "Summary of skills related to MySQL Database Lock", "Collection of skills related to MySQL stored procedures", "Summary of commonly used functions in MySQL", "Collection of MySQL Log Operation skills" and "Summary of MySQL transaction Operation skills"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.