A
Basically you are looking for exclusive access to the next row in a queue table. Assuming this kind of table:
CREATE TABLE dbo.GUIDQueue
(
RowNumber bigint IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TheGuid uniqueidentifier NOT NULL,
Claimed bit NOT NULL DEFAULT 0
);
-- insert some n number of rows
DECLARE @n int = 1000;
;WITH src(guid) AS
(
SELECT TOP (@n) NEWID()
FROM sys.all_objects AS o
CROSS JOIN sys.all_objects AS s
)
INSERT dbo.GUIDQueue(TheGuid) SELECT guid FROM src;
This procedure will serialize deletes without blocking another session from taking the next value:
ALTER PROCEDURE dbo.PluckFromQueue
@NextGUID uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @g TABLE(NextGUID uniqueidentifier);
;WITH NextGUID AS
(
SELECT TOP (1) TheGuid
FROM dbo.GUIDQueue WITH (ROWLOCK, READPAST, UPDLOCK)
ORDER BY RowNumber
)
DELETE NextGUID OUTPUT deleted.TheGUID INTO @g;
SELECT @NextGUID = MAX(NextGUID) FROM @g;
END
GO
Or if you want to update instead:
ALTER PROCEDURE dbo.PluckFromQueue
@NextGUID uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @g TABLE(NextGUID uniqueidentifier);
;WITH NextGUID AS
(
SELECT TOP (1) RowNumber, TheGuid, Claimed
FROM dbo.GUIDQueue WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE Claimed = 0
ORDER BY RowNumber
)
UPDATE NextGUID SET Claimed = 1
OUTPUT deleted.TheGUID INTO @g;
SELECT @NextGUID = MAX(NextGUID) FROM @g;
END
GO
You would grab the next item off the queue like this:
DECLARE @guid uniqueidentifier;
EXEC dbo.PluckFromQueue @NextGUID = @guid OUTPUT;
SELECT @guid;
And then use @guid for whatever subsequent processing you need to do.
To test that this is serialized, put this in two windows:
BEGIN TRANSACTION;
DECLARE @guid uniqueidentifier;
EXEC dbo.PluckFromQueue @NextGUID = @guid OUTPUT;
SELECT @guid;
-- COMMIT TRANSACTION;
Run one, then the other, both will get a value. Don't forget to commit both.
Remus and gbn talk about why you want those three locking locking hints to the dequeue query.
Using tables as Queues
FIFO queue table for multiple workers
SQL Server Process Queue Race Condition
If you want to observe what happens without those hints (and maybe this is the behavior you want), comment them out, alter the procedure, and repeat the experiment above - you will see that the first blocks the second until the first commits. No matter how close in timing you think you can get, one will win and the other will wait. But don't exacerbate that by wrapping the procedure call in a transaction in either case. The only reason you would want that is if you wanted to "undo" the dequeue operation. If you end up not using the GUID off the queue due to some condition or exception, oh well, consider it a loss (or you could always insert that value back into the queue).
As above, don't forget to commit the second transaction, too.
I wrote about something similar here (the beauty of generating a whole bunch of unique numbers you don't have to confirm are unique later), but in that article I didn't address what to do in case you want to roll back a claim:
Generating Random Numbers in SQL Server Without Collisions
You might consider adding some kind of background process to monitor the size of the queue (or how many unclaimed rows are left, in which case a filtered index might be handy), so that you can automatically replenish with new rows once it gets to a certain point (instead of running out mid-transaction). Make sure your background process counts rows in a smart way.
