Analysis of the implementation of MySQL statement locking
|
Abstract: Analysis of two MySQL SQL statement locks Take a look at what locks are added to the following SQL statements 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 the primary key, is there an index on the id column? (4) If there is a secondary index on the id column, is this index a secondary index? (5) What are the execution plans of the two SQL statements? Index scan or full table scan The actual execution plan needs to be based on the output of MySQL Combination 1: id column is the primary key, RC isolation level Combination 2: id column is a secondary unique index, RC isolation level Combination 3: id column is a secondary non-unique index, RC isolation level Combination 4: id column has no index, RC isolation level Combination 5: id column is the primary key, RR isolation level Combination 6: id column is a secondary unique index, RR isolation level Combination 7: id column is a secondary non-unique index, RR isolation level Combination 8: There is no index on the id column, RR isolation level Serializable Isolation Level In the RR RC isolation level, SQL1: select is not locked, and snapshot read is used; the following only discusses the locking of SQL2: delete operation. Combination 1: id primary key + RC ---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 unique index + RC ---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-unique index + RC ---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: No id index + RC ---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 Combination 6: ID unique index + RR Combination 7: ID non-unique index + RR ---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 5985, ACTIVE 7 sec 4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2 MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X 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 64; asc d;; 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 62; asc b;; RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 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 64; asc d;; 1: len 6; hex 000000001761; asc a;; 2: len 7; hex 3f0000016d0132; asc ? m 2;; 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 62; asc b;; 1: len 6; hex 000000001761; asc a;; 2: len 7; hex 3f0000016d0110; asc ? m ;; 3: len 4; hex 8000000a; asc ;; RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000b; asc ;; 1: len 1; hex 66; asc f;; Combination 8: ID without index + RR ---TRANSACTION 1592639, ACTIVE 4 sec 2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2 MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X MySQL ---TRANSACTION 6000, ACTIVE 3 sec 2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2 MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 61; asc a;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0110; asc N ;; 3: len 4; hex 8000000f; asc ;; 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 000000001770; asc p;; 2: len 7; hex 47000001730110; asc G s ;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 63; asc c;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0122; asc N ";; 3: len 4; hex 80000006; 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 000000001770; asc p;; 2: len 7; hex 4700000173012c; asc G s ,;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 66; asc f;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0134; asc N 4;; 3: len 4; hex 8000000b; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 2; hex 7a7a; asc zz;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e013d; asc N =;; 3: len 4; hex 80000002; asc ;; Combination 9: Serializable For the simple SQL mentioned above, the last case: Serializable isolation level. For SQL2: delete from t1 where id = 10;, the Serializable isolation level is exactly the same as the Repeatable Read isolation level, so it is not introduced here. The Serializable isolation level affects SQL1: select * from t1 where id = 10; This SQL statement is snapshot read and not locked at the RC and RR isolation levels. However, at the Serializable isolation level, SQL1 will add a read lock, which means that snapshot reads no longer exist, and MVCC concurrency control is downgraded to Lock-Based CC. Conclusion: In MySQL/InnoDB, the so-called read without locking is not applicable to all situations, but is related to the isolation level. At the Serializable isolation level, reading without locking is no longer valid, and all read operations are current reads. You may also be interested in:
|
<<: How to configure VMware virtual machine NAT mode
>>: js dynamically implements table addition and deletion operations
Recommend
Detailed steps to install Hadoop cluster under Linux
Table of contents 1. Create a Hadoop directory in...
Use of align-content in flex layout line break space
1. The effect diagram implemented in this article...
MySQL data migration using MySQLdump command
The advantages of this solution are simplicity an...
Some ways to eliminate duplicate rows in MySQL
SQL statement /* Some methods of eliminating dupl...
How to solve the error when connecting to MySQL in Linux: Access denied for user 'root'@'localhost'(using password: YES)
Preface Recently connected to mysql /usr/local/my...
JavaScript implements simple scroll window
This article example shares the specific code of ...
How to upload and download files between Linux server and Windows system
Background: Linux server file upload and download...
Solve the problem that await does not work in forEach
1. Introduction A few days ago, I encountered a p...
The advantages and disadvantages of nginx and lvs and their suitable usage environment
At the very beginning, let's talk about what ...
Solve the problem after adding --subnet to Docker network Create
After adding –subnet to Docker network Create, us...
Simple tutorial on using Navicat For MySQL
recommend: Navicat for MySQL 15 Registration and ...
Meta viewport makes the web page full screen display control on iPhone
In desperation, I suddenly thought, how is the Sin...
Realize map aggregation and scattering effects based on vue+openlayer
Table of contents Preface: Result: 1. Polymerizat...
Detailed explanation of simple html and css usage
I will use three days to complete the static page...
Vue project implements graphic verification code
This article example shares the specific code of ...