PRB: Deadlock May Be Encountered When Using UPDLOCK Hint

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: