PRB: Deadlock May Be Encountered When Using UPDLOCK Hint

ID: Q179362

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

You may run into a deadlock when using the UPDLOCK hint. Consider the following example:

User A starts the following transaction:


   BEGIN TRAN
   SELECT COL1
         FROM TAB1(UPDLOCK)
         WHERE COL1 = 1

Then user B starts another transaction with the same commands (this transaction will be blocked by user A):


   BEGIN TRAN
   SELECT COL1
         FROM TAB1(UPDLOCK)
         WHERE COL1 = 1

Now if user A issues the following UPDATE within the same transaction


   UPDATE TAB1
      SET   COL1 = 0
      WHERE COL1 = 1

The following error will occur if there is no index for TAB1:

Msg 1205, Level 13, State 2
Your server command (process id 11) was deadlocked with another process
and has been chosen as deadlock victim. Re-run your command.

CAUSE

The same update lock is promoted to a table lock when there is no index for a table. Or, when the UPDATE statement is unrestricted, a previous Update_page lock is promoted to a lock type Ex_table. If another transaction already placed an Ex_intent lock on the same table, the lock escalation cannot succeed and the deadlock is detected.

WORKAROUND

To work around this problem, do either of the following:

Create an index on TAB1.

-or-

Add a WHERE clause to the UPDATE statement if you already have an index on the table.

MORE INFORMATION

For more information, see the following article in the Microsoft Knowledge Base:

Q169960 : INF: Analyzing and Avoiding Deadlocks in SQL Server


Keywords          : SSrvLock 
Version           : WINNT:6.0 6.5
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: April 16, 1999