轉(zhuǎn)自：https://blog.csdn.net/Hudonyo/article/details/81101326
1 前言
數(shù)據(jù)庫(kù)大并發(fā)操作要考慮死鎖和鎖的性能問(wèn)題刻像〕┞颍看到網(wǎng)上大多語(yǔ)焉不詳(尤其更新鎖），所以這里做個(gè)簡(jiǎn)明解釋细睡，為下面描述方便谷羞，這里用T1代表一個(gè)數(shù)據(jù)庫(kù)執(zhí)行請(qǐng)求，T2代表另一個(gè)請(qǐng)求溜徙，也可以理解為T1為一個(gè)線程湃缎，T2 為另一個(gè)線程。T3,T4以此類推蠢壹。下面以SQL Server(2005)為例嗓违。
2 鎖的種類
- 共享鎖(Shared lock)。
例1：
----------------------------------------
T1: select * from table (請(qǐng)想象它需要執(zhí)行1個(gè)小時(shí)之久图贸，后面的sql語(yǔ)句請(qǐng)都這么想象）
T2: update table set column1='hello'
過(guò)程：
T1運(yùn)行 （加共享鎖)
T2運(yùn)行
If T1 還沒(méi)執(zhí)行完
T2等......
else
鎖被釋放
T2執(zhí)行
endif
T2之所以要等蹂季，是因?yàn)門2在執(zhí)行update前，試圖對(duì)table表加一個(gè)排他鎖疏日，
而數(shù)據(jù)庫(kù)規(guī)定同一資源上不能同時(shí)共存共享鎖和排他鎖偿洁。所以T2必須等T1
執(zhí)行完，釋放了共享鎖沟优，才能加上排他鎖涕滋，然后才能開(kāi)始執(zhí)行update語(yǔ)句。
例2：
----------------------------------------
T1: select * from table
T2: select * from table
這里T2不用等待T1執(zhí)行完挠阁，而是可以馬上執(zhí)行宾肺。
分析：
T1運(yùn)行溯饵，則table被加鎖，比如叫l(wèi)ockA
T2運(yùn)行爱榕，再對(duì)table加一個(gè)共享鎖瓣喊，比如叫l(wèi)ockB。
兩個(gè)鎖是可以同時(shí)存在于同一資源上的（比如同一個(gè)表上）黔酥。這被稱為共
享鎖與共享鎖兼容。這意味著共享鎖不阻止其它session同時(shí)讀資源洪橘，但阻
止其它session update
例3：
----------------------------------------
T1: select * from table
T2: select * from table
T3: update table set column1='hello'
這次跪者，T2不用等T1運(yùn)行完就能運(yùn)行，T3卻要等T1和T2都運(yùn)行完才能運(yùn)行熄求。
因?yàn)門3必須等T1和T2的共享鎖全部釋放才能進(jìn)行加排他鎖然后執(zhí)行update
操作渣玲。
例4：（死鎖的發(fā)生）
----------------------------------------
T1:
begin tran
select * from table (holdlock) (holdlock意思是加共享鎖，直到事物結(jié)束才釋放)
update table set column1='hello'
T2:
begin tran
select * from table(holdlock)
update table set column1='world'
假設(shè)T1和T2同時(shí)達(dá)到select弟晚，T1對(duì)table加共享鎖忘衍，T2也對(duì)加共享鎖，當(dāng)
T1的select執(zhí)行完卿城，準(zhǔn)備執(zhí)行update時(shí)枚钓，根據(jù)鎖機(jī)制，T1的共享鎖需要升
級(jí)到排他鎖才能執(zhí)行接下來(lái)的update.在升級(jí)排他鎖前瑟押，必須等table上的
其它共享鎖釋放搀捷，但因?yàn)閔oldlock這樣的共享鎖只有等事務(wù)結(jié)束后才釋放，
所以因?yàn)門2的共享鎖不釋放而導(dǎo)致T1等(等T2釋放共享鎖多望，自己好升級(jí)成排
他鎖）嫩舟，同理，也因?yàn)門1的共享鎖不釋放而導(dǎo)致T2等怀偷。死鎖產(chǎn)生了家厌。
例5：
----------------------------------------
T1:
begin tran
update table set column1='hello' where id=10
T2:
begin tran
update table set column1='world' where id=20
這種語(yǔ)句雖然最為常見(jiàn)，很多人覺(jué)得它有機(jī)會(huì)產(chǎn)生死鎖椎工，但實(shí)際上要看情
況饭于，如果id是主鍵上面有索引，那么T1會(huì)一下子找到該條記錄(id=10的記
錄）晋渺，然后對(duì)該條記錄加排他鎖镰绎，T2，同樣木西，一下子通過(guò)索引定位到記錄畴栖，
然后對(duì)id=20的記錄加排他鎖，這樣T1和T2各更新各的八千，互不影響吗讶。T2也不
需要等燎猛。
但如果id是普通的一列，沒(méi)有索引照皆。那么當(dāng)T1對(duì)id=10這一行加排他鎖后重绷，
T2為了找到id=20，需要對(duì)全表掃描膜毁，那么就會(huì)預(yù)先對(duì)表加上共享鎖或更新
鎖或排他鎖(依賴于數(shù)據(jù)庫(kù)執(zhí)行策略和方式昭卓，比如第一次執(zhí)行和第二次執(zhí)行
數(shù)據(jù)庫(kù)執(zhí)行策略就會(huì)不同）。但因?yàn)門1已經(jīng)為一條記錄加了排他鎖瘟滨，導(dǎo)致
T2的全表掃描進(jìn)行不下去候醒，就導(dǎo)致T2等待。
死鎖怎么解決呢杂瘸？一種辦法是倒淫，如下：
例6：
----------------------------------------
T1:
begin tran
select * from table(xlock) (xlock意思是直接對(duì)表加排他鎖)
update table set column1='hello'
T2:
begin tran
select * from table(xlock)
update table set column1='world'
這樣，當(dāng)T1的select 執(zhí)行時(shí)败玉，直接對(duì)表加上了排他鎖敌土，T2在執(zhí)行select時(shí)，就需要等T1事物完全執(zhí)行完才能執(zhí)行运翼。排除了死鎖發(fā)生返干。
但當(dāng)?shù)谌齻€(gè)user過(guò)來(lái)想執(zhí)行一個(gè)查詢語(yǔ)句時(shí)，也因?yàn)榕潘i的存在而不得不等待南蹂，第四個(gè)犬金、第五個(gè)user也會(huì)因此而等待。在大并發(fā)
情況下六剥，讓大家等待顯得性能就太友好了晚顷，所以，這里引入了更新鎖疗疟。
</pre>
-
更新鎖(Update lock)
為解決死鎖该默，引入更新鎖。
例7：
T1:
begin tran
select * from table(updlock) (加更新鎖)
update table set column1='hello'
T2:
begin tran
select * from table(updlock)
update table set column1='world'更新鎖的意思是：“我現(xiàn)在只想讀策彤，你們別人也可以讀栓袖，但我將來(lái)可能會(huì)做更新操作，我已經(jīng)獲取了從共享鎖（用來(lái)讀）到排他鎖
（用來(lái)更新）的資格”店诗。一個(gè)事物只能有一個(gè)更新鎖獲此資格裹刮。T1執(zhí)行select，加更新鎖庞瘸。
T2運(yùn)行捧弃，準(zhǔn)備加更新鎖，但發(fā)現(xiàn)已經(jīng)有一個(gè)更新鎖在那兒了，只好等违霞。當(dāng)后來(lái)有user3嘴办、user4...需要查詢table表中的數(shù)據(jù)時(shí)，并不會(huì)因?yàn)門1的select在執(zhí)行就被阻塞买鸽，照樣能查詢涧郊，相比起例6，這提高
了效率眼五。例8:
T1: select * from table(updlock) (加更新鎖）
T2: select * from table(updlock) (等待妆艘，直到T1釋放更新鎖，因?yàn)橥粫r(shí)間不能在同一資源上有兩個(gè)更新鎖）
T3: select * from table (加共享鎖弹砚，但不用等updlock釋放双仍，就可以讀）這個(gè)例子是說(shuō)明：共享鎖和更新鎖可以同時(shí)在同一個(gè)資源上。這被稱為共享鎖和更新鎖是兼容的桌吃。
例9:
T1:
begin
select * from table(updlock) (加更新鎖）
update table set column1='hello' (重點(diǎn)：這里T1做update時(shí)，不需要等T2釋放什么苞轿，而是直接把更新鎖升級(jí)為排他鎖茅诱，然后執(zhí)行update)
T2:
begin
select * from table (T1加的更新鎖不影響T2讀取）
update table set column1='world' (T2的update需要等T1的update做完才能執(zhí)行)我們以這個(gè)例子來(lái)加深更新鎖的理解搬卒，
第一種情況：T1先達(dá)瑟俭，T2緊接到達(dá)；在這種情況中契邀，T1先對(duì)表加更新鎖摆寄，T2對(duì)表加共享鎖，假設(shè)T2的select先執(zhí)行完坯门，準(zhǔn)備執(zhí)行update微饥，
發(fā)現(xiàn)已有更新鎖存在，T2等古戴。T1執(zhí)行這時(shí)才執(zhí)行完select欠橘，準(zhǔn)備執(zhí)行update，更新鎖升級(jí)為排他鎖现恼，然后執(zhí)行update肃续，執(zhí)行完成，事務(wù)
結(jié)束叉袍，釋放鎖始锚，T2才輪到執(zhí)行update。第二種情況：T2先達(dá)喳逛，T1緊接達(dá)瞧捌；在這種情況，T2先對(duì)表加共享鎖艺配，T1達(dá)后察郁，T1對(duì)表加更新鎖衍慎，假設(shè)T2 select先結(jié)束，準(zhǔn)備
update皮钠，發(fā)現(xiàn)已有更新鎖稳捆，則等待，后面步驟就跟第一種情況一樣了麦轰。這個(gè)例子是說(shuō)明：排他鎖與更新鎖是不兼容的乔夯，它們不能同時(shí)加在同一子資源上。
</pre>
-
排他鎖（獨(dú)占鎖款侵，Exclusive Locks)
這個(gè)簡(jiǎn)單末荐，即其它事務(wù)既不能讀，又不能改排他鎖鎖定的資源新锈。
例10
T1: update table set column1='hello' where id<1000
T2: update table set column1='world' where id>1000假設(shè)T1先達(dá)甲脏，T2隨后至，這個(gè)過(guò)程中T1會(huì)對(duì)id<1000的記錄施加排他鎖.但不會(huì)阻塞T2的update妹笆。
例11 (假設(shè)id都是自增長(zhǎng)且連續(xù)的）
T1: update table set column1='hello' where id<1000
T2: update table set column1='world' where id>900如同例10块请，T1先達(dá)，T2立刻也到拳缠，T1加的排他鎖會(huì)阻塞T2的update.
</pre> -
意向鎖(Intent Locks)
<pre style="box-sizing: border-box; outline: 0px; padding: 8px; margin: 0px 0px 24px; position: relative; white-space: pre-wrap; word-wrap: break-word; overflow-x: auto; font-family: Consolas, Inconsolata, Courier, monospace; font-size: 14px; line-height: 22px; color: rgb(0, 0, 0); word-break: break-all;">意向鎖就是說(shuō)在屋（比如代表一個(gè)表）門口設(shè)置一個(gè)標(biāo)識(shí)墩新，說(shuō)明屋子里有人（比如代表某些記錄）被鎖住了。另一個(gè)人想知道屋子
里是否有人被鎖窟坐，不用進(jìn)屋子里一個(gè)一個(gè)的去查海渊，直接看門口標(biāo)識(shí)就行了。當(dāng)一個(gè)表中的某一行被加上排他鎖后哲鸳，該表就不能再被加表鎖臣疑。數(shù)據(jù)庫(kù)程序如何知道該表不能被加表鎖？一種方式是逐條的判斷該
表的每一條記錄是否已經(jīng)有排他鎖帕胆，另一種方式是直接在表這一層級(jí)檢查表本身是否有意向鎖朝捆，不需要逐條判斷。顯然后者效率高懒豹。例12：
T1: begin tran
select * from table (xlock) where id=10 --意思是對(duì)id=10這一行強(qiáng)加排他鎖
T2: begin tran
select * from table (tablock) --意思是要加表級(jí)鎖假設(shè)T1先執(zhí)行芙盘，T2后執(zhí)行，T2執(zhí)行時(shí)脸秽，欲加表鎖儒老，為判斷是否可以加表鎖，數(shù)據(jù)庫(kù)系統(tǒng)要逐條判斷table表每行記錄是否已有排他鎖记餐，
如果發(fā)現(xiàn)其中一行已經(jīng)有排他鎖了驮樊，就不允許再加表鎖了。只是這樣逐條判斷效率太低了。實(shí)際上囚衔，數(shù)據(jù)庫(kù)系統(tǒng)不是這樣工作的挖腰。當(dāng)T1的select執(zhí)行時(shí)，系統(tǒng)對(duì)表table的id=10的這一行加了排他鎖练湿，還同時(shí)悄悄的對(duì)整個(gè)表
加了意向排他鎖(IX)猴仑，當(dāng)T2執(zhí)行表鎖時(shí)，只需要看到這個(gè)表已經(jīng)有意向排他鎖存在肥哎，就直接等待辽俗，而不需要逐條檢查資源了。例13：
T1: begin tran
update table set column1='hello' where id=1
T2: begin tran
update table set column1='world' where id=1這個(gè)例子和上面的例子實(shí)際效果相同篡诽，T1執(zhí)行崖飘，系統(tǒng)對(duì)table同時(shí)對(duì)行家排他鎖、對(duì)頁(yè)加意向排他鎖杈女、對(duì)表加意向排他鎖朱浴。
</pre> -
計(jì)劃鎖(Schema Locks)
例14：
alter table .... (加schema locks，稱之為Schema modification (Sch-M) locks
DDL語(yǔ)句都會(huì)加Sch-M鎖
該鎖不允許任何其它session連接該表达椰。連都連不了這個(gè)表了赊琳，當(dāng)然更不用說(shuō)想對(duì)該表執(zhí)行什么sql語(yǔ)句了。例15:
用jdbc向數(shù)據(jù)庫(kù)發(fā)送了一條新的sql語(yǔ)句砰碴，數(shù)據(jù)庫(kù)要先對(duì)之進(jìn)行編譯，在編譯期間板丽，也會(huì)加鎖呈枉，稱之為：Schema stability (Sch-S) locks
select * from tableA
編譯這條語(yǔ)句過(guò)程中，其它session可以對(duì)表tableA做任何操作(update,delete埃碱，加排他鎖等等）猖辫，但不能做DDL(比如alter table)操作。
</pre> Bulk Update Locks 主要在批量導(dǎo)數(shù)據(jù)時(shí)用（比如用類似于oracle中的imp/exp的bcp命令）砚殿。不難理解啃憎，程序員往往也不需要關(guān)心，不贅述了似炎。
3 何時(shí)加鎖辛萍？
如何加鎖，何時(shí)加鎖羡藐，加什么鎖贩毕，你可以通過(guò)hint手工強(qiáng)行指定，但大多是數(shù)據(jù)庫(kù)系統(tǒng)自動(dòng)決定的仆嗦。這就是為什么我們可以不懂鎖也可
以高高興興的寫SQL辉阶。
例15:
T1: begin tran
update table set column1='hello' where id=1
T2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 事物隔離級(jí)別為允許臟讀
go
select * from table where id=1
這里，T2的select可以查出結(jié)果。如果事物隔離級(jí)別不設(shè)為臟讀谆甜，則T2會(huì)等T1事物執(zhí)行完才能讀出結(jié)果垃僚。
數(shù)據(jù)庫(kù)如何自動(dòng)加鎖的？
- T1執(zhí)行规辱，數(shù)據(jù)庫(kù)自動(dòng)加排他鎖
- T2執(zhí)行谆棺，數(shù)據(jù)庫(kù)發(fā)現(xiàn)事物隔離級(jí)別允許臟讀，便不加共享鎖按摘。不加共享鎖包券，則不會(huì)與已有的排他鎖沖突，所以可以臟讀炫贤。
例16:
T1: begin tran
update table set column1='hello' where id=1
T2: select * from table where id=1 --為指定隔離級(jí)別溅固，則使用系統(tǒng)默認(rèn)隔離級(jí)別，它不允許臟讀
如果事物級(jí)別不設(shè)為臟讀兰珍，則：
- T1執(zhí)行侍郭，數(shù)據(jù)庫(kù)自動(dòng)加排他鎖
- T2執(zhí)行，數(shù)據(jù)庫(kù)發(fā)現(xiàn)事物隔離級(jí)別不允許臟讀掠河，便準(zhǔn)備為此次select過(guò)程加共享鎖亮元，但發(fā)現(xiàn)加不上，因?yàn)橐呀?jīng)有排他鎖了唠摹，所以就
等啊等爆捞。直到T1執(zhí)行完，釋放了排他鎖勾拉，T2才加上了共享鎖煮甥，然后開(kāi)始讀....
</pre>
4 鎖的粒度
鎖的粒度就是指鎖的生效范圍，就是說(shuō)是行鎖藕赞，還是頁(yè)鎖成肘，還是整表鎖. 鎖的粒度同樣既可以由數(shù)據(jù)庫(kù)自動(dòng)管理，也可以通過(guò)手工指定hint來(lái)管理斧蜕。
例17：
T1: select * from table (paglock)
T2: update table set column1='hello' where id>10
T1執(zhí)行時(shí)双霍，會(huì)先對(duì)第一頁(yè)加鎖，讀完第一頁(yè)后批销，釋放鎖洒闸，再對(duì)第二頁(yè)加鎖，依此類推风钻。假設(shè)前10行記錄恰好是一頁(yè)(當(dāng)然顷蟀，一般不可能
一頁(yè)只有10行記錄），那么T1執(zhí)行到第一頁(yè)查詢時(shí)骡技，并不會(huì)阻塞T2的更新鸣个。
例18：
T1: select * from table (rowlock)
T2: update table set column1='hello' where id=10
T1執(zhí)行時(shí)羞反，對(duì)每行加共享鎖，讀取囤萤，然后釋放昼窗，再對(duì)下一行加鎖;T2執(zhí)行時(shí)，會(huì)對(duì)id=10的那一行試圖加鎖涛舍，只要該行沒(méi)有被T1加上行鎖澄惊，
T2就可以順利執(zhí)行update操作。
例19：
T1: select * from table (tablock)
T2: update table set column1='hello' where id = 10
T1執(zhí)行富雅，對(duì)整個(gè)表加共享鎖. T1必須完全查詢完掸驱，T2才可以允許加鎖，并開(kāi)始更新没佑。
以上3例是手工指定鎖的粒度毕贼，也可以通過(guò)設(shè)定事物隔離級(jí)別，讓數(shù)據(jù)庫(kù)自動(dòng)設(shè)置鎖的粒度蛤奢。不同的事物隔離級(jí)別鬼癣，數(shù)據(jù)庫(kù)會(huì)有不同的
加鎖策略（比如加什么類型的鎖，加什么粒度的鎖）啤贩。具體請(qǐng)查聯(lián)機(jī)手冊(cè)待秃。
</pre>
5 鎖與事物隔離級(jí)別的優(yōu)先級(jí)
手工指定的鎖優(yōu)先，
例20:
T1: GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT * FROM table (NOLOCK)
GO
T2: update table set column1='hello' where id=10
T1是事物隔離級(jí)別為最高級(jí)痹屹，串行鎖章郁，數(shù)據(jù)庫(kù)系統(tǒng)本應(yīng)對(duì)后面的select語(yǔ)句自動(dòng)加表級(jí)鎖，但因?yàn)槭止ぶ付薔OLOCK志衍，所以該select
語(yǔ)句不會(huì)加任何鎖驱犹，所以T2也就不會(huì)有任何阻塞。
</pre>
6 數(shù)據(jù)庫(kù)的其它重要Hint以及它們的區(qū)別
holdlock 對(duì)表加共享鎖足画，且事物不完成，共享鎖不釋放佃牛。
-
tablock 對(duì)表加共享鎖淹辞，只要statement不完成，共享鎖不釋放俘侠。
與holdlock區(qū)別象缀，見(jiàn)下例：
例21
T1:
begin tran
select * from table (tablock)
T2:
begin tran
update table set column1='hello' where id = 10T1執(zhí)行完select，就會(huì)釋放共享鎖爷速，然后T2就可以執(zhí)行update. 此之謂tablock. 下面我們看holdlock
例22
T1:
begin tran
select * from table (holdlock)
T2:
begin tran
update table set column1='hello' where id = 10T1執(zhí)行完select央星，共享鎖仍然不會(huì)釋放，仍然會(huì)被hold(持有）惫东，T2也因此必須等待而不能update. 當(dāng)T1最后執(zhí)行了commit或
rollback說(shuō)明這一個(gè)事物結(jié)束了莉给，T2才取得執(zhí)行權(quán)毙石。 -
TABLOCKX 對(duì)表加排他鎖
例23：
T1: select * from table(tablockx) (強(qiáng)行加排他鎖）
其它session就無(wú)法對(duì)這個(gè)表進(jìn)行讀和更新了，除非T1執(zhí)行完了颓遏，就會(huì)自動(dòng)釋放排他鎖徐矩。
例24：
T1: begin tran
select * from table(tablockx)
這次，單單select執(zhí)行完還不行叁幢，必須整個(gè)事物完成（執(zhí)行了commit或rollback后）才會(huì)釋放排他鎖滤灯。 -
xlock 加排他鎖
那它跟tablockx有何區(qū)別呢？它可以這樣用曼玩，
例25:
select * from table(xlock paglock) 對(duì)page加排他鎖
而TABLELOCX不能這么用鳞骤。xlock還可這么用：select * from table(xlock tablock) 效果等同于select * from table(tablockx)
</pre>
7 鎖的超時(shí)等待
例26
SET LOCK_TIMEOUT 4000 用來(lái)設(shè)置鎖等待時(shí)間，單位是毫秒黍判，4000意味著等待
4秒可以用select @@LOCK_TIMEOUT查看當(dāng)前session的鎖超時(shí)設(shè)置豫尽。-1 意味著
永遠(yuǎn)等待。
T1: begin tran
udpate table set column1='hello' where id = 10
T2: set lock_timeout 4000
select * from table wehre id = 10
</pre>
T2執(zhí)行時(shí)样悟，會(huì)等待T1釋放排他鎖拂募，等了4秒鐘，如果T1還沒(méi)有釋放排他鎖窟她，T2就會(huì)拋出異常： Lock request time out period exceeded.
8 附：各種鎖的兼容關(guān)系表
| Requested mode | IS | S | U | IX | SIX | X |
| Intent shared (IS) | Yes | Yes | Yes | Yes | Yes | No |
| Shared (S) | Yes | Yes | Yes | No | No | No |
| Update (U) | Yes | Yes | No | No | No | No |
| Intent exclusive (IX) | Yes | No | No | Yes | No | No |
| Shared with intent exclusive (SIX) | Yes | No | No | No | No | No |
| Exclusive (X) | No | No | No | No | No | No |
9 如何提高并發(fā)效率
- 悲觀鎖：利用數(shù)據(jù)庫(kù)本身的鎖機(jī)制實(shí)現(xiàn)陈症。通過(guò)上面對(duì)數(shù)據(jù)庫(kù)鎖的了解，可以根據(jù)具體業(yè)務(wù)情況綜合使用事務(wù)隔離級(jí)別與合理的手工指定鎖的方式比如降低鎖的粒度等減少并發(fā)等待震糖。
- 樂(lè)觀鎖：利用程序處理并發(fā)录肯。原理都比較好理解，基本一看即懂吊说。方式大概有以下3種
- 對(duì)記錄加版本號(hào).
- 對(duì)記錄加時(shí)間戳.
- 對(duì)將要更新的數(shù)據(jù)進(jìn)行提前讀取论咏、事后對(duì)比。
不論是數(shù)據(jù)庫(kù)系統(tǒng)本身的鎖機(jī)制颁井，還是樂(lè)觀鎖這種業(yè)務(wù)數(shù)據(jù)級(jí)別上的鎖機(jī)制厅贪，本質(zhì)上都是對(duì)狀態(tài)位的讀、寫雅宾、判斷养涮。
