Hi, i'm implemeting kind of a queue in a table in SQL Server 2000.

I read the oldest record in a table which Table.Locked = 0 and update it to
Table.Locked = 1.

The Store procedure is like:
Select @id = id from Table where Locked = 0 order by date desc
Update Table SET Locked = 1 where id = @id

Everything worked OK until I run a several instances of the same process.
Sometimes two processes take the same ID.

Should I use transaction? Wich Isolation level? or should I lock the update
statment in order to avoid that more than one process take the same id?

Thanks in advance

Laura (Laura@discussions.microsoft.com) writes:
[quoted text, click to view]

First of all, you should investigate your possibilities to move to
SQL 2005, as SQL 2005 includes Service Broker which is a complete
framework for handling queues.

In lieu of SQL 2005, this code will work for SQL 2000:

BEGIN TRANSACTION
Select @id = id from Table WITH (UPDLOCK)
where Locked = 0 order by date desc

Update Table SET Locked = 1 where id = @id

COMMIT TRANSACTION

The UPDLOCK hint is a shared lock, so everyone else can still read
the row. However, no other process can update the row, nor obtain a
UPDLOCK on it. Thus, parallel processes will be serialized at this
point.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

To add to Erland's response, I think you need to add a TOP 1 clause so that
only one row is processed:

Select top 1 @id = id from Table WITH (UPDLOCK)
where Locked = 0 order by date desc

Also, it's a good idea to include SET XACT_ABORT ON in procs with explicit
transactions.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]