本文我們將跟大家分享實作MySQL語句加鎖的方法,MySQL的加鎖分析,一直是比較困難的議題。我在工作過程中,常常會有同事諮詢這方面的問題。今天我們來簡單談談這個問題,希望能幫助大家。
看一下下面的SQL語句加什麼鎖定
SLQ1:select * from t1 where id = 10; SQL2:delete from t1 where id = 10;
(1)id 是不是主鍵
(2)目前系統的隔離等級是什麼
(3)id列如果不是主鍵,那麼id列上有索引嗎
(4)id列上如果有二級索引,那麼這個索引是二級索引嗎
(5)兩個SQL的執行計畫是什麼?索引掃描還是全表掃描
實際的執行計劃需要根據MySQL的輸出為準
組合一:id列是主鍵,RC隔離等級
組合二:id列是二級唯一索引,RC隔離等級
組合三:id列是二級非唯一索引,RC隔離等級
組合四:id列沒有索引,RC隔離等級
組合五:id列是主鍵,RR隔離等級
組合六:id列是二級唯一索引,RR隔離等級
組合七:id列是二級非唯一索引,RR隔離等級
組合八:id列上沒有索引,RR隔離等級
Serializable隔離等級
在RR RC隔離等級下,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
在唯一索引上的更新需要兩個X鎖,一個對應唯一索引id=10 記錄,一個對應於叢集索引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
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無索引+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
---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
---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無索引+RR #######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#########針對前面提到的簡單的SQL,最後一個情況:Serializable隔離等級。對於SQL2:delete from t1 where id = 10; 來說,Serializable隔離等級與Repeatable Read隔離等級完全一致,因此不做介紹。 ######Serializable隔離級別,影響的是SQL1:select * from t1 where id = 10; 這條SQL,在RC,RR隔離級別下,都是快照讀,不加鎖。但在Serializable隔離級別,SQL1會加讀鎖,也就是說快照讀不復存在,MVCC並發控制降級為Lock-Based CC。 ######在MySQL/InnoDB中,所謂的讀不加鎖,並不適用於所有的情況,而是隔離等級相關的。 Serializable隔離級別,讀取不加鎖就不再成立,所有的讀取操作,都是目前讀取。 ######相關推薦:#########Mysql高並發加鎖事務處理############PHP下如何對檔案進行加鎖_PHP教學## ##########MySQL交易以及加鎖定機制#######
以上是實作MySQL語句加鎖的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章!