Resolving Deadlock Problems

Deadlocks befuddle many programmers and are the bane of many applications. A deadlock occurs when, without some intervening action, processes cannot get the locks they need no matter how long they wait. Simply waiting for locks is not a deadlock condition. SQL Server automatically detects the deadlock condition and terminates one of the processes to resolve the situation. The process gets the infamous error message 1205 indicating that it was selected as the "victim" and that its batch and the current transaction have been canceled. The other process can then get the locks it needs and proceed. The two general forms of deadlocks are cycle deadlocks and conversion deadlocks. (See Chapter 14 for more on these two kinds of deadlocks.)

Cycle Deadlock Example

If you repeatedly run the following two transactions simultaneously from different Osql.EXE sessions, you're nearly assured of encountering a "deadly embrace" (cycle deadlock) almost immediately from one of the two processes. It should be clear why: one of the processes gets an exclusive lock on a row in the authors table and needs an exclusive lock on a row in the employee table. The other process gets an exclusive lock on the row in employee, but it needs an exclusive lock for the same row in authors that the first process has locked.

 -- Connection 1 USE pubs WHILE (1=1) BEGIN BEGIN TRAN UPDATE employee SET lname='Smith' WHERE emp_id='PMA42628M' UPDATE authors SET au_lname='Jones' WHERE au_id='172-32-1176' COMMIT TRAN END -- Connection 2 USE pubs WHILE (1=1) BEGIN BEGIN TRAN UPDATE authors SET au_lname='Jones' WHERE au_id='172-32-1176' UPDATE employee SET lname='Smith' WHERE emp_id='PMA42628M' COMMIT TRAN END 

The result is the dreaded error 1205 (shown here) from one of the connections. The other connection continues along as if no problem has occurred.

 Server: Msg 1205, Level 13, State 50, Line 1 Transaction (Process ID 52) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

If you simply rewrite one of the batches so that both batches first update authors and then update employee, the two connections will run forever without falling into a cycle deadlock. Or you can first update employee and then update authors from both connections. Which table you update first doesn't matter, but the updates must be consistent and you must follow a known protocol. If your application design specifies a standard protocol for accessing the tables consistently, one of the connections gets the exclusive page lock on the first table and the other process must wait for the lock to be released. Simply waiting momentarily for a lock is normal and usually fast, and it happens frequently without your realizing it. It is not a deadlock.

Conversion Deadlock Example

Now run the following transaction simultaneously from two different OSQL.EXE or SQL Query Analyzer sessions. You're running the same script, so it's obvious that the two processes follow a consistent order for accessing tables. But this example quickly produces a deadlock. A delay has been added so that you encounter the race condition more quickly, but the condition is lurking there even without the WAITFOR DELAY—the delay just widens the window.

 USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors WHERE au_id='172-32-1176' -- Add 5 sec sleep to widen the window for the deadlock WAITFOR DELAY '00:00:05' UPDATE authors SET au_lname='Updated by ' + CONVERT(varchar, @@spid) WHERE au_id='172-32-1176' COMMIT TRAN 

You can correct this example in a couple of ways. Does the isolation level need to be Repeatable Read? If Read Committed is sufficient, you can simply change the isolation level to get rid of the deadlock and to provide better concurrency. If you use Read Committed isolation, the shared locks can be released after the SELECT, and then one of the two processes can acquire the exclusive lock it needs. The other process waits for the exclusive lock and acquires it as soon as the first process finishes its update. All operations progress smoothly, and the queuing and waiting happen invisibly and so quickly that it is not noticeable to the processes.

But suppose that you need Repeatable Read (or Serializable) isolation. In this case, you should serialize access by using an update lock, which you request using the UPDLOCK hint. Recall from Chapter 14 that an update lock is compatible with a shared lock for the same resource. But two update locks for the same resource are not compatible, and update and exclusive locks are also not compatible. Acquiring an update lock does not prevent others from reading the same data, but it does ensure that you're first in line to upgrade your lock to an exclusive lock if you subsequently decide to modify the locked data. (The important issue here is that the exclusive lock can be acquired, so this technique works equally well even if that second statement is a DELETE and not an UPDATE, as in this example.)

By serializing access to the exclusive lock, you prevent the deadlock situation. The serialization also reduces concurrency, but that's the price you must pay to achieve the high level of transaction isolation. A modified example follows; multiple simultaneous instances of this example will not deadlock. Try running about 20 simultaneous instances and see for yourself. None of the instances deadlocks and all complete, but they run serially. With the built-in 5-second sleep, it takes about 100 seconds for all 20 connections to complete because one connection completes about every 5 seconds. This illustrates the lower concurrency that results from the need for higher levels of consistency (in this case, Repeatable Read).

 USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors (UPDLOCK) WHERE au_id='172-32-1176' -- Add 5 sec sleep to widen the window for the deadlock WAITFOR DELAY '00:00:05' UPDATE authors SET au_lname='Updated by ' + CONVERT(varchar, @@spid) WHERE au_id='172-32-1176' COMMIT TRAN 

As a general strategy, you should add the UPDLOCK hint (or some other serialization) if you discover during testing that conversion deadlocks are occurring. Or add UPDLOCK from the outset if you detect from your CRUD analysis that deadlocks are likely, and then try backing it off during multiuser testing to see if it is absolutely necessary. Either approach is viable. If you know that in most cases the read-for-update (discussed later) will follow with an actual update, you might opt for the UPDLOCK hint from the outset and see if you can back it off later. But if the transaction is short and you will later update the data in most cases, there isn't much point in backing off the update lock. The shared lock will need to upgrade to an exclusive lock, so getting the update lock in the first place makes good sense.

Preventing Deadlocks

Deadlocks can usually be avoided, although you might have to do some detailed analysis to solve the problems that cause them. Sometimes the cure is worse than the ailment, and you're better off handling deadlocks rather than totally preventing them, as we'll discuss in the next section. Preventing deadlocks (especially conversion deadlocks) requires a thorough understanding of lock compatibility. These are the main techniques you can use to prevent deadlocks:

• To prevent cycle deadlocks, make all processes access resources in a consistent order.
• Reduce the transaction isolation level if it's suitable for the application.
• To prevent conversion deadlocks, explicitly serialize access to a resource.

Deadlock prevention is a good reason to use stored procedures. By encapsulating the data access logic in stored procedures, it's easier to impose consistent protocols for the order in which resources (for example, tables) are accessed, which can help you avoid cycle deadlocks. But in so doing, you do not reduce the likelihood of conversion deadlocks. As noted above, conversion deadlocks are best dealt with by serializing access to a resource or by lowering the transaction isolation level if appropriate. The most common scenario for conversion deadlocks is the read-for-update situation. If a resource will be read within a transaction requiring Repeatable Read or Serializable isolation and will be updated later, you should request an update lock on the read using the UPDLOCK hint. The update lock will let other users read the data but will prevent them from updating the data or doing a read-for-update. The net effect is that once the update lock is acquired, the process will be next in line for the exclusive lock needed to actually modify it.

Handling Deadlocks

The cost of serializing access is that other users wanting to read-for-update (or actually update or delete) must wait. If you are not experiencing deadlocks, serializing access might needlessly reduce concurrency. You might have a case in which a transaction often does a read-for-update but only infrequently does the update. In this case, deadlocks might be infrequent. The best course of action might be to not prevent deadlocks and simply deal with them when they occur.

Preventing deadlocks can significantly reduce concurrency because the read-for-update would be blocked. Instead, you can simply write your applications to handle deadlocking. Check for deadlock message 1205, and retry the transaction. With retry logic, you can live with moderate deadlock activity without adding a lot of serialization to the application. It's still a good idea to keep count of how often you experience deadlocks; if the incidence is high, the wasted effort and constant retrying are likely to be worse than the cost of preventing the deadlock in the first place. How you write the deadlock handler will depend on the language or tool you use to build your application. But an application that is prone to deadlocks should have a deadlock handler that retries. Such a handler must be written in the host language. There is no way to do a retry directly within a stored procedure or a batch because deadlock error 1205 terminates the batch.

Volunteering to Be the Deadlock Victim

Recall from Chapter 14 that the LOCK_MONITOR process in SQL Server attempts to choose as the deadlock victim the process that would be the least expensive to roll back, considering the amount of work the process has already done. But a process can also offer to "sacrifice itself" as the victim for deadlock resolution. You can make this happen by using the SET DEADLOCK_PRIORITY LOW | NORMAL statement. If a process has a deadlock priority of LOW and the other participating process is NORMAL (the default), the LOW process is chosen as the victim even if it was not the process that closed the loop.

Adding SET DEADLOCK_PRIORITY LOW to one of the connections (and not the other) indicates that it will be selected as the victim, even if it will be more expensive to roll back. You might find this useful if, for example, you are building summary tables for reporting purposes, and also performing OLTP activities on the same database and you occasionally have deadlocks and you know that one process is more important than the other. You set the less important process to LOW. It might also be useful if one application was written with a good deadlock handler and the other was not. Until the application without the handler can be fixed, the "good" application can make the simple change of volunteering itself and then handle a deadlock with retry logic when the deadlock occurs later.

Watching Locking Activity

Locking problems often result from locks that you don't even realize are being taken. You might be updating only table A, but blocking issues arise on table B because of relationships that you don't realize exist. If, for example, a foreign key relationship exists between table A and table B and the update on A is causing some query activity to B, some shared locks must exist. Or a trigger or a nested call to another procedure might cause some locking that isn't obvious to you.

In cases like these, you must be able to watch locks to look for locking operations that you weren't aware of. The graphical lock display of SQL Server Enterprise Manager is the most convenient way to watch locking in many cases. However, SQL Server Enterprise Manager provides only a snapshot of the current state of locking; you can miss a lot of locking activity that occurs in the time it takes you to refresh the display.

Identifying the Culprit

As with most debugging situations, the hardest part of solving a problem with locking is understanding the problem. If you get complaints that "the system is hung," it's a good bet that you have a blocking problem. Most blocking problems happen because a single process holds locks for an extended period of time. A classic case (as I discussed earlier) is an interactive application that holds locks until the user at the other end takes an action, such as clicking a button to commit the transaction or scrolling to the end of the output, which causes all the results to be processed. If the user goes to lunch and doesn't take that action, everyone else might as well grab lunch too, because they're not going to get much work done. Locks pile up, and the system seems to hang. The locks in the system are reported in the pseudo-system table, syslockinfo in the master database. I'll talk about a related pseudo-system table, sysprocesses, later in the chapter.

NOTE

---

The pseudo-system tables are not maintained as on-disk structures. Locking and process data are by definition relevant only at runtime. So syslockinfo and sysprocesses are presented as system tables, and although they can be queried just like other system tables, they do not have on-disk storage as normal tables do.

A big challenge in trying to identify the cause of blocking is that when you experience such problems, you probably have hundreds or thousands of locks piled up. At these times, it's hard to see the trees through the forest. Usually, you just want to see which process is holding up everyone else. Once you identify it, of course, you need to immediately get it out of the way, either by forcing it to complete or, if necessary, by issuing the KILL command on the connection. The longer-term solution is to rework the application so that it will not hold locks indefinitely.

SQL Server Enterprise Manager provides a graphical way to watch locking activity, as shown in Figure 16-6; this is the best method most of the time. From the Management folder, choose Current Activity. You can look at locks either by process or by object. If you select the Locks / Process ID option, the graphic shows which processes are blocked and which processes are blocking. When you double-click on the process in the right pane, you see the last command the process issued. This is what Figure 16-6 shows. You can then send a message to the offending user to complete the transaction (although this is a short-term solution at best). Or you can kill the process from SQL Server Enterprise Manager, which isn't ideal but is sometimes the best short-term course of action. You can select a particular process from the left pane, under the Locks / Process ID option, and the right pane will show all the locks held by that process.

Figure 16-6. Viewing the last batch issued by a blocking process in SQL Server Enterprise Manager.

Alternatively, you can select the Locks / Object option under Current Activity. The left pane displays a graphic of each locked object. If you then select one of these objects, the right pane shows which processes have that object locked. Again, when you double-click on the process in the right pane, you see the last command the process issued.

But sometimes even SQL Server Enterprise Manager can get blocked by locking activity in tempdb, so it's useful to know how to monitor-lock the old-fashioned way, by using system stored procedures or by querying directly from the syslockinfo table. You start by running sp_who2 and sp_lock. Most people are familiar with sp_who but not sp_who2, which works in almost the same way but formats the output in a more readable way and contains more of the columns from the sysprocesses table. However, sysprocesses contains additional information that neither sp_who nor sp_who2 reports, so you might even want to write your own sp_who3! We'll look at the sysprocesses table in more detail later in this chapter.

The BlkBy column of the sp_who2 output shows the ID (spid) of a blocking process. The procedure sp_lock provides a formatted and sorted listing of syslockinfo that decodes the lock types into mnemonic forms ,such as IS (Intent-Shared) instead of type 7. In Chapter 14, we looked at output from sp_lock to watch the various types and modes of locking. If your users say that the system is hung, try to log on and execute sp_who2 and sp_lock. If you can log on and execute these procedures, you immediately know that the system is not hung. If you see a nonzero spid in the BlkBy column of the sp_who2 output or if the Status value is WAIT in the sp_lock output, blocking is occurring. It's normal for some blocking to occur—it simply indicates that one process is waiting for a resource held by another. If such contention didn't exist, you wouldn't even need to lock. But if you reissue the query for lock activity a moment later, you expect to find that same blockage cleared up. In a smoothly running system, the duration that locks are held is short, so long pileups of locks don't occur.

When a major lock pileup occurs, you can get a lengthy chain of processes blocking other processes. It can get pretty cumbersome to try to track this manually. You can create a procedure like the following to look for the process at the head of a blocking chain:

 USE master GO CREATE PROCEDURE sp_leadblocker AS IF EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)) SELECT spid, status, loginame=SUBSTRING(loginame,1,12), hostname=substring(hostname, 1, 12), blk=CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0 ELSE SELECT 'No blocking processes found!' 

Once you identify the connection (spid) causing the problem, check to see the specific locks that it is holding. You can query from syslockinfo for this, but simply running sp_lock spid will probably give you exactly what you need. There might be multiple separate lock chains, in which case the batch will return more than one spid. You can follow the same procedure for each one.

The syslockinfo Table

If a lot of blocking is going on, you might want to take a look at all locks held that are blocking other users. You can use the procedure sp_blockinglocks (which is not a part of the installed SQL Server product but is included on the companion CD) to print out a list of all locks held that are blocking other processes. The procedure examines the syslockinfo table for resources that have more than one lock listed and are held by different processes with different status values. For example, if process 52 has an X lock on the sales table with a status of GRANT and process 53 is trying to read from that table, the output of sp_blockinglocks looks like this:

 spid db ObjId IndId Type Resource Mode Status ------ ----- ----------- ------ ---- ---------------- -------- ------ 52 pubs 149575571 0 TAB X GRANT 53 pubs 149575571 0 TAB IS WAIT 

It can also be useful to see the last command issued by a blocking process; you can do this by double-clicking on a process ID in the Current Activity panes of SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER (spid) for this—which is the same thing SQL Server Enterprise Manager does when you double-click on a process. DBCC INPUTBUFFER reads the memory from within the server that was used for the last command. So DBCC INPUTBUFFER can access whatever command is still in the buffer for a given connection, even if it has already been executed.

Usually, by this point you have enough of a handle on the problem to turn your attention to the application, which is where the resolution will ultimately lie. But if you need or want to go a bit further, you can find out the depth at which the connection is nested within a transaction. Blocking problems often happen because the connection doesn't realize the nested depth and hasn't applied the correct pairing of COMMIT commands. (Chapter 12 discusses the scoping and nesting levels of transactions.) A connection can determine its own nesting depth by querying @@TRANCOUNT. Starting with SQL Server 7, you can also determine the nesting level of any current connection, not just your own. This involves inspecting the sysprocesses table directly because the information is not included in either sp_who or sp_who2.

The sysprocesses Table

As I've mentioned, some columns in the sysprocesses table don't show up in the output of either sp_who or sp_who2. Table 16-2 describes these columns.

Table 16-2. Columns in the sysprocesses table that aren't available via sp_who or sp_who2.

Column Name	Data Type	Description
waittype	binary(2)	The waittypes of 1 through 15 correspond to the mode of lock being waited on. Hex values 0x40 through 0x4f correspond to miscellaneous waits. 0x81 is a wait to write to the log. 0x400 through 0x40f are latch waits. 0x800 is a wait on a network write.
waittime	int	Indicates the current wait time in milliseconds. The value is 0 when the process is not waiting.
lastwaittype	nchar(32)	A string indicating the name of the last or current wait type.
waitresource	nchar(32)	A textual representation of a lock resource.
memusage	int	Indicates the number of pages in the memory cache that are currently allocated to this process. A negative number means that the process is freeing memory allocated by another process.
login_time	datetime	Indicates the time when a client process logged on to the server. For system processes, it indicates the time when SQL Server startup occurred.
ecid	smallint	An execution context ID that uniquely identifies the subthreads operating on behalf of a single process.
kpid	smallint	The operating system thread ID.
open_tran	smallint	Indicates the current transaction nesting depth for the process.
uid	smallint	The user ID for the user who executed the command.
sid	binary(85)	A globally unique identifier (GUID) for the login.
nt_domain	nchar(128)	The operating system domain for the client.
nt_username	nchar(128)	The operating system username for the process.
net_address	nchar(12)	An assigned unique identifier for the network interface card on each user's workstation.
net_library	nchar(12)	The name of the client's network library DLL.

The procedure sp_who2 translates the dbid into a database name and translates the numeric value for its status into a string. The most common status values you'll see are BACKGROUND, ROLLBACK, DORMANT (used when a connection is being retained while waiting for Remote Procedure Call [RPC] processing), SLEEPING, and RUNNABLE.

The values that the sysprocesses table holds in the waittype column are the same ones you'll see in the req_mode column of syslockinfo. They can be translated as follows:

 Lock Mode value ----------- ------- NULL 1 Sch-S 2 Sch-M 3 S 4 U 5 X 6 IS 7 IU 8 IX 9 SIU 10 SIX 11 UIX 12 BU 13 RangeS-S 14 RangeS-U 15 RangeIn-Nul 16 RangeIn-S 17 RangeIn-U 18 RangeIn-X 19 RangeX-S 20 RangeX-U 21 RangeX-X 22 

NOTE

---

These values are not the same as the values shown in SQL Server Books Online under the description for the syslockinfo table. The values in SQL Server Books Online are incorrect.

The Trace Flag for Analyzing Deadlock Activity

Trace flags are useful for analyzing deadlock situations. When a process is part of a deadlock, the victim process realizes that the deadlock occurred when it gets error message 1205. Any other process participating in the deadlock is unaware of the situation. To resolve the deadlock, you probably want to see both processes; trace flag 1204 provides this information.

The following is a fragment of the output from sqlservr.exe, which was started from the command line using –T1204. This example uses the conversion deadlock script that we used previously—issuing the same batch from two connections—to illustrate the output of trace flag 1204. To capture deadlock information, you must start SQL Server from a command prompt; the output of the trace flag will be displayed in the command window. If you're running a named instance of SQL Server 2000, you must be in the binn directory for that instance (such as \mssql$MyInstanceName\binn), and you must also supply the instance name with the -s parameter.

 sqlservr _c _T1204 -s MyInstance Deadlock encountered .... Printing deadlock information Wait-for graph Node:1 KEY: 5:1977058079:1 (02014f0bec4e) CleanCnt:2 Mode: S Flags: 0x0 Grant List:: Owner:0x192713e0 Mode: S Flg:0x0 Ref:1 Life:02000000 SPID:52 ECID:0 SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 7 Input Buf: Language Event: USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors WHERE au_id='172-32-1176' -- Add 5 sec sleep to widen the window for the deadlock WAITFOR DELAY '00:00:05' UPDATE authors SET au_lname='Updated by ' + CO Requested By: ResType:LockOwner Stype:'OR' Mode: X SPID:51 ECID:0 Ec:(0x192794e8) Value:0x19271400 Cost:(0/0) Node:2 KEY: 5:1977058079:1 (02014f0bec4e) CleanCnt:2 Mode: S Flags: 0x0 Grant List:: Owner:0x192712e0 Mode: S Flg:0x0 Ref:1 Life:02000000 SPID:51 ECID:0 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 7 Input Buf: Language Event: USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors WHERE au_id='172-32-1176' -- Add 5 sec sleep to widen the window for the deadlock WAITFOR DELAY '00:00:05' UPDATE authors SET au_lname='Updated by ' + CO Requested By: ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x192c34e8) Value:0x1926d400 Cost:(0/0) Victim Resource Owner: ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x192c34e8) Value:0x1926d400 Cost:(0/0) 

This output shows the spid for both processes affected, shows a fragment of their input buffer (but not the entire command), and notes that neither process can upgrade its locks because of the circular relationship. Process 52 requests an update lock on a key that process 51 already has an update lock on. At the same time, process 51 requests an exclusive lock on a key that process 52 already has a shared lock on. Thus, the two processes are in a circular relationship. The output can help you solve the problem—you know the processes involved and the locks that could not be acquired, and you have an idea of the commands being executed.

NOTE

---

The number assigned to trace flag 1204 is intended to be easy to remember. Recall that error 1205 is the well-known error message that an application receives when it is chosen as the deadlock victim.

Lock Hints

You can specify a lock type or duration with syntax similar to that for specifying index hints. Chapter 14 explains lock compatibility and other issues that are essential to using lock hints properly. Lock hints work only in the context of a transaction, so if you use these hints, you must use BEGIN TRAN/COMMIT (or ROLLBACK) TRAN blocks (or you must run with implicit transactions set to ON). The lock hint syntax is as follows:

 SELECT select_list FROM table_name [(lock_type)] 

TIP

---

Remember to put the lock hint in parentheses; if you don't, it will be treated as an alias name for the table instead of as a lock hint.

You can specify one of the following keywords for lock_type:

HOLDLOCK

This is equivalent to the SERIALIZABLE hint described below. This option 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 takes update page locks instead of shared page 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 takes a shared lock on the table even if page locks would be taken otherwise. This option is useful when you know you'll escalate to a table lock or if you need to get a complete snapshot of a table. You can use this option with holdlock if you want the table lock held until the end of the transaction block (repeatable read).

PAGLOCK

This keyword takes 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 takes 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 specifies that a shared row lock be taken when a single shared page or table lock is normally taken.

READUNCOMMITTED | READCOMMITTED | 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 of all tables in all statements in a transaction.

NOLOCK

This allows uncommitted, or dirty, reads. Shared locks are not issued by the scan, and the exclusive locks of others are not honored. This hint is equivalent to READUNCOMMITTED.

READPAST

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

XLOCK

This takes an exclusive lock that will be 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 appropriate level of granularity.

Setting a Lock Timeout

Although it is not specifically a query hint, SET 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 that a query is automatically canceled by the client 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 5 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 0 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 of these techniques let you "get around" locking 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 titles table:

    USE pubs BEGIN TRANSACTION UPDATE titles SET price = price * 10 WHERE title_id = 'BU1032' 

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

    USE pubs SET LOCK_TIMEOUT 0 SELECT * FROM titles SELECT * FROM authors 

   Notice that after error 1222 is received, the second select statement is executed, returning all the rows from the authors table.

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

    USE pubs SELECT * FROM titles (READPAST) SELECT * FROM authors 

   SQL Server skips (reads past) only one row, and the remaining 17 rows of titles are returned, followed by all the authors rows.

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

    USE pubs SELECT * FROM titles (READUNCOMMITTED) SELECT * FROM authors 

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

NOTE

---

The NOLOCK, READUNCOMMITTED, and READPAST table hints are not allowed for tables that are targets of delete, insert, or update operations.

Combining Hints

You can combine index and lock hints and use different hints for different tables, and you can combine HOLDLOCK with the level of shared locks. Here's an example:

 BEGIN TRAN SELECT title, au_lname FROM titles (TABLOCK) JOIN titleauthor ON titles.title_id=titleauthor.title_id JOIN authors (PAGLOCK, HOLDLOCK, INDEX(1)) ON authors.au_id=titleauthor.au_id