Aug 13

Written by: Javier Callico
8/13/2007 

Imagine the following scenario: The table defined below is populated with "key values" available to a given application. These values are generated by other application and inserted in batches from time to time into this "key pool" table.

/* Create table */
CREATE TABLE [KeyPool](
[KeyPoolId] [int] IDENTITY(1,1) NOT NULL,
[Key] [varchar](30) NOT NULL,
[IsUsed] [bit] NOT NULL
CONSTRAINT [PK_KeyPool] PRIMARY KEY CLUSTERED
([KeyPoolId] ASC))

/* Insert test values */
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00001',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00002',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00002',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00003',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00004',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00005',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00006',0)
INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00007',0)

How you select a unique key from this table and also marking it as "used" so is not returned again. Given the current conditions, around 5 different processes may be requesting a value at the same time.

One possible solution is:

DECLARE @Key VARCHAR(30)

BEGIN TRANSACTION

SELECT TOP 1 @Key=[Key]
FROM [KeyPool] WITH (UPDLOCK, HOLDLOCK) /* This lock will persist until the transaction is commited */
WHERE IsUsed = 0

UPDATE [KeyPool]
SET IsUsed = 1
WHERE [Key]=@Key

COMMIT

SELECT @Key

Note that this solution is designed to work with a "key pool" table which contains 1 million rows and around 5000 new values are added every week.

This approach works but I'm not sure if there is better way to solve this problem... any ideas?