MySQL database InnoDB Storage engine lock mechanism (Original: Song Libing)

Original article addressMySQL database InnoDB Storage engine Lock Mechanism

00-Basic Concepts

-Basic lock types

A. Shared lock is also called a read lock.
B. exclusive locks are also called write locks.

Compatibility Matrix of-s and X locks

S x
S +-
X --
+ Compatible,-incompatible

-Lock Granularity

A. Table lock)
B. Row lock)

-Intention lock)

Because the table lock overwrites the row lock data, the table lock conflicts with the row lock. For example:

A. trx1 begin
B. trx1 adds an X lock to T1 to modify the table structure.
C. trx2 begin
D. trx2 to T1One recordApply the S or X lock (the transaction is blocked and the lock is successful ).

Trx1 locks the entire table by operating on the entire table. Therefore, trx2 cannot apply an X or s lock to a single record of T1 to read or repair this record.
Intention locks are introduced to facilitate the detection of conflicts between table-level locks and row-level locks.
A. Intention locks are divided into intention read locks (is) and intention write locks (IX ).
B. The intention lock isTable-Level LockBut it indicates that the transaction is reading or writing a row of records, rather than the entire table.
Therefore, there will be no conflict between intention locks, and the real rush will be checked when a row lock is added.
C. before locking a row of records,First, add an intention lock to the table.. That is, the table intention lock and row lock must be added at the same time.

After the intention lock is adopted, the above example becomes:
A. trx1 begin
B. trx1 adds an X lock to T1 to modify the table structure.
C. trx2 begin
D. Add trx2 to T1IX lock(The transaction is blocked, waiting for the lock to succeed, and then adding an X lock to a row of records .)

-Compatibility Matrix of table locks

Is ix s x
Is ++-
IX ++ --
S +-
X ----
+ Compatible,-incompatible
A. Intention locks do not conflict with each other, because intention locks only represent operations on a row of records. When a row lock is added, the system determines whether a conflict exists.

01-row lock

Intuitively, a row lock is to lock a row of records and prevent other transactions from operating on this row of records. Here is an implicit logic:
A. the insert operation will never be blocked, because the insert operation will not operate on an existing record (insert is not considered here
Duplicate processing ). Is this logic correct? This is related to the user's usage. In some cases, it is acceptable to the user,
In some cases, this is unacceptable to users.

-Phantom read)

If the insert operation is not blocked, phantom read will be generated. The MySQL Manual contains phantom read introduction.
A. MVCC can avoid phantom reading. However, MVCC is only valid for select statements.
Select... [Lock in share mode | for update], the update and delete statements are invalid.
B. The next-key mechanism is adopted to avoid phantom read through locks. The next-key locks the gap between two records,
To prevent insert operations.

-Row lock mode
The row Lock S and X locks are precisely subdivided and called precise mode in the code. These precise modes,
Makes the lock granularity smaller. Conflicts can be reduced.
A. Gap lock: only the gap is locked.
B. Record lock: only lock records.
C. Next-key lock (called ordinary lock in the Code), lock records and gaps at the same time.
D. Insert intention lock, which is used during insertion. Insert an intent lock in the code,
In fact, a lock_insert_intention mark is added to the Gap lock.
The MySQL Manual provides a detailed description of these modes.

-Compatibility Matrix of row lock mode

G I R n (existing locks, including waiting locks)
G ++++
I-++-
R ++ --
N ++ --
+ Compatible.-incompatible. I indicates inserting an intent lock,
G indicates the gap lock, I indicates the inserted intention lock, r indicates the record lock, and N indicates the next-key lock.
The S lock and S lock are fully compatible. Therefore, you do not need to compare the exact mode when determining the compatibility.
Precise mode detection is used between S, X, and X.

This matrix is derived from the code of lock0lock. C: lock_rec_has_to_wait. From this matrix, we can see several features:
A. no conflict exists between insert operations.
B. gap and next-key will block insert.
C. gap and record, and next-key do not conflict
D. Conflicts between record, record, and next-key.
E. The existing insert locks do not prevent any locks to be added.

There are also several questions:
A. Why does inserting an intent lock not block the gap lock? Under certain circumstances, the insert operation may be delayed indefinitely.

Insert operation Delay

B. If no lock is blocked, is this lock necessary?
-Currently, we can see that the lock is used to wake up the waiting thread.
-But this does not mean that after being awakened, you can directly perform the insert operation. You need to determine whether a lock conflict exists again.
C. Can the gap + lock_insert_intention lock be changed directly to the insert_intention lock?
I am still reading it.

-B + tree row lock
InnoDB row locks are not a simple concept of data row locks. It refers to the row lock on each B + tree. It can also be understood
The row lock on the index. Therefore, when you operate a row of records, multiple rows may be locked on different B + trees. For example:
   CREATE TABLE t1(c1 INT KEY, c2 int, c3 int, INDEX(c2));
   INSERT INTO t1 VALUES(1, 1, 1), (3, 3, 3)
   UPDATE t1 c3 = 10 WHERE c2 <= 2 
The update statement locks both secondary index and clustered index.

-Use of row lock mode
Under what circumstances are these row locks used? The MySQL Manual provides a detailed introduction.
A. Next-key is used on the index used by the where condition (specifically, the index used for search ).
In the above example, use next-key lock on index (C2.
B. Record lock is used on indexes not used by the where condition. In the above example, the Cluster Index uses record
Lock. Therefore, the above update statement will simultaneously add next-Key to key 1 with index (C2) and record to primary key 1.
Lock. When another session is inserted concurrently (, 2), (, 2), it can be successful, but (, 2) It will be blocked.

Next-key and record

During the test, we found that select... [For update | lockin share mode] may cause all records to be locked.
When the table is small, select uses the full table scan method. When this method is used, all data is traversed,
Therefore, all data is locked. Although ha_innobase: unlock_row () is called for non-conforming records (),
However, it will not be released at the Repeatable read level. It may be a bug.
C. A and B are also applicable to select... [For update | lock in share mode], update and delete statements.
D. The gap lock is obviously used in the index used by the where condition. Unlike next-key, the gap lock is only applied to the upper boundary (first
More than the matching records. The next-key value is added to all qualified records. Record with condition C2 = 2 in the above example,
Apply a gap lock to C2 = 3.
? When performing a forward query, InnoDB actually adds the next-key lock to the boundary.This may be restricted by implementation.
Currently, gap is used in the following scenarios:
-The supremum record is always a gap lock.
-Reverse query (order by DESC.
-When the equivalence matches an exact key value, the gap lock is applied to the next record.
-When the equivalence matches the prefix of an exact key value, the gap lock is applied to the next record ..
E. insert is usually not locked. Wait only when the gap or next-key lock is applied to other transactions at the insertion point,
To create an insert intention lock. The lock is in the waiting status.

-Impact of isolation level on next-key lock
A. Read uncommitted and read committed do not need to lock the gap. The nexk-key is changed to the record lock.
B. When repeatable reads and serializable, the next-key lock is usually used.
In 2 cases, you do not need to lock the Gap:
-Query a unique value, such as where c1 = 1. C1 is the primary key or unique key, and the query results do not contain null fields.
-When innodb_locks_unsafe_for_binlog is enabled. Here are some questions worth thinking about:
? In this case, the gap lock is applied to delete update to prevent data inconsistency between the master and slave.
When BINLOG is not used, there is no need to apply a gap lock to delete and update.
? When row format BINLOG is used, whether or not the master node will be caused by no gap lock, and the slave does not match.
? Even if innodb_locks_unsafe_for_binlog is set, select... [] Whether a gap lock is allowed.
It mainly works in row0sel. C: row_search_for_mysql () to determine the lock to be added.

02-latency Lock Mechanism
If a table has many indexes, isn't it necessary to add a lot of locks to different B-trees when operating a record?
First, let's look at the status information of a transaction:

   CREATE TABLE t1(c1 INT KEY, c2 INT);
   BEGIN;
   INSERT INTO t1 VALUES(1, 1);
   INSERT INTO t1 VALUES(2, 2);
   SHOW ENGINE INNODB STATUS;

Status information:
   LIST OF TRANSACTIONS FOR EACH SESSION:
   ---TRANSACTION 501, ACTIVE 0 sec
   1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 2


-Implicit lock
Lock is a pessimistic ordered mechanism. It is assumed that a conflict is likely to occur, so the lock is applied when operating data.
If the possibility of conflict is small, most locks are unnecessary.

InnoDB implements a latency lock mechanism to reduce the number of locks, known as implicit lock in code ).
The implicit lock has an important element. The transaction ID (trx_id). The logical process of the implicit lock is as follows:
A. Each record of InnoDB contains an implicit trx_id field, which exists in the B + tree of the Cluster Index.
B. Check whether the transaction is an active transaction (not committed or rolled back) based on the trx_id in the record before you operate a record ).
For an active transaction, the implicit lock is first converted to an explicit lock (that is, a lock is added to the transaction ).
C. check whether there is a lock conflict. If there is a conflict, create the lock and set it to the waiting status. If no conflict exists, skip to E.
D. Wait for the lock to succeed, wake up, or time out.
E. Write Data and write your trx_id to the trx_id field. Page lock ensures the correctness of the operation.

Related code:
A. lock_rec_convert_impl_to_expl () converts an implicit lock into a display lock.
B. lock_rec_lock () is used for locking and test row lock conflicts. Its first parameter indicates whether it is an implicit lock. So special
Note this parameter. If this parameter is set to true, no lock will be applied if there is no conflict.
C. Test the specific content of the row lock conflict in lock_rec_has_wait ()
D. The created waiting lock is lock_rec_enqueue_waiting ()
E. The row lock is lock_rec_add_to_queue ()

-Implicit lock features
A. Lock is applied only when a conflict is likely to occur, reducing the number of locks.
B. Implicit locks are for modified B + tree records, so they are all record-type locks. It cannot be of the gap or next-Key type.

-Use of implicit locks
A. the insert operation only applies to implicit locks and does not require explicit locks.
B. During the query, update and delete directly use the display lock on the index and primary key used for the query, and use the implicit lock on other indexes.
Theoretically, implicit locks can be applied to primary keys. The explicit lock should be used in advance to reduce the possibility of deadlock.
Insert, update, and delete operations on the B + tree are performed on the B + tree of the primary key. Therefore, you can lock the primary key.
Effectively prevent deadlocks.

-Implicit lock on secondary index
As mentioned above, trx_id only exists on the primary key. How can we implement the implicit index on the secondary index?
Obviously, secondary search is performed on the primary key B + tree through the primary key value in the secondary index. This overhead is very large.
InnoDB has an optimization for this process:
A. Each page has a max_trx_id. The maximum transaction ID is updated every time the secondary index record is modified.
B. when determining whether to change the implicit lock to an explicit lock, first set max_trx_id of the page and the minimum trx_id of the transaction list.
Comparison. If max_trx_id is smaller than the minimum trx_id in the transaction list, you do not need to convert it to the display lock.

The code is in lock_sec_rec_some_has_impl_off_kernel ().
  /* Some transaction may have an implicit x-lock on the record only
  if the max trx id for the page >= min trx id for the trx list, or
  database recovery is running. We do not write the changes of a page
  max trx id to the log, and therefore during recovery, this value
  for a page may be incorrect. */

  if (page_get_max_trx_id(page) < trx_list_get_min_trx_id()
      && !recv_recovery_is_on()) {
     return(NULL);
  }

03-lock implementation

-Lock Storage
A. Table-> locks stores all table-level locks of a table.
B. lock_sys-> rec_hash stores row locks of all tables. The hash value is calculated based on (spaceid, pageno.
C. Trx-> trx_locks stores all the locks of transactions, including table-level locks and row-level locks. All locks of a transaction in the transaction
End with release. The code is in lock_release_off_kernel (). If there is a waiting lock, it can be authorized,
The lock will be changed to the authorized lock and wake up the corresponding transaction.

-Unique Identification of row locks
The first impression is that the key value recorded in each row is used for unique identification of the row lock, but the key value occupies a large space.
InnoDB uses Page No. + heap No. to uniquely identify row locks. We can understand heap No. As
An auto-increment value. Each physical record is assigned a unique heap no when it is created.
A. The key value can be understood as a logical value. Page No. + heap No. is physical.
B. Although physical resources occupy a small amount of space, the processing is more complicated. For example, half of the records when splitting a B + tree page
To move to a new page, you must migrate the existing locks.
The d Functions of lock movement include: lock_move_reorganize_page (), lock_move_rec_list_start (),
Lock_move_rec_list_end ().
When deleting and inserting data, you must also inherit the gap lock. Lock_rec_inherit_to_gap ()
Lock_rec_inherit_to_gap_if_gap_lock ().

-Deadlock)
A. Timeout mechanism. When the lock to be added conflicts with other locks, add a waiting lock and return the db_lock_wait error.
Row_mysql_handle_error calls srv_suspend_mysql_thread to suspend a thread.
B. Deadlock Detection mechanism. Every time a waiting lock is created, lock_deadlock_occurs () must be called to detect deadlocks.
The deadlock detection method is waits-for graph. Implemented in lock_deadlock_recursive.
When a deadlock is detected, select a transaction and roll it back to release the deadlock. Which transaction rollback can be selected?
-If a non-transactional table (for example, MyISAM table) is modified for a transaction, the modification cannot be rolled back.
Non-transactional is rolled back.
-If both transactions have modified the non-transactional table or none of them. Then compare the number of records modified by the two transactions and the addition
Number of locks. Transactions with a small sum will be rolled back. Trx_weight_ge () implements this logic