Lock Modes - 锁模式

Lock mode
Description

Explanation

Shared (S)
共享锁
Used for read operations that do not change or update data, such as a SELECT statement.

用于无更新的读取。大家可以同时读取同一数据。
Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

读取时，SQL Server自动启动共享锁。

何时发生：读取数据时（If Read Committed, Repeatable, and  Serializable）。

其它事务能读吗？可以，共享读取。

Update(U)

更新锁

(not for all updates, only for the scenario that  two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently)
Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

当2个Transactions都想先读、后更改同一数据时，会发生死锁的情况。用更新锁可以防止这种情况下的死锁发生。
In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

More details on
http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx

设想一下，有2个事务几乎同时读到了同一数据。此时2个都是共享锁。2个又想同时更改这同一数据，这时这2个共享锁都必须变成Exclusive锁才可以更改数据，我等你释放你的共享锁，你等我释放我的共享锁。死锁就发生了。

使用更新锁可以防止这种情况的发生。先读到数据的那个人会得到更新锁，当他更改数据时(假定后读的那个已读完了)，这个更新锁就变成了排它锁（即共享锁è更新锁è排它锁）。而后读的那个人必须要等第一个的更新完成后才会进行。

在这种情况下，SQL Server自动启动更新锁。

何时发生：当2个Transactions都想先读、后又想同时更改同一数据时（适用于Repeatable, and  Serializable Transaction Isolation Levels.）

其它事务能读吗？第二个transaction可以（注意: Kalen's 例子使用了Read Committed, 非Repeatable, or  Serializable)。

Exclusive (X)

排它锁
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

更改数据时出现。确保多个事务不能同时更改同一个数据。
Exclusive (X) locks prevent access to a resource by concurrent transactions. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

防止同时更新数据。

在更改数据时，SQL Server自动启动排它锁锁。

何时发生：更改数据时（适用于所有 Transaction Isolation Levels）。

其它能读吗？可以。但前提是必须使用 NOLOCK 表提示或Uncommitted 隔离。否则，必须等。
Intent(I)

意向锁

Used when:
·        To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

·        To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.



Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.
Intent locks serve two purposes:

  • To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  • To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Intent shared (IS) - Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

Intent exclusive (IX) - Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

Shared with intent exclusive (SIX) - Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

More reading:

If a user has an X lock on a row, he will also get an Intent-Exclusive (IX) lock on the page that contains the row, and an IX lock on the table. 

http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/

当要锁定的资源有层次结构时，SQL Server会先将意向锁放在较高层资源，表明有较低级别的资源上会有锁。这样，其他事务就不能跳过意向锁而更改较高层的资源，以致于在较低级别资源上设置的锁无效。

例如，在页或行上设置共享锁（S 锁）之前，SQL Server会先在表级层次设置共享意向锁。 在表级设置意向锁后，可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。另外、意向锁还可以提高性能。例如，在上例中，因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁主要有三类：

意向共享 (IS) – 表明将会有读取层次结构中某些（而并非所有）低层资源的请求或将要在这些低层资源有共享锁。意向共享锁实际上是设置在层次结构中的上一层。

意向排他 (IX) - 表明将会有修改层次结构中某些（而并非所有）低层资源的请求或将要在这些低层资源有排它锁。意向排他锁实际上是设置在层次结构中的上一层。IX 是 IS 的超集，因此，它也保护针对低层级别资源请求的共享锁

意向排他共享 (SIX) - 表明将会有针对层次结构中所有低层资源的共享锁以及针对某些（而并非所有）低层资源的意向排他锁。 顶级资源允许使用并发 IS 锁。 例如，获取表上的 SIX 锁也将获取正在修改的页上的意向排他锁以及修改的行上的排他锁。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其他事务对资源进行更新，但是其他事务可以通过获取表级的 IS 锁来读取层次结构中的低层资源。

何时发生：几乎总是（适用于 Read Committed, Repeatable, and  Serializable Transaction Isolation Levels）。

其它能读吗？可以。如果是读取请求的话。

Schema

架构
Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.

当你进行表数据定义语言 (DDL) 操作（例如添加列或删除表）时，数据库引擎会使用架构修改 (Sch-M) 锁。 在ＤＤＬ操作期间，Sch-M 锁将阻止对表进行访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。

某些数据操作语言 (DML) 操作（例如表截断）也使用架构修改 (Sch-M) 锁阻止对正在被截断的表的访问。

数据库引擎在编译和执行查询时则使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止任何事务锁，包括排它 (X) 锁。 因此，在编译的过程中，其他事务（包括那些针对表使用 X 锁的事务）将继续运行。 但是，无法针对表执行ＤＤＬ和需要 Sch-M 锁的DML 操作（如表截断）。

何时发生：（１）执行ＤＤＬ操作和Ｔｒｕｎｃａｔｅ表时，Sch-M 锁启动，所有其它操作被阻止；　（２）编译和执行查询时则使用架构稳定性 (Sch-S) 锁，不能执行ＤＤＬ和表截断。

其它能读吗？（1）架构修改锁。不能；（2）编译和执行查询时，可以。

Bulk Update (BU)

大容量更新 (BU)
Used when bulk copying data into a table and the TABLOCK hint is specified.

在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。
The Database Engine uses bulk update (BU) locks when bulk copying data into a table, and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.

在将数据大容量复制到表中时，如果该表设定了 TABLOCK 提示或使用 sp_tableoption 设置了 table lock on bulk load 表选项，数据库引擎将使用大容量更新 (BU) 锁。该锁（BU 锁）将允许多个线程同时将数据并发地大容量加载到同一表，同时也防止其他非大容量加载数据的操作访问该表。

何时发生：当Bulk Insert时，且该表设定了 TABLOCK 提示或设置了 table lock on bulk load 表选项。

其它事务能读吗？不能

注：TABLOCK 表提示 = 该表有共享锁（Shared Lock）

Key-range
键范围
Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。
Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁可防止幻读。 通过保护行之间键的范围，它还防止对事务访问的记录集进行幻像插入或删除。

何时发生： 使用 serializable transaction isolation level 时

其它事务能读吗？能，

See more below on 键范围锁定(key-range locks)













　
========== Extended Reading  on 键范围锁定 ==================

键范围锁定解决了幻像读并发问题，并支持可串行事务。键范围锁覆盖单个记录以及记录之间的范围，可以防止对事务访问的记录集进行幻像插入或删除。键范围锁仅用于代表在可串行隔离级别上操作的事务。
可串行性要求如果任意一个查询在一个事务中后面的某一时刻再次执行，其所获取的行集应与该查询在同一事务中以前执行时所获得的行集相同。如果本查询试图提取的行不存在，则在试图访问该行的事务完成之前，其它事务不能插入该行。如果允许另一个事务插入该行，则它将以幻像出现。
如果另一个事务试图插入驻留在锁定数据页上的行，页级锁定可以防止添加幻像行，并维护可串行性。但是，如果该行要添加到未被第一个事务锁定的数据页，应设定锁定机制防止添加该行。
键范围锁通过覆盖索引行和索引行之间的范围来工作（而不是锁定整个基础表的行）。因为第二个事务在该范围内进行任何行插入、更新或删除操作时均需要修改索引，而键范围锁覆盖了索引项，所以在第一个事务完成之前会阻塞第二个事务的进行。

 键范围锁模式
键范围锁包括范围组件和行组件，按范围-行格式指定：
  • 范围表示保护两个连续索引项之间的范围的锁模式。
  • 行表示保护索引项的锁模式。
  • 模式表示使用的组合锁模式。键范围锁模式由两部分组成。第一部分表示用于锁定索引范围 (RangeT) 的锁类型，第二部分表示用于锁定特定键 (K) 的锁类型。这两部分用下划线 (_) 连接，如 RangeT_K。
范围
行
模式
·        描述
RangeS
S
RangeS_S
共享范围，共享资源锁；可串行范围扫描。
RangeS
U
RangeS_U
共享范围，更新资源锁；可串行更新扫描。
RangeI
NULL
RangeI_N
插入范围，空资源锁；用于在索引中插入新键之前测试范围。
RangeX
X
RangeX_X
排它范围，排它资源锁；用于更新范围中的键。

说明  内部空锁模式与所有其它锁模式相兼容。
键范围锁模式有一个兼容性矩阵，表示哪些锁与在重叠键和范围上获取的其它锁兼容。
现有的授权模式
请求模式
S
U
X
RangeS_S
RangeS_U
RangeI_N
RangeX_X
共享 (S)
是
是
否
是
是
是
否
更新 (U)
是
否
否
是
否
是
否
排它 (X)
否
否
否
否
否
是
否
RangeS_S
是
是
否
是
是
否
否
RangeS_U
是
否
否
是
否
否
否
RangeI_N
是
是
是
否
否
是
否
RangeX_X
否
否
否
否
否
否
否

会话锁
当键范围锁与其它锁重叠时，创建会话锁。
锁 1
锁 2
会话锁
S
RangeI_N
RangeI_S
U
RangeI_N
RangeI_U
X
RangeI_N
RangeI_X
RangeI_N
RangeS_S
RangeX_S
RangeI_N
RangeS_U
RangeX_U

在不同的复杂环境下（有时是在运行并发进程时），可以在一小段时间内观察到会话锁。

 可串行范围扫描、单独提取、删除和插入
键范围锁定确保以下四种方案是可串行的：
  • 范围扫描查询
  • 单独提取不存在的行
  • 删除操作
  • 插入操作
然而，在可以发生键范围锁定之前，必须满足下列条件：
  • 事务隔离级别必须设置为 SERIALIZABLE。
  • 对数据执行的操作必须使用索引范围访问。只有当查询处理程序（如优化器）选择访问数据的索引路径时，才激活范围锁定。
下列每种方案的示例都基于下图中的表和索引。

 范围扫描查询
为了确保范围扫描查询是可串行的，每次在同一事务中执行的相同查询应返回同样的结果。其它事务不能在范围扫描查询中插入新行；否则这些插入将成为幻像插入。例如，下面的查询使用前面插图中的表和索引：
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'
键范围锁放置在与数据行范围（名称在值 Adam 和 Dale 之间的行）对应的索引项上，以防止添加或删除满足上述查询条件的新行。虽然此范围中的第一个名称是 Adam，但是此索引项的 RangeS_S 模式键范围锁确保了以字母 A 开头的新名称（如 Abigail）不能添加在 Adam 之前。同样，Dale 索引项的 RangeS_S 键范围锁确保了以字母 C 开头的新名称（如 Clive）不能添加在 Carlos 之后。
说明  控制的 RangeS_S 锁数量为 n+1，此处 n 是满足查询条件的行数。

 单独提取不存在数据
如果事务中的查询试图选择不存在的行，则以后在相同的事务中发出这一查询时，必须返回相同的结果。不允许其它事务插入不存在的行。例如，对于下面的查询：
SELECT name FROM mytable WHERE name = 'Bill'
键范围锁放置在对应于名称范围 Ben 到 Bing 之间的索引项上，因为名称 Bill 将插入到这两个相邻的索引项之间。RangeS_S 模式键范围锁放置在索引项 Bing 上。这样可以防止任何其它事务在索引项 Ben 和 Bing 之间插入插入值（如 Bill）。

 删除操作
在事务中删除值时，在事务执行删除操作期间不必锁定值所属的范围。锁定删除的键值直至事务结束足以保持可串行性。例如，对于下面的 DELETE 语句：
DELETE mytable WHERE name = 'Bob'
排它 (X) 锁放置在对应于名称 Bob 的索引项上。其它事务可以在删除值 Bob 的前后插入或删除值。但是任何试图读取、插入或删除值 Bob 的事务将被阻塞，直到删除的事务提交或回滚为止。
可以使用下列三种基本锁模式执行范围删除：行锁、页锁或表锁。页、表或行锁定策略由查询优化器确定，或者可以由用户通过优化程序提示（如 ROWLOCK、PAGLOCK 或 TABLOCK）指定。在使用页锁或表锁的情况下，SQL Server 立即释放包含已删除行的索引页，并假定从页中删除了所有行。相反，使用行锁时，所有删除的行只是被标记为已删除；以后通过后台任务从索引页中删除它们。

 插入操作
在事务中插入值时，在事务执行插入操作期间不必锁定值所属的范围。锁定插入的键值直至事务结束足以维护可串行性。例如，对于下面的 INSERT 语句：
INSERT mytable VALUES ('Dan')
RangeI_N 模式键范围锁放置在对应于名字 David 的索引项上以测试范围。如果已授权锁定，则插入 Dan，并且排它 (X) 锁放置在值 Dan 上。RangeI_N 模式键范围锁仅对测试范围必需，而不在执行插入操作的事务期间保留。其它事务可以在插入值 Dan 的前后插入或删除值。但是，任何试图读取、插入或删除值 Dan 的事务将被阻塞，直到插入的事务提交或回滚为止。