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

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

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

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

???

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.

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

?

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.

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

?

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.

?? 所以我們首先要了解了鎖的類型。什么是鎖呢？簡單來講，它是一種內(nèi)存結(jié)構(gòu)（32位操作系統(tǒng)中占64字節(jié)，或者64位操作系統(tǒng)占128字節(jié)），從保護級別來看，分行鎖，頁鎖，表鎖，數(shù)據(jù)庫鎖等等。

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

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

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

• 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),這些鎖一般出現(xiàn)在我們使用了select語句查詢時，我想提醒的是，并不是所有的select查詢都會有共享鎖，有些情況是不需要共享鎖的，我們后續(xù)再講。

• 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),這些鎖一般出現(xiàn)在往數(shù)據(jù)表寫入數(shù)據(jù)，比如插入數(shù)據(jù)，更新數(shù)據(jù)以及刪除數(shù)據(jù)。排它鎖保證同一時間只有一個會話能夠?qū)?shù)據(jù)進行寫入操作至于寫入的事務(wù)結(jié)束。

• 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在調(diào)用update語句時先要搜索出哪些數(shù)據(jù)行是需要更新的。比如我們查詢：“update MyTable set Column1=0 where Colum1 is null” SQL SERVER會申請更新鎖去查詢數(shù)據(jù)表的每一行是否是符合更新條件的(Column is null)。一旦找到需要更新的數(shù)據(jù)行，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,等等)。這些鎖表明它的子對象中有級別更高的鎖。比如如何一個數(shù)據(jù)行上有X鎖，會在這個行數(shù)據(jù)所處的數(shù)據(jù)頁，表，數(shù)據(jù)庫級別上存在IX的鎖。這樣設(shè)計的主要目的是為了優(yōu)化。當(dāng)你需要以獨占方式去訪問數(shù)據(jù)庫時(如果此時X鎖設(shè)置在數(shù)據(jù)庫級別上)，它會遍歷所有對象中的所有行，來判斷是否有比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 ?你將會看到更加詳細的內(nèi)容，包含一個超過400個格子的表格。但對于我們來講，只需要關(guān)注如下壓縮之后的版本即可。

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

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

????? 其實這個顏色的標(biāo)注容易讓人不理解，還是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:

???? 從這張表格中可以得出下面三個關(guān)注：

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.

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

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

• Part 1: Major Lock Types

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