Knowledge Base

PRB: Deadlock May Be Encountered When Using UPDLOCK Hint

Article ID: 179362

Article Last Modified on 10/16/2003

APPLIES TO

Microsoft SQL Server 6.0 Standard Edition
Microsoft SQL Server 6.5 Standard Edition

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

Keywords: kbprb KB179362

Microsoft KB Archive/179362

Contents