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 SimpleTable