In the previous article, "Introduction to Innodb's Locking Mechanism", I mentioned record locking, but I did not describe it in detail. Let's talk about it briefly in this article.
Row-level locks in a database have different names depending on the fine-grained size of the lock.
- 记录锁（Record Lock）指的是对索引记录的锁定。
- Gap Lock locks the gaps between index records.
Next-Key Lock, on the other hand, is a fusion of record lock and gap lock, locking both index records and gaps. Its range is left open and right closed.
什么是Record Lock
记录锁，即Record Lock，是针对索引记录而言的锁定。 例如，执行以下语句：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 会对满足条件c1=10的记录进行锁定，以防止其他任何事务插入、更新或删除具有相同c1值的行。
What is Gap Lock
Gap lock, or gap lock, is a lock on a gap between indexed records, or a gap before the first indexed record or after the last indexed record.
In this context, the "gap" refers to the position in the InnoDB index data structure where a new value can be inserted.
When you use SELECT... When a FOR UPDATE statement locks a set of rows, InnoDB can create locks that are applied to the actual values in the index and the gaps between them. For example, if you choose to update all values greater than 10, a gap lock will prevent another transaction from inserting a new value greater than 10.
Because the presence of locks can affect the concurrency of a database, gap locks only work at the isolation level of Repeatable Reads.
在Repeatable Reads隔离级别下，针对锁定的读操作（例如select ... for update、lock in share mode）、update操作和delete操作，会执行以下加锁操作：
- For unique indexes with unique search criteria, InnoDB locks only the index records found, not the gaps.
- For other search criteria, InnoDB locks the scanned index range and uses a gap lock or next-key lock to prevent other transactions from inserting gaps in the range.
换句话说，在处理**SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE**等语句时，除了对具有唯一搜索条件的唯一索引外，还会获取间隙锁或next-key锁，即锁定其扫描的范围。
什么是Next-Key Lock
A next-key lock is a combination of a record lock on an indexed record and a gap lock on the gap between an indexed record.
Suppose an index contains values 10, 11, 13, and 20. The possible next-key locks for this index include the following compartments:
For the last gap, ∞ isn't really an index record, so in effect, this next-key lock only locks the gap after the maximum index value.
Therefore, the range of Next-Key locks is open on the left and closed on the right.
与Gap Lock一样，Next-Key Lock只有在InnoDB的可重复读（RR）隔离级别中才会生效。
Let's talk about the MySQL locking mechanism
According to the summary in Mr. Dingqi's "MySQL Practical 45 Lectures", the locking rules can be summarized into two "principles", two "optimizations" and one "bug":
- Principle 1: The basic unit of locking is the next-key lock, which forms a front open and back closed interval.
- Principle 2: Only objects accessed during the lookup process are locked.
- Optimization 1: For equivalent queries on indexes, the next-key lock will degenerate into a row lock when a unique index is locked.
- Optimization 2: For equal-value queries on the index, when traversing to the right and the last value does not meet the equivalence condition, the next-key lock will degenerate into a gap lock.
- A bug: Range queries on unique indexes are accessed until the first value of the condition is not met.
When we run update t set d=d+1 where id = 7, since there is no record of id=7 in table t, so:
- 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
- According to Optimization 2, this is an equivalent query (id=7), and id=10 does not satisfy the query condition, and the next-key lock degenerates into a gap lock, so the final lock range is (5,10).
当我们执行select * from t where id>=10 and id<11 for update的时候：
- According to principle 1, the locking unit is next-key lock, and (5,10) will be added with next-key lock, and the range search will continue to be searched backwards, and the id=15 line will be found and stopped
- According to the equivalent condition on the primary key id of Optimization 1, it degenerates into a row lock, and only adds the row lock of id=10.
- 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。 因此最终加的是行锁 id=10 和 next-key lock(10,15]。
当执行 select * from t where id>10 and id<=15 for update 时：
- According to principle 1, the locking unit is next-key lock, which adds next-key lock to (10,15), and since id is a unique key, it should be stopped when it reaches the id=15 line.
- However, InnoDB actually scans forward to the first row that doesn't meet the criteria, which is id=20. Since this is a range scan, the next-key lock (15,20) on the index ID will also be locked.
Suppose there are currently the following records in the database table:
当执行 select id from t where c=5 lock in share mode 时：
- According to principle 1, the locking unit is a next-key lock, so (0,5) is added with a next-key lock. It should be noted that c is a normal index, so it cannot immediately stop at the record of c=5, and you need to traverse to the right until you find c=10 and then give up.
- According to principle 2, all access should be locked, so (5,10) should be added with a next-key lock.
- According to Optimization 2: Equivalence, traversing to the right, the last value does not satisfy the equivalence condition of c=5, so it degenerates into a gap lock (5,10).
- According to principle 2, only objects that are accessed are locked. Since this query uses an overlay index and does not need to access the primary key index, no locks are added to the primary key index.
当执行 select * from t where c>=10 and c<11 for update 时：
- According to principle 1, the locking unit is next-key lock, which adds a next-key lock to (5,10) and continues to look backwards until the id=15 line is found.
- 根据原则 2，访问到的都要加锁，因此需要加 next-key lock (10,15]。
- Since index c is a non-unique index and has no optimization rules, i.e., it does not degenerate into row locks, the final locks added by session A are (5,10) and (10,15) on index c.
epilogue
In the past, we introduced the lock mechanism in InnoDB, and there are three types of locks, namely Record Lock, Gap Lock, and Next-Key Lock.
Record Lock represents a record lock, and the lock is the index record. Gap Lock is a gap lock, which refers to the gap between index records. Next-Key Lock is a combination of Record Lock and Gap Lock, locking both index records and gaps. His range is left open and right closed.
In the RR level of InnoDB, the basic unit of locking is next-key lock, which locks whenever the scanned data is locked. Range queries on a unique index access up to the first value that doesn't meet the criteria.
At the same time, in order to improve performance and concurrency, there are two optimization points:
- 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
- When an equivalent query on an index traverses to the right and the last value does not meet the equality condition, the next-key lock degenerates into a gap lock.
That's all for the introduction of locks, but in fact, although these locks introduced by the isolation level of RR can solve many problems such as phantom reads to a certain extent, they will also bring some side effects, such as reduced concurrency, easy to lead to deadlocks, etc.
For more information, please click Full-Scenario Live Streaming Solution - Aerospace Cloud Network Solution.