PRB: Deadlock May Be Encountered When Using UPDLOCK Hint (179362)


The information in this article applies to:

  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
This article was previously published under Q179362

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:

169960 : INF: Analyzing and Avoiding Deadlocks in SQL Server

Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbprb KB179362
©2003 Microsoft Corporation. All rights reserved.