Analysis of Lock Implementation of MySQL Statement
- 2021-09-11 21:39:30
- OfStack
Summary: Analysis of two SQL statement locks in MySQL
Look at what locks are added to the following SQL statement under 1
SLQ1 : select * from t1 where id = 10;
SQL2 : delete from t1 where id = 10;
(1) Is id a primary key
(2) What is the isolation level of the current system
(3) If the id column is not a primary key, is there an index on the id column
(4) If there is a 2-level index on the id column, is this index a 2-level index
(5) What are the execution plans for the two SQL? Index scan or full table scan
The actual execution plan needs to be based on the output of MySQL
Combination 1: id column is primary key, RC isolation level
Combination 2: id column is level 2 only 1 index, RC isolation level
Combination 3: The id column is a level 2 non-only 1 index, and the RC isolation level
Combination 4: id column without index, RC isolation level
Combination 5: id column is primary key, RR isolation level
Combination 6: id column is level 2 only 1 index, RR isolation level
Combination 7: id column is level 2 non-only 1 index, RR isolation level
Combination 8: No index on id column, RR isolation level
Serializable Isolation Level
Under the isolation level of RR RC, SQL1: select is not locked, and snapshot reading is adopted; The locking of SQL2: delete operations is discussed below
Percona
Combination 1: id Primary Key + RC
Percona
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5936, ACTIVE 171 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root
TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX
RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001730; asc 0;;
2: len 7; hex 26000001550110; asc & U ;;
3: len 1; hex 61; asc a;;
Combination 2: id only 1 index + RC
Updates on the only one index require two X locks, one corresponding to the only one index id=10 record and one corresponding to the cluster index name= 'd' record
Percona
---TRANSACTION 1286327, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up
TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX
RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5938, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root
TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001732; asc 2;;
2: len 7; hex 27000001560110; asc ' V ;;
3: len 4; hex 8000000a; asc ;;
Combination 3: id Non-only 1 Index + RC
If ID is listed as a common index, all the corresponding records that meet the query conditions of SQL will be locked; At the same time, these records on the primary key index will also be locked
Percona
---TRANSACTION 1286339, ACTIVE 9 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up
TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5940, ACTIVE 3 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root
TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570110; asc ( W ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570132; asc ( W 2;;
3: len 4; hex 8000000a; asc ;;
Combination 4: id No Index + RC
Percona
---TRANSACTION 1286373, ACTIVE 5 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up
TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5946, ACTIVE 2 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root
TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a0110; asc + Z ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a012c; asc + Z ,;;
3: len 4; hex 8000000a; asc ;;
Combination 5: id Primary Key + RR
Reference combination 1
Combination 6: id only 1 index + RR
Reference combination 2
Combination 7: id Non-only 1 Index + RR
Percona
---TRANSACTION 1592633, ACTIVE 24 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up
Trx read view will not see trx with id >= 1592634, sees < 1592634
TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec
MySQL
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
Combination 8: id No Index + RR
Percona
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
Combination 9: Serializable
For the simple SQL mentioned earlier, the last case: Serializable isolation level. For SQL2: delete from t1 where id = 10; In general, the Serializable isolation level is completely 1 to the Repeatable Read isolation level, so it will not be introduced.
Serializable isolation level, which affects SQL 1: select * from t1 where id = 10; This SQL, under the isolation levels of RC and RR, is a snapshot read without locking. However, at the Serializable isolation level, SQL1 will add a read lock, which means that snapshot reads no longer exist, and MVCC concurrency control will be downgraded to Lock-Based CC.
Conclusion: In MySQL/InnoDB, the so-called read unlocked is not applicable to all cases, but is related to the isolation level. Serializable isolation level, read no longer holds without lock, all read operations, are the current read.