|
|
Posted by Erland Sommarskog on 02/15/06 01:19
Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> 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
Yet a variation is:
SET ROWCOUNT 1
UPDATE PinsTabel
SET @ID = ID,
@Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0
It is essential to have a (clustered) index on Acquired_By.
Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.
Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|