[翻译]: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