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.
with (updlock)
to prevent other threads running this code to fetch the same rows at the same time. – Mikael Eriksson Jun 7 '15 at 9:44set transaction isolation level serializable; begin transaction; select * from Messages;
. Both with return record set. – Saeed Neamati Jun 7 '15 at 11:00