Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to lock the MySQL statement, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand how to lock the MySQL statement.

SLQ1:select * from T1 where id = 10 * SQL2where id delete from T1 where id = 10

(1) whether id is the 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 secondary index on the id column, is it a secondary index?

(5) what is the implementation plan of 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 the primary key, RC isolation level

Combination 2: id column is the second-level unique index, RC isolation level

Combination 3: id column is a secondary non-unique index, RC isolation level

Group 4: id column has no index, RC isolation level

Combination five: id column is the primary key, RR isolation level

Combination 6: id column is the second-level unique index, RR isolation level

Combination 7: id column is a secondary non-unique index, RR isolation level

Group 8: no index on id column, RR isolation level

Serializable isolation level

Under the RR RC isolation level, SQL1:select is unlocked and snapshot read is used. The locking of SQL2:delete operations is only discussed below.

Percona

Combination one: id primary key + RC

Percona

-TRANSACTION 1286310, ACTIVE 9 sec2 lock struct (s), heap size 360,1 row lock (s), undo log entries 1MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning upTABLE LOCK table `test`.`t1` trx id 1286310 lock mode IXRECORD 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 171sec2 lock struct (s), heap size 360,1 row lock (s), undo log entries 1MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost rootTABLE LOCK table `test`.`t1` trx id 5936 lock mode IXRECORD 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 gapRecord lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000a; asc; 1: len 6; hex 0000001730; asc 0 2: len 7; hex 26000001550110; asc & U;; 3: len 1; hex 61; asc a

Combination two: id unique index + RC

Updates on a unique index require two X locks, one corresponding to the unique index id=10 record and one corresponding to the clustered index name='d' record

Percona

-TRANSACTION 1286327, ACTIVE 3 sec3 lock struct (s), heap size 360,2 row lock (s), undo log entries 1MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning upTABLE LOCK table `test`.`t2` trx id 1286327 lock mode IXRECORD 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 gapRECORD 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 sec3 lock struct (s), heap size 360,2 row lock (s), undo log entries 1MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374localhost rootTABLE LOCK table `test`.`t2` trx id 5938 lock mode IXRECORD 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 gapRecord lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 800000a; 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 gapRecord lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 1; hex 64; asc dscape; 1: len 6; hex 0000001732; asc 2; 2: len 7; hex 27000001560110; asc'V; 3: len 4; hex 8000a;

Combination 3: id non-unique index + RC

If ID is listed as a normal index, then all records that meet the SQL query criteria will be locked; at the same time, records on the primary key index will also be locked.

Percona

-TRANSACTION 1286339, ACTIVE 9 sec3 lock struct (s), heap size 360,4 row lock (s), undo log entries 2MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning upTABLE LOCK table `test`.`t3` trx id 1286339 lock mode IXRECORD 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 gapRECORD 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

Combination 4: id No Index + RC

Percona

-TRANSACTION 1286373, ACTIVE 5 sec2 lock struct (s), heap size 360,2 row lock (s), undo log entries 2MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning upTABLE LOCK table `test`.`t4` trx id 1286373 lock mode IXRECORD 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 sec2 lock struct (s), heap size 360,2 row lock (s), undo log entries 2MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost rootTABLE LOCK table `test`.`t4` trx id 5946 lock mode IXRECORD 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 gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 1; hex 62; asc btwitch; 1: len 6; hex 000000173a; asc: 2: len 7; hex 2b0000015a0110; asc + Z; 3: len 4; hex 80000000a; asc; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 1; hex 64; asc dscape; 1: len 6; hex 000000173a; asc:; 2: len 7; hex 2b0000015a012c; asc + Z,; 3: len 4; hex 8000000a; asc;

Combination five: id primary key + RR

Reference combination one

Combination six: id unique index + RR

Reference combination two

Combination 7: id non-unique index + RR

Percona

-TRANSACTION 1592633, ACTIVE 24 sec4 lock struct (s), heap size 1184, 5 row lock (s), undo log entries 2MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning upTrx read view will not see trx with id > = 1592634 Sees < 1592634TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IXRECORD LOCKS space id 31 page no 4 n bits 80 index `idx_ key`of table `test`.`t3` trx id 1592633 lock_mode XRECORD 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 gapRECORD 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 sec4 lock struct (s), heap size 1184, 5 row lock (s), undo log entries 2MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost rootTABLE LOCK table `test`.`t3` trx id 5985 lock mode IXRECORD LOCKS space id 8 page no 4 n bits 80 index `idx_ key`of table `test`.`t3` trx id 5985 lock_mode XRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 800000a; asc; 1: len 1; hex 64; asc d Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 8000000a; asc; 1: len 1; hex 62; asc bechitaneRecord 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 gapRecord lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 1; hex 64; asc d tinct; 1: len 6; hex 0000001761; asc aX; 2: len 7; hex 3f0000016d0132; asc? M 2: len; 3: len 4; hex 8000000a; asc; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 1; hex 62; asc b; 1: len 6; hex 0000001761; 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 recRecord lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000000b; asc; 1: len 1; hex 66; asc f

Combination eight: id No Index + RR

Percona

-TRANSACTION 1592639, ACTIVE 4 sec2 lock struct (s), heap size 360,7 row lock (s), undo log entries 2MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning upTABLE LOCK table `test`.`t4` trx id 1592639 lock mode IXRECORD 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 sec2 lock struct (s), heap size 360,7 row lock (s), undo log entries 2MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost rootTABLE LOCK table `test`.`t4` trx id 6000 lock mode IXRECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format Info bits 00: len 1; hex 61; asc an asc; 1: len 6; hex 0000001722; 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 320: len 1; hex 62; asc b politics; 1: len 6; hex 0000001770; asc pscape; 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 00: len 1; hex 63; asc c-politics; 1: len 6; hex 0000001722; 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 320: len 1; hex 64; asc d politics; 1: len 6; hex 0000001770; asc pscape; 2: len 7; hex 47000001730c; asc G s 3: len 4; hex 80000000a; asc; Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 1; hex 66; asc f * *; 1: len 6; hex 0000001722; 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 00: len 2; hex 7a7a; asc zz;; 1: len 6; hex 0000001722; asc" 2: len 7; hex 9e0000014e013d; asc N =;; 3: len 4; hex 80000002; asc

Combination 9: Serializable

For the simple SQL mentioned earlier, the last case is the 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.

The Serializable isolation level affects SQL1:select * from T1 where id = 10. This SQL, under the RC,RR isolation level, is snapshot read and unlocked. But at the Serializable isolation level, SQL1 adds read locks, that is, snapshot reads no longer exist, and MVCC concurrency control is degraded to Lock-Based CC.

In MySQL/InnoDB, the so-called read unlocking does not apply in all cases, but is related to the isolation level. Serializable isolation level, read without lock is no longer established, all read operations are the current read.

After reading the above about MySQL sentence how to lock, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.