SQL Server – Use Table as GUID Pool Without Race Conditions

blockingsql server

I am in a position where I will be issued with a list of GUIDs to be assigned by a SQL Server database at runtime.

My immediate idea is to create a table for the GUIDs. When a batch of GUIDs is ordered, they will be inserted into the table.

Then, at runtime, a SPROC will be called to pick a GUID from the table. I can either retrieve the next GUID and immediately DELETE it from the table, or retreive the next GUID and UPDATE another column to claim it as used.

But this causes me to wonder about the potential for race conditions where two calls are simultaneously made to the database for a GUID. It seems to me that the same GUID could be retrieved for the two calls if the timing were perfectly inconvenient.

Q1: Am I correct to be concerned about this potential race condition? i.e. can it happen?

Q2: Is there a way to avoid such a condition (e.g. set a SPROC to block on each call?)

One way I can think of which would at least make the race condition fail safe is to set up another table where GUID assignments are made, and set the GUID column to unique. Then, in the runtime process, when a GUID is retrieved it will be assigned to the product via the GUID assignment table. If two products were issued the same GUID, then when it came time to assign them one of the products would fail to assign and be rejected.

Best Answer

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.

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:

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.

Related Question