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
  • 704,970
  • 168
  • 1,303
  • 1,425
Win
  • 58,817
  • 13
  • 98
  • 174

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
  • 408,740
  • 77
  • 567
  • 659
  • 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