MySQL語(yǔ)句加鎖的實(shí)現(xiàn)分析

更新時(shí)間：2017年10月18日 08:44:29 作者：笱局長(zhǎng)

MySQL的加鎖分析，一直是一個(gè)比較困難的話(huà)題。我在工作過(guò)程中，經(jīng)常會(huì)有同事咨詢(xún)這方面的問(wèn)題。今天我們來(lái)簡(jiǎn)單談?wù)勥@個(gè)問(wèn)題

摘要： MySQL兩條SQL語(yǔ)句鎖的分析

看一下下面的SQL語(yǔ)句加什么鎖

SLQ1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;

（1）id 是不是主鍵

（2）當(dāng)前系統(tǒng)的隔離級(jí)別是什么

（3）id列如果不是主鍵，那么id列上有索引嗎

（4）id列上如果有二級(jí)索引，那么這個(gè)索引是二級(jí)索引嗎

（5）兩個(gè)SQL的執(zhí)行計(jì)劃是什么？索引掃描還是全表掃描

實(shí)際的執(zhí)行計(jì)劃需要根據(jù)MySQL的輸出為準(zhǔn)

組合一：id列是主鍵，RC隔離級(jí)別
組合二：id列是二級(jí)唯一索引，RC隔離級(jí)別
組合三：id列是二級(jí)非唯一索引，RC隔離級(jí)別
組合四：id列沒(méi)有索引，RC隔離級(jí)別
組合五：id列是主鍵，RR隔離級(jí)別
組合六：id列是二級(jí)唯一索引，RR隔離級(jí)別
組合七：id列是二級(jí)非唯一索引，RR隔離級(jí)別
組合八：id列上沒(méi)有索引，RR隔離級(jí)別

Serializable隔離級(jí)別

在RR RC隔離級(jí)別下，SQL1：select 均不加鎖，采用的是快照讀；以下僅討論SQL2：delete操作的加鎖
Percona

組合一：id主鍵+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;;

組合二：id唯一索引+RC
在唯一索引上的更新需要兩個(gè)X鎖，一個(gè)對(duì)應(yīng)唯一索引id=10 記錄，一個(gè)對(duì)應(yīng)于聚簇索引name='d'的記錄
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   ;;

組合三：id非唯一索引+RC
ID列為普通索引，那么對(duì)應(yīng)的所有滿(mǎn)足SQL查詢(xún)條件的記錄，都會(huì)被加鎖；同時(shí)，這些記錄在主鍵索引上的記錄，也會(huì)被加鎖
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   ;;

組合四：id無(wú)索引+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   ;;

組合五：id主鍵+RR
參考組合一

組合六：id唯一索引+RR
參考組合二

組合七：id非唯一索引+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 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;;

組合八：id無(wú)索引+RR
Percona

---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   ;;

組合九：Serializable

針對(duì)前面提到的簡(jiǎn)單的SQL，最后一個(gè)情況：Serializable隔離級(jí)別。對(duì)于SQL2：delete from t1 where id = 10; 來(lái)說(shuō)，Serializable隔離級(jí)別與Repeatable Read隔離級(jí)別完全一致，因此不做介紹。

Serializable隔離級(jí)別，影響的是SQL1：select * from t1 where id = 10; 這條SQL，在RC，RR隔離級(jí)別下，都是快照讀，不加鎖。但是在Serializable隔離級(jí)別，SQL1會(huì)加讀鎖，也就是說(shuō)快照讀不復(fù)存在，MVCC并發(fā)控制降級(jí)為L(zhǎng)ock-Based CC。

結(jié)論：在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是隔離級(jí)別相關(guān)的。Serializable隔離級(jí)別，讀不加鎖就不再成立，所有的讀操作，都是當(dāng)前讀。

您可能感興趣的文章: