I have table that suffers from racing condition and I don't know how to fix it. I can't solve it 100% in code, so I need the server to help me.
The scenario
Admins of a system may accept or reject an appointment. This can be done by email (via token etc) or by logging in the system and doing it via admin panel.
The problem appears when two admin do it at the same time:
t = 0:
admin A clicks accept from the system email;
admin B clicks reject from the admin panel;
t = 1:
back-end checks was appointment already handled?
This happens at the same time for both admins, so they both
pass the test -> appointment is waiting for approval...
t = 2:
back-end A sends query "update status to accepted"
back-end B sends query "update status to rejected"
t = 3: both sqls hit the server.
The server than changes the appointment to accepted, then proceeds to execute the second query, and changes the appointment to rejected.
This table has heavy traffic, so locking it is not an option.
What I think could solve my problem, is to:
first: have a query lock the row;
second: check the the status column to see if it is already set;
third: if not set, then set the value;
fourth: release the lock;
meanwhile, the second query would keep trying to acquire the lock. After it succeeded, it would then proceed to check whether the value was already set, and then it would find that indeed it was, and would return an error or something.
How can I go about doing something like this on Sql Server?
The flow would look something like this:
while (can't read) {
try to acquire lock;
}
// lock acquired.
read appointment from table;
if value is null, set the value;
release lock;
I figured out my framework is using READ_COMMITTED_SNAPSHOT
as default.