數(shù)據(jù)庫(kù)中的鎖

1 前言

數(shù)據(jù)庫(kù)大并發(fā)操作要考慮死鎖和鎖的性能問題。看到網(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 鎖的種類

1. 共享鎖(Shared lock)。

   例1：
   ----------------------------------------
   T1:    select * from table (請(qǐng)想象它需要執(zhí)行1個(gè)小時(shí)之久，后面的sql語(yǔ)句請(qǐng)都這么想象）
   T2:    update table set column1='hello'
   
   過程：
   
   T1運(yùn)行 （加共享鎖)
   T2運(yùn)行
   If T1 還沒執(zhí)行完
       T2等......
   else
       鎖被釋放
       T2執(zhí)行
   endif
   
   T2之所以要等，是因?yàn)門2在執(zhí)行update前，試圖對(duì)table表加一個(gè)排他鎖，
   而數(shù)據(jù)庫(kù)規(guī)定同一資源上不能同時(shí)共存共享鎖和排他鎖。所以T2必須等T1
   執(zhí)行完，釋放了共享鎖，才能加上排他鎖，然后才能開始執(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í)行接下來的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ǔ)句雖然最為常見，很多人覺得它有機(jī)會(huì)產(chǎn)生死鎖，但實(shí)際上要看情
   況，如果id是主鍵上面有索引，那么T1會(huì)一下子找到該條記錄(id=10的記
   錄），然后對(duì)該條記錄加排他鎖，T2，同樣，一下子通過索引定位到記錄，
   然后對(duì)id=20的記錄加排他鎖，這樣T1和T2各更新各的，互不影響。T2也不
   需要等。
   
   但如果id是普通的一列，沒有索引。那么當(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過來想執(zhí)行一個(gè)查詢語(yǔ)句時(shí)，也因?yàn)榕潘i的存在而不得不等待，第四個(gè)、第五個(gè)user也會(huì)因此而等待。在大并發(fā)
   情況下，讓大家等待顯得性能就太友好了，所以，這里引入了更新鎖。

2. 更新鎖(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)在只想讀，你們別人也可以讀，但我將來可能會(huì)做更新操作，我已經(jīng)獲取了從共享鎖（用來讀）到排他鎖
   （用來更新）的資格”。一個(gè)事物只能有一個(gè)更新鎖獲此資格。
   
   T1執(zhí)行select，加更新鎖。
   T2運(yùn)行，準(zhǔn)備加更新鎖，但發(fā)現(xiàn)已經(jīng)有一個(gè)更新鎖在那兒了，只好等。
   
   當(dāng)后來有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è)例子是說明：共享鎖和更新鎖可以同時(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è)例子來加深更新鎖的理解，
   
   第一種情況：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è)例子是說明：排他鎖與更新鎖是不兼容的，它們不能同時(shí)加在同一子資源上。

3. 排他鎖（獨(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è)過程中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.

4. 意向鎖(Intent Locks)

   意向鎖就是說在屋（比如代表一個(gè)表）門口設(shè)置一個(gè)標(biāo)識(shí)，說明屋子里有人（比如代表某些記錄）被鎖住了。另一個(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ì)表加意向排他鎖。

5. 計(jì)劃鎖(Schema Locks)

   例14：
   ----------------------------------------
   alter table .... (加schema locks，稱之為Schema modification (Sch-M) locks
   
   DDL語(yǔ)句都會(huì)加Sch-M鎖
   該鎖不允許任何其它session連接該表。連都連不了這個(gè)表了，當(dāng)然更不用說想對(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ǔ)句過程中，其它session可以對(duì)表tableA做任何操作(update,delete，加排他鎖等等），但不能做DDL(比如alter table)操作。

6. Bulk Update Locks 主要在批量導(dǎo)數(shù)據(jù)時(shí)用（比如用類似于oracle中的imp/exp的bcp命令）。不難理解，程序員往往也不需要關(guān)心，不贅述了。

3 何時(shí)加鎖？

如何加鎖，何時(shí)加鎖，加什么鎖，你可以通過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)加鎖的？

1) T1執(zhí)行，數(shù)據(jù)庫(kù)自動(dòng)加排他鎖
2) 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è)為臟讀，則：
1) T1執(zhí)行，數(shù)據(jù)庫(kù)自動(dòng)加排他鎖
2) T2執(zhí)行，數(shù)據(jù)庫(kù)發(fā)現(xiàn)事物隔離級(jí)別不允許臟讀，便準(zhǔn)備為此次select過程加共享鎖，但發(fā)現(xiàn)加不上，因?yàn)橐呀?jīng)有排他鎖了，所以就
   等啊等。直到T1執(zhí)行完，釋放了排他鎖，T2才加上了共享鎖，然后開始讀....

4 鎖的粒度

鎖的粒度就是指鎖的生效范圍，就是說是行鎖，還是頁(yè)鎖，還是整表鎖. 鎖的粒度同樣既可以由數(shù)據(jù)庫(kù)自動(dòng)管理，也可以通過手工指定hint來管理。

例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的那一行試圖加鎖，只要該行沒有被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才可以允許加鎖，并開始更新。

以上3例是手工指定鎖的粒度，也可以通過設(shè)定事物隔離級(jí)別，讓數(shù)據(jù)庫(kù)自動(dòng)設(shè)置鎖的粒度。不同的事物隔離級(jí)別，數(shù)據(jù)庫(kù)會(huì)有不同的
加鎖策略（比如加什么類型的鎖，加什么粒度的鎖）。具體請(qǐng)查聯(lián)機(jī)手冊(cè)。

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ì)有任何阻塞。

6 數(shù)據(jù)庫(kù)的其它重要Hint以及它們的區(qū)別

1) holdlock 對(duì)表加共享鎖，且事物不完成，共享鎖不釋放。
2) tablock  對(duì)表加共享鎖，只要statement不完成，共享鎖不釋放。
   與holdlock區(qū)別，見下例：
   例21
   ----------------------------------------
   T1:
   begin tran
   select * from table (tablock)
   T2:
   begin tran
   update table set column1='hello' where id = 10

   T1執(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 = 10
   
   T1執(zhí)行完select，共享鎖仍然不會(huì)釋放，仍然會(huì)被hold(持有），T2也因此必須等待而不能update. 當(dāng)T1最后執(zhí)行了commit或
   rollback說明這一個(gè)事物結(jié)束了，T2才取得執(zhí)行權(quán)。
  
3) TABLOCKX 對(duì)表加排他鎖
  
   例23：
   ----------------------------------------
   T1:    select * from table(tablockx) (強(qiáng)行加排他鎖）
   其它session就無法對(duì)這個(gè)表進(jìn)行讀和更新了，除非T1執(zhí)行完了，就會(huì)自動(dòng)釋放排他鎖。
   例24：
   ----------------------------------------
   T1:    begin tran
          select * from table(tablockx)
   這次，單單select執(zhí)行完還不行，必須整個(gè)事物完成（執(zhí)行了commit或rollback后）才會(huì)釋放排他鎖。
  
4) xlock 加排他鎖
   那它跟tablockx有何區(qū)別呢？

   它可以這樣用，
   例25:
   ----------------------------------------
   select * from table(xlock paglock) 對(duì)page加排他鎖
   而TABLELOCX不能這么用。

   xlock還可這么用：select * from table(xlock tablock) 效果等同于select * from table(tablockx)

7 鎖的超時(shí)等待

例26

SET LOCK_TIMEOUT 4000 用來設(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

T2執(zhí)行時(shí)，會(huì)等待T1釋放排他鎖，等了4秒鐘，如果T1還沒有釋放排他鎖，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ā)效率

1. 悲觀鎖：利用數(shù)據(jù)庫(kù)本身的鎖機(jī)制實(shí)現(xiàn)。通過上面對(duì)數(shù)據(jù)庫(kù)鎖的了解，可以根據(jù)具體業(yè)務(wù)情況綜合使用事務(wù)隔離級(jí)別與合理的手工指定鎖的方式比如降低鎖的粒度等減少并發(fā)等待。

2. 樂觀鎖：利用程序處理并發(fā)。原理都比較好理解，基本一看即懂。方式大概有以下3種

   1. 對(duì)記錄加版本號(hào).

   2. 對(duì)記錄加時(shí)間戳.

   3. 對(duì)將要更新的數(shù)據(jù)進(jìn)行提前讀取、事后對(duì)比。

不論是數(shù)據(jù)庫(kù)系統(tǒng)本身的鎖機(jī)制，還是樂觀鎖這種業(yè)務(wù)數(shù)據(jù)級(jí)別上的鎖機(jī)制，本質(zhì)上都是對(duì)狀態(tài)位的讀、寫、判斷。

獲取【下載地址】 

springmvc4 mybatis 整合 框架源碼 bootstrap html5 mysql oracle sqlsever spring SSM