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

This article mainly introduces what are the locking rules in the database, the article is very detailed, has a certain reference value, interested friends must read it!

With a gap lock coupled with a row lock, it is easy to make a mistake in determining whether a lock waiting will occur.

Because the gap lock is only valid under the repeatable readable isolation level, this text silently approves repeated reading.

Locking rule

Principle 1

The basic unit of locking is next-key lock, which is opened before and closed after.

Principle 2

Only objects accessed during the lookup process are locked.

Optimization 1

An equivalent query on an index, when a unique index is locked, the next-key lock is reduced to a row lock.

Optimization 2

When the equivalent query on the index traverses to the right and the last value does not satisfy the equivalence condition, the next-key lock is reduced to a gap lock.

A bug

The range query on the unique index accesses the first value that does not meet the condition.

Data preparation

Table name: t

New data: (0rem 0rem 0), (5je 5jue 5), (10je l0pl 10), (15je 15je 15), (20je 20r 20), (25pr 25r 25)

The following examples are basically illustrated in accordance with the pictures, so I suggest you compare them with the manuscripts. Some examples may "destroy the outlook on life, world outlook and values." I also suggest you to practice it after reading the article.

Case

Equivalent query gap lock

Gap lock of equivalent query

There is no id=7 in table t, so according to principle 1, the locking unit next-key lock, so the locking range of session A (5d10)

At the same time, according to optimization 2, equivalent query (id=7), but id=10 does not satisfy, next-key lock degenerates into a gap lock, so the lock range is finally added (5J10)

Therefore, the record of session B inserting id=8 into this gap will be locked, but it is possible for session C to modify the id=10 line.

Non-unique index equivalent lock

A lock added only to a non-unique index

Session A wants to add a read lock to the row clock5 of index c

According to principle 1, add the lock unit next-key lock, so add next-key lock to (0P5).

C is a normal index, so you can't stop accessing canti5 immediately. You need to traverse to the right and find clock10 before giving up. According to principle 2, all accesses should be locked, so add next-key lock to (5d10)

At the same time, it conforms to the optimization 2: equivalence judgment, traversing to the right, and the last value does not meet the equivalent condition of cantilever 5, so it is reduced to a gap lock (5mem10).

According to principle 2, only objects accessed will be locked. This query uses an override index and does not need to access the primary key index, so there is no lock on the primary key index, so the update statement of session B can be executed.

But if session C is to be inserted, it will be locked by the gap lock of session A.

In this example, lock in share mode only locks and overrides the index, but it is different if it is for update. When executing for update, the system will assume that you are going to update the data next, so it will add a row lock to the qualified row on the primary key index by the way.

This example shows that the lock is added to the index; at the same time, it gives us guidance that if you want to use lock in share mode to lock rows to prevent data from being updated, you have to bypass the optimization of overriding the index and add fields in the query field that do not exist in the index. For example, change the query statement of session A to select d from t where censor 5 lock in share mode. You can verify the effect for yourself.

3 primary key index range lock

Range query.

For our table t, do the following two query statements have the same locking range?

Mysql > select * from t where id=10 for update

Mysql > select * from t where id > = 10 and id=10 and c10 and id

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.