2

I am wondering how to rewrite the following SQL Server 2005/2008 script for SQL Server 2000 which didn't have OUTPUT yet.

Basically, I would like to update rows and return the updated rows without creating deadlocks.

Thanks in advance!

UPDATE TABLE 
SET Locked = 1
OUTPUT INSERTED.*
WHERE Locked = 0
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Win
  • 61,100
  • 13
  • 102
  • 181

1 Answers1

4

You can't in SQL Server 2000 cleanly

What you can do is use a transaction and some lock hints to prevent a race condition. Your main problem is 2 processes accessing the same row(s), not a deadlock. See SQL Server Process Queue Race Condition for more, please.

BEGIN TRANSACTION

SELECT * FROM TABLE WITH (ROWLOCK, READPAST, UPDLOCK) WHERE Locked = 0

UPDATE TABLE
SET Locked = 1
WHERE Locked = 0

COMMIT TRANSACTION

I haven't tried this, but you could also try a SELECT in an UPDATE trigger from INSERTED.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • It works. Thank you. I also added the link from the original thread for reference. http://www.mssqltips.com/tip.asp?tip=1257 – Win Jul 12 '11 at 22:54