【轉】資料庫鎖機制

標籤：nsa   div   blank   acl   等等   hello   details   statement   二次   

1 前言

資料庫大並行作業要考慮死結和鎖的效能問題。看到網上大多語焉不詳(尤其更新鎖定），所以這裡做個簡明解釋，為下面描述方便，這裡用T1代表一個資料庫執行請求，T2代表另一個請求，也可以理解為T1為一個線程，T2 為另一個線程。T3,T4以此類推。下面以SQL Server(2005)為例。

2 鎖的種類

1. 共用鎖定(Shared lock)。

   例1：----------------------------------------T1:    select * from table (請想象它需要執行1個小時之久，後面的sql語句請都這麼想象）T2:    update table set column1=‘hello‘過程：T1運行 （加共用鎖定)T2運行If T1 還沒執行完    T2等......else    鎖被釋放    T2執行endifT2之所以要等，是因為T2在執行update前，試圖對table表加一個獨佔鎖定，而資料庫規定同一資源上不能同時共存共用鎖定和獨佔鎖定。所以T2必須等T1執行完，釋放了共用鎖定，才能加上獨佔鎖定，然後才能開始執行update語句。例2：----------------------------------------T1:    select * from tableT2:    select * from table這裡T2不用等待T1執行完，而是可以馬上執行。分析：T1運行，則table被加鎖，比如叫lockAT2運行，再對table加一個共用鎖定，比如叫lockB。兩個鎖是可以同時存在於同一資源上的（比如同一個表上）。這被稱為共用鎖定與共用鎖定相容。這意味著共用鎖定不阻止其它session同時讀資源，但阻止其它session update例3：----------------------------------------T1:    select * from tableT2:    select * from tableT3:    update table set column1=‘hello‘這次，T2不用等T1運行完就能運行，T3卻要等T1和T2都運行完才能運行。因為T3必須等T1和T2的共用鎖定全部釋放才能進行加獨佔鎖定然後執行update操作。例4：（死結的發生）----------------------------------------T1:begin transelect * from table (holdlock) (holdlock意思是加共用鎖定，直到事物結束才釋放)update table set column1=‘hello‘T2:begin transelect * from table(holdlock)update table set column1=‘world‘假設T1和T2同時達到select，T1對table加共用鎖定，T2也對加共用鎖定，當T1的select執行完，準備執行update時，根據鎖機制，T1的共用鎖定需要升級到獨佔鎖定才能執行接下來的update.在升級獨佔鎖定前，必須等table上的其它共用鎖定釋放，但因為holdlock這樣的共用鎖定只有等事務結束後才釋放，所以因為T2的共用鎖定不釋放而導致T1等(等T2釋放共用鎖定，自己好升級成獨佔鎖定），同理，也因為T1的共用鎖定不釋放而導致T2等。死結產生了。例5：----------------------------------------T1:begin tranupdate table set column1=‘hello‘ where id=10T2:begin tranupdate table set column1=‘world‘ where id=20這種語句雖然最為常見，很多人覺得它有機會產生死結，但實際上要看情況，如果id是主鍵上面有索引，那麼T1會一下子找到該條記錄(id=10的記錄），然後對該條記錄加獨佔鎖定，T2，同樣，一下子通過索引定位到記錄，然後對id=20的記錄加獨佔鎖定，這樣T1和T2各更新各的，互不影響。T2也不需要等。但如果id是普通的一列，沒有索引。那麼當T1對id=10這一行加獨佔鎖定後，T2為了找到id=20，需要對全表掃描，那麼就會預先對錶加上共用鎖定或更新鎖定或獨佔鎖定(依賴於資料庫執行策略和方式，比如第一次執行和第二次執行資料庫執行策略就會不同）。但因為T1已經為一條記錄加了獨佔鎖定，導致T2的全表掃描進行不下去，就導致T2等待。死結怎麼解決呢？一種辦法是，如下：例6：----------------------------------------T1:begin transelect * from table(xlock) (xlock意思是直接對錶加獨佔鎖定)update table set column1=‘hello‘T2:begin transelect * from table(xlock)update table set column1=‘world‘這樣，當T1的select 執行時，直接對錶加上了獨佔鎖定，T2在執行select時，就需要等T1事物完全執行完才能執行。排除了死結發生。但當第三個user過來想執行一個查詢語句時，也因為獨佔鎖定的存在而不得不等待，第四個、第五個user也會因此而等待。在大並發情況下，讓大家等待顯得效能就太友好了，所以，這裡引入了更新鎖定。

2. 更新鎖定(Update lock)

   為解決死結，引入更新鎖定。例7：----------------------------------------T1:begin transelect * from table(updlock) (加更新鎖定)update table set column1=‘hello‘T2:begin transelect * from table(updlock)update table set column1=‘world‘更新鎖定的意思是：“我現在只想讀，你們別人也可以讀，但我將來可能會做更新操作，我已經擷取了從共用鎖定（用來讀）到獨佔鎖定（用來更新）的資格”。一個事物只能有一個更新鎖定獲此資格。T1執行select，加更新鎖定。T2運行，準備加更新鎖定，但發現已經有一個更新鎖定在那兒了，只好等。當後來有user3、user4...需要查詢table表中的資料時，並不會因為T1的select在執行就被阻塞，照樣能查詢，相比起例6，這提高了效率。例8:----------------------------------------T1:    select * from table(updlock)    (加更新鎖定）T2:    select * from table(updlock)    (等待，直到T1釋放更新鎖定，因為同一時間不能在同一資源上有兩個更新鎖定）T3:    select * from table (加共用鎖定，但不用等updlock釋放，就可以讀）這個例子是說明：共用鎖定和更新鎖定可以同時在同一個資源上。這被稱為共用鎖定和更新鎖定是相容的。例9:----------------------------------------T1:beginselect * from table(updlock)      (加更新鎖定）update table set column1=‘hello‘  (重點：這裡T1做update時，不需要等T2釋放什麼，而是直接把更新鎖定升級為獨佔鎖定，然後執行update)T2:beginselect * from table               (T1加的更新鎖定不影響T2讀取）update table set column1=‘world‘  (T2的update需要等T1的update做完才能執行)我們以這個例子來加深更新鎖定的理解，第一種情況：T1先達，T2緊接到達；在這種情況中，T1先對錶加更新鎖定，T2對錶加共用鎖定，假設T2的select先執行完，準備執行update，發現已有更新鎖定存在，T2等。T1執行這時才執行完select，準備執行update，更新鎖定升級為獨佔鎖定，然後執行update，執行完成，事務結束，釋放鎖，T2才輪到執行update。第二種情況：T2先達，T1緊接達；在這種情況，T2先對錶加共用鎖定，T1達後，T1對錶加更新鎖定，假設T2 select先結束，準備update，發現已有更新鎖定，則等待，後面步驟就跟第一種情況一樣了。這個例子是說明：獨佔鎖定與更新鎖定是不相容的，它們不能同時加在同一子資源上。

3. 獨佔鎖定（獨佔鎖，Exclusive Locks)

   這個簡單，即其它事務既不能讀，又不能改獨佔鎖定鎖定資源。例10T1:    update table set column1=‘hello‘ where id<1000T2:    update table set column1=‘world‘ where id>1000假設T1先達，T2隨後至，這個過程中T1會對id<1000的記錄施加獨佔鎖定.但不會阻塞T2的update。例11 (假設id都是自增長且連續的）T1:    update table set column1=‘hello‘ where id<1000T2:    update table set column1=‘world‘ where id>900如同例10，T1先達，T2立刻也到，T1加的獨佔鎖定會阻塞T2的update.

4. 意圖鎖定(Intent Locks)

   意圖鎖定就是說在屋（比如代表一個表）門口設定一個標識，說明屋子裡有人（比如代表某些記錄）被鎖住了。另一個人想知道屋子裡是否有人被鎖，不用進屋子裡一個一個的去查，直接看門口標識就行了。當一個表中的某一行被加上獨佔鎖定後，該表就不能再被加表鎖。資料庫程式如何知道該表不能被加表鎖？一種方式是逐條的判斷該表的每一條記錄是否已經有獨佔鎖定，另一種方式是直接在表這一層級檢查表本身是否有意圖鎖定，不需要逐條判斷。顯然後者效率高。例12：----------------------------------------T1:    begin tran       select * from table (xlock) where id=10  --意思是對id=10這一行強加獨佔鎖定T2:    begin tran       select * from table (tablock)     --意思是要加表級鎖       假設T1先執行，T2後執行，T2執行時，欲加表鎖，為判斷是否可以加表鎖，資料庫系統要逐條判斷table表每行記錄是否已有獨佔鎖定，如果發現其中一行已經有獨佔鎖定了，就不允許再加表鎖了。只是這樣逐條判斷效率太低了。實際上，資料庫系統不是這樣工作的。當T1的select執行時，系統對錶table的id=10的這一行加了獨佔鎖定，還同時悄悄的對整個表加了意向獨佔鎖定(IX)，當T2執行表鎖時，只需要看到這個表已經有意向獨佔鎖定存在，就直接等待，而不需要逐條檢查資源了。例13：----------------------------------------T1:    begin tran       update table set column1=‘hello‘ where id=1T2:    begin tran       update table set column1=‘world‘ where id=1這個例子和上面的例子實際效果相同，T1執行，系統對table同時對行家獨佔鎖定、對頁加意向獨佔鎖定、對錶加意向獨佔鎖定。

5. 計劃鎖(Schema Locks)

   例14：----------------------------------------alter table .... (加schema locks，稱之為Schema modification (Sch-M) locksDDL語句都會加Sch-M鎖該鎖不允許任何其它session串連該表。連都連不了這個表了，當然更不用說想對該表執行什麼sql語句了。例15:----------------------------------------用jdbc向資料庫發送了一條新的sql語句，資料庫要先對之進行編譯，在編譯期間，也會加鎖，稱之為：Schema stability (Sch-S) locksselect * from tableA編譯這條語句過程中，其它session可以對錶tableA做任何操作(update,delete，加獨佔鎖定等等），但不能做DDL(比如alter table)操作。

6. Bulk Update Locks 主要在批量導資料時用（比如用類似於oracle中的imp/exp的bcp命令）。不難理解，程式員往往也不需要關心，不贅述了。

3 何時加鎖？

如何加鎖，何時加鎖，加什麼鎖，你可以通過hint手工強行指定，但大多是資料庫系統自動決定的。這就是為什麼我們可以不懂鎖也可以高高興興的寫SQL。例15:----------------------------------------T1:    begin tran       update table set column1=‘hello‘ where id=1T2:    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  -- 事物隔離等級為允許髒讀       go       select * from table where id=1這裡，T2的select可以查出結果。如果事物隔離等級不設為髒讀，則T2會等T1事物執行完才能讀出結果。資料庫如何自動加鎖的？1) T1執行，資料庫自動加獨佔鎖定2) T2執行，資料庫發現事物隔離等級允許髒讀，便不加共用鎖定。不加共用鎖定，則不會與已有的獨佔鎖定衝突，所以可以髒讀。例16:----------------------------------------T1:    begin tran       update table set column1=‘hello‘ where id=1T2:    select * from table where id=1 --為指定隔離等級，則使用系統預設隔離等級，它不允許髒讀如果事物層級不設為髒讀，則：1) T1執行，資料庫自動加獨佔鎖定2) T2執行，資料庫發現事物隔離等級不允許髒讀，便準備為此次select過程加共用鎖定，但發現加不上，因為已經有獨佔鎖定了，所以就   等啊等。直到T1執行完，釋放了獨佔鎖定，T2才加上了共用鎖定，然後開始讀....

4 鎖的粒度

鎖的粒度就是指鎖的生效範圍，就是說是行鎖，還是頁鎖，還是整表鎖. 鎖的粒度同樣既可以由資料庫自動管理，也可以通過手工指定hint來管理。

例17：----------------------------------------T1:    select * from table (paglock)T2:    update table set column1=‘hello‘ where id>10T1執行時，會先對第一頁加鎖，讀完第一頁後，釋放鎖，再對第二頁加鎖，依此類推。假設前10行記錄恰好是一頁(當然，一般不可能一頁只有10行記錄），那麼T1執行到第一頁查詢時，並不會阻塞T2的更新。例18：----------------------------------------T1:    select * from table (rowlock)T2:    update table set column1=‘hello‘ where id=10T1執行時，對每行加共用鎖定，讀取，然後釋放，再對下一行加鎖;T2執行時，會對id=10的那一行試圖加鎖，只要該行沒有被T1加上行鎖，T2就可以順利執行update操作。例19：----------------------------------------T1:    select * from table (tablock)T2:    update table set column1=‘hello‘ where id = 10T1執行，對整個表加共用鎖定. T1必須完全查詢完，T2才可以允許加鎖，並開始更新。以上3例是手工指定鎖的粒度，也可以通過設定事物隔離等級，讓資料庫自動化佈建鎖的粒度。不同的事物隔離等級，資料庫會有不同的加鎖策略（比如加什麼類型的鎖，加什麼粒度的鎖）。具體請查線上手冊。

5 鎖與事物隔離等級的優先順序

手工指定的鎖優先，例20:----------------------------------------T1:    GO       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE       GO       BEGIN TRANSACTION       SELECT * FROM table (NOLOCK)       GOT2:    update table set column1=‘hello‘ where id=10T1是事物隔離等級為最進階，串列鎖，資料庫系統本應對後面的select語句自動加表級鎖，但因為手工指定了NOLOCK，所以該select語句不會加任何鎖，所以T2也就不會有任何阻塞。

6 資料庫的其它重要Hint以及它們的區別

1) holdlock 對錶加共用鎖定，且事物不完成，共用鎖定不釋放。2) tablock  對錶加共用鎖定，只要statement不完成，共用鎖定不釋放。   與holdlock區別，見下例：   例21   ----------------------------------------   T1:   begin tran   select * from table (tablock)   T2:   begin tran   update table set column1=‘hello‘ where id = 10   T1執行完select，就會釋放共用鎖定，然後T2就可以執行update. 此之謂tablock. 下面我們看holdlock   例22   ----------------------------------------   T1:   begin tran   select * from table (holdlock)   T2:   begin tran   update table set column1=‘hello‘ where id = 10      T1執行完select，共用鎖定仍然不會釋放，仍然會被hold(持有），T2也因此必須等待而不能update. 當T1最後執行了commit或   rollback說明這一個事物結束了，T2才取得執行權。  3) TABLOCKX 對錶加獨佔鎖定     例23：   ----------------------------------------   T1:    select * from table(tablockx) (強行加獨佔鎖定）   其它session就無法對這個表進行讀和更新了，除非T1執行完了，就會自動釋放獨佔鎖定。   例24：   ----------------------------------------   T1:    begin tran          select * from table(tablockx)   這次，單單select執行完還不行，必須整個事物完成（執行了commit或rollback後）才會釋放獨佔鎖定。  4) xlock 加獨佔鎖定   那它跟tablockx有何區別呢？   它可以這樣用，   例25:   ----------------------------------------   select * from table(xlock paglock) 對page加獨佔鎖定   而TABLELOCX不能這麼用。   xlock還可這麼用：select * from table(xlock tablock) 效果等同於select * from table(tablockx)

7 鎖的逾時等待

例26

SET LOCK_TIMEOUT 4000 用來設定鎖等待時間，單位是毫秒，4000意味著等待4秒可以用select @@LOCK_TIMEOUT查看當前session的鎖逾時設定。-1 意味著永遠等待。T1: begin tran    udpate table set column1=‘hello‘ where id = 10T2: set lock_timeout 4000    select * from table wehre id = 10

T2執行時，會等待T1釋放獨佔鎖定，等了4秒鐘，如果T1還沒有釋放獨佔鎖定，T2就會拋出異常： Lock request time out period exceeded.

8 附：各種鎖的相容關係表

| 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 如何提高並發效率

1. 悲觀鎖：利用資料庫本身的鎖機制實現。通過上面對資料庫鎖的瞭解，可以根據具體業務情況綜合使用交易隔離等級與合理的手工指定鎖的方式比如降低鎖的粒度等減少並發等待。
2. 樂觀鎖：利用程式處理並發。原理都比較好理解，基本一看即懂。方式大概有以下3種
   1. 對記錄加版本號碼.
   2. 對記錄加時間戳記.
   3. 對將要更新的資料進行提前讀取、事後對比。

不論是資料庫系統本身的鎖機制，還是樂觀鎖這種業務資料層級上的鎖機制，本質上都是對狀態位的讀、寫、判斷。

【原文】http://blog.csdn.net/samjustin1/article/details/52210125

【轉】資料庫鎖機制