|
Preface
This article aims to make a brief introduction to the transaction lock module of InnoDB so that readers can have an initial understanding of it. This article first introduces the concepts of row-level locking and table-level locking, and then some of its internal implementations. The article ends with two interesting cases. All the code and examples in this article are based on the latest MySQL V5.7.10. Row-level lock InnoDB supports concurrency control to the row level. In this section, we will analyze several common row-level lock types, and when these types of locks will be used. LOCK_REC_NOT_GAP A lock with this flag indicates that this lock object only locks the record and does not lock the gap before the record. This type of record lock generally applies to the RC isolation level (except the duplicate key check on the unique secondary index, in which case the LOCK_ORDINARY type of lock is always applied). LOCK_GAP It indicates the lock is only effective for a specific range and does not lock the record itself. It usually stands for the lock between two index records, before the first index record, or after the last record. You can understand it as a range lock. Gap locks are usually used for the RR isolation level. You can avoid gap locks by switching to the RC isolation level, or enabling the innodb_locks_unsafe_for_binlog option. At this time, the gap lock will only be applied when you check for foreign key constraints or duplicate keys. LOCK_ORDINARY(Next-Key Lock) This is the so-called NEXT-KEY lock. It locks the record and the gap before the record. In the current MySQL version, the RR isolation level is applied by default, and the NEXT-KEY lock aims to solve the phantom read issue of the RR isolation level. The phantom read means different row records will be shown when you execute identical queries in a transaction. This is not allowed at the RR isolation level. Suppose there are records No 1, 4, 5, 8 and 12 on the index. We execute the similar statements: SELECT... WHERE col > 10 FOR UPDATE. If we do not apply a gap lock between Record No 8 and Record No 12, some other sessions may insert a record between them, such as Record No 9. When we execute the same SELECT FOR UPDATE operation, we will see the newly inserted record. This is also why we need to judge whether the next record has been locked when inserting a record. LOCK_S (share lock) The share lock serves to prevent modification to a row record by other transaction locks after the row record is read in the transaction. But all the share locks generated by read requests do not conflict with each other. The share locks will be requested in the following cases in InnoDB. 1. General queries will add the LOCK_S lock to the record when the isolation level is Serializable. But this is also subject to a specific scenario: non-transaction reads require no locks at the Serializable isolation level. (However, in the latest V5.7.10, the output of SHOW ENGINE INNODB STATUS won't include the read-only transaction information. You can only obtain the number of locks of the read-only transaction from the informationschema.innodb_trx table). 2. Statements similar to SQL SELECT...IN SHARE MODE will apply the S lock to records. Other threads can conduct concurrent queries, but modifications from other threads are not allowed. For different isolation levels, the actions also vary: • RC isolation level: LOCK_REC_NOT_GAP | LOCK_S • RR isolation level: If the query condition is of a unique index and looks for a unique equivalent, the LOCK_REC_NOT_GAP | LOCK_S will be applied; For queries of non-unique indexes, or the query will hit multiple records, the LOCK_ORDINARY | LOCK_S will be applied, that is, the record and the gap before the record will be locked. 3. Usually, the INSERT operation does not apply a lock. But when you insert or update a record, and a duplicate key (or a duplicate key marked to be deleted) is detected, the LOCK_S will be applied for general INSERT/UPDATE operations, and the X lock will be applied for statements similar to REPLACE INTO or INSERT...ON DUPLICATE. For different index types, the locks also vary: • For clustered indexes (see the row_ins_duplicate_error_in_clust function), when the isolation level is equal to or lower than the RC level, the S or X record lock similar to LOCK_REC_NOT_GAP will be applied. Otherwise, the LOCK_ORDINARY type of record lock will be applied (NEXT-KEY LOCK). • For secondary unique indexes, if the duplicate key is detected, the LOCK_ORDINARY type of record lock will always be applied in the current version (see the row_ins_scan_sec_index_for_duplicate function). As a matter of fact, according to the design concept of RC, the gap lock should not be applied (bug#68021). The official side also attempted to fix this issue to apply the LOCK_REC_NOT_GAP lock for the RC isolation level. However, the effort introduced another issue, leading to the invalidity of the unique constraint of secondary indexes (bug#73170). Because of this serious bug, the official team quickly reverted the fix. 4. Foreign key check When we delete a record in the parent table, we need to check whether any reference constraints apply (row_pd_check_references_constraints). At this time, the records in the sub table will be scanned (dict_table_t::referenced_list) and the share lock will be applied. But the cases vary. Let's illustrate. At the RC isolation level, we have two test tables: create table t1 (a int, b int, primary key(a)); create table t2 (a int, b int, primary key (a), key(b), foreign key(b) references t1(a)); insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,6), (7,8), (10,11); insert into t2 values (1,2), (2,2), (4,4); Execute the SQL statement: delete from t1 where a = 10; • Apply LOCKREC_NOT_GAP|LOCK_X on Record No 10 in Table t1 • Apply LOCK_ORDINARY|LOCK_S on the supremum record (indicating the maximum record) in Table t2, that is, to lock the (4, ~) range Execute the SQL statement: delete from t1 where a = 2; • Apply LOCK_REC_NOT_GAP|LOCK_X on records (2, 3) in Table t1 • Apply LOCK_REC_NOT_GAP|LOCK_S on records (1, 2) in Table t2. Here a reference constraint is detected, so the check can exit without scanning (2, 2) and an error is reported. Execute the SQL statement: delete from t1 where a = 3; • Apply LOCK_REC_NOT_GAP|LOCK_X on records (3, 4) in Table t1 • Apply LOCK_GAP|LOCK_S on records (4, 4) in Table t2 In addition, we can also see from the code that when the scanned record is deleted, the LOCK_ORDINARY|LOCK_S lock will also be applied. For details, see the row_ins_check_foreign_constraint function. 5. When you use the INSERT…SELECT statement to insert data, the LOCK_S lock will be applied to the data retrieved in the SELECT table. LOCK_X (exclusive lock) The exclusive lock aims to prevent concurrent modifications to the same record. Usually the UPDATE or DELETE operations, or operations similar to SELECT...FOR UPDATE will add the exclusive lock to the records. We can take the following table as an example: create table t1 (a int, b int, c int, primary key(a), key(b)); To insert the data: insert into t1 values (1,2,3), (2,3,4),(3,4,5), (4,5,6),(5,6,7); Execute the SQL statement (query through secondary indexes): update t1 set c = c +1 where b = 3; • At the RC isolation level: 1. The NOT GAP X lock is applied to lock the secondary index record; 2. The NOT GAP X lock is also applied to lock the corresponding clustered index record. • At the RR isolation level: 1. The LOCK_ORDINARY|LOCK_X is applied to lock the secondary index record; 2. The NOT GAP X lock is applied to lock the clustered index record. Execute the SQL statement (update the secondary index data through clustered index search): update t1 set b = b +1 where a = 2; • The LOCK_REC_NOT_GAP | LOCK_X is applied to the clustered index record. • When you mark the secondary index to be deleted, check for the lock on the secondary index record (lock_sec_rec_modify_check_and_lock). If a lock object in conflict with the LOCK_X | LOCK_REC_NOT_GAP exists, the lock object will be created and a wait error code will be returned; otherwise the lock object does not need to be created; o By now we have held the exclusive lock on the clustered index, so we can guarantee that other threads won’t modify this record. (Modifications to records always follow the sequence of clustered index first, and then the secondary index.) It does not matter even if there are no locks on the secondary indexes. But if another thread has held a lock for the secondary index record, you have to wait. • After the index is marked to be deleted, you need to follow the locking principle for inserting the intention lock to insert the updated secondary index record. We consider the mixed scenarios of the above two types of SQL statements. One is to lock the secondary index record first, and then lock the clustered index; the other is to lock the clustered index first and then check for secondary index conflicts. In such scenarios of concurrent updates, a deadlock may occur. The locking behavior in different scenarios and for different isolation levels vary. For example, at the RC isolation level, non-conforming records discovered with the WHERE condition will be released immediately, but at the RR level, they will last until the end of the transaction. You can view how an SQL function performs the locking operation through the GDB or the lock_rec_lock breakpoint function. LOCK_INSERT_INTENTION (insert an intention lock) The INSERT INTENTION lock is one type of gap locks. When multiple sessions insert data to the same gap, they do not need to wait for each other. For example, there are records No 4 and No 8 on the current index, and two concurrent sessions insert records No 6 and No 7 at the same time. They will apply the gap lock to records (4, 8) respectively, but with no conflicts in between (because the records inserted are not in conflict). When you want to insert a record to a data page, you will always call the lock_rec_insert_check_and_lock function for lock checking (except inserting data when establishing an index). The function will check whether a lock object exists on the record following the current place of insert. Here the following record is not in the physical sense, but in the logical sequence. If no lock objects exist on the next record: if the record is on a secondary index, the maximum transaction ID on the secondary index page will be updated to the current transaction ID first, and then success is returned directly. If a lock object exists on the next record, you have to judge whether the lock object has locked the gap. If the gap is locked, and the lock is identified as in conflict with the inserted intention gap lock, the current operation needs to wait. The LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION lock will be applied and the operation enters the waiting state. But the inserted intention locks do not conflict with each other. This means there may be multiple sessions applying for inserting the intention lock in the same gap. Lock table update We know that the gap lock is described in a record to indicate the gap between the record and the record before it. But if an insert or update operation occurs before the record, the previously described gap will change, and InnoDB needs to update the lock table. For inserting data, suppose there is a session holding a lock between the records [3, 9] (no matter whether it is in conflict with the inserted intention lock). Now we insert a new record No 5, we should call the lock_update_insert function. Here the function will traverse all the record locks on Record No 9. If these locks are not inserted intention locks and are LOCK_GAP or NEXT-KEY LOCK (no flag of LOCK_REC_NOT_GAP) (lock_rec_inherit_to_gap_if_gap_lock), a new lock object will be added for the transactions of these sessions, and the lock type is LOCK_REC | LOCK_GAP. The locked range in this example is (3, 5). All conforming sessions inherit from this new gap to avoid invalidating the previous gap locks. For data deleting operations, the lock_update_delete function will be called. It will traverse the record locks on the deleted records. When the following conditions are met, a new gap lock will be applied to the transactions of these locks. The lock Heap No is the next record of the deleted record: lock_rec_inherit_to_gap for (lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no); lock != NULL; lock = lock_rec_get_next(heap_no, lock)) { if (!lock_rec_get_insert_intention(lock) && !((srv_locks_unsafe_for_binlog || lock->trx->isolation_level <= TRX_ISO_READ_COMMITTED) && lock_get_mode(lock) == (lock->trx->duplicates ? LOCK_S : LOCK_X))) { lock_rec_add_to_queue( LOCK_REC | LOCK_GAP | lock_get_mode(lock), heir_block, heir_heap_no, lock->index, lock->trx, FALSE); } } From the judgment above, we can see that at the RC isolation level, sessions may also inherit the LOCK GAP lock, which is also the only unexpected issue in the current InnoDB version: when identifying the duplicate key, we can currently tolerate the gap lock. The above code snippet was just updated in the latest version. In earlier versions, it might introduce damages to the secondary indexes. After completing the inheritance of the gap lock, InnoDB will wake up all the lock objects waiting for the record (lock_rec_reset_and_release_wait). LOCK_PREDICATE From MySQL V5.7, MySQL integrated the boost.geometry library to better support the spatial data type, and supports establishing indexes on the spatial data columns. In InnoDB, this index is different from the general index. It is based on the R-TREE structure and currently supports 2D data description, instead of 3D data description. R-TREE is different from BTREE. It can describe the multi-dimensional space while multi-dimensional data does not have a specific data order, so you cannot structure the NEXT-KEY lock at the RR isolation level to prevent phantom reads. To this end, InnoDB uses a model called predicate lock to lock a queried data section called MBR (minimum bounding rectangle/box). In this sense, this lock does not apply to a specific record. We can understand it as a page-level lock. The predicate locks are stored in a different lock hash from that for general record locks or table locks (aforementioned) and there is no conflict between them. Code of the predicate lock can be found in the lock/lock0prdt.cc file. For the design of the predicate lock, see the official WL#6609. The code volume on this is too huge and I have limited knowledge in the spatial implementation of InnoDB, so I will not talk about it much in this article. We can discuss this content in a dedicated section about the spatial index in the future. Implicit lock InnoDB usually applies no locks on the insert operations, but solves the conflict through the implicit lock approach. The transaction ID is stored in the clustered index record. If another session retrieves this record, it will judge whether the transaction ID of this record belongs to an active transaction, and assist this transaction in creating a record lock. Then it places itself in the wait queue. The logic is designed like this because a newly inserted record won't be immediately modified by other threads concurrently under most circumstances, and the overhead for creating a lock is expensive as it involves competition of global resources. Identification of lock conflicts The compatibility matrix of locking models can be quickly identified through the following arrays: static const byte lock_compatibility_matrix[5][5] = { /** IS IX S X AI / / IS / { TRUE, TRUE, TRUE, FALSE, TRUE}, / IX / { TRUE, TRUE, FALSE, FALSE, TRUE}, / S / { TRUE, FALSE, TRUE, FALSE, FALSE}, / X / { FALSE, FALSE, FALSE, FALSE, FALSE}, / AI / { TRUE, TRUE, FALSE, FALSE, FALSE} }; For record locks, the lock models contain LOCK_S and LOCK_X only. Other flags are used for lock descriptions, such as the aforementioned four types of descriptions, namely LOCK_GAP, LOCK_REC_NOT_GAP, LOCK_ORDINARY, and LOCK_INSERT_INTENTION. When you identify whether two locks conflict with each other, even if the two locks comply with the compatibility matrix, they are regarded compatible under several circumstances, with no wait required (see the lock_rec_has_to_wait function). • For gap locks (the lock object is established on the supremum, or the lock type applied for is LOCK_GAP), if the lock type applied for is not an inserted intention lock, the lock does not need to wait for any locks. This is because different sessions can apply for different types of locks for the same gap, while the gap locks are designed to be conflict-free. • Lock objects of the LOCK_ORDINARY or LOCK_REC_NOT_GAP types do not need to wait for LOCK_GAP type locks. • LOCK_GAP type locks do not need to wait for LOCK_REC_NOT_GAP type lock objects. • All lock requests do not need to wait for inserted intention locks. |
|
