CODEWITHSUNDEEP
sqlsql-servertransactionsisolation-level
alexey
alexey

Reputation: 8480

SQL Server transaction isolation and atomicity

I have the following workflow in my application:

Thread 1 executes Transaction 1:

BEGIN TRANSACTION
    UPDATE Items SET IsReady = 1 WHERE Id = 100
    INSERT INTO ProcessorQueue (ItemId) VALUES (100)
COMMIT TRANSACTION

Thread 2 polls ProcessorQueue table to get new rows to process. So it creates Transaction 2 where it selects a queue row and if it's found selects corresponding item status (IsReady) from Items table.

Sometimes it reads IsReady = 0. Why? How to fix it?

Both transactions level is read committed.

Upvotes: 2

Views: 112

Answers (1)

dean
dean

Reputation: 10098

I'm pretty much sure that this couldn't happen as described, and that this would be possible only if thread 2 is running under read uncommitted isolation level.

Having said that, these two statements can be written as one, using the output clause:

update Items
set IsReady = 1
output inserted.Id into ProcessorQueue
where Id = 100

Upvotes: 1

Related Questions