Posted by Hugo Kornelis on 02/14/06 23:20

On 13 Feb 2006 20:00:07 -0800, Bobus wrote:

>Hi,
>
>I have a table which contains a bunch of prepaid PINs. What is the
>best way to fetch a unique pin from the table in a high-traffic
>environment with lots of concurrent requests?
>
>For example, my PINs table might look like this and contain thousands
>of records:
>
> ID PIN ACQUIRED_BY
>DATE_ACQUIRED
> ....
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ....
>
>10 users request a pin at the same time. What is the easiest/best way
>to ensure that the 10 users will get 10 different unacquired pins?
>
>Thanks for any help...

Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION


--
Hugo Kornelis, SQL Server MVP

[Back to original message]