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

This article introduces the knowledge of "what locks are there in MySQL". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Ordinary lock

InnoDB implements standard row-level locks, while there are two types of row-level locks:

Shared lock (shared lock, hereinafter referred to as S lock): intended for sharing. That is, a shared lock that allows multiple transactions to hold a record together, which is mainly used for read operations.

Exclusive lock (hereinafter referred to as X lock): intended to repel. Only one transaction can be allowed to hold an exclusive lock for one record, which is mainly used for update and delete operations.

If you have ever known the JUC package in Java, you will find that this is a bit like the read-write lock ReentrantReadWriteLock in JUC. Their purpose is to improve the concurrency of read operations.

If one transaction T1 holds the S lock of row r and another transaction T2 wants to acquire the lock in row r, T2 acquires a different lock as follows:

If T2 wants to acquire the S lock of row r, T2 will get the lock immediately.

If T2 wants to acquire the X lock of row r, T2 will be blocked until T1 releases the S lock of row r.

If there is an X lock of transaction T1 holding r and another transaction T2 wants to acquire the lock in row r, no matter what lock T2 acquires will be blocked.

The compatibility of the X lock with the S lock is shown in the following figure:

On the far left is the lock held, and at the top is the lock you want to apply for. As can be seen from the figure, anything related to the X lock will conflict, that is, it will cause blocking.

Intention lock

InnoDB allows multiple granularity locks to coexist, so table locks and row locks coexist. In order for multiple granularity locks to coexist, InnoDB uses intentional locks. An intention lock is a table-level lock that indicates that a transaction is holding the lock or is planning to apply for a lock.

There are two types of intention locks:

Shared intention lock (intention shared lock, hereinafter referred to as IS): indicates that a transaction holds a shared lock on a row in a table or intends to acquire a shared lock on a row.

Shared exclusive lock (intention exclusive lock, hereinafter referred to as IX): indicates the exclusive lock of the row in the transaction holding table or the exclusive lock of the row intended to be acquired.

IS and IX are just intended to express the intention that they will not block any operation except for full table requests. Their main purpose is simply to indicate that you hold a row lock, or that you intend to acquire it.

The rules for the use of intention locks are as follows:

When a transaction acquires a shared row lock in a table, it needs to acquire an IS lock or a higher-level lock in the table.

When a transaction acquires an exclusive row lock in a table, it needs to acquire the IX lock in the table first.

There is a very important point here: you need to apply for an intention lock only when you acquire the row lock in the table. If you are executing statements such as ALTER TABLE that need to lock the entire table, you do not need to apply for an intention lock, you can directly apply for a table-level X lock.

The compatibility of X, S, IS, and IX locks at the table level is as follows:

Note: the X lock and S lock here are also table-level locks, so don't take it for granted that they are row-level locks.

Why did the lock appear intentionally? Let's consider the following scenarios (assuming there is no intention lock):

A transaction A wants to modify row r in table t, so An acquires the X lock of row r, and transaction A now holds a row lock. At this point, transaction B wants to use the ALTER TABLE statement to modify the structure of table t, which first needs to obtain the X lock of table t, but transaction B does not know whether any rows in the table are locked, so it can only traverse one row, and then lock the traversing rows, until it finds that no rows in the table have been locked before, and now it can modify the structure of the table. But it finds that there are already some rows in the table that are locked, so it cannot modify the table structure and needs to wait for these locks to be released.

There is a big problem, in the worst case, you need to traverse all the rows to know if any rows are locked, which is very performance-consuming, and intention locking can solve this problem. Let's now consider how intention locks solve this problem in the same scenario:

If a transaction A wants to modify the row r in table t, it first needs to acquire the IX lock of table t, then successfully acquire the IX lock, and then apply for the X lock of row r. After the application is successful, transaction A holds two locks, namely the IX lock of table t and the X lock of row r. At this time, transaction B wants to use ALTER TABLE statement to modify the structure of table t, this statement needs to obtain the X lock of table t, transaction B can check whether there is a lock on table t to determine whether the row in the table is locked. When it finds that there is an IX lock on table t, transaction B will be blocked because it knows that there are already rows in the table that have been locked, so it cannot apply for the X lock of table t.

When we look at the compatibility table above, we also know that the table-level IX lock and the table-level X lock conflict, so it just corresponds to this scenario.

Record lock

A record lock is a lock on an indexed record, in other words, a record lock locks only the index. Each table must have a primary key index (user-defined primary key, unique index, implicitly generated), and records in non-leaf nodes in that primary key index are locked using this record lock.

Suppose you execute the statement: select * from user where id = 10 for update

If id is the primary key in the user table, the record with an id of 10 will be locked in the primary key index. And other transactions that want to update or delete this record will be blocked, and other operations can be performed only after the record lock in the record is released.

In addition to primary key indexes, there will be secondary indexes in InnoDB. The secondary index is the same as the primary key index, when using the secondary index as the query condition, the records of the qualified secondary index will be locked with the record lock, and then the corresponding primary key index will also be locked with the record lock.

Suppose you execute the statement: select * from user where name ='c 'for update

If id is the primary key in the user table, name is the secondary index in the user table. The index of name ='c' under the secondary index is locked first, and then back to the table to lock the primary key index with a primary key index of 9.

Gap lock

A gap lock (Gap for short) is a lock on the gap between index records, or a lock on the gap before the first index record and after the last record. Gap lock is one of the main means to prevent phantom reading, which means that the same transaction executes the same query statement at different times, resulting in different result sets. So how do gap locks prevent phantom reading? In fact, by locking specified gaps, these gaps cannot be inserted into new records, thus preventing the growth of data.

Suppose we execute this statement: select * from user where id > 5 and id

< 9 for update; 由于间隙锁的存在，其他事务如果想要插入 id 在 5 和 9 之间的记录是无法成功的，会被阻塞，直到间隙锁释放。比如想要插入 id 为 6 的记录，就会阻塞，如下图所示(省略部分无关的字段)。间隙锁跨越的间隙可能为一个值、多个值、甚至为空值。 通过上图我们可以知道： (5, 7]：id 为 5 的索引记录与 id 为 7 的索引记录之间的间隙被间隙锁锁定了 (7, 9]：id 为 7 的索引记录与 id 为 9 的索引记录之间的间隙被间隙锁锁定了 因为这两个间隙被间隙锁锁定了，所以在这两个间隙之间的记录是无法插入，只有等间隙锁释放之后才可以插入。我们还要注意到，id 为 7 的记录是被记录锁锁定的，所以在 id 为 7 的记录上执行更新、删除操作时会被阻塞的。 我们上面还说到，间隙锁还在第一条记录的前面和最后一条记录的后面加锁，我们来看看这是什么情况。 假设我们执行此条语句：select * from user for update; 因为该语句没有使用索引，所以会进行全表扫描。将扫描到的每一条记录都加上记录锁，并且将所有的间隙也加间隙锁。最终的加锁情况如下图所示(省略部分无关的字段)：

There are two implicit records in each table: the minimum record (infimum) and the maximum record (supermum)

From the above figure, we can find that the locked interval is as follows:

(- ∞, 5)

(5, 7)

(7, 9)

(9, 10)

(10, 12)

(12, + ∞)

And all records are locked by record locks. This looks like a table lock because any operation on the table (except snapshot reads) is blocked.

However, gap locks are not used in all cases, and they are not used in the following situations:

Isolation levels are RC, RU.

Use a unique index for an equivalent comparison to get an index record. This is because only one record can be obtained by the equivalent comparison of a unique index, and there will be no multiple records, so there will be no inconsistencies in multiple reads.

The main purpose of gap lock is to prevent transactions from inserting records into the gap, and gap locks can coexist, and multiple transactions can acquire locks with the same gap at the same time. There is no difference between shared gap locks and exclusive gap locks, they are exactly the same thing.

Next-Key lock

The Next-Key lock is not a difficult thing to understand, it is essentially a combination of the record lock on the index record and the gap lock between the index record.

When looking up and scanning tables, InnoDB will add record locks to all scanned records, which may be shared locks or exclusive locks. Therefore, row-level locks are actually index record locks.

The second of the two examples of gap locks is actually a Next-Key lock, because the memory enclosed in each parenthesis includes an index record lock and a gap lock, which perfectly fits the definition of Next-Key.

Under the default REPEATABLE READ isolation level, InnoDB uses Next-Key locks when looking up and scanning indexes to prevent phantom reads.

Insert intention lock

The insert intention lock (II Gap for short) is a special gap lock that is used only when inserting a record. This lock indicates the intention to insert. It is completely different from the table-level intention lock mentioned above, the insertion intention lock is a row-level lock, and it is compatible with each other and does not conflict with each other, so multiple transactions can acquire II Gap locks with the same gap at the same time.

Official example:

Suppose there are index records with values of 4 and 7, respectively, and individual transactions try to insert values 5 and 6, respectively. Before obtaining an exclusive lock for inserting rows, each transaction uses an insert intent lock to lock the gap between 4 and 7, but do not block each other, because the rows are conflict-free.

Inserting the intention lock only conflicts with the gap lock and the Next-Key lock. Because the main function of the gap lock is to prevent the occurrence of illusion, and the insertion intention lock needs to be obtained before the insertion operation is executed, and there is a conflict between the insertion intention lock and the gap lock, which can block the insertion operation. so the gap lock can prevent the occurrence of false reading.

AUTO-INC lock

AUTO-INC lock is also called self-increment lock (AI lock for short). It is a special table lock that is used when inserting data into a table with an AUTO_INCREMENT column. When there are self-incrementing columns in the table in which the data is inserted, the database needs to automatically generate self-increment, and it will acquire the AUTO-INC lock of the related table before it is generated. The insert operations of other transactions will be blocked, which ensures the uniqueness of self-increment.

The AUTO-INC lock has the following characteristics:

Each table has its own AUTO-INC lock and is not compatible with each other.

It does not follow the two-stage lock protocol, which is not released when the transaction is committed, but after the execution of the insert statement is completed, which improves the performance of concurrent insertion.

Once the self-increment is allocated, it will be added by one. Even if it is rolled back, the self-increment will not be reduced by one, but will continue to use the next value, so the self-increment may not be continuous.

Because the table lock is used when inserting, it is bound to cause a degradation in the performance of concurrent inserts. Therefore, InooDB provides an algorithm for controlling self-increasing locking with an innodb_autoinc_lock_mode configuration item that allows the user to choose how to trade off between a predictable sequence of automatic incremental values and the maximum concurrency of the insert operation.

There are three options for this configuration:

0: using the traditional locking mode, the concurrency performance is the worst.

1: the default mode.

2: the highest concurrency performance, but there is no guarantee that the self-increment in the same insert statement is continuous.

To learn more about this configuration, check out this document in MySQL.

Summary

The compatibility of the four row locks for InnoDB, as shown in the following table:

Note: the first column represents the lock already held, and the first row represents the lock to be acquired.

A conclusion can be drawn from the table:

Inserting an intention lock does not affect other transactions to acquire other locks.

Inserting intention locks is affected by Gap locks and Next-Key locks. If a transaction wants to acquire the insert intention lock of the specified gap, then the Gap and Next-Key locks in that gap must not be held by other transactions, otherwise, they will be blocked.

If we remove the effect of inserting an intention lock, the compatibility table is as follows:

From the table, we can draw the following conclusions:

When the locks of both transactions involve record locks, there will be conflicts.

Gap locks do not conflict with other locks (not including insertion intention locks).

This is the end of the content of "what locks in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.