Mysql中select加鎖的示例分析

這篇文章將為大家詳細(xì)講解有關(guān)Mysql中select加鎖的示例分析，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

引言

大家在面試中有沒遇到面試官問你下面六句Sql的區(qū)別呢

select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update

如果你能清楚的說出，這六句sql在不同的事務(wù)隔離級(jí)別下，是否加鎖，加的是共享鎖還是排他鎖，是否存在間隙鎖，那這篇文章就沒有看的意義了。
之所以寫這篇文章是因?yàn)槟壳盀橹咕W(wǎng)上這方面的文章太片面，都只說了一半，且大多沒指明隔離級(jí)別，以及where后跟的是否為索引條件列。在此，我就不一一列舉那些有誤的文章了，大家可以自行百度一下，大多都是講不清楚。
OK，要回答這個(gè)問題，先問自己三個(gè)問題

• 當(dāng)前事務(wù)隔離級(jí)別是什么

• id列是否存在索引

• 如果存在索引是聚簇索引還是非聚簇索引呢？

OK，開始回答

正文

• innodb一定存在聚簇索引，默認(rèn)以主鍵作為聚簇索引

• 有幾個(gè)索引，就有幾棵B+樹(不考慮hash索引的情形)

• 聚簇索引的葉子節(jié)點(diǎn)為磁盤上的真實(shí)數(shù)據(jù)。非聚簇索引的葉子節(jié)點(diǎn)還是索引，指向聚簇索引B+樹。

下面啰嗦點(diǎn)基礎(chǔ)知識(shí)

鎖類型

共享鎖(S鎖):假設(shè)事務(wù)T1對(duì)數(shù)據(jù)A加上共享鎖，那么事務(wù)T2可以讀數(shù)據(jù)A，不能修改數(shù)據(jù)A。
排他鎖(X鎖):假設(shè)事務(wù)T1對(duì)數(shù)據(jù)A加上共享鎖，那么事務(wù)T2不能讀數(shù)據(jù)A，不能修改數(shù)據(jù)A。
我們通過update、delete等語句加上的鎖都是行級(jí)別的鎖。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申請(qǐng)表級(jí)別的鎖。
意向共享鎖(IS鎖):一個(gè)事務(wù)在獲?。ㄈ魏我恍?或者全表）S鎖之前，一定會(huì)先在所在的表上加IS鎖。
意向排他鎖(IX鎖):一個(gè)事務(wù)在獲取（任何一行/或者全表）X鎖之前，一定會(huì)先在所在的表上加IX鎖。

意向鎖存在的目的?

OK，這里說一下意向鎖存在的目的。假設(shè)事務(wù)T1，用X鎖來鎖住了表上的幾條記錄，那么此時(shí)表上存在IX鎖，即意向排他鎖。那么此時(shí)事務(wù)T2要進(jìn)行LOCK TABLE … WRITE的表級(jí)別鎖的請(qǐng)求，可以直接根據(jù)意向鎖是否存在而判斷是否有鎖沖突。

加鎖算法

我的說法是來自官方文檔:
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
加上自己矯揉造作的見解得出。
ok，記得如下三種，本文就夠用了
Record Locks：簡單翻譯為行鎖吧。注意了，該鎖是對(duì)索引記錄進(jìn)行加鎖！鎖是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行鎖最終都會(huì)落到聚簇索引上！
Gap Locks：簡單翻譯為間隙鎖，是對(duì)索引的間隙加鎖，其目的只有一個(gè)，防止其他事物插入數(shù)據(jù)。在Read Committed隔離級(jí)別下，不會(huì)使用間隙鎖。這里我對(duì)官網(wǎng)補(bǔ)充一下，隔離級(jí)別比Read Committed低的情況下，也不會(huì)使用間隙鎖，如隔離級(jí)別為Read Uncommited時(shí)，也不存在間隙鎖。當(dāng)隔離級(jí)別為Repeatable Read和Serializable時(shí)，就會(huì)存在間隙鎖。
Next-Key Locks：這個(gè)理解為Record Lock+索引前面的Gap Lock。記住了，鎖住的是索引前面的間隙！比如一個(gè)索引包含值，10，11，13和20。那么，間隙鎖的范圍如下

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

快照讀和當(dāng)前讀

最后一點(diǎn)基礎(chǔ)知識(shí)了，大家堅(jiān)持看完，這些是后面分析的基礎(chǔ)！
在mysql中select分為快照讀和當(dāng)前讀，執(zhí)行下面的語句

select * from table where id = ?;

執(zhí)行的是快照讀，讀的是數(shù)據(jù)庫記錄的快照版本，是不加鎖的。（這種說法在隔離級(jí)別為Serializable中不成立，后面我會(huì)補(bǔ)充。）
那么，執(zhí)行

select * from table where id = ? lock in share mode;

會(huì)對(duì)讀取記錄加S鎖 (共享鎖)，執(zhí)行

select * from table where id = ? for update

會(huì)對(duì)讀取記錄加X鎖 (排他鎖)，那么

加的是表鎖還是行鎖呢？

針對(duì)這點(diǎn)，我們先回憶一下事務(wù)的四個(gè)隔離級(jí)別，他們由弱到強(qiáng)如下所示:

• Read Uncommited(RU)：讀未提交，一個(gè)事務(wù)可以讀到另一個(gè)事務(wù)未提交的數(shù)據(jù)！

• Read Committed (RC)：讀已提交，一個(gè)事務(wù)可以讀到另一個(gè)事務(wù)已提交的數(shù)據(jù)!

• Repeatable Read (RR):可重復(fù)讀，加入間隙鎖，一定程度上避免了幻讀的產(chǎn)生！注意了，只是一定程度上，并沒有完全避免!我會(huì)在下一篇文章說明!另外就是記住從該級(jí)別才開始加入間隙鎖(這句話記下來，后面有用到)!

• Serializable：串行化，該級(jí)別下讀寫串行化，且所有的select語句后都自動(dòng)加上lock in share mode，即使用了共享鎖。因此在該隔離級(jí)別下，使用的是當(dāng)前讀，而不是快照讀。

那么關(guān)于是表鎖還是行鎖，大家可以看到網(wǎng)上最流傳的一個(gè)說法是這樣的，

InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。 InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖！

這句話大家可以搜一下，都是你抄我的，我抄你的。那么，這句話本身有兩處錯(cuò)誤！
錯(cuò)誤一:并不是用表鎖來實(shí)現(xiàn)鎖表的操作，而是利用了Next-Key Locks，也可以理解為是用了行鎖+間隙鎖來實(shí)現(xiàn)鎖表的操作!
為了便于說明，我來個(gè)例子，假設(shè)有表數(shù)據(jù)如下，pId為主鍵索引

執(zhí)行語句(name列無索引)

select * from table where name = `aaa` for update

那么此時(shí)在pId=1,2,7這三條記錄上存在行鎖(把行鎖住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在間隙鎖(把間隙鎖住了)。因此，給人一種整個(gè)表鎖住的錯(cuò)覺！

ps:對(duì)該結(jié)論有疑問的，可自行執(zhí)行show engine innodb status;語句進(jìn)行分析。

錯(cuò)誤二:所有文章都不提隔離級(jí)別！
注意我上面說的，之所以能夠鎖表，是通過行鎖+間隙鎖來實(shí)現(xiàn)的。那么，RU和RC都不存在間隙鎖，這種說法在RU和RC中還能成立么？
因此，該說法只在RR和Serializable中是成立的。如果隔離級(jí)別為RU和RC，無論條件列上是否有索引，都不會(huì)鎖表，只鎖行！

分析

下面來對(duì)開始的問題作出解答，假設(shè)有表如下，pId為主鍵索引

RC/RU+條件列非索引

(1)select * from table where num = 200
不加任何鎖，是快照讀。
(2)select * from table where num > 200
不加任何鎖，是快照讀。
(3)select * from table where num = 200 lock in share mode
當(dāng)num = 200，有兩條記錄。這兩條記錄對(duì)應(yīng)的pId=2，7，因此在pId=2，7的聚簇索引上加行級(jí)S鎖，采用當(dāng)前讀。
(4)select * from table where num > 200 lock in share mode
當(dāng)num > 200，有一條記錄。這條記錄對(duì)應(yīng)的pId=3，因此在pId=3的聚簇索引上加上行級(jí)S鎖，采用當(dāng)前讀。
(5)select * from table where num = 200 for update
當(dāng)num = 200，有兩條記錄。這兩條記錄對(duì)應(yīng)的pId=2，7，因此在pId=2，7的聚簇索引上加行級(jí)X鎖，采用當(dāng)前讀。
(6)select * from table where num > 200 for update
當(dāng)num > 200，有一條記錄。這條記錄對(duì)應(yīng)的pId=3，因此在pId=3的聚簇索引上加上行級(jí)X鎖，采用當(dāng)前讀。

RC/RU+條件列是聚簇索引

恩，大家應(yīng)該知道pId是主鍵列，因此pId用的就是聚簇索引。此情況其實(shí)和RC/RU+條件列非索引情況是類似的。
(1)select * from table where pId = 2
不加任何鎖，是快照讀。
(2)select * from table where pId > 2
不加任何鎖，是快照讀。
(3)select * from table where pId = 2 lock in share mode
在pId=2的聚簇索引上，加S鎖，為當(dāng)前讀。
(4)select * from table where pId > 2 lock in share mode
在pId=3，7的聚簇索引上，加S鎖，為當(dāng)前讀。
(5)select * from table where pId = 2 for update
在pId=2的聚簇索引上，加X鎖，為當(dāng)前讀。
(6)select * from table where pId > 2 for update
在pId=3，7的聚簇索引上，加X鎖，為當(dāng)前讀。

這里，大家可能有疑問

為什么條件列加不加索引，加鎖情況是一樣的？

ok,其實(shí)是不一樣的。在RC/RU隔離級(jí)別中，MySQL Server做了優(yōu)化。在條件列沒有索引的情況下，盡管通過聚簇索引來掃描全表，進(jìn)行全表加鎖。但是，MySQL Server層會(huì)進(jìn)行過濾并把不符合條件的鎖當(dāng)即釋放掉，因此你看起來最終結(jié)果是一樣的。但是RC/RU+條件列非索引比本例多了一個(gè)釋放不符合條件的鎖的過程！

RC/RU+條件列是非聚簇索引

我們在num列上建上非唯一索引。此時(shí)有一棵聚簇索引(主鍵索引，pId)形成的B+索引樹，其葉子節(jié)點(diǎn)為硬盤上的真實(shí)數(shù)據(jù)。以及另一棵非聚簇索引(非唯一索引，num)形成的B+索引樹，其葉子節(jié)點(diǎn)依然為索引節(jié)點(diǎn)，保存了num列的字段值，和對(duì)應(yīng)的聚簇索引。

接下來分析開始
(1)select * from table where num = 200
不加任何鎖，是快照讀。
(2)select * from table where num > 200
不加任何鎖，是快照讀。
(3)select * from table where num = 200 lock in share mode
當(dāng)num = 200，由于num列上有索引，因此先在 num = 200的兩條索引記錄上加行級(jí)S鎖。接著，去聚簇索引樹上查詢，這兩條記錄對(duì)應(yīng)的pId=2，7，因此在pId=2，7的聚簇索引上加行級(jí)S鎖，采用當(dāng)前讀。
(4)select * from table where num > 200 lock in share mode
當(dāng)num > 200，由于num列上有索引，因此先在符合條件的 num = 300的一條索引記錄上加行級(jí)S鎖。接著，去聚簇索引樹上查詢，這條記錄對(duì)應(yīng)的pId=3，因此在pId=3的聚簇索引上加行級(jí)S鎖，采用當(dāng)前讀。
(5)select * from table where num = 200 for update
當(dāng)num = 200，由于num列上有索引，因此先在 num = 200的兩條索引記錄上加行級(jí)X鎖。接著，去聚簇索引樹上查詢，這兩條記錄對(duì)應(yīng)的pId=2，7，因此在pId=2，7的聚簇索引上加行級(jí)X鎖，采用當(dāng)前讀。
(6)select * from table where num > 200 for update
當(dāng)num > 200，由于num列上有索引，因此先在符合條件的 num = 300的一條索引記錄上加行級(jí)X鎖。接著，去聚簇索引樹上查詢，這條記錄對(duì)應(yīng)的pId=3，因此在pId=3的聚簇索引上加行級(jí)X鎖，采用當(dāng)前讀。

RR/Serializable+條件列非索引

RR級(jí)別需要多考慮的就是gap lock，他的加鎖特征在于，無論你怎么查都是鎖全表。如下所示
接下來分析開始
(1)select * from table where num = 200
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加S鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
(2)select * from table where num > 200
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加S鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
(3)select * from table where num = 200 lock in share mode
在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加S鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
(4)select * from table where num > 200 lock in share mode
在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加S鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
(5)select * from table where num = 200 for update
在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加X鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
(6)select * from table where num > 200 for update
在pId = 1,2,3,7（全表所有記錄）的聚簇索引上加X鎖。并且在
聚簇索引的所有間隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

RR/Serializable+條件列是聚簇索引

恩，大家應(yīng)該知道pId是主鍵列，因此pId用的就是聚簇索引。該情況的加鎖特征在于，如果where后的條件為精確查詢(=的情況)，那么只存在record lock。如果where后的條件為范圍查詢(>或<的情況)，那么存在的是record lock+gap lock。
(1)select * from table where pId = 2
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，是當(dāng)前讀，在pId=2的聚簇索引上加S鎖，不存在gap lock。
(2)select * from table where pId > 2
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，是當(dāng)前讀，在pId=3,7的聚簇索引上加S鎖。在(2,3)(3,7)(7,+∞)加上gap lock
(3)select * from table where pId = 2 lock in share mode
是當(dāng)前讀，在pId=2的聚簇索引上加S鎖，不存在gap lock。
(4)select * from table where pId > 2 lock in share mode
是當(dāng)前讀，在pId=3,7的聚簇索引上加S鎖。在(2,3)(3,7)(7,+∞)加上gap lock
(5)select * from table where pId = 2 for update
是當(dāng)前讀，在pId=2的聚簇索引上加X鎖。
(6)select * from table where pId > 2 for update
在pId=3,7的聚簇索引上加X鎖。在(2,3)(3,7)(7,+∞)加上gap lock
(7)select * from table where pId = 6 [lock in share mode|for update]
注意了，pId=6是不存在的列，這種情況會(huì)在(3,7)上加gap lock。
(8)select * from table where pId > 18 [lock in share mode|for update]
注意了，pId>18，查詢結(jié)果是空的。在這種情況下，是在(7,+∞)上加gap lock。

RR/Serializable+條件列是非聚簇索引

這里非聚簇索引，需要區(qū)分是否為唯一索引。因?yàn)槿绻欠俏ㄒ凰饕?，間隙鎖的加鎖方式是有區(qū)別的。
先說一下，唯一索引的情況。如果是唯一索引，情況和RR/Serializable+條件列是聚簇索引類似，唯一有區(qū)別的是:這個(gè)時(shí)候有兩棵索引樹，加鎖是加在對(duì)應(yīng)的非聚簇索引樹和聚簇索引樹上！大家可以自行推敲!
下面說一下，非聚簇索引是非唯一索引的情況，他和唯一索引的區(qū)別就是通過索引進(jìn)行精確查詢以后，不僅存在record lock，還存在gap lock。而通過唯一索引進(jìn)行精確查詢后，只存在record lock，不存在gap lock。老規(guī)矩在num列建立非唯一索引
(1)select * from table where num = 200
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，是當(dāng)前讀，在pId=2，7的聚簇索引上加S鎖，在num=200的非聚集索引上加S鎖，在(100,200)(200,300)加上gap lock。
(2)select * from table where num > 200
在RR級(jí)別下，不加任何鎖，是快照讀。
在Serializable級(jí)別下，是當(dāng)前讀，在pId=3的聚簇索引上加S鎖，在num=300的非聚集索引上加S鎖。在(200,300)(300,+∞)加上gap lock
(3)select * from table where num = 200 lock in share mode
是當(dāng)前讀，在pId=2，7的聚簇索引上加S鎖，在num=200的非聚集索引上加S鎖，在(100,200)(200,300)加上gap lock。
(4)select * from table where num > 200 lock in share mode
是當(dāng)前讀，在pId=3的聚簇索引上加S鎖，在num=300的非聚集索引上加S鎖。在(200,300)(300,+∞)加上gap lock。
(5)select * from table where num = 200 for update
是當(dāng)前讀，在pId=2，7的聚簇索引上加S鎖，在num=200的非聚集索引上加X鎖，在(100,200)(200,300)加上gap lock。
(6)select * from table where num > 200 for update
是當(dāng)前讀，在pId=3的聚簇索引上加S鎖，在num=300的非聚集索引上加X鎖。在(200,300)(300,+∞)加上gap lock
(7)select * from table where num = 250 [lock in share mode|for update]
注意了，num=250是不存在的列，這種情況會(huì)在(200,300)上加gap lock。
(8)select * from table where num > 400 [lock in share mode|for update]
注意了，pId>400，查詢結(jié)果是空的。在這種情況下，是在(400,+∞)上加gap lock。

關(guān)于“Mysql中select加鎖的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。