26個例子來搞懂資料庫鎖

　　1 前言

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

　　2 鎖的種類共享鎖(Shared lock)。

　　例1：

　　----------------------------------------

　　T1: select * from table (請想象它需要執行1個小時之久，後面的sql語句請都這麼想象）

　　T2: update table set column1='hello'

　　過程：

　　T1運作 （加共享鎖)

　　T2運作

　　If T1 還沒執行完

　　T2等......

　　else

　　鎖被釋放

　　T2執行

　　endif

　　T2之是以要等，是因為T2在執行update前，試圖對table表加一個排他鎖，

　　而資料庫規定同一資源上不能同時共存共享鎖和排他鎖。是以T2必須等T1

　　執行完，釋放了共享鎖，才能加上排他鎖，然後才能開始執行update語句。

　　例2：

　　T1: select * from table

　　T2: select * from table

　　這裡T2不用等待T1執行完，而是可以馬上執行。

　　分析：

　　T1運作，則table被加鎖，比如叫lockA

　　T2運作，再對table加一個共享鎖，比如叫lockB。

　　兩個鎖是可以同時存在于同一資源上的（比如同一個表上）。這被稱為共

　　享鎖與共享鎖相容。這意味着

二手QQ出售

共享鎖不阻止其它session同時讀資源，但阻

　　止其它session update

　　例3：

　　T3: update table set column1='hello'

　　這次，T2不用等T1運作完就能運作，T3卻要等T1和T2都運作完才能運作。

　　因為T3必須等T1和T2的共享鎖全部釋放才能進行加排他鎖然後執行update

　　操作。

　　例4：（死鎖的發生）

　　T1:

　　begin tran

　　select * from table (holdlock) (holdlock意思是加共享鎖，直到事物結束才釋放)

　　update table set column1='hello'

　　T2:

　　select * 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：

　　update table set column1='hello' where id=10

　　update 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：

　　select * from table(xlock) (xlock意思是直接對表加排他鎖)

　　select * from table(xlock)

　　這樣，當T1的select 執行時，直接對表加上了排他鎖，T2在執行select時，就需要等T1事物完全執行完才能執行。排除了死鎖發生。

　　但當第三個user過來想執行一個查詢語句時，也因為排他鎖的存在而不得不等待，第四個、第五個user也會是以而等待。在大并發

　　情況下，讓大家等待顯得性能就太友好了，是以，這裡引入了更新鎖。

　　2，更新鎖(Update lock)

　　為解決死鎖，引入更新鎖。

　　例7：

　　select * from table(updlock) (加更新鎖)

　　select * from table(updlock)

　　更新鎖的意思是：“我現在隻想讀，你們别人也可以讀，但我将來可能會做更新操作，我已經擷取了從共享鎖（用來讀）到排他鎖

　　（用來更新）的資格”。一個事物隻能有一個更新鎖獲此資格。

　　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:

　　begin

　　select * from table(updlock) (加更新鎖）

　　update table set column1='hello' (重點：這裡T1做update時，不需要等T2釋放什麼，而是直接把更新鎖更新為排他鎖，然後執行update)

　　select * 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，發現已有更新鎖，則等待，後面步驟就跟第一種情況一樣了。

　　這個例子是說明：排他鎖與更新鎖是不相容的，它們不能同時加在同一子資源上。排他鎖（獨占鎖，Exclusive Locks)

　　這個簡單，即其它事務既不能讀，又不能改排他鎖鎖定的資源。

　　例10

　　T1: update table set column1='hello' where id<1000

　　T2: update table set column1='world' where id>1000

　　假設T1先達，T2随後至，這個過程中T1會對id<1000的記錄施加排他鎖.但不會阻塞T2的update。

　　例11 (假設id都是自增長且連續的）

　　T2: update table set column1='world' where id>900

　　如同例10，T1先達，T2立刻也到，T1加的排他鎖會阻塞T2的update.

　　3，意向鎖(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：

　　update table set column1='hello' where id=1

　　update table set column1='world' where id=1

　　這個例子和上面的例子實際效果相同，T1執行，系統對table同時對行家排他鎖、對頁加意向排他鎖、對表加意向排他鎖。

　　4，計劃鎖(Schema Locks)

　　例14：

　　alter table .... (加schema locks，稱之為Schema modification (Sch-M) locks

　　DDL語句都會加Sch-M鎖

　　該鎖不允許任何其它session連接配接該表。連都連不了這個表了，當然更不用說想對該表執行什麼sql語句了。

　　例15:

　　用jdbc向資料庫發送了一條新的sql語句，資料庫要先對之進行編譯，在編譯期間，也會加鎖，稱之為：Schema stability (Sch-S) locks

　　select * from tableA

　　編譯這條語句過程中，其它session可以對表tableA做任何操作(update,delete，加排他鎖等等），但不能做DDL(比如alter table)操作。

　　Bulk Update Locks 主要在批量導資料時用（比如用類似于oracle中的imp/exp的bcp指令）。不難了解，程式員往往也不需要關心，不贅述了。3 何時加鎖？

　　如何加鎖，何時加鎖，加什麼鎖，你可以通過hint手工強行指定，但大多是資料庫系統自動決定的。這就是為什麼我們可以不懂鎖也可

　　以高高興興的寫SQL。

　　T2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 事物隔離級别為允許髒讀

　　go

　　select * from table where id=1

　　這裡，T2的select可以查出結果。如果事物隔離級别不設為髒讀，則T2會等T1事物執行完才能讀出結果。

　　資料庫如何自動加鎖的？

　　1) T1執行，資料庫自動加排他鎖

　　2) T2執行，資料庫發現事物隔離級别允許髒讀，便不加共享鎖。不加共享鎖，則不會與已有的排他鎖沖突，是以可以髒讀。

　　例16:

　　T2: select * from table where id=1 --為指定隔離級别，則使用系統預設隔離級别，它不允許髒讀

　　如果事物級别不設為髒讀，則：

　　2) T2執行，資料庫發現事物隔離級别不允許髒讀，便準備為此次select過程加共享鎖，但發現加不上，因為已經有排他鎖了，是以就

　　等啊等。直到T1執行完，釋放了排他鎖，T2才加上了共享鎖，然後開始讀....

　　4 鎖的粒度

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

　　例17：

　　T1: select * from table (paglock)

　　T2: update table set column1='hello' where id>10

　　T1執行時，會先對第一頁加鎖，讀完第一頁後，釋放鎖，再對第二頁加鎖，依此類推。假設前10行記錄恰好是一頁(當然，一般不可能

　　一頁隻有10行記錄），那麼T1執行到第一頁查詢時，并不會阻塞T2的更新。

　　例18：

　　T1: select * from table (rowlock)

　　T2: update table set column1='hello' where id=10

　　T1執行時，對每行加共享鎖，讀取，然後釋放，再對下一行加鎖;T2執行時，會對id=10的那一行試圖加鎖，隻要該行沒有被T1加上行鎖，

　　T2就可以順利執行update操作。

　　例19：

　　T1: select * from table (tablock)

　　T1執行，對整個表加共享鎖. T1必須完全查詢完，T2才可以允許加鎖，并開始更新。

　　以上3例是手工指定鎖的粒度，也可以通過設定事物隔離級别，讓資料庫自動設定鎖的粒度。不同的事物隔離級别，資料庫會有不同的

　　加鎖政策（比如加什麼類型的鎖，加什麼粒度的鎖）。具體請查聯機手冊。

　　5 鎖與事物隔離級别的優先級

　　手工指定的鎖優先，

　　例20:

　　T1: GO

　　SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

　　GO

　　BEGIN TRANSACTION

　　SELECT * FROM table (NOLOCK)

　　T1是事物隔離級别為最進階，串行鎖，資料庫系統本應對後面的select語句自動加表級鎖，但因為手工指定了NOLOCK，是以該select

　　語句不會加任何鎖，是以T2也就不會有任何阻塞。

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

　　1) holdlock 對表加共享鎖，且事物不完成，共享鎖不釋放。

　　2) tablock 對表加共享鎖，隻要statement不完成，共享鎖不釋放。

　　與holdlock差別，見下例：

　　例21

　　select * from table (tablock)

　　T1執行完select，就會釋放共享鎖，然後T2就可以執行update. 此之謂tablock. 下面我們看holdlock

　　例22

　　select * from table (holdlock)

　　T1執行完select，共享鎖仍然不會釋放，仍然會被hold(持有），T2也是以必須等待而不能update. 當T1最後執行了commit或

　　rollback說明這一個事物結束了，T2才取得執行權。

　　3) TABLOCKX 對表加排他鎖

　　例23：

　　T1: select * from table(tablockx) (強行加排他鎖）

　　其它session就無法對這個表進行讀和更新了，除非T1執行完了，就會自動釋放排他鎖。

　　例24：

　　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 意味着

　　永遠等待。

　　udpate table set column1='hello' where id=10

　　T2: 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 如何提高并發效率悲觀鎖：利用資料庫本身的鎖機制實作。通過上面對資料庫鎖的了解，可以根據具體業務情況綜合使用事務隔離級别與合理的手工指定鎖的方式比如降低鎖的粒度等減少并發等待。樂觀鎖：利用程式處理并發。原理都比較好了解，基本一看即懂。方式大概有以下3種對記錄加版本号.對記錄加時間戳.對将要更新的資料進行提前讀取、事後對比。

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