For Update lock analysis

Posted by dwees on Wed, 26 Jan 2022 07:16:22 +0100

MySQL InnoDB lock - For Update lock analysis:

2. Current read lock analysis: REPEATABLE-READ Repeatable reading,READ-COMMITTED Read committed

3. Lock mode description and 8.0 data_locks table

1, InnoDB lock

1. Global lock

Global read lock, flush tables with read lock, the whole library is in read-only state. A typical scenario of global lock: full database logical backup, - single transaction to achieve consistent read.

2. Table lock

Table lock, lock tables... read/write, actively add read lock or write lock on the table;

Metadata lock (MDL) is automatically added when accessing the table to prevent the concurrent conflict between DDL and DML and ensure the correctness of reading and writing;

Auto increment lock, auto in the table_ Self increment generation control of increment field, memory self increment counter, and persistence through redo after 8.0;

Intent locks only conflict with table level locks and will not block locks other than table lock requests, indicating that a transaction is about to lock or is locking an N row;

Intent shared lock (IS): Select Lock in share mode: add an intention to share lock on the table before locking the corresponding record line;
Intent exclusive lock (IX): SELECT For update: pessimistic lock. All scanned rows in the table will be locked with exclusive intent. If there is a row lock in the scanned row, it will be blocked; Apply an exclusive lock to the SELECT index to block other transaction modifications or SELECT For share (added in 8.0);

3. Row lock

Row lock, transaction lock. Only when row lock conflict occurs, can transaction lock queue.

Two phase lock protocol: the row lock is added when needed and released at the end of the transaction.

Three algorithms of row lock: record lock, gap lock and next key lock

record lock: added to the index. If there is no index in the table, it will be added to the clustered index created by default;
Gap lock: lock a range. Under the repeatable read isolation level, the row lock will become a gap lock (fan lock) to reduce concurrency. For the current read (dml, select for update), if there is an index on the where condition column, add gap lock on the index to realize repeatable read;
Next key lock: Gap Lock+Record Lock, lock a range and lock the record itself;

① for unique index or primary key, next key lock is reduced to Record Lock, that is, only the index itself is locked, not the range.

② auxiliary index (secondary index). Next key locking is used by default. The locking range is from the previous index to the next index, open on the left and closed on the right.

### session 1
root@test 15:51 > begin;

root@test 15:51 > show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_birthday` (`birthday`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 15:52 > select * from student;
+----+-------+---------------------+
| id | name  | birthday            |
+----+-------+---------------------+
|  1 | abcd  | 1995-06-27 00:00:00 |
|  2 | abef  | 1995-01-24 00:00:00 |
|  3 | abg   | 1995-07-26 00:00:00 |
|  4 | cdmn  | 1995-06-13 00:00:00 |
+----+-------+---------------------+

root@test 15:52 > select * from student where birthday > '1995-06-27 00:00:00' and birthday < '1995-07-26 00:00:00' for update;
Empty set (0.02 sec)

### session 2
root@test 15:51 > begin;

# Left open
root@test 15:54 > update student set name = 'abcd' where birthday = "1995-06-27 00:00:00";

# Right close (blocking update)
root@test 15:55 > update student set name = 'abg' where birthday = '1995-07-26 00:00:00';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2, Current read lock analysis

Analyze the locking of current reads (dml, select... for update) at different isolation levels

1. REPEATABLE-READ repeatable read

root@test 15:06 > show global variables like "%iso%";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

1.1. The table has no explicit primary key and index

root@test 15:30 > show create table t_student;
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                               |
+-----------+----------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------------------------------------------------------------------------------+

root@test 15:30 > begin;

root@test 15:31 > select * from t_student for update;
+----+-------+
| id | name  |
+----+-------+
|  1 | jack  |
|  2 | kuzma |
|  3 | linda |
+----+-------+

As mentioned above, it can be read repeatedly. There is no primary key on the table (an implicit primary key aggregation organization table will be automatically generated) and no index. The locking of the current read of the whole table SELECT:

① add IX lock to the table

② add next key lock on "maximum" (maximum indicates the value higher than any index in the table), that is, the gap after the maximum index value is locked

③ add next key lock on the three records respectively

root@test 16:08 > begin;

# where with conditional id = 3
root@test 16:08 > select * from t_student where id = 3 for update;
+----+-------+
| id | name  |
+----+-------+
|  3 | linda |
+----+-------+

root@test 16:09 > show engine innodb status\G
# The lock information is as above, and added to IX, supremum and three records in the table Next-Key Lock

# Open another session
root@test 16:09 > begin;

root@test 16:13 > insert into t_student values(2,"tom");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@test 16:15 > insert into t_student values(4,"tom");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

As mentioned above, why is the insert blocked? What about delete? What about update?

Every time a record is inserted, the generated clustered index (DB_ROW_ID) is self incremented and will be inserted at the end of the table every time. Therefore, it is possible to insert a record with id=10. Therefore, it is necessary to add "supreme pseudo record" to prevent data insertion.

---TRANSACTION 582122, ACTIVE 3874 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 12529, OS thread handle 123145486712832, query id 94463 localhost root update
insert into t_student values(5,"tom")
Trx read view will not see trx with id >= 582123, sees < 582121
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX
RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Even records that do not meet the where condition will be added with next key lock to prevent unreal reading. Therefore, deleting or updating other session transactions will cause unreal reading, which will be blocked.

---TRANSACTION 582122, ACTIVE 3788 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 12529, OS thread handle 123145486712832, query id 94461 localhost root updating
update t_student set name = "linda" where id = 3
Trx read view will not see trx with id >= 582123, sees < 582121
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000090a; asc       ;;
 1: len 6; hex 00000008e1c8; asc       ;;
 2: len 7; hex f4000001e40110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 6a61636b; asc jack;;

------------------
TABLE LOCK table `test`.`t_student` trx id 582122 lock mode IX
RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582122 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000090a; asc       ;;
 1: len 6; hex 00000008e1c8; asc       ;;
 2: len 7; hex f4000001e40110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 6a61636b; asc jack;;

1.2. The table has an explicit primary key and no index

Without the where condition, the gap after the maximum index value of the IX primary key on the table is locked, and all records of the table correspond to the primary key with a record lock
The where condition is the primary key. In table IX, the primary key adds a record lock (X, REC_NOT_GAP), and only the condition records are locked, because the primary key is guaranteed to be unique and no gap lock is required

---TRANSACTION 582139, ACTIVE 12 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12528, OS thread handle 123145486434304, query id 94472 localhost root
TABLE LOCK table `test`.`t_student` trx id 582139 lock mode IX
RECORD LOCKS space id 169 page no 3 n bits 72 index PRIMARY of table `test`.`t_student` trx id 582139 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000008e1c8; asc       ;;
 2: len 7; hex f4000001e4011f; asc        ;;
 3: len 5; hex 6b757a6d61; asc kuzma;;

1.3. The table has no explicit primary key and an index

Without the where condition, the gap after the maximum index value of IX and id on the table is locked, and the corresponding index of all records in the table is locked
The where condition is a normal index field

root@test 22:01 > show create table t_student;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                     |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `ix_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 22:10 > begin;

root@test 22:11 > select * from t_student where id = 2 for update;
+----+-------+
| id | name  |
+----+-------+
|  2 | kuzma |
+----+-------+

root@test 22:11 > show engine innodb status\G
---TRANSACTION 582176, ACTIVE 14 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 12534, OS thread handle 123145487269888, query id 94485 localhost root
TABLE LOCK table `test`.`t_student` trx id 582176 lock mode IX
RECORD LOCKS space id 170 page no 4 n bits 72 index ix_id of table `test`.`t_student` trx id 582176 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000912; asc       ;;

RECORD LOCKS space id 170 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582176 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000912; asc       ;;
 1: len 6; hex 00000008e200; asc       ;;
 2: len 7; hex be00000146011f; asc     F  ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 5; hex 6b757a6d61; asc kuzma;;

RECORD LOCKS space id 170 page no 4 n bits 72 index ix_id of table `test`.`t_student` trx id 582176 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000913; asc       ;;

As mentioned above, the locking condition when filtering a common index is:

① add IX lock to the table

② add next key lock lock to the index corresponding to id=2, and the interval is (- ∞, 2]

③ add an X record lock to the clustered index corresponding to the index

④ to prevent unreal reading (because it is an ordinary index, the record with id=2 can be inserted repeatedly), add a lock_mode X locks gap before rec to the index record interval (2,3)

In other words, (the test case is slightly inappropriate) if the test is enlarged to 10 ~ 20 ~ 30, the record insertion between ID and 29 will be blocked, and the insertion id=30 will not be blocked because it is not within the range of gap lock.

1.4. Tables have explicit primary keys and indexes

There is no where condition, as described in 1.2.1
There are where conditions. In fact, as long as the primary key or unique key index can be accurately located, it is the IX, primary key or unique key on the table that adds a record lock (X, REC_NOT_GAP), and only locks the condition records

2. Read committed read committed

root@test 23:57 > show global variables like "tx_isolation";
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

2.1. The table has no explicit primary key and index

root@test 23:57 > show create table t_people;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                  |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| t_people | CREATE TABLE `t_people` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------+

root@test 23:58 > select * from t_people;
+------+-------+
| id   | name  |
+------+-------+
|    1 | jack  |
|    2 | kuzma |
|    3 | linda |
+------+-------+

As follows, table t_ An exclusive lock is added to people, and an X record lock is added to the hidden primary key of all rows in the table (the third in the row format is the hidden primary key, which is automatically generated to organize table data when the primary key is not displayed in the table). Only the record itself is locked, not the range gap lock.

What if you bring the where condition?

root@test 00:44 > begin;

root@test 00:45 > select * from t_people where id = 2 for update;
+------+-------+
| id   | name  |
+------+-------+
|    2 | kuzma |
+------+-------+

root@test 00:45 > show engine innodb status;
......
---TRANSACTION 582053, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12521, OS thread handle 123145487269888, query id 94339 localhost root
TABLE LOCK table `test`.`t_people` trx id 582053 lock mode IX
RECORD LOCKS space id 166 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_people` trx id 582053 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000000908; asc       ;;
 1: len 6; hex 00000008e19e; asc       ;;
 2: len 7; hex d2000001580110; asc     X  ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 5; hex 6b757a6d61; asc kuzma;;
 ......
 # Just in id = 2 Added to my record X Record lock

2.2. The table has an explicit primary key and no index

Without the where condition, as described above, X record lock is added to all rows in the table, and the range is not locked
When the where condition is the primary key field, an IX lock is added to the table and a record lock (X, REC_NOTGAP) is added to the primary key. Where the primary key is, the primary key has been guaranteed to be unique. Non range locks are added to specific records

root@test 00:49 > show engine innodb  status;
---TRANSACTION 582071, ACTIVE 7 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12521, OS thread handle 123145487269888, query id 94353 localhost root
TABLE LOCK table `test`.`t_people` trx id 582071 lock mode IX
RECORD LOCKS space id 167 page no 3 n bits 72 index PRIMARY of table `test`.`t_people` trx id 582071 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000008e19e; asc       ;;
 2: len 7; hex d2000001580110; asc     X  ;;
 3: len 5; hex 6b757a6d61; asc kuzma;;

The where condition includes primary key fields and non primary key fields, which is the same as above, because the primary key has been locked.

2.3. The table has no primary key and index

Without the where condition, similarly, X record lock is added to all rows in the table, and the range is not locked
The where condition is an ordinary index field, and so is a unique index

root@test 20:24 > show create table t_student;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                         |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `ix_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------+

root@test 20:25 > begin;

root@test 20:27 > select * from t_student where name = 'kuzma' for update;
+----+-------+
| id | name  |
+----+-------+
|  2 | kuzma |
+----+-------+

root@test 20:27 > show engine innodb status;
---TRANSACTION 582093, ACTIVE 57 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12525, OS thread handle 123145486712832, query id 94388 localhost root
TABLE LOCK table `test`.`t_student` trx id 582093 lock mode IX
RECORD LOCKS space id 168 page no 4 n bits 72 index ix_name of table `test`.`t_student` trx id 582093 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 5; hex 6b757a6d61; asc kuzma;;
 1: len 6; hex 00000000090b; asc       ;;

RECORD LOCKS space id 168 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t_student` trx id 582093 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000000090b; asc       ;;
 1: len 6; hex 00000008e1c8; asc       ;;
 2: len 7; hex f4000001e4011f; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 5; hex 6b757a6d61; asc kuzma;;

As above, when the where condition is a common index field, the locking is as follows:

① add IX to the table to lock it exclusively

② add an X record lock on the index corresponding to name='kuzma '

　　③ GEN_CLUST_INDEX, add X record lock on the corresponding (automatically generated) clustered index

2.4. The table has an explicit primary key and an index

Without the where condition, all row records are added with a record lock, and there is no gap range lock
The where condition is a common index or a primary key index. As mentioned above, first add an exclusive lock on the table, then add an X record lock on the corresponding common index (not required if it is a primary key), and finally add an X record lock on the corresponding clustered index (primary key)

3, Lock mode description and 8.0 data_locks table

3.1 lock mode_ MODE

Show lock seen in engine InnoDB status_ Mode, as follows:

Lock mode	explain
IX	Intentional exclusive lock
X	Next key lock lock record and gap before recording (X)
S	Next key lock lock record and gap before record (S)
X, REC_NOT_GAP	Lock only the record itself
S, REC_NOT_GAP	Lock only the record itself
X, GAP	Gap lock, do not lock the record itself (X)
S, GAP	Gap lock, do not lock the record itself (S)
X, GAP, INSERT_INTENTION	Insert intent lock, gap range, exclusive

Data of 3.2 and 8.0_ Locks table

Before 8.0, you can view the lock information by setting innodb_status_output_locks=ON, and then in the output of show engine innodb status, you can see the detailed lock information print, as shown above.

Performance after 8.0_ schema. data_ Locks, which is newly added to record the locking of the table. The previous innodb status lock information can be printed and better recorded and displayed in the form of a table.

Topics: MySQL

Programmer Think