@TOC
An in-depth understanding of MySQL’S MVCC principles
MySQL provides many types of locks:
-
The Server layer implements both global locking and metadata locking.
-
In data engine, MyISAM, Memory and other storage engines implement table locks (and only support table locks), BerkeleyDB storage engine implements page-level locks, InnoDB implements row locks and table locks.
Usually the most common, the most commonly used is InnoDB row lock, so here mainly to explore InnoDB row lock.
As the name implies, a row lock is used to lock each row in a database table. A row lock is used to lock each row in a database table. For example, a row lock is used to lock each row in a database table. If you lock a field that has no index, you end up locking all records on the primary key index. In InnoDB’s implementation, there are 3 main algorithms for row locking:
-
Record Lock: A Lock on a single row Record, here we call Record Lock.
-
Gap Lock: A Gap Lock is applied to a Gap/range that does not contain real records. The only purpose of Gap Lock is to prevent other transactions from inserting data at RR and SERIALIZABLE isolation levels. If an index with 2, 4, 5, 9, 12 five values and that the index could be lock lock range for clearance (- up, 2), (2, 4), (4, 5), (5, 9), (9, 12), (12 + up).
-
A next-key Lock is used to Lock both a Record and a Gap between a Record and its predecessor. If an index with 2, 4, 5, 9, 12 five values and that the index could be adjacent key lock lock range is (- up, 2), (2, 4), (4, 5), (5, 9], (9, 12), (12 + up). In InnoDB, the basic unit of locking is next-key Lock, although in some special cases it degrades to Record Lock or Gap Lock.
Some students will ask: “what lock will be added to this SQL statement”, in fact, this is a false statement, because a statement needs to be added to the lock is affected by many aspects. In actual scenarios, row-level locking and locking rules are complex. The locking conditions may vary with different query conditions, indexes, and isolation levels, and even the locking rules of different versions of MySQL may be slightly different.
The default RR isolation level is the same as the default RR isolation level. The default RR isolation level is the same as the default RR isolation level. Do not add Gap Lock and next-key Lock), do not consider any operation on the table intent Lock. Mysql 8.0.27
-
When querying the fields corresponding to primary key indexes, unique indexes, and normal indexes, which indexes will be locked?
-
When the query condition is equivalent query or range query and the query result exists or does not exist, which records on the index are locked (which ranges of data are locked)?
MySQL 8.0.27 MySQL 8.0.27 MySQL 8.0.27
CREATE TABLE `t_lock_test` (
`id` int NOT NULL,
`mobile` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL.PRIMARY KEY (`id`),
UNIQUE KEY `idx_mobile` (`mobile`) USING BTREE,
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `t_lock_test` VALUES (2.'17118168721'.'Bob'.31);
INSERT INTO `t_lock_test` VALUES (4.'15373838350'.'Bob'.30);
INSERT INTO `t_lock_test` VALUES (5.'13785078432'.'Kara'.20);
INSERT INTO `t_lock_test` VALUES (9.'18901970832'.'Anna'.30);
INSERT INTO `t_lock_test` VALUES (12.'17837938413'.'Kara'.25);
Copy the codeWe create a table called T_lock_test with id as primary key, mobile as unique index, name as non-unique index, and age as no index. And five records are inserted:
The three index trees corresponding to this table are:
For the sake of brevity, only the leaf node of the index is drawn below. For example, the primary key index is simplified as:
One note: in any case, a row lock will only be held on actual records (such as those with id=12, 4, 5, 9, 12 in the primary key index above) and supremum pseudo-records (described below).
1 Query the primary key index
1.1 What lock should be added at what position in the index when equivalent query records exist? Why is that?
Example 1: Transaction 5564 executesselect * from t_lock_test where id=5 for update; By performance_schema. Data_locks table view of acquiring a lock MySQL affairs, found on the primary key index id = 5 record with the lock:
The range of indexes locked is as follows:
At this point another transaction 5565 executesselect * from t_lock_test where id=5 for update;Blocked, waiting for lock failed:
Transaction 5565 also acquires the lock on primary key index (id=5) :
It can be concluded that when a primary key index is queried, if the query condition is equivalent query and the record exists, only the records that meet the condition are locked.
1.2 What lock should be added at what position in the index when equivalent query records do not exist? Why is that?
Example 1: Transaction 5566 executesselect * from t_lock_test where id=7 for update;, the locking situation is as follows:
Select * from primary key where id=9; select * from primary key where id=9; select * from primary key where id=9; select * from primary key where id=9;
Insert into T_lock_test values(8,’13118161267′,’Bob’,31); It will block because the interval (5,9) is already locked, so inserting records with ids 6, 7, and 8 will block. Unlike the RC isolation level, gap locks are not applied to records with ID =9 under the RC isolation level.
It can be concluded that when the query condition is the primary key index, if the query condition is equivalent query and the record does not exist, a gap lock will be added to the next record of the gap where the query condition is (equivalent to the range of the lock is the gap where the query condition is).
Example 2: Here extends an example when a transaction executesselect * from t_lock_test where id=13 for update;, the locking situation is as follows:
Supremum pseudo-record is the maximum bound pseudo-record (equal to + infinity + infinity). Corresponding to this is infimum pseudo-record (equivalent to minus infinity – infinity). Since the supremum pseudo-record has a maximum value of plus infinity, its gap lock and its adjacent key lock can be treated as the same (in t_lock_test table supremum pseudo-record has a gap lock and a neighboring key lock range of (12,+∞)). The basic unit of locking is a neighboring key lock, so there is no need to degrade it, so any subsequent locking of the Supremum pseudo-record is basically a neighboring key lock.
1.3 What lock should be placed at what position in the index when a range query record exists? Why is that?
Example 1: When transaction 5682 executes separatelyselect * from t_lock_test where id>4 and id <8 for update; ,select * from t_lock_test where id>4 and id <=8 for update; ,select * from t_lock_test where id>4 and id <9 for update; When, the locking situation is the same:
Select * from user where id=5 and id <8, id>4 and ID <=8, id>4 and ID <9; select * from user where id=5 and id <8; In addition, other parts of the query condition (id>5 and ID <9) need to be locked, that is, records with ID =9 need to be locked, because the query condition does not contain ID =9, so it is degraded to gap lock.
Example 2: Select * from t_lock_test where ID >4 and ID <=9 for update; Is locked as follows:
Add a neighboring key lock to the record whose ID =5 and a neighboring key lock to the record whose ID =9. Different from example 1, this query condition contains the record whose ID =9. Therefore, the lock on id=9 will not degenerate into a gap lock.
It can be concluded that, when the query condition is range query and the record exists, adjacent key locks will be added to the record that meets the condition, and gap locks will be added to the next record of the remaining unlocked gap (equivalent to the range of lock is the corresponding interval of the query condition).
Consider another special case, when transaction 5680 executesselect * from t_lock_test where id>7 and id <=12 for update;Mysql > alter table pseudo-record (id=9); mysql > alter table pseudo-record (id=12);
In MySQL practice 45, the author summed up “two principles”, “two optimizations” and “a bug” for the row lock rule. 5. X series <=5.7.24, 8.0 series <=8.0.13), where “a bug” is that a range query on a unique index will reach the first value that does not meet the condition. Therefore, the supremum pseudo-record is also locked with a neighboring key.
Mysql > alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 alter table 8.0.27 A range query on a unique index, if the maximum value in the record is in the range of the query, is accessed up to the first value (which is essentially the supremum pseudo-record) that does not satisfy the condition.
I verified this in MySQL 8.0.13, in example 2 when a transaction executesselect * from t_lock_test where id>4 and id <=9 for update; , the locking situation is as follows:
This section of the situation may be complicated, there are different MySQL versions of the lock implementation of different situations, recommend readers to do it.
1.4 What lock should be placed in the index when the range query record does not exist? Why is that?
Example 1: When the transaction executesselect * from t_lock_test where id>5 and id <9 for update; Select * from index where id=9;
Example 1: When the transaction executesselect * from t_lock_test where (id>6 and id <9) or id>12 for update; Supremum pseudo-record (id=9); supremum pseudo-record (id=9);
2 Set the search criteria to unique indexes
2.1 What lock should be added at what position in the index when equivalent query records exist? Why is that?
Example 1: When the transaction executesselect * from t_lock_test where mobile='18901970832' for update;Is locked as follows:
Select idx_mobile (id= ‘18901970832’) from primary key (idx_mobile, id=9); Why add a record lock to a primary key index? Delete from t_lock_test where id=9 or delete from t_lock_test where name= ‘Anna’ If the primary key index is not locked, concurrent operations can modify the record mobile=’18901970832′ with conditions other than mobile. In the first case, why did not lock the primary key index (unique index, non-unique index)? Because even if the condition of modifying data is not a primary key index, you will eventually have to look up the primary key index (the data pages to be modified are the primary key index leaf nodes ~); However, when the condition for modifying data is the primary key index, there is no need to look up other indexes.
select * from t_lock_test where mobile=18901970832 for update;, because the mobile field is vARCHAR type, but the query condition in SQL is numerical type, MySQL will try to carry out implicit conversion in order to avoid direct error reporting. The mobile column in the database will be converted into data of the same type as in SQL by using the function and then perform equivalent judgment, while the index column will not be queried by using the function. A transaction executes this statement and locks each record on the primary key index as follows:
Here we can draw a conclusion: when the query condition is unique index, if the query condition is equivalent query and the records exist, the records will be locked on the unique index, and the records will be locked on the primary key positions of those records on the primary key index.
2.2 What lock should be added to the index when the equivalent query record does not exist? Why is that?
Example 1: When transaction 5575 executesselect * from t_lock_test where mobile='15933661689' for update;If the primary key index does not exist, a gap lock will be added to the next gap in the query condition:
Because on the unique index idx_Mobile,mobile='15933661689'This entry is inmobile='15373838350'andmobile='17118168721'Between these two records, so it’s rightmobile='17118168721'There’s a gap lock on this record.
In this case, the primary key index is not locked. Insert into T_lock_test values(ID, ‘15933661689’,name,age); Insert id (mobile=’17118168721′); insert id (mobile=’17118168721′);
It can be concluded that when the query condition is unique index, if the query condition is equivalent query and the record does not exist, only the gap lock will be added to the next record in the gap where the query condition is. No locks on primary key indexes.
2.3 When a range query record exists, what lock should be placed at what position in the index? Why is that?
Example 1: When transaction 6682 executesselect * from t_lock_test where mobile>'13931766909' and mobile<'17041965526' for update;, the locking situation is as follows:
On the IDx_mobile indexmobile='15373838350'andmobile='17118168721'Records are locked with adjacent keys, and becausemobile='15373838350'This record is in the scope of the query and there is a real record, so the primary key index corresponding to this record (id=4 record) is also locked.
Yi? Select idx_mobile (‘15373838350’) from idx_mobile (‘15373838350′) Mobile =’17118168721’ (‘17041965526’ < ‘17118168721’)
At this point the other transaction 6683 executesselect * from t_lock_test where mobile='17118168721' for update;Idx_mobile =’17118168721′; WAITING: idx_mobile =’17118168721′;
But another transaction, 6690, executesselect * from t_lock_test where id=2 for update;Select * from primary key where id=2 and lock status = GRANTED;
In this case, the next record in the query condition is locked on the unique index, but not on the corresponding primary key index.
Mentioned author in MySQL combat 45 speak to row lock locking rules concludes that “a bug” – the only index range queries can access to the first value is does not meet the conditions, so you can understand for this situation is also belong to the “bug”, When the unique index IDx_mobile petits for ‘17041965526’ (because it doesn’t exist), continue backward until you access the record ‘17118168721’ and lock it.
It can be concluded that: when the query condition is unique index, if the query condition is range query and the record exists, the query record will be adjacent locked on the unique index, and the next record of the remaining unlocked gap will be adjacent locked (which is different from the above 1.3 and will not degenerate into a gap lock). On a primary key index, a record lock is placed on the primary key index for records in the range of the query.
2.4 What lock should be placed in the index when the range query record does not exist? Why is that?
Transaction executionselect * from t_lock_test where mobile>'13931766909' and mobile<'15101965526' for update;, the locking situation is as follows:
Alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350′ alter table mobile=’15373838350
Conclusions can be drawn here: when the query condition is a unique index, if the query condition is a range query and the record does not exist, the next record in the gap of the query condition will be locked on the unique index. No locks on primary key indexes.
3 Set the search criteria to non-unique indexes
3.1 What lock should be added at what position in the index when equivalent query records exist? Why is that?
Transaction executionselect * from t_lock_test where name='Bob' for update;, the locking situation is as follows:
Select * from idx_name where name=’Bob’ and name=’Kara’; select * from idx_name where name=’Bob’ and name=’Kara’; The purpose is to prevent the insertion of name=’Bob’ in the three gaps: Anna-9 to Bo-2, Bo-2 to Bo-4, and Bo-4 to kara-5. Select * from primary key where id=2 and id=4;
Conclusions can be drawn here: when the query condition is non-unique index, if the query condition is equivalent query and the record exists, on the non-unique index, adjacent key lock will be added to the record that meets the query condition, and gap lock will be added to the next record of the remaining unlocked gap. On the primary key index, a record lock is added to the corresponding position of the primary key index for the records that meet the query conditions.
3.2 What lock should be placed in the index when the equivalent query record does not exist? Why is that?
Transaction executionselect * from t_lock_test where name='Danny' for update;, the locking situation is as follows:
3.3 When a range query record exists, what lock should be placed at what position in the index? Why is that?
Transaction executionselect * from t_lock_test where name > 'Anna' and name < 'Danny' for update;, the locking situation is as follows:
Select * from idx_name where name=’Bob’ and name=’Kara’; select * from idx_name where name=’Bob’ and name=’Kara’ Insert ‘Bob’ <= name <‘Danny’ between boB-4 and kara-5 Select * from primary key where id=2 and id=4;
Conclusions can be drawn here: when the query condition is non-unique index, if the query condition is range query and the record exists, on the non-unique index, the record that meets the query condition will be locked by adjacent key, and the next record of the remaining unlocked gap will be locked by adjacent key (in fact, it is ok to add gap lock). On the primary key index, a record lock is added to the corresponding position of the primary key index for the records that meet the query conditions.
3.4 What lock should be placed in the index when the range query record does not exist? Why is that?
When the transaction executesselect * from t_lock_test where name > 'Danny' and name < 'Ella' for update;, the locking situation is as follows:
Select name=’Kara’ from ‘Danny’ <= name <‘Ella’ from ‘bob-4’ to ‘Kara 5 ‘from ‘Danny’ <= name <‘Ella’ from’ bob-4 ‘to ‘Kara 5’ from ‘Kara
It can be concluded that when the query condition is non-unique index, if the query condition is range query and the record does not exist, on the non-unique index, the next record in the gap of the query condition will be locked by the adjacent key (actually, the gap lock will be ok). No locks on primary key indexes.
4. The query condition is non-index
When the transaction executesselect * from t_lock_test where age=20 for update;,select * from t_lock_test where age=21 for update;,select * from t_lock_test where age>21 and age<=25 for update;,select * from t_lock_test where age>25 and age<30 for update;, lock is the same:
A neighborhood lock is added to each record in the table, including the supremum pseudo-record.
It can be concluded that when the query condition is non-index, whether it is equivalent query or range query, whether there is query result or not, all records in the table will be locked by neighboring key, which is often referred to as “lock table”.
— Hu Yuyang “In-depth analysis of MySQL row lock add lock rules”