I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application:
- Get unprocessed messages (using the
IsProcessing
flag) - Set their
IsProcessing
to true (in RAM) and update theirIsProcessing
status - Do the business
- Set their
IsProcessing
to false (in RAM) and update theirIsProcessing
status
This works fine when runs sequentially. But when I run multiple instances of my application (concurrency), I see that some messages are processed twice or thrice. Here's what happens:
- Instance A gets some unprocessed messages
- While instance A is setting the
IsProcessing
to true in RAM, instance B gets some messages, and chances are that it fetches one or more of the messages which are already fetched by instance A
And this is what I've done in hope of preventing it:
- Begin transaction (serializable)
- Get unprocessed messages (using the
IsProcessing
flag) - Set their
IsProcessing
to true (in RAM) and update theirIsProcessing
status - Commit transaction
- Do the business
- Set their
IsProcessing
to false (in RAM) - Begin transaction (serializable)
- Update their
IsProcessing
status - Commit transaction
I don't know why, but during steps 1 to 4, other instances can still perform read queries. This is not desired. I want to exclusively prevent anything, even read queries from being executed on messages table during step 1 to 4.
How can I do that? What am I missing in my design? The goal is to make sure that while a message is queued for processing, no other instance would process it again.
Best Answer
You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a
TOP()
cluse will be able to set the flag on at most one row. By using theOUTPUT
cluse you can return this to the application automically.I define a simple test table and populate it:
The output clause needs a table variable to receive the changed values:
A bit of debug code to make the "before" and "after" states obvious:
And the statement itself:
And the results:
This is the output from the second execution of the above.
The row with
id=2
had gone fromIsProcessing=0
toIsProcessing=1
and thatid
is returned in the table variable.With this trivial data the rows are processed in the sequence they were created. In a more complex environment the optimizer may choose any row that matches the where clause. If you have a requirement to process rows in, say, time sequence further qualification will be required.
I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.
It is of course possible that there are no rows with
IsProcessing=0
at the time the statement runs. In this case the table variable will have zero rows.To fully isolate each transaction from the other you could try
sp_getapplock
. This will add overhead and reduce concurrency. You have to be careful to release the app lock as soon as you can in both success and failure scenarios.