原文: [翻譯]:SQL死鎖-鎖的類型

??? 很久沒有寫博客了，這里面的原因有很多。最近的一個項目由于客戶明確提出要做下性能壓力測試，使用的工具就是VS自帶的壓力測試工具。以前其它項目做壓力測試后反饋的其中一個重要問題就是數據庫的死鎖。沒想到我們這個項目測試時死鎖同樣的發生了，我之前的項目由于很少參與壓力測試，基本上也不會去了解死鎖，以及死鎖如何解決的問題。

??? 既然有了這個需求，那么要想解決死鎖就需要對死鎖的相關知識有一定的了解，對于非DBA的來講并不需要了解的特別深，知道基本概念以及常見分析方法即可，畢竟我們不靠這個吃飯，沒必要達到特別細的境界。于時我找到了這一系列的文章，加上我的理解特此翻譯過來加深印象。

??? 注：我的英文并不是特別好，且我不會嚴格按原文的邏輯順序來翻譯，會加入一些自己的理解，所以為了不讓其它讓被我的翻譯誤解，會有原文對照。

???

One of the most challenging issues for developers who don’t live in RDBMS world is how to make the system working seamlessly in multi-user environment. The code which works perfectly in development and QA starts to fall apart when dozens of users access the system. There are timeouts, deadlocks and other issues that developer cannot even reproduce in house. It does not really matter that SQL Server uses row level locking, that transaction isolation level set to read uncommitted – locking, blocking and deadlocking still occurs.

??? 對于程序員來講最大的挑戰之一就是能夠確保開發的系統在多人使用時也能無縫的工作。有的時候開發堪稱完美的代碼當出現多人訪問系統時會出現系統崩潰的情況，這其中的原因有超時，死鎖或者是其它一些我們意想不到的原因。盡管SQL SERVER是行鎖定，也可以設置事務級別來讀取未提交的數據，但死鎖，阻塞的問題仍然會發生。

?

Today I’m going to start the series of the posts about locking in Microsoft SQL Server. I’ll try to explain why blocking and deadlocks occur in the system, how you can troubleshoot related problems and what should you do in order to minimize it. We will cover different transaction isolation levels and see how and why it affects behavior of the system. And talk about quite a few other things.

?? 今天我將開始寫一系列關于SQL SERVER鎖的文章，我會解釋為什么阻塞以及死鎖會出現在系統中，以及你怎樣去排除解決這些問題，如何將問題的影響降到最低。我們會通過修改不同的事務級別來看是如何影響系統行為的，同時我也會談一些其它相關的內容。

?

So let’s start with the lock types. What is the lock? In short, this is in-memory structure (64 bytes on 32 bit OS or 128 bytes on 64 bit OS). The structure has the owner, type and resource hash that links it to the resource it protects (row, page, table, file, database, etc). Obviously it’s more complicated and has quite a few other attributes, but for our practical purposes that level of details is enough.

?? 所以我們首先要了解了鎖的類型。什么是鎖呢？簡單來講，它是一種內存結構（32位操作系統中占64字節，或者64位操作系統占128字節），從保護級別來看，分行鎖，頁鎖，表鎖，數據庫鎖等等。

??? 注：這段我并未深入理解，所以精簡了一些。

SQL Server has more than 20 different lock types but for now let’s focus on the most important ones.

?? SQL SERVER 有20多種不同的鎖，但我們只需要關注幾種重要的即可。

• Shared locks (S). Those locks acquired by readers during read operations such as SELECT. I’d like to mention that it happens in most part of the cases but not all the time. There are some cases when readers don’t acquire (S) locks. We will talk about it later.

??????? 共享鎖(S),這些鎖一般出現在我們使用了select語句查詢時，我想提醒的是，并不是所有的select查詢都會有共享鎖，有些情況是不需要共享鎖的，我們后續再講。

• Exclusive locks (X). Those locks acquired by writers during data modification operators such as Insert, Update or Delete. Those locks prevent one object to be modified by the different sessions. Those locks are always acquired and held till end of transaction

??????? 排它鎖(X),這些鎖一般出現在往數據表寫入數據，比如插入數據，更新數據以及刪除數據。排它鎖保證同一時間只有一個會話能夠對數據進行寫入操作至于寫入的事務結束。

• Update locks (U). Those locks are the mix between shared and exclusive locks. SQL Server uses them with data modification statements while searching for the rows need to be modified. For example, if you issue the statement like: “ update MyTable set Column1 = 0 where Column1 is null ” SQL Server acquires update lock for every row it processes while searching for Column1 is null . When eligible row found, SQL Server converts (U) lock to (X).

??????? 更新鎖(U),這些鎖界于共享鎖以及排它鎖之間。SQL SERVER在調用update語句時先要搜索出哪些數據行是需要更新的。比如我們查詢：“update MyTable set Column1=0 where Colum1 is null” SQL SERVER會申請更新鎖去查詢數據表的每一行是否是符合更新條件的(Column is null)。一旦找到需要更新的數據行，SQL SERVER會將更新鎖升級成排它鎖。

• Intent locks (IS, IX, IU, etc). Those locks indicate locks on the child objects. For example, if row has (X) lock, it would introduce (IX) locks on page, table and database level. Main purpose of those locks is optimization. This about situation when you need to have exclusive access to the database (i.e. (X) lock on database level). If SQL Server did not have intent locks, it would have to scan all rows in the all objects and see if there are any low level locks acquired.

?????? 意向鎖(IS,IX,IU,等等)。這些鎖表明它的子對象中有級別更高的鎖。比如如何一個數據行上有X鎖，會在這個行數據所處的數據頁，表，數據庫級別上存在IX的鎖。這樣設計的主要目的是為了優化。當你需要以獨占方式去訪問數據庫時(如果此時X鎖設置在數據庫級別上)，它會遍歷所有對象中的所有行，來判斷是否有比X鎖低的鎖存在。

?????? 注：這個我理解的不是很好，可能是SQL SERVER在獲取鎖的機制遵循由低到高的原則，即要想獲得級別高的鎖先要獲取級別低的鎖，從而降低同一資源的相互競爭。

Obviously the biggest question is lock compatibility. If you open MSDN site you’ll see nice and “easy to understand” matrix with more than 400 cells. But for our practical purpose let’s focus on the smaller version:

?????? 很明顯，最大的問題就是這些鎖之間的兼容性問題。如果你打開 MSDN site ?你將會看到更加詳細的內容，包含一個超過400個格子的表格。但對于我們來講，只需要關注如下壓縮之后的版本即可。

????? 注：剛開始看的時候，我對作者標注的顏色代表的含義也不太清楚，看了他后面的說明我大致理解了下，具體如下：

• • 綠色：代表完全兼容，不會發生阻塞以及死鎖
  • 黃色：代表在特定情況下會出現不兼容的情況
  • 紅色：最容易造成死鎖?

????? 其實這個顏色的標注容易讓人不理解，還是MSDN上的無著色的表格會好比較單純點：

??

?	兼容模式	?	?	?	?	?
鎖請求模式	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

So what we need to remember are basically 3 things:

???? 從這張表格中可以得出下面三個關注：

1. (S) locks are compatible with (S) and (U) locks.

????????? 共享鎖和共享鎖以及更新鎖是兼容

1. (X) locks are incompatible with any other lock types

????????? 排它所和任何鎖都不兼容

1. (U) locks are compatible with (S) but incompatible with (U)

????????? 更新鎖和共享鎖之間是兼容的，但更新鎖與更新鎖之間是不兼容的

Simple enough. Next time we will look at transaction isolation levels and see how it affects lock behavior.

??????????下一次我們會講事務級別是如何影響鎖行為的。

????? 原文地址如下：

• Part 1: Major Lock Types

[翻譯]:SQL死鎖-鎖的類型