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

Today, I will talk to you about the introduction of MySQL InnoDB locks and the example analysis of what kind of locks are added to different SQL statements. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The text of the article begins.

"what kind of lock to add" is related to the following factors

Isolation level of the current transaction

Is SQL consistent unlocked read (consistent nonlocking read) or DML (INSERT/UPDATE/DELETE) or locked read (locking read)

Whether the index is used when SQL executes, and the type of index used (primary key index, secondary index, unique index)

Let's introduce these factors first.

1. Isolation level (isolation level)

Database transactions need to meet the ACID principle, and "I" means isolation, which requires that two transactions do not affect each other and cannot see the data that has not been committed by the other party. The database has four isolation levels (isolation level), according to the isolation from weak to strong (corresponding, performance and concurrency from strong to weak) are

Read Uncommitted . Hereinafter referred to as RU

Read Committed . Hereinafter referred to as RC

Repeatable Read (the default isolation level for MySQL). Hereinafter referred to as RR

Serializable

"I" means isolation is achieved through the locking mechanism. The mention of locks involves deadlocks, and it should be made clear that the possibility of deadlocks is not affected by the isolation level, which changes the behavior of read operations, while deadlocks are caused by write operations.

-- View the global and session isolation levels of transactions (MySQL 5.7.19 and before using tx_isolation) select @ @ global.transaction_isolation, @ @ session.transaction_isolation;-- set the global transaction isolation level to repeatable read set global transaction isolation level repeatable read-- set the current session transaction isolation level to read uncommitted set session transaction isolation level read uncommitted

For detailed syntax for setting and viewing transaction isolation levels, see https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

Consistent unlocked read and locked read

InnoDB has two different types of SELECT, normal SELECT and locked read SELECT. There are two kinds of lock-in SELECT, namely, SELECT. FOR SHARE and SELECT... FOR UPDATE; lock read SELECT other than the normal SELECT.

Do different SELECT need to be locked?

Use consistent unlocked reads and no locks when using normal SELECT

Lock read SELECT use lock read, lock

In addition, when DML (INSERT/UPDATE/DELETE), you need to query the records in the table first. At this time, lock read and lock are also used.

The FOR SHARE syntax was added in MySQL 8.0, and FOR SHARE and LOCK IN SHARE MODE are equivalent, but FOR SHARE is used instead of LOCK IN SHARE MODE, but LOCK IN SHARE MODE is still available for backward compatibility.

1. Consistent unlocked read (consistent nonlocking read)

InnoDB uses multi-version concurrency control (MVCC, multiversion concurrency control) to increase the concurrency of read operations. MVCC means that InnoDB uses point-in-time snapshots to obtain query results and does not set any locks on the accessed table during reading, so transaction T2 is free to modify the data read by transaction T1 at the same time as transaction T1 reads. This read operation is called consistent unlocked read. The read operation here is normal SELECT.

MVCC is not required when isolation levels are RU and Serializable, so MVCC and consistent unlocked reads exist only when RC and RR are present.

Is there any difference between consistent unlocked reads at the two isolation levels RC and RR? Yes, the snapshots are taken at different times under the two isolation levels.

In RC, every consistent read within the same transaction always sets and reads its own snapshot. In other words, each time you read it, you take a snapshot of * again (therefore, you can always read the data submitted by * during RC).

In the case of RR, all consistent reads within the same transaction always read the same snapshot, which is taken when performing * consistent reads of the transaction.

2. Lock read (locking read)

If you query data first, and then insert / update related data within the same transaction, ordinary SELECT statements will not give you enough protection. Other transactions can update / delete the rows of data you just checked out. InnoDB provides two kinds of locked reads, namely: SELECT... FOR SHARE and SELECT... FOR UPDATE . Both of them provide additional security.

Both lock reads set exclusive or shared locks on each index record (index record) encountered during the search (note: not in the final result set). In addition, if the current isolation level is RR, it also sets exclusive or shared gap lock on the gap in front of each index record (exclusive and shared gap lock are no different, they are equivalent).

After reading the background introduction, let's take a look at the various locks provided by InnoDB.

3. 8 different types of locks provided by InnoDB

There are eight lock types in InnoDB, of which Intention Locks and AUTO-INC Locks are table-level locks, and the rest are row-level locks. In addition, although shared lock or exclusive lock (Shared and Exclusive Locks) is also one of the eight lock types, it is not a specific lock, it is a lock mode that is used to "decorate" various other types of locks.

MySQL5.7 and previously, you can view the lock of a transaction through information_schema.innodb_locks, but you can only see the lock that blocks the transaction; if the transaction is not blocked, you can't see the lock of the transaction in the table.

MySQL8.0 removes information_schema.innodb_locks and adds performance_schema.data_locks, so you can check the locks of the transaction through performance_schema.data_locks. Unlike MySQL5.7 and before, performance_schema.data_locks can see not only the locks blocking the transaction, but also the locks held by the transaction, that is, even if the transaction is not blocked, you can still see the locks held by the transaction As mentioned in the paragraph * *, performance_schema.data_locks doesn't always see all the locks. The change in the table name also reflects that the 8.0 performance_schema.data_locks is more generic, and even if you use a storage engine other than InnoDB, you can still see the locking of transactions from performance_schema.data_locks.

The column LOCK_MODE of performance_schema.data_locks indicates the type of lock, and when we introduce the various locks below, we also indicate the LOCK_MODE of the lock.

1. Shared lock or exclusive lock (Shared and Exclusive Locks)

It is not a type of lock, but a mode for various other locks, each with either shard or exclusive mode.

When we talk about shared locks (S locks) or exclusive locks (X locks), we generally refer to shared locks on lines or exclusive locks on rows. It should be noted that there are also shared locks and exclusive locks in table locks, that is, S locks on tables and X locks on tables. In addition to these two kinds of locks, table locks also include intention shared locks (Shard Intention Locks) and intention exclusive locks (Exclusive Intention Locks), namely IX locks, which will be mentioned below. Table locks, in addition to these four, there are other types of locks, these locks are used when accessing the meta-information of the table (create table/alter table/drop table, etc.), this article does not discuss these locks, details can be seen: commonly used SQL statement MDL plus lock source code analysis.

The compatibility of shared locks (S locks) and exclusive locks (X locks) on data row r is as follows:

Assuming that T1 holds an S lock on data row r, when T2 requests a lock on r:

If T2 requests an S lock on r, T2 immediately acquires the S lock. Both T1 and T2 hold S locks on r at the same time.

If T2 requests an X lock on r, T2 cannot acquire the X lock. T2 must wait until T1 releases the S lock on r.

Assuming that T1 holds an X lock on r, when T2 requests a lock on r:

When T2 requests any type of lock on r, T2 cannot acquire the lock. At this point, T2 must wait until T1 releases the X lock on r.

2. Intention lock (Intention Locks)

Watch lock. It means that the table lock is already held and the row lock for some / some rows on the table will be acquired later. There are two modes: shard or exclusive.

LOCK_MODE is: IS or IX.

Intention locks are used to lock hierarchical data structures, and the parent-level lock must be acquired before the child-level lock can be acquired. You can look at the hierarchy of InnoB this way: all data in InnoDB is a collection of schema, schema is a collection of tables, and tables are collections of rows. The intention lock is that before acquiring the lock of the child level (data row), you need to acquire the lock of the parent level (table).

The purpose of the intention lock is to tell other transactions that a transaction has locked or is about to lock a row of data. Before a transaction acquires a row lock, it must first acquire the intention lock, that is:

Before a transaction can acquire an S lock on a row, the transaction must first acquire an IS lock on the table or a stronger lock on the table.

Before a transaction can acquire an X lock on a row, the transaction must first acquire an IX lock on the table.

When a transaction requests a lock, if the requested lock is compatible with the existing lock, the transaction can successfully obtain the requested lock; if the requested lock conflicts with the existing lock, the transaction cannot obtain the requested lock.

The compatibility matrix for table-level locks (table-level lock) is as follows:

For the compatibility matrix above, be sure to note two points:

In the compatibility matrix above, S is a table (not row) shared lock and X is a table (not row) exclusive lock.

The intention locks IS and IX are compatible with any row lock (that is, compatible with the row's X lock or the row's S lock).

Therefore, the intention lock will only block the full table request (for example: LOCK TABLES... WRITE), it doesn't block anything else. Because of LOCK TABLES. WRITE needs to set an X-table lock, which will be blocked by the intended lock IS or IX.

InnoDB allows table and row locks to coexist and uses intent locks to support multi-granularity locks (multiple granularity locking). How do intention locks support multi-granularity locks? let's give an example as follows

T1: SELECT * FROM T1 WHERE iTunes 1 FOR UPDATE

T2: LOCK TABLE t1 WRITE

When T1 executes, it needs to acquire the X lock of the row of iNo.1, but before T1 acquires the row lock, T1 must first acquire the IX lock of T1 table, and there is no conflict, so T1 successfully acquires the IX lock of T1 table, and then successfully acquires the X lock of iSecret1 row; when T2 executes, it needs to acquire the X lock of T1 table, but T2 finds that the IX lock has been set on T1 table, so T2 is blocked (because the X lock of table is not compatible with the IX lock of table).

Assuming that there is no intention lock, then:

When T1 executes, you need to acquire the X lock of the row of iExam1 (no longer need to acquire the intention lock of T1 table) When T2 executes, it needs to acquire the X lock of T1 table. Can T2 acquire the X lock of T1 table? T2 cannot know immediately. T2 has to traverse every data row of table T1 to check whether the existing lock on a row conflicts with the X lock of the T1 table that it is about to set. This judgment method is really inefficient, because the entire table needs to be traversed.

Therefore, the intention lock is used to realize the quick judgment of whether the table lock conflicts or not. The intention lock is to coordinate the relationship between the row lock and the table lock, or it can be said that the intention lock is to coordinate the relationship between the read-write lock on the table and the read-write lock on the row (that is, different granularity locks).

3. Index record lock (Record Locks)

The so-called row lock locks the index record. A row lock is an index record lock, and the so-called "locking a row" or "setting a lock on a row" is actually setting a lock on a specific index record (or index entry, index entry) of an index. There are two modes: shard or exclusive.

The LOCK_MODE are: SMagneRECHNOTROGAP or XJRECHNOTROGAP.

A row lock is an index record lock, and an index record lock always locks an index record, even if no index is defined on the table. When the table does not define an index, InnoDB automatically creates a hidden clustered index (the index name is GEN_CLUST_INDEX), which is used to perform record lock.

4. Clearance lock (Gap Locks)

A lock on the gap between index records that locks records that do not yet exist, that is, the gap between index records. There are two modes: shard or exclusive, but there is no difference between the two modes. They are equivalent.

The LOCK_MODE are: SmallGap or XBGAP.

Gap lock can coexist (co-exist). Transaction T1 holding gap lock on a gap does not prevent transaction T2 from holding gap lock on the same gap at the same time. There is no difference between shared gap lock and exclusive gap lock, they do not conflict, they both perform the same function.

The gap locked by gap lock can be the gap in front of * index records, or the gap between two adjacent index records, or the gap behind an index record.

The index is organized by the B+ tree, so the index is arranged in order from small to large. When searching for a given record on the index record, InnoDB will add gap lock to the * * records that do not meet the query criteria to prevent the insertion of new records that meet the conditions.

The figure above shows that when InnoDB scans on the index, it finds the record of c2q11, and then InnoDB scans again. It finds that the next record is c2q18, which does not meet the condition. InnoDB encounters * records 18 that do not meet the query criteria, so InnoDB sets gap lock on 18, and this gap lock locks the interval (11,18).

Why do you need gap lock? the only purpose of gap lock is to prevent other transactions from inserting data rows into gap. It is used to prevent the generation of phantom rows (phantom row) when the isolation level is RR; when the isolation level is RC, gap lock is disabled during search and index scanning, and gap lock is valid only when foreign key constraint checks and repeated key checks, which is why there will be phantom rows in RC.

How does gap lock prevent other transactions from inserting data rows into gap? Look at the picture below.

The index is organized by the B + tree, so the index is arranged in order from small to large, and if you want to insert 10, the only place you can insert is the red interval in the image above. When inserting between 10 and 10, we think it is inserted after 10 of the * face. If the red interval is blocked, then no other transactions can be inserted into 10.

Question 1: when T2 is to be inserted at 10:00, where is it allowed to be inserted in the image above (note: the index is ordered)?

Answer: (8, 10) and (10, 10, 11). If we insert between 10 and 10, we think it is inserted after the 10 of *.

As long as the red interval in the picture is blocked, T2 can no longer be inserted into 10. What are the characteristics of the above two intervals? Yes, these two intervals are: the gap in front of each record that meets the condition, and the gap in front of a record that does not meet the condition. InnoDB uses the next key lock (Next-Key Locks) or gap lock (Gap Locks) to block this interval.

Question 2: gap lock is used to block the insertion of new data rows, so will T2, insert into g values ('zonal, 9) be blocked? What about inserting ('zonal, 8), (' zonal, 10), ('zealous, 11)?

A: in the figure above, the gap lock for the update setting of T1 is (8, 10) and (10 insert intention lock 11), while the range of insert intention lock is (insert value, a downward index value). For a detailed description of insert intention lock, see 6. 0 below. Insert the intention lock (Insert Intention Locks).

So, for the above inserted values, the resulting insert intention lock is as follows:

When inserting ('zonal, 8), insert intention lock is (8, 10)-- conflict, overlapping with gap lock (8, 10)

When inserting ('zonal, 9), insert intention lock is (9,10)-- conflict, overlapping with gap lock (8,10)

When inserting ('zonal, 10), insert intention lock is (10, 11)-- conflict, overlapping with gap lock (10, 11)

When inserting ('zonal, 11), insert intention lock is (11,15)-- no conflict

Is this the case? look at the picture below.

Yes, consistent with our analysis, in order to see more clearly, we listed the results as follows

Question 3: "gap is the solution to the phantom row problem". Insertion will lead to phantom row, but updates will also produce phantom row.

For example, T1 and T2PowerT1 in the figure above update the lines of all iTunes 8 to 108, T2, and update the rows of iTunes 15 to 8. If T2 is not blocked, isn't there an extra line in the WHERE condition of T1, that is, there is phantom row in T1?

A: nice question. Let's analyze what locks are added to T1 and T2 respectively.

Locks added by T1: next-key lock on idx_i (5,8], 'baked on PRIMARY, and gap lock on idx_i (8, 10)

Locks added by T2: next-key lock (11,15) on idx_i, 'fags on PRIMARY, and gap lock (15108) on idx_i. * this gap lock is because T1 adds a new value of 108 to idx_i.

According to the above analysis, the locks of T1 and T2 do not overlap, that is, the result of our analysis is that T2 will not be blocked.

However, the picture above clearly shows that T2 is indeed blocked because T2 insert intention lock and T1 gap lock (8,10) conflict. It's strange that T2 is an update statement, why is there an insert intention lock?

I don't know the exact reason, because I couldn't find the document to say it. According to my inference, update... When set successfully finds the result set and then performs the update, insert intention lock is set on the new value that is about to be updated into the row (if the result set is not found, there is no insert intention lock), so T2 sets insert intention lock (8,10) on the new value 8 on idx_i. Eventually, T2 insert intention lock (8,10) collided with T1 gap lock (8,10), and T2 was blocked.

So, update... When set successfully finds the result set, index record lock and insert intention lock are set on the new values that are about to be updated into the row. As mentioned earlier, the range of insert intention lock is (insert value, next value), if T2 is update g set item9 where item15; then update. If the new value set by set is 9, then T2 insert intention lock is (9,10), which still conflicts with T1 gap lock (8,10), right? Yes, it is. Interested students can have a try.

5. Next key lock (Next-Key Locks)

Next-key lock is a combination of (the index record lock on the index record) + (the lock on the gap in front of the index record). It locks the index record and the gap in front of the index record. There are two modes: shard or exclusive.

LOCK_MODE are: s or X.

When InnoDB searches or scans an index, the lock InnoDB sets on the index record it encounters is next-key lock, which locks the index record itself and the gap in front of the index record ("gap" immediately before that index record). That is, if transaction T1 has a next-key lock on index record r, T2 cannot insert a new index record (gap immediately before r in the index order) in the gap immediately before r.

Next-key lock will also be added to "supremum pseudo-record". What is supremum pseudo-record? It is a pseudo record (pseudo-record) in the index, representing the possible * * values in this index. The next-key lock set on supremum pseudo-record locks the "possible * values in this index" and the gap in front of this value. "possible * values in this index" do not exist in the index, so The next-key lock actually locks the gap before the possible * * value in this index, that is, the gap after the actual * * value that currently exists in this index. For example, in the following figure, the next-key lock on supremum pseudo-record locks the interval (18, positive infinity), and it is this next-key lock that prevents other transactions from inserting larger values such as 19100.

Next-key lock on supremum pseudo-record locks the gap "larger than the current actual value in the index", "bigger than", "bigger than bigger"

6. Insert intention lock (Insert Intention Locks)

A special kind of gap lock. After the INSERT operation inserts successfully, the index record lock is set on the newly inserted row, but before inserting the row, the INSERT operation first sets the insert intention lock on the gap between the index records, and the range of the lock is (insert value, a downward index value). There are two modes: shard or exclusive, but there is no difference between the two modes. They are equivalent.

The LOCK_MODE are: s _ Magna _ GAPP _ INSERTIVITION or X _ Magna _ GAPP _ INSERTIMETONINTION.

Insert intention lock emits the intention to insert in this way: when multiple transactions insert concurrently into the same index gap, multiple transactions do not have to wait for each other.

Assuming that index records with values 4 and 7 already exist, transactions T1 and T2 try to insert index values 5 and 6 respectively, and both transactions set insert intention lock before getting the index record lock being inserted on the row, so T1 insert intention lock (5, 7) and T2 insert intention lock (6, 7) do not block each other, although the two insert intention lock overlap.

If the ranges of gap lock or next-key lock overlap with insert intention lock, gap lock or next-key lock blocks insert intention lock. This feature is used to solve phantom row problems when the isolation level is RR; although insert intention lock is also a special gap lock, unlike ordinary gap lock, insert intention lock does not block each other, which greatly provides concurrency at insert time. The summary is as follows:

Gap lock blocks insert intention lock. In fact, gap lock exists only to block insert intention lock

Gap lock will not block each other.

Insert intention lock will not block each other.

Insert intention lock will not block gap lock.

Before INSERT inserts a row, insert intention lock is first set on the gap between index records, and after the operation is successfully inserted, index record lock is set on the newly inserted row.

We use the following three diagrams to illustrate the scope and features of insert intention lock

The above illustration shows that T1 sets gap lock (13,18) and T2 sets insert intention lock (16,18). The ranges of the two locks overlap, so T1 gap lock (13,18) blocks T2 insert intention lock (16,18).

The figure above shows that T1 sets insert intention lock (13,18) and index record lock 13t T2 sets gap lock (17,18). Although T1 insert intention lock (13,18) and T2 gap lock (17,18) overlap, T2 is not blocked. Because insert intention lock does not block gap lock.

The figure above shows that T1 sets insert intention lock (11,18), index record lock 11t T2 sets next-key lock (5,11], index record lock 'b' on PRIMARY, gap lock (11,18). At this point: T1 index record lock 11 and T2 next-key lock (5,11) collide, so T2 is blocked.

7. Self-increasing lock (AUTO-INC Locks)

Watch lock. When inserting a row into a table with an AUTO_INCREMENT column, the transaction needs to first acquire the AUTO-INC table-level lock of the table so that it can generate continuous self-increment. Request the lock at the beginning of the insert statement and release the lock at the end of the insert statement (note: after the end of the statement, not after the end of the transaction).

You might think that in daily development, all of our tables use AUTO_INCREMENT as the primary key, so the lock is used very frequently. But it may not be what you think. Before introducing AUTO-INC table-level locks, let's take a look at the closely related SQL statements and the system variable innodb_autoinc_lock_mode

INSERT-like statement

Insert

Insert... Select

Replace

Replace... Select

Load data

Plus, simple-inserts, bulk-inserts, mixed-mode-inserts

Simple-inserts

The number of records to be inserted can be determined in advance (the statement can be determined in advance when the statement is initially processed) so the number of self-increment required can be determined in advance.

Including: single-row or multiple-row insert, replace without embedded subqueries. But insert. On duplicate key update is not

Bulk-inserts

The number of records to be inserted cannot be determined in advance, so the number of self-increment required cannot be determined in advance.

Including: insert... Select, replace... Select, load data

In this case, InnoDB can only allocate self-increment one line at a time. Whenever a row of data is processed, InnoDB assigns a self-increment to the AUTO_INCREMENT column of that row

Mixed-mode-inserts

Is also a simple-inserts statement, but specifies some (but not all) values for self-incrementing columns. That is, the number of records to be inserted can be known in advance, but the value of the partial self-increment column is specified.

INSERT INTO T1 (C1 and c2) VALUES (1 NULL,'b'), (NULL,'d'), (5)

INSERT... ON DUPLICATE KEY UPDATE is also mixed-mode, and at worst, it is INSERT followed by a UPDATE, at this point, the value assigned to the AUTO_INCREMENT column may or may not be used during the UPDATE phase.

Take another look at the system variable innodb_autoinc_lock_mode, which has three candidate values 0J1 and 2

Before 8.0.3, the default value is 1, that is, continuous locking mode (consecutive lock mode); and 8.0.3 and later, the default value is 2, that is, interleaved locking mode (interleaved lock mode).

a. When innodb_autoinc_lock_mode=0, INSERT-like statements need to acquire AUTO-INC table-level locks

b. When innodb_autoinc_lock_mode=1, if the number of rows inserted can be determined in advance, there is no need to acquire AUTO-INC table-level locks; if the number of rows inserted cannot be determined in advance, you need to acquire AUTO-INC table-level locks. Therefore, both simple-inserts and mixed-mode inserts do not need AUTO-INC table-level locks. In this case, lightweight mutex mutual exclusion is used to gain self-added value; bulk-inserts needs to acquire AUTO-INC table-level locks.

c. When innodb_autoinc_lock_mode=2, AUTO-INC table level locks are no longer used at all

The version of our production database is 5.6.23-72.1, pure database autoinception lockout locks 1, and we mostly use simple-inserts in our daily development, so we don't use AUTO-INC table-level locks at all, so AUTO-INC table-level locks are not used much.

LOCK_MODE:AUTO-INC table-level locks are not used much, and AUTO-INC locks are released at the end of the statement, which is difficult to see in performance_schema.data_locks, so they are not captured. Interested students can use INSERT. Try SELECT capture.

8. Spatial index (Predicate Locks for Spatial Indexes)

We seldom use MySQL's spatial index. Therefore, this article ignores this type of lock

At this point, we are done with the eight types of locks in MySQL InnoDB. Let's end with an example of eight types of introductions.

T1 is executed first, transaction ID is 8428th T2 and transaction ID is 8429.

The figure above illustrates:

Any transaction needs to add a table-level lock intention lock before locking the row, that is, the IX of the third row and the IX of the * row.

Idx_c is the secondary index, and InnoDB encounters clock222 when scanning idx_c, so next-key lock is added to idx_c, that is, the X of the fourth line. Next-key lock is index record lock+gap lock, so this next-key lock locks the index record on idx_c with a value of 222and the gap in front of the 222s, that is, the gap (22,222).

Idx_c is a secondary index, and when you add index record lock to any index other than the primary key index, you need to add index record lock to the primary key index of the row, so you add index record lock on the PRIMARY, that is, the fifth line of the XJRECHINTION gap.

InnoDB scanned cantilever 2222 again, which is idx_c, * index records that do not meet the index scan conditions, so InnoDB added gap lock lock on gap lock,c=2222 to "gap in front of 2222 on idx_c", which should have been (222,2222), but T1 is about to insert cantilever 224 on idx_c, so the range of gap lock lock on cantilever 2222 is (224,2222). That is, the Xgrad gap in the sixth line.

The fact that InnoDB is about to insert cend224224 on idx_c is also not satisfied with cqm222, so InnoDB adds gap lock to cym224, which locks the gap in front of 224, that is, (2222224), that is, the XMague gap in the seventh line.

After T2 succeeds in INSERT, it will add index record lock to the newly inserted row, but before T2 inserts, the first thing to do is to get the table-level lock intention lock and set the insert intention lock of each index of the table. The range of the lock is (insert value, a downward index value), so the insert intention lock range on setting idx_c is (226,2222), which overlaps with the gap lock range of the sixth row of transaction T1 (224,2222). Therefore, transaction T2 is blocked and T2 must wait until T1 releases the gap lock of the sixth line.

Not all locks of T2 can be seen in the performance_schema.data_ locks table. For example, T2 also has to set insert intention lock on iux_b, but this lock is not available in performance_schema.data_locks. For information about which locks are shown in performance_schema.data_locks, see the * * paragraph of this article.

List these locks and their ranges as shown in the following figure

What kind of locks are added to different SQL?

OK, we have learned about the different types of locks in InnoDB, so what kind of locks are added to different SQL statements?

Let's think about it in the simplest way, what do we do with locks? What the lock needs to do is to achieve the purpose of transaction isolation, that is, two concurrent transactions T1 and T2, if T1 is modifying some rows, T2 must be blocked when T2 concurrently reads / modifies / inserts rows that meet T1 query conditions, which is the root cause of lock existence. Index record lock, gap lock, and next-key lock are all means of implementation, which enable the lock not only to achieve its goal, but also to achieve concurrency. So, when we consider what locks are added to the SQL in transaction T1, think that when T1 executes, if the concurrent transaction T2 does not touch the rows of T1, T2 does not need to be blocked. If T2 is to read / modify / insert rows that meet T1 conditions, T2 has to be blocked by T1. The specific implementation of T1 blocking T2 is that T1 adds index record lock to existing lines so that T2 can not touch existing lines, and T1 adds gap lock to non-existing lines so that T2 cannot insert new rows that meet the conditions.

As we said earlier, "what kind of lock to add" is related to the following factors

Isolation level of the current transaction

Is SQL consistent unlocked read (consistent nonlocking read) or DML or locked read (locking read)

Whether the index is used when SQL executes, and the type of index used (primary key index, secondary index, unique index)

Let's take a look at what locks are added when using different indexes under different isolation levels. Before discussing, let's eliminate the situations that do not need to be discussed.

First, normal SELECT uses consistent unlocked reads, so there are no locks at all. No need to discuss

Furthermore, as developers, we almost never use isolation levels RU and Serializable. These two isolation levels do not need to be discussed.

So, all that's left is to read SELECT or DML (INSERT/UPDATE/DELETE) statements for a given lock, what kind of locks will be added when using different types of indexes at different isolation levels? Give the answer directly, and the locking principle is as follows

(1) when RR, if a non-unique index is used for searching or scanning, next-key lock is set on each index record scanned.

Here, "every index record scanned" refers to every record encountered in the search when scanning the index used in the execution plan. It doesn't matter whether the WHERE condition excludes a row of data. InnoDB doesn't remember the exact WHERE condition, and InnoDB stubbornly recognizes only the index range it scans (index range).

You may think that InnoDB is unreasonable in setting locks, regardless of some lines excluded by the WHERE condition, which greatly increases the scope of locks. However, when we understand the process when MySQL executes SQL, it will be easy to understand. MySQL's execution plan selects only one index and uses one index to scan. The process for MySQL to execute the SQL statement is that the InnoDB engine performs the index scan, and then returns the result to the MySQL server. The MySQL server will evaluate other query conditions other than the index condition to get the final result set. When locking, only the index scanned by InnoDB is considered, and other WHERE conditions evaluated by the MySQL server are not considered. Of course, MySQL uses multiple indexes at the same time when using index_merge optimization, but it's not special to set locks at this time, and for each index used, InnoDB sets next-key lock on every index record it scans.

The added lock is usually next-key lock, which locks the index record itself and the gap in front of each index record, thus preventing other transactions from inserting records into the gap immediately before the index record.

If the secondary index (secondary index) is used in the search and the row lock is set on the secondary index, InnoDB also sets the lock on the corresponding clustered index; when the table does not define a clustered index, InnoDB automatically creates a hidden clustered index (the index name is GEN_CLUST_INDEX), and sets it to this automatically created index when you need to set a lock on the clustered index.

(II), RR, if the unique search condition of the unique index is used, InnoDB only sets index record lock on the index records that meet the conditions, and does not lock the gap in front of the index record; if you use the unique index for range search, it will still lock the gap in front of each scanned index record, and then set a lock on the clustered index.

(3) when RR, set gap lock or next-key lock on * * index records that do not meet the search criteria.

In general, gap lock is set for equivalence conditions and next-key lock is set for range conditions. This gap lock or next-key lock locks the gap in front of * * records that do not meet the search criteria.

When RR, INSERT must first set insert intention lock for each index on the table before inserting a new row.

The range of each insert intention lock is (the value of an index column of the row to be inserted, the index value downward from the value given by the row to be inserted). The insert intention lock can be seen in performance_schema.data_locks only if the insert intention lock conflicts with a gap lock or next-key lock.

(5) when RC, InnoDB sets index record lock only on lines that fully meet the WHERE condition.

(6) when RC, gap lock is disabled.

Because of this, there is no gap lock or next-key lock in RC. Why is that? Let's think about it. Gap lock is used to solve the phantom row problem. New rows cannot be inserted in the interval blocked by gap lock, because the inserted insert intention lock conflicts with gap lock, thus preventing the insertion of new rows. However, the isolation level RC allows phantom row, so gap lock is disabled when RC.

(VII), RR or RC, for primary keys or unique indexes, shared next-key lock or shared index record lock is set on duplicate index records when there is a duplicate key error (duplicate-key error). This can lead to a deadlock.

Suppose T1, T2, T3 three transactions, T1 already holds X lock, T2 and T3 have repeated key errors, so T2 and T3 are waiting to acquire S lock, at this time, when T1 rollback or commit releases X lock, T2 and T3 both acquire S lock, and T2 and T3 both request X lock, "T2 and T3 both hold S lock and both request X lock", and deadlock occurs.

All right, the rules are all listed, and it's time to put it into practice. When we show the lock below, we also point out the isolation level currently in use, the index on the table, and the SQL statement of the transaction.

Practice 1: indexes cannot be used when searching, that is, when a full table scan is performed, InnoDB locks all rows of the table

The figure above shows that when the search condition cannot use the index, InnoDB has to lock all rows of the table. Therefore, the index is so important that when querying, it can speed up the query; when updating, in addition to quickly finding the specified row, it can also reduce the range of locked rows and improve the concurrency of insertion.

Practice 2: the difference between unique index and non-unique index, equivalent query and range query locking

When searching using a unique index as an equivalent query, InnoDB only needs to add index record lock; when using a unique index for a range query or when using a non-unique index for any query, InnoDB needs to add next-key lock or gap lock.

Example 1 demonstrates that when searching or scanning with a non-unique index idx_c, InnoDB locks the index itself, as well as the gaps in front of the index records, that is, next-key lock: X and gap lock: XMagee gap. Next-key lock locks both the index record itself and the gap in front of the index record, while gap lock only locks the gap in front of the index record. When an equivalent condition, set gap lock on an index record that does not meet the condition.

Example 2 demonstrates the unique search condition for using a unique index iux_b, that is, when performing an equivalent lookup using a unique index, InnoDB only needs to lock the index itself, that is, index record lock: X, REC_NOT_GAP, and does not lock the gap in front of the index.

Example 3 demonstrates that when using a unique index iux_b for range scanning, you still need to lock every index record scanned and lock the gap in front of each index record, that is, next-key lock: X. When a range condition, set next-key lock on an index record that does not meet the condition.

Practice 3: different locking at different isolation levels

Regardless of the isolation level, when the SQL statement is executed, the index scan is performed by InnoDB, and then the result set is returned to the MySQL server, and the MySQL server evaluates the query conditions other than the index condition to get the final result set.

In the figure above, the same SQL is performed under different isolation levels. Regardless of the isolation level, index record lock on PRIMARY will always be added, and we won't discuss it. On idx_b, when the isolation level is RC, InnoDB adds index record lock, that is, XGrai Reclamation Nottle gap, and when the isolation level is RR, InnoDB adds next-key lock, that is, X. Note: there is no gap lock or next-key lock in RC.

The figure above illustrates that the isolation level of the transaction also affects which lock is set. As we said earlier, gap lock is used to block phantom row, while RC allows phantom row, so gap lock is disabled when RC. Therefore, in the figure above, gap lock or next-key lock is not set on the index when RC.

Practice 4: locking is also needed when operating index records that do not exist

In the figure above, there is no index record for baked 266 on idx_b, so does it need to be locked when updating the record of baked 266? Yes, it also needs to be locked.

On RR, InnoDB sets gap lock or next-key lock on * index records that do not meet the search criteria, regardless of whether bounce 266 exists. In general, gap lock is set for equivalence conditions and next-key lock is set for range conditions. In the above figure is the equivalent condition, so InnoDB sets gap lock, that is, the XPowerGap in the above figure, whose range is (226,266). It is this gap lock that makes it impossible for concurrent transactions to insert values greater than or equal to 2222. When RC, because gap lock is prohibited, gap lock will not be added, and concurrent transactions can insert values greater than or equal to b columns.

The figure above shows that locking is also required when manipulating index records that do not exist.

Practice 5: when you repeat a key error (duplicate-key error), a shared lock will be added. This can lead to a deadlock.

For primary keys or unique indexes, shared next-key lock or shared index record lock is set on duplicate index records when there is a duplicate key error (duplicate-key error). This can lead to a deadlock.

The figure above shows that T1 sets exclusive index record lock on primary key 1. Duplicate key errors occur when T2 and T3 are inserted, so both T2 and T3 set shared next-key lock on primary key 1. As shown in the above figure

If T1 rollback releases its index record lock at this point, the shared next-key lock that T2 and T3 are waiting to acquire succeeds, and then T2 and T3 compete for the index record lock on primary key 1, so T2 and T3 deadlock because they both hold shard next-key lock, and neither side will give up the shared next-key lock they have already got, so no one can get the index record lock of primary key 1.

It is important to be clear that the possibility of a deadlock is not affected by the isolation level, which changes the behavior of read operations, while deadlocks are caused by write operations. Deadlock is not scary. MySQL selects a victim, and then automatically rolls back the victim transaction after the number of seconds specified by the system variable innodb_lock_wait_timeout is reached; starting from MySQL5.7, the new system variable innodb_deadlock_detect (default ON) is added. If this variable is turned on, MySQL will no longer wait, and roll back the victim transaction as soon as the deadlock is detected.

The figure above shows that in the state above, when T1 commit, T1 releases the index record lock on primary key 1, so T2 and T3 are successful in waiting for the shared next-key lock to be acquired, and then T2 and T3 compete for the index record lock on primary key 1, so T2 and T3 deadlock because both of them hold shard next-key lock, and neither side will give up the shared next-key lock they have already got, so no one can get the index record lock of primary key 1.

5. Can you see all the locks in performance_schema.data_locks?

Obviously, performance_schema.data_locks doesn't show all the locks, so what locks does it show? Unfortunately, I didn't find the document to say this, and although the document (https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html) says: "every lock held by a transaction and every lock request blocked by a transaction has a row in the table", many of our examples show that it doesn't show all locks. According to my experiment, I guess that performance_schema.data_locks shows the lock on the index touched by the WHERE condition. "the index touched by the WHERE condition" refers to the index used by the SQL in the actual execution, that is, the index shown by the key column of the SQL execution plan. Because of this, there are no locks in INSERT, but only locks on idx_b in update g set a=a+1 where bounds 22. It needs to be emphasized that I experimented and guessed it myself, and I didn't see it in the document.

Suppose that T1 and T2 transactions operate on the same table and execute T1 first. Although only locks on indexes touched by T1's WHERE condition are displayed in performance_schema.data_locks, locks are actually set on indexes that T1's WHERE condition cannot touch. Although the index idx of the table is not touched by T1, that is, performance_schema.data_locks shows that T1 does not set any locks on the index idx, when T2 performs lock read / insert / update / delete, it touches the index idx,T2 and suddenly finds that T1 is already locked on the index idx.

Let's look at the following three examples

Example 1 of "performance_schema.data_locks cannot see all locks"

The figure above shows that when T1 executes, only after touching index idx_b,T1, you can only see locks on idx_b in performance_schema.data_locks. It seems that T1 does not set any locks on idx_a. However, when T2 execution touches index idx_a, T2 suddenly discovers that T1 has already set index record lock on idx_a.

Example 2 of "performance_schema.data_locks cannot see all locks"

When inserting a new row, the insert intention lock is set first, and then index record lock is set on the inserted row after the insertion is successful.

The figure above shows that T1 inserts a new row, but in performance_schema.data_locks, we can see neither the insert intention lock of T1 setting nor the index record lock of T1 setting. This is because the WHERE condition of T1 does not touch any indexes (T1 does not have a WHERE condition at all), so we do not see these two locks of T1; however, when T2 wants to delete the newly inserted rows of T1, T2 suddenly discovers that T1 has set index record lock on index c2.

Example 3 of "performance_schema.data_locks cannot see all locks"

When you insert a new row, the insert intention lock will not be displayed in performance_schema.data_locks, because the WHERE condition does not touch any indexes at insert time (there is no WHERE condition at insert time).

The figure above illustrates that the insert intention lock of T2 conflicts with the gap lock of T1 when inserting a new row, so we can observe in performance_schema.data_locks that T2 needs to request insert intentin lock when inserting a new row.

After reading the above, do you have any further understanding of the introduction of MySQL InnoDB locks and the example analysis of what kind of locks are added to different SQL statements? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.