Sql-server – Handle concurrency on Sql Server to modify specific column

concurrencylock-escalationsql server

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.

Best Answer

We had a similar requirement and so added an extra where clause to the query. E.g.

UPDATE AppointmentTable 
SET STatus = [Approved/Rejected] 
WHERE AppointmentID = @ID AND Status = Pending

Then afterwards, check how many rows were updated. If there were 1 rows updated then report back to the user that their action was recorded. If 0 rows were updated then report back to the user that someone else has already done the work.

Isn't there a chance the queries will be executed "at the same time"

Nope. If they execute "at the same time" one of them will still get the update lock first. The other will have to wait for the update lock. When the 2nd gets the lock it still has to check the where clause to validate it's still true. If it's no longer true then it skips that row and no update happens (@@rowcount = 0). The key is to have the old value in the where clause.

Related Question