PRB: Deadlock May Be Encountered When Using UPDLOCK Hint

• Microsoft SQL Server, versions 6.0 and 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

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

   UPDATE TAB1
      SET   COL1 = 0
      WHERE COL1 = 1

   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:

   ARTICLE-ID: Q169960
   TITLE     : INF: Analyzing and Avoiding Deadlocks in SQL Server