Microsoft KB Archive/179362

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.