貓肥熊老爸的窩

記得以前在處理專案時，常遇到一個交易封鎖了一堆交易的問題，非常困擾。遇到這種情形，通常都是踢掉幾個封鎖他人的交易作為解決辦法。但是不是原本的DBA設計就是有問題的呢?為了讓日後的資料架構的設計能夠減少LOCK的狀況，小弟就很努力的先去了解一下SQL Server的Lock機置。

當我們要開始介紹Lock之前，首先來瞭解一下資源(Reource)的層級。如下表，SQL Server將資源分為11級並且可以進行鎖定，由上而下，越往下代表層次越低。

資料庫引擎進行鎖定時，通常是以同時產生多種層級以上的鎖定來完整的保護資源，這一組的多層級的鎖定，我們稱之為「鎖定層級(lock hierarchy)」。我們在此舉個例子。系統為了要完整保護索引的讀取，資料庫引擎必需同時產生一個Shared Lock(S)在一筆資料列上，以及分別產生一個Intend Lock(IS)在資料頁(PAGE)及資料表(TABLE)上。這個例子可以看出，一個交易針對一個資料表並不是只產生一個鎖定而已，而是產生多種鎖定來保護他自己所能讀取的資源。

以上我們大概知道鎖定資源的層級後，接下來我們就開始來談談鎖定吧。

以SQL Server的Lock的模式分為主要的三種，這三種模式分別為Shared Lock、Exclusive Lock以及Update Lock。

共享式鎖定 Shared Lock(S)

共享鎖定，共同分享讀取資料表內的資訊，此鎖定僅會在對資料進行讀取時產生。這種模式的鎖定是不會影響到其他人讀取相同資料，例如，當您透過select 指令查詢資料時，就會有共享鎖定的產生。 那為何要有這樣的鎖定機置呢? 共享鎖定，其實是一種非常悲觀的模式設計，主要用途是當資料進行查詢的同時，防止其它交易對同份資料進行更新，當讀取的動作結束後，除非有額外設定「Lock Hints」或是「Isolation Level」否則系統會迅速得將資料解鎖，並且釋放。「Lock Hints」及「Isolation Level」我們往後會談到。一個交易的Shared Lock 可與 其他交易的Shared Lock 或是 Update Lock相容。

獨佔式鎖定 Exclusive Lock(X)

獨佔鎖定，當下達insert、update以及delete這類DML指令時，系統其實會內含兩個操作，首先會產生Shared Lock讀取資料，接著產生Exclusive Lock 進行資料的實際更新，而當Exclusive Lock產生後，其他的交易是無法針對相同資料進行封鎖。一個交易的Exclusive Lock無法與其他交易的任何種類的Lock相容。

更新鎖定 Update Lock(U)

更新鎖定是為了防止常見的死鎖(Dead Lock)發生而設計的。此鎖定是在下達Insert、Update、Delete 指令後產生。針對同一份資源中，此模式的鎖定只有一個交易允許產生Update Lock，並且在DML指令更新完後，而尚未Commit前轉換成Exclusive Lock。

哇，好像感覺頭暈暈的耶，我們在此來舉個例子吧。首先我們先舉個將會發生dead lock的例子，請參考下圖。

如上圖，當交易AB同時針對一個資料表進行查詢與更新就會產生死鎖(dead lock)。

步驟一，當交易A第一次進行查詢時會產生Shared Lock。

步驟二交易B第一次進行查詢時也會產生一個Shared Lock，因為Shared Lock 相容的關係所以SQL會繼續往下進行。

接著步驟三，交易A進行update時，系統會企圖進行exclusive lock，但因交易B的已經產生了Shared Lock，並且exclusive lock無法與其他模式的Lock相容，導致交易A要等待交易B將Shared Lock釋放後才進行Exclusive lock。

步驟四，交易B在進行Update時，系統會企圖進行exclusive lock，相同的因交易A的已經產生了Shared Lock，導致交易B要等待交易A將Shared Lock釋放後才進行Exclusive lock。A交易等B交易，而B交易也等著A交易，最後就Dead Lock發生囉。

以上瞭解了Dead Lock如何產生後，接著我們來看Update Lock如何防止Dead Lock吧!!

如上圖的第三步驟，當要進行更新或修改資料時，系統會產生一個Update Lock，因為Udapte Lock與Shared Lock 相容並且沒有任何一個交易有進行Update Lock 所以Update Lock成功的產生，並且在更新資料後（尚未Commit)，馬上會產生一個Exclusive Lock。

接著第四步驟，交易B因為交易A已經產生了一個Update Lock或Exclusive lock 所以交易B會等到交易A釋放Exclusive Lock後開始運作。這樣大家都瞭解了嗎? 總之就是誰先取得Update Lock誰就是贏家。

各個主要鎖定之間的相容性，請參考下表：

上表中，Shared Lock與Shared Lock及Upadate Lock相容，Update Lock只能與Shared Lock相容，而獨佔模式無法與任何其他的鎖定模式相容。

       企圖式鎖定 Intend Lock (I)

資料庫引擎使用Intend Lock 確保交易可以針對低層級鎖定的資源中，進行Shared Lock或是Exclusive Lock。一個交易在進行低層級資源鎖定前所發出的鎖定，我們就稱之為「Intend Lock」，簡單的說就是一個交易針對一份資源要進行鎖定前，會發出『我要鎖定這個資源了喔，你們晚點再用喔』這樣的預警訊息給其他交易，而此時如果已經有其它交易已經進行Intend Lock，它就會回個訊息給你『抱歉耶!! 我已經先到啦，你排我後面吧!!』。

Intend Lock 有兩種用途，第一種就是防止其他交易的較低層次的資源鎖定廢除時，仍然可以修改較高層次的資源鎖定。第二種就是提高系統偵測高層級發生鎖定衝突的速度。

企圖式鎖定為什麼可以提高偵測鎖定的效率呢?? 在此我們再舉個例子，當一個交易針對一個資料表內的資料頁(Pages)或資料列(Rows)進行Shared Lock之前，會先對該資料表(Table)產生一個Intend Lock以防止後續的其它交易對該資料表進行Exclusive Lock。因為資料庫引擎在檢測資料表層級Intend Lock時，就可以決定此交易是否可以完整的鎖定整個資料表，所以若此交易可以完整的鎖定資料表，資料庫引擎便可以省去了逐項去檢查每個資料頁或資料列的鎖定檢測，如此一來就可以達到檢測鎖定時的效能。

企圖共享鎖定(Intend Shared Lock )(IS)

用途在於確保交易是否能夠對較低層級的資源進行Shared Lock 鎖定。

企圖獨占鎖定(Intend Exclusive Lock )(IX)

用途在於確保交易是否能夠對一些(非全部)低層級的資源進行Excluive Lock 鎖定，此鎖定也同時確保交易是否能夠對低層級的資源進行Shared Lock 。

含共享的企圖獨佔鎖定Shared with intent exclusive (SIX)

用途在於確保交易是否能夠對所有的低層級的資源進行Shared Lock鎖定並且能夠對部分低層級資源進行Exclude Lock。舉個例子，當交易對資料表發出SIX lock時，資料表本身在產生Shared Lock的同時，也會針對要異動的資料頁或是異動過的資料列進行Exclusive Lock鎖定。SIX鎖定每個資源中只能存在一份，也就是同時只能有一個交易能夠對該資源進行SIX的鎖定，這是為了防止其它交易對此資源內的資料進行修改，但是其它交易仍可以進行IS鎖定要求，進行讀取資料唷。

企圖更新鎖定 Intent update (IU)

確保交易可以針對資料頁(Pages)內所有的資料進行更新，這類的LOCK只能產生在資料頁的層級中，如果資料頁內的資料已異動過，則IU的鎖定會轉變成IX鎖定。

  共享企圖更新鎖定 Shared intent update (SIU)

由Shared Lock 與 IU Lock組成的一種鎖定，此兩種鎖定可以分別產生並且交易可同時擁有。舉例來說，當一個交易執行查詢時下達了PAGLOCK Hint後，接著對資料進行更新，首先會在查詢時產生一個Shared Lock，並且在資料更新前產生一個IU的鎖定。

更新企圖更獨佔鎖定 Update intent exclusive (UIX)

由U Lock 與 IX Lock組成的一種鎖定，此兩種鎖定可以分別產生並且交易可同時擁有。