Microsoft SQL Server 2008 Internals: Transactions and Concurrency

  • 3/11/2009
Contents

Controlling Locking

The SQL Server Query Optimizer usually chooses the correct type of lock and the lock mode. You should override this behavior only if thorough testing has shown that a different approach is preferable. Keep in mind that by setting an isolation level, you have an impact on the locks that are held, the conflicts that cause blocking, and the duration of your locks. Your isolation level is in effect for an entire session, and you should choose the one that provides the data consistency required by your application. Table-level locking hints can be used to change the default locking behavior only when necessary. Disallowing a locking level can adversely affect concurrency.

Lock Hints

T-SQL syntax allows you to specify locking hints for individual tables when they are referenced in SELECT, INSERT, UPDATE, and DELETE statements. The hints tell SQL Server the type of locking or row versioning to use for a particular table in a particular query. Because these hints are specified in a FROM clause, they are called table-level hints. SQL Server Books Online lists other table-level hints besides locking hints, but the vast majority of them affect locking behavior. They should be used only when you absolutely need finer control over locking at the object level than what is provided by your session’s isolation level. The SQL Server locking hints can override the current transaction isolation level for the session. In this section, I will mention only some of the locking hints that you might need to obtain the desired concurrency behavior.

Many of the locking hints work only in the context of a transaction. However, every INSERT, UPDATE, and DELETE statement is automatically in a transaction, so the only concern is when you use a locking hint with a SELECT statement. To get the benefit of most of the following hints when used in a SELECT query, you must use an explicit transaction, starting with BEGIN TRAN and terminating with either COMMIT TRAN or ROLLBACK TRAN. The lock hint syntax is as follows:

SELECT select_list
FROM object [WITH (locking hint)]

DELETE [FROM] object [WITH (locking hint)]
[WHERE <search conditions>]

UDPATE object [WITH (locking hint)]
SET <set_clause>
[WHERE <search conditions>]

INSERT [INTO] object [WITH (locking hint)]
<insert specification>

TIP

httpatomoreillycomsourcemspimages1123894.jpg

Not all the locking hints require the keyword WITH, but the syntax without WITH will go away in a future version of SQL Server. It is recommended that all hints be specified using WITH.

You can specify one of the following keywords for the locking hint:

  • HOLDLOCK. This hint is equivalent to the SERIALIZABLE hint. Using this hint is similar to specifying SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, except that the SET option affects all tables, not only the one specified in this hint.

  • UPDLOCK. This hint forces SQL Server to take update locks instead of shared locks while reading the table and holds them until the end of the transaction. Taking update locks can be an important technique for eliminating conversion deadlocks.

  • TABLOCK. This hint forces SQL Server to take a shared lock on the table even if page locks would be taken otherwise. This hint is useful when you know you escalate to a table lock or if you need to get a complete snapshot of a table. You can use this hint with HOLDLOCK if you want the table lock held until the end of the transaction block to operate in Repeatable Read isolation. If you use this hint with a DELETE statement on a heap, it allows SQL Server to deallocate the pages as the rows are deleted. (If row or page locks are obtained when deleting from a heap, space will not be deallocated and cannot be reused by other objects.)

  • PAGLOCK. This hint forces SQL Server to take shared page locks when a single shared table lock might otherwise be taken. (To request an exclusive page lock, you must use the XLOCK hint along with the PAGLOCK hint.)

  • TABLOCKX. This hint forces SQL Server to take an exclusive lock on the table that is held until the end of the transaction block. (All exclusive locks are held until the end of a transaction, regardless of the isolation level in effect. This hint has the same effect as specifying both the TABLOCK and the XLOCK hints together.)

  • ROWLOCK. This hint specifies that a shared row lock should be taken when a single shared page or table lock is normally taken.

  • READUNCOMMITTED | REPEATABLEREAD | SERIALIZABLE. These hints specify that SQL Server should use the same locking mechanisms as when the transaction isolation level is set to the level of the same name. However, the hint controls locking for a single table in a single statement, as opposed to locking all tables in all statements in a transaction.

  • READCOMMITTED. This hint specifies that SELECT operations comply with the rules for the Read Committed isolation level by using either locking or row versioning. If the database option READ_COMMITTED_SNAPSHOT is OFF, SQL Server uses shared locks and releases them as soon as the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, SQL Server does not acquire locks and uses row versioning.

  • READCOMMITTEDLOCK. This hint specifies that SELECT statements use the locking version of Read Committed isolation (the SQL Server default). No matter what the setting is for the database option READ_COMMITTED_SNAPSHOT, SQL Server acquires shared locks when it reads the data and releases those locks when the read operation is completed.

  • NOLOCK. This hint allows uncommitted, or dirty, reads. Shared locks are not requested so that the statement does not block when reading data that is holding exclusive locks. In other words, no locking conflict is detected. This hint is equivalent to READUNCOMMITTED.

  • READPAST. This hint specifies that locked rows are skipped (read past). READPAST applies only to transactions operating at the READ COMMITTED isolation level and reads past row-level locks only.

  • XLOCK. This hint specifies that SQL Server should take an exclusive lock that is held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the specified resource.

Setting a Lock Timeout

Setting a LOCK_TIMEOUT also lets you control SQL Server locking behavior. By default, SQL Server does not time out when waiting for a lock; it assumes optimistically that the lock will be released eventually. Most client programming interfaces allow you to set a general timeout limit for the connection so a query is canceled by the client automatically if no response comes back after a specified amount of time. However, the message that comes back when the time period is exceeded does not indicate the cause of the cancellation; it could be because of a lock not being released, it could be because of a slow network, or it could just be a long-running query.

Like other SET options, SET LOCK_TIMEOUT is valid only for your current connection. Its value is expressed in milliseconds and can be accessed by using the system function @@LOCK_TIMEOUT. This example sets the LOCK_TIMEOUT value to five seconds and then retrieves that value for display:

SET LOCK_TIMEOUT 5000;
SELECT @@LOCK_TIMEOUT;

If your connection exceeds the lock timeout value, you receive the following error message:

Server: Msg 1222, Level 16, State 50, Line 1
Lock request time out period exceeded.

Setting the LOCK_TIMEOUT value to 0 means that SQL Server does not wait at all for locks. It basically cancels the entire statement and goes on to the next one in the batch. This is not the same as the READPAST hint, which skips individual rows.

The following example illustrates the difference between READPAST, READUNCOMMITTED, and setting LOCK_TIMEOUT to 0. All these techniques let you avoid blocking problems, but the behavior is slightly different in each case.

  1. In a new query window, execute the following batch to lock one row in the HumanResources.Department table:

    USE AdventureWorks2008;
BEGIN TRAN;
UPDATE HumanResources.Department
SET ModifiedDate = getdate()
WHERE DepartmentID = 1;

  2. Open a second connection, and execute the following statements:

    USE AdventureWorks2008;
SET LOCK_TIMEOUT 0;
SELECT * FROM HumanResources.Department;
SELECT * FROM Sales.SalesPerson;

    Notice that after error 1222 is received, the second SELECT statement is executed, returning all 17 rows from the SalesPerson table. The batch is not cancelled when error 1222 is encountered.

    WARNING

    httpatomoreillycomsourcemspimages1123922.jpg

    Not only is a batch not cancelled when a lock timeout error is encountered, but any active transaction will not be rolled back. If you have two UPDATE statements in a transaction and both must succeed if either succeeds, a lock timeout for one of the UPDATE statements will still allow the other statement to be processed. You must include error handling in your batch to take appropriate action in the event of an error 1222.

  3. Open a third connection, and execute the following statements:

    USE AdventureWorks2008 ;
SELECT * FROM HumanResources.Department (READPAST);
SELECT * FROM Sales.SalesPerson;

    SQL Server skips (reads past) only one row, and the remaining 15 rows of Department are returned, followed by all the SalesPerson rows. The READPAST hint is frequently used in conjunction with a TOP clause, in particular TOP 1, where your table is serving as a work queue. Your SELECT must get a row containing an order to be processed, but it really doesn’t matter which row. So SELECT TOP 1 * FROM <OrderTable> returns the first unlocked row, and you can use that as the row to start processing.

  4. Open a fourth connection, and execute the following statements:

    USE AdventureWorks2008 ;
SELECT * FROM HumanResources.Department (READUNCOMMITTED);
SELECT * FROM Sales.SalesPerson;

    In this case, SQL Server does not skip anything. It reads all 16 rows from Department, but the row for Department 1 shows the dirty data that you changed in step 1. This data has not yet been committed and is subject to being rolled back.

The READUNCOMMITTED hint is probably the least useful because of the availability of row versioning. In fact, anytime you find yourself needing to use this hint, or the equivalent NOLOCK, you should consider whether you can actually afford the cost of one of the snapshot-based isolation levels.

Save to your account

This chapter is from the book