How to select unique key values from a "key pool"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to select unique key values from a "key pool"

Author

Topic

jcallico
Starting Member

4 Posts

Posted - 2007-08-15 : 15:09:14

Imagine the following scenario: The table defined below is populated with the correct key values available to our 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)

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

Our current implementation is causing deadlocks. They were rare in the past but now they are happening more frequently as the size of the "key pool" table increases.

DECLARE @Key VARCHAR(30)

BEGIN TRANSACTION

SELECT TOP 1 @Key=[Key]
FROM [KeyPool] WITH (UPDLOCK, HOLDLOCK)
WHERE IsUsed = 0

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

COMMIT

SELECT @Key

Please note that this table currently contains 1 million rows and around 5000 new values are added every week.

Is there a better way to approach this problem?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 15:31:16

Looks like you are making something relatively simple quite complicated.

1) Just use a globally unique identifier

or

2) just use an identity column as the key value and use a standard INSERT / retrieve scope_identity() which will not result in any deadlocks

or

3) you can also use composite primary keys, or concatenate two values together to form the primary key. i.e., let each process that needs an unique key generate their own identity, and then just append a unique "process code" to each identity so they are all guaranteed to be unique across all processes.

it depends on your specific needs. If you explain your business process better, we can help with a good solution.

- Jeff
http://weblogs.sqlteam.com/JeffS

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-15 : 15:33:32

In a table with a million rows (and growing), the problem is likely your choice of fields on your update, you're not using the clustered index. Take a look at the execution plan of what you have to verify.

I'd try something like this...

Declare @KeyPoolID int, @Key varchar(30)

Begin Tran

Select Top 1 @KeyPoolID = KeyPoolID
From KeyPool With (UpdLock, HoldLock)
Where IsUsed = 0

Update KeyPool
Set IsUsed = 1
Where [KeyPoolID] = @KeyPoolID

Commit

Select [Key]
From KeyPool
Where KeyPoolID = @KeyPoolID

jcallico
Starting Member

4 Posts

Posted - 2007-08-15 : 16:00:51

Jeff

As explained before these key values are calculated by another application and must comply with certain bussiness rules like MD5 for example.

jcallico
Starting Member

4 Posts

Posted - 2007-08-15 : 16:20:22

Joe

I just tried your solution and it doesn't seem to have a big impact in the overall speed.

I forgot to mention that we also have an index on the [Key] column.

jcallico
Starting Member

4 Posts

Posted - 2007-08-15 : 16:26:14

Sorry, I meant MOD10 and not MD5 in my last message to Jeff.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 16:49:02

This statement:

Select Top 1 @KeyPoolID = KeyPoolID
From KeyPool With (UpdLock, HoldLock)
Where IsUsed = 0

is not using any indexes, so it will perform poorly. You can try adding an index to the IsUsed column, but typically they say don't bother with indexes on BIT columns .... I think in this case, though, it may help, since you only want one single row where IsUsed =0.

You can also try changing the TOP 1 to SELECT min(KeyPoolID) before and after indexing the bit column to see if that helps any.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources