All about Concurrency - Locking Modes, Transaction Isolation Level, Locking Hints, and Table Hints

Lock Modes

The Microsoft SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions. (数据库引擎用此来决定在不同的情况下同发事务如何同时读写同一资源)

Shared – 一个事务读一资源时，其它事务也可同时读这同一资源。但不可以同时更改这个资源。一般情况下，读完了，锁就释放了，可以更改了。但如果事务隔离等级是 Repeatable Read或更高，或者某些锁提示会导致共享锁在读完后也不被释放，而一直锁到整个事务结束。

Exclusive – 一个事务正在更改一资源，其它事务无法同时更改，但如果有 Nolock表提示或事务隔离等级是Read Uncommitted，其它事务则可以同时读这正在被修改的资源。

Update – 两个事务先后读同一资源，在读完之前，这2个事务又都想更改这同一资源。先读的那个事务将会得到更新锁，继而变成排它锁。死锁的情况就不会发生了。

Intent – 为了减少一个结构中不同层次中的不同锁之间的冲突，或提高锁的效率，数据库引擎会在较高层次上设置意向锁。

Schema – 如果一个事务正在对一个表进行DDL操作，数据库引擎启动架构更新锁 (Sch-M) ，整个表被锁，所有的其它事务都无法接触到该表。读也不行。表截断时也是这样。

如果数据库引擎正在编译和执行查询，它则启动架构稳定锁(Sch-S)。这种锁不会阻止事务锁包括排它锁（即在编译和执行查询时，其它事务可以在相关的表上设置排它锁），但它会阻止那些需要架构更新锁的操作如DDL和表截断等。

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

Key-range –当事务隔离级别是串化时，数据库引擎将启动键范围锁。该锁会对所读取的记录集进行隐式保护。即通过保护行之间键的范围来防止幻读、幻插或幻删。

Isolation Levels in the Database Engine

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

事务隔离等级是指一个读取事务与其它更改数据的事务之间的隔离程度(即一个读，一个改)。不同事务隔离等级将决定那个读取事务读到的数据是否是脏、幻或可重复性数据。为简单起见，只考虑2个事务，A事务和B事务。假定B事务正在更改数据，它对所修改的数据拥有排它锁。在它修改操作完成之前，它始终拥有排它锁，不管事务隔离等级的设置是什么。在B事务更改数据的操作完成之前，如果A事务也想修改这同一数据，它必须等B先完成。但如果A事务想读取B事务正在修改的数据，事务隔离等级的选择就起关键作用了。事务隔离等级越宽松，越可能允许更多的用户同时读取数据。所以，事务隔离等级的选择主要取决于对多用户同时读取数据的需求和对所读到的数据的完整性的要求之间的平衡。

ANSI定义了四种：

Read Uncommitted - B改的时候，A能读，但A读到的数据未必是最终的有效数据，即读到的可能是脏数据。

Read Committed – 在这种情况下， A读不到B还未完成的修改。即A不可能读到脏数据。但A事务本身可能会更改数据。这样的话，A就有可能读到非重复性数据或幻数据。

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

Repeatable Read - B无法同时改动A正在读的数据，A也无法去读B正在改动而还未Committed的数据，但B可以添加数据。A也可以读到这些刚添加的数据。不过，这些读到的数据是幻读(即第一次和第二次使用了相同的Query，但两次读到的结果不同，第二次读到了刚添加的数据）。

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

=========Background Information on Phantom Read from Wiki=========

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

This can occur when range locks are not acquired on performing a SELECT ... WHERE operation. The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats a ranged SELECT ... WHERE query and, between both operations, Transaction 2 creates (i.e. INSERT) new rows (in the target table) which fulfill thatWHERE clause.

Transaction 1	Transaction 2
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;
	/* Query 2 */ INSERT INTO users VALUES ( 3, 'Bob', 27 ); COMMIT;
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30; COMMIT;

Note that Transaction 1 executed the same query twice. If the highest level of isolation were maintained, the same set of rows should be returned both times, and indeed that is what is mandated to occur in a database operating at the SQL SERIALIZABLE isolation level. However, at the lesser isolation levels, a different set of rows may be returned the second time.

In the SERIALIZABLE isolation mode, Query 1 would result in all records with age in the range 10 to 30 being locked, thus Query 2 would block until the first transaction was committed. In REPEATABLE READ mode, the range would not be locked, allowing the record to be inserted and the second execution of Query 1 to include the new row in its results.

Serializable - 除了Repeatable Read的限制外，在A读完之前，B可以向表中添加新的Rows。但新添加的rows不能落在A所读键范围之内（the range of keys）。

除ANSI 的四种外，SQL Server 2005又利用行版（row versioning）添加了2种：

READ_COMMITTED_SNAPSHOT (在Statement level)

• READ_COMMITTED_SNAPSHOT ON – When this database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks.

• READ_COMMITTED_SNAPSHOT OFF - When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation behaves as it did in earlier versions of SQL Server.

Both implementations meet the ANSI definition of read committed isolation.

SNAPSHOT (在Transaction level) 

B更改数据时，A读到的是B事务刚开始时的数据。A读的和B更改的不是同一个时刻的数据。

How to Set up Transaction Isolation Level?

• READ_COMMITTED_SNAPSHOT是Database Level的option:

          ALTERR DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

• 其它的都是在statement或Transaction level：

          SET TRANSACTION ISOLATION LEVEL

          { READ UNCOMMITTED

            | READ COMMITTED

            | REPEATABLE READ

            | SNAPSHOT

            | SERIALIZABLE

          }

Outcomes of Different Transaction Isolation Levels:

Isolation level	Dirty Read	Nonrepeatable Read	Lost Update	Phantom Read
Read uncommitted	Yes	Yes	Yes	Yes
Read committed	No	Yes	Yes	Yes
Repeatable read	No	No	No	Yes
Serializable	No	No	No	No
Snapshot	No	No	No	No

Dirty Read - occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

Nonrepeatable Read - occurs when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

Lost Update - occurs when two transactions select the same row for updates, the later update overwrites the earlier update as each transaction is unaware of the other.
Phantom Read - occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Locking Hints (a.k.a. table-level locking hints)

锁提示与表提示很相近，也在表的层次。它可以Override the current transaction isolation level for the session.

Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. The hints specify the type of locking or row versioning the instance of the SQL Server Database Engine uses for the table data. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.

Three Purposes of Locking Hints:

GRANULARITY control: RowLock, PageLock, TabLock, DBLOck

LOCKMODES Control: UPDLOCK, XLOCK

DURATION Control: HOLDLOCK, NOLOCK

Example:

As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

USE AdventureWorks2008R2;

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

GO

BEGIN TRANSACTION;

GO

SELECT Title

    FROM HumanResources.Employee WITH (NOLOCK);

GO

-- Get information about the locks held by the transaction.

SELECT

        resource_type,

        resource_subtype,

        request_mode

    FROM sys.dm_tran_locks

    WHERE request_session_id = @@spid;

-- End the transaction.

ROLLBACK;

GO

Table Hints 

通过指定锁定方法、一个或多个索引、查询处理操作（如表扫描或索引查找）或其他选项，表提示在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。 表提示在 DML 语句的 FROM 子句中指定，仅影响在该子句中引用的表或视图。

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 

[ NOEXPAND ] {

    INDEX  ( index_value [ ,...n ] ) | INDEX =  ( index_value )    | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]

  | FORCESCAN

  | FORCESEEK

  | HOLDLOCK

  | NOLOCK

  | NOWAIT

  | PAGLOCK

  | READCOMMITTED

  | READCOMMITTEDLOCK

  | READPAST

  | READUNCOMMITTED

  | REPEATABLEREAD

  | ROWLOCK

  | SERIALIZABLE

  | SPATIAL_WINDOW_MAX_CELLS = integer

  | TABLOCK

  | TABLOCKX

  | UPDLOCK

  | XLOCK

}

--Example 1:

USE AdventureWorks2012;

GO

UPDATE Production.Product

WITH (TABLOCK)

SET ListPrice = ListPrice * 1.10

WHERE ProductNumber LIKE 'BK-%';

GO

--Example 2:

--The following example uses the FORCESCAN hint to force the query optimizer to perform a scan operation

--on the Sales.SalesOrderDetail table.

USE AdventureWorks2012;

GO

SELECT h.SalesOrderID, h.TotalDue, d.OrderQty

FROM Sales.SalesOrderHeader AS h

    INNER JOIN Sales.SalesOrderDetail AS d

    WITH (FORCESCAN)

    ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);

Contrast of Table Hints and Table-level Locking Hints:

Table Hints	Locking hint	Description
HOLDLOCK	HOLDLOCK	Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK	NOLOCK	Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK	PAGLOCK	Use page locks where a single table lock would usually be taken.
READCOMMITTED	READCOMMITTED	Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST	READPAST	Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED	READUNCOMMITTED	Equivalent to NOLOCK.
REPEATABLEREAD	REPEATABLEREAD	Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
ROWLOCK	ROWLOCK	Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE	SERIALIZABLE	Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
TABLOCK	TABLOCK	Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX	TABLOCKX	Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
UPDLOCK	UPDLOCK	Use update locks instead of shared locks while reading a table, and hold locks until the end of the sttement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
XLOCK	XLOCK	Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
NOWAIT		Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.
SPATIAL_WINDOW_MAX_CELLS = integer		Specifies the maximum number of cells to use for tessellating a geometry or geography object. number is a value between 1 and 8192. This option allows for fine-tuning of query execution time by adjusting the tradeoff between primary and secondary filter execution time. A larger number reduces secondary filter execution time, but increases primary execution filter time and a smaller number decreases primary filter execution time, but increase secondary filter execution. For denser spatial data, a higher number should produce a faster execution time by giving a better approximation with the primary filter and reducing secondary filter execution time. For sparser data, a lower number will decrease the primary filter execution time. This option works for both manual and automatic grid tessellations.
NOEXPAND ] {		Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.
INDEX  ( index_value [ ,...n ] ) | INDEX =  ( index_value )    | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]		The INDEX() syntax specifies the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement. The alternative INDEX = syntax specifies a single index value. Only one index hint per table can be specified. If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. If multiple indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index accessed. If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns. The maximum number of indexes in the table hint is 250 nonclustered indexes.
FORCESCAN		Introduced in SQL Server 2008 R2 SP1, this hint specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. When this occurs, the amount of memory granted for the operation is too small and query performance is impacted. FORCESCAN can be specified with or without an INDEX hint. When combined with an index hint, (INDEX = index_name, FORCESCAN), the query optimizer considers only scan access paths through the specified index when accessing the referenced table. FORCESCAN can be specified with the index hint INDEX(0) to force a table scan operation on the base table.
FORCESEEK		Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. In that case, the query optimizer considers only index seek operations through the specified index using at least the specified index columns.