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.set transaction isolation level serializable; begin transaction; select * from Messages;
. Both with return record set.