Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "case analysis of shared lock and exclusive lock under MySQL and InnoDB". The editor shows you the operation process through the actual case, and the operation method is simple, fast and practical. I hope this article "case analysis of shared lock and exclusive lock under MySQL and InnoDB" can help you solve the problem.

Shared lock (Shared Lock)

Shared lock, S lock and read lock are all what he calls them.

And I like to ask him to share the reading lock.

A shared (S) lock permits the transaction that holds the lock to read.

A shared lock allows transactions that hold the lock to read.

The sharing here is, read sharing.

That is, whether at the row or table level, if a shared read lock is applied to some data, other transactions can continue to read (that is, allow to hold the shared read lock), but cannot write, that is, read-write mutual exclusion.

Upper table-level shared lock, that is, table-level shared read lock:

Select * from table (table) lock in share mode

Uplink shared lock, that is, row-level shared read lock:

Select * from table (table) where id = 10 lock in share mode

At this point, note that under InnoDB, you don't use row locks as much as you want. Let's review the trigger conditions of row locks again (mentioned at the beginning):

Exclusive lock (Exclusive Lock)

Exclusive lock, write lock and X lock are all what he calls them.

An exclusive (X) lock permits the transaction that holds the lock to update or delete.

Monopolization, the word. Have you ever played basketball? I didn't know how to play basketball in junior high school before, so I didn't pass the ball with it.

Yes, I am very lonely. Just like this exclusive write lock (exclusive lock), it's very unique.

When a transaction adds an exclusive write lock (exclusive lock) to some data, only the current transaction can modify or delete the data.

Other affairs, can not read, can not write. Because this lock is very unique, we have to wait for this unique lock to be used (released) before other transactions can take advantage of it.

Therefore, the exclusive write lock (exclusive lock) is mutually exclusive between read and write.

By the way, how to add an exclusive lock (exclusive write lock):

Table-level exclusive lock, that is, table-level exclusive write lock:

Select * from table for update

Select * from table where id = 10 for update

Let me talk a little bit more here, and note that under InnoDB, you don't use a row lock if you want to use a row lock. Let's review the trigger conditions of the row lock again (mentioned at the beginning):

The above sql can uplink exclusive locks because it hits the index, and id is the index.

Maybe when you see here, you just know something about shared locks & exclusive locks, such as reading sharing, reading and writing mutual exclusion, and so on.

Red transaction operation one

Blue transaction operation two

Shared lock (shared read lock) exclusive lock (exclusive write lock) shared lock (shared read lock) can, compatible, cannot read together, not compatible, want to write, etc. Shared lock exclusive lock (exclusive write lock) can not, incompatible, put on the exclusive lock, others can not move anything, incompatible, put on the exclusive lock, others can not move anything

So if you see here, it's still about the shared lock & the exclusive lock is still just in the clouds.

Then I'll just have to do it!

Practical introduction, demonstration of the so-called reading sharing, read-write mutual exclusion, write-write mutual exclusion.

Before demonstrating reading sharing, reading and writing mutual exclusion, I must point out one point!

In this article, I introduced some ways to apply shared locks (shared read locks) and exclusive locks (exclusive write locks). But you can see that the written query sql is followed by something, lock in share mode, for update. Wait.

So one thing I want to make clear is that

If you are using an ordinary query, it is not locked at all!

It's like what we usually write.

Select * from table

Select * from table where age=18

Select statement does not add any lock type by default select statement does not add any lock type select statement does not add any lock type by default

And exclusive lock, except for select. For update, InnoDB engine default modify, insert, delete (update,insert,delete) will add exclusive lock to the operation-related data.

Don't talk too much nonsense, let's do talent:

Prepare some data for testing.

Build a table:

DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int (11) NULL DEFAULT NULL, `sex` tinyint (1) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1

Do some simulation data:

(id primary key index)

The first little practice:

We don't talk nonsense, let's go directly to the shared read lock to see if it is in line with our theory of reading sharing, reading and writing mutual exclusion!

1.

two。

3. Then, based on the current situation, if we execute the query statement that uses the exclusive write lock, we can find that the read and write are mutually exclusive:

4. To verify, let's see if there is a transaction waiting for a lock:

Let's check again. Those transactions are using locks.

As can be seen from the results, 34844 transactions are using S locks, that is, shared read locks.

34847 transactions are using X locks, that is, exclusive write locks (but because the shared read lock is on first, read and write are mutually exclusive), resulting in 34847 transactions waiting for the lock.

5. So if we don't COMMIT shared read locks all the time, will 34847 transactions wait forever for locks? Then it must be impossible to allow such a waiting scenario:

So mysql will have a mechanism to wait for the lock resource to time out, which will directly return the result of the query failure.

When some data is on shared read lock S, only other transactions are allowed to share read lock S, because read sharing

Exclusive write lock X is not allowed on other transactions (unless the shared read lock S is released) because read and write are mutually exclusive.

The second little practice:

1. We directly put an exclusive write lock X on a row of data (note that our transaction is not performing COMMIT):

two。 Let's go to the shared read lock to get the data and see what happens.

This is the exclusive write lock X read-write mutual exclusion, write-write mutual exclusion (the scene of write-write mutual exclusion is not shown).

Again, let's see if there is a transaction waiting for the lock resource:

3. So if the exclusive write lock is not released, do you wait for the rest of the transaction?

Similarly, waiting for the timeout to return the query failed:

Add a little practice:

1. Again, first put an exclusive write lock on a certain data, not COMMIT:

two。 To execute a normal query, select:

As you can see, ordinary select statements can be obtained normally, why? Because we mentioned earlier:

So I have to say again, the so-called read sharing, read-write mutual exclusion, write-write mutual exclusion, are all for lock resources, if you do not lock resources competition, then there must be no mutual exclusion.

That's all for the example analysis of shared lock and exclusive lock under MySQL and InnoDB. Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.