Concurrency Issues

Is there any way to get the sample below working so that both "threads" are guaranteed to get unique and incrementing values?

I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.

IF OBJECT_ID('SimpleTable') IS NOT NULL
DROP TABLE SimpleTable

CREATE TABLE SimpleTable (
A INTEGER
)
INSERT INTO SimpleTable (A) VALUES (1)

-- Run in one window
DECLARE @.value INTEGER

BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
WAITFOR DELAY '00:00:05'
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION

SELECT @.value
SELECT A FROM SimpleTable

-- Run in a second window
DECLARE @.value INTEGER

BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION

SELECT @.value
SELECT A FROM SimpleTableUse an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

-PatP|||Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

-PatP

Actually, I did just that. However, I was kind of curious if it was possible to acheive with transactions or with some explicit locking calls.|||There definitely is a way to do it using just SQL statements with Transact-SQL's locking model. The identity process is simpler and supported though.

-PatP|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.

Thank you. That will work. Except BOL calls those locking "hints" which means the solution isn't guaranteed to work even though it probably will on today's implementations.|||I don't see how it will not work, but you're free to refute it.|||It won't work if a future implementation (either a future version or service pack of SQL Server) decides to ignore the locking "hint". The database engine is completely free to obey or ignore "hints" at will so you shouldn't base an algorithm on that.

I don't see how it will not work, but you're free to refute it.