實現MySQL語句加鎖的方法

418

本文我們將和大家分享實現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
ID列為普通索引，那么對應的所有滿足SQL查詢條件的記錄，都會被加鎖；同時，這些記錄在主鍵索引上的記錄，也會被加鎖
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
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?&gt;=?1592634,?sees?<p>MySQL<br></p><p class="jb51code"><br></p><pre class="brush:sql;">---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
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

針對前面提到的簡單的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高并發加鎖事務處理

Mysql高并發加鎖事務處理

Mysql高并發加鎖事務處理

? 版權聲明
THE END
數據庫
# mysql
喜歡就支持一下吧
點贊8 分享