4

Say I have the following table:

ID|Read
-------
 1|true
 2|false
 3|false
 4|false

... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it.

So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true.

CREATE PROCEDURE [dbo].[getMinID]
(
  @QueryID INT OUTPUT 
)
BEGIN
  SELECT TOP 1 @QueryID = [ID] from Table
  UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID 
END

The problem is that I have ten (10) asynchronous Threads executing dbo.getMinID, at the same time, and I CANNOT have them select the SAME [ID] under any circumstances. I am worried that a second thread my execute between my SELECT and UPDATE statement, thus returning [ID]: 2 in both scenarios.

How can I ensure that I do not select/update the same record twice, no matter how many threads are acting upon the Stored Procedure? ALSO, please keep in mind that the table CONSTANTLY has new rows added, so I cannot lock the table!

gbn
  • 422,506
  • 82
  • 585
  • 676
Theofanis Pantelides
  • 4,724
  • 7
  • 29
  • 49

4 Answers4

3

If you mean a concurrency safe queue type locking, then use ROWLOCK, UPDLOCK, READPAST hints?

SQL Server Process Queue Race Condition

BEGIN TRAN

SELECT TOP 1 @QueryID = [ID] from Table WITH (ROWLOCK, UPDLOCK, READPAST)
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID 

COMMIT TRAN -- TRAM

However, in one statement. something like

WITH T AS
(
    --ORDER BY with TOP , or perhaps MIN is better?
    SELECT TOP 1 [Read], [ID] from Table
    WITH (ROWLOCK, UPDLOCK, READPAST) ORDER BY [Read]
)
UPDATE
    T
SET
    [Read] = 1;
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

If you want it to be atomic, you must lock something, but that doesn't mean you have to lock it for long. I would first try with some tightly scoped transactions, but I'd also be interested to try the update variant that does a SELECT at the same time :

UPDATE TOP (1) [foo]
SET [read] = 1
OUTPUT INSERTED.id
WHERE [read] = 0

You could see if that has any concurrency issues - in honesty, I don't know without checking! You may need to add something like WITH (ROWLOCK). Personally, though, I'd want to keep it simple and try a serializable transaction.

Also note that this doesn't guarantee which record you will get (first? last?)

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
1

Make your transaction isolation level SERIALIZABLE and place an exclusive lock with your SELECT command:

SELECT TOP 1 @QueryID = [ID] from Table WITH (XLOCK) ORDER BY id DESC
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID

This will place an XLOCK on the top key range and will prevent concurrent queries from reading the top record.

This way, no transactions will ever get the same record.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

put the select and update and the select statement in a transaction and in the start of the transaction lock the table so the outher threads will wait. Best Regards, Iordan

IordanTanev
  • 6,130
  • 5
  • 40
  • 49
  • i don't want to lock the table because INSERTs are being done constantly. plus tomorrow there may be 1000 Threads running on the same table, which means i need a less-intrusive, yet explicit way of doing this. – Theofanis Pantelides Jan 21 '10 at 12:45
  • 1
    @Theofanis - it doesn't matter *how* fast your db access is if you make a mess of the data; first try it in the simplest way (with a transaction), *then* challenge the performance. – Marc Gravell Jan 21 '10 at 12:50
  • 1
    If this is the case and the number of threads can go up to 1000 the try adding a new null column int the table. the first thing the tread will do is write its id in the new column so no other thread will be able to take this row – IordanTanev Jan 21 '10 at 12:52