CODEWITHSUNDEEP
sqlsql-servert-sqlconcurrency
zaidasp
zaidasp

Reputation: 23

Users updating same row at the same time SQL Server

I want to create a SQL Server table that has a Department and a Maximum Capacity columns (assume 10 for this scenario). When users add them selves to a department the system will check the current assignment count (assume 9 for this scenario) in the department and compare it to the maximum value. If it is below the maximum, they will be added.

The issue is this: what if two users submit at the same time and the when the code retrieves the current assignment count it will be 9 for both. One user updates the row sooner so now its 10 but the other user has already retrieved the previous value before the update (9) and so both are valid when compared and we end up with 11 users in the department.

Is this even possible and how can one solve it?

Upvotes: 0

Views: 1235

Answers (1)

Dale K
Dale K

Reputation: 27449

The answer to your problem lies in understanding "Database Concurrency" and then choosing the correct solution to your specific scenario.

It too large a topic to cover in a single SO answer so I would recommend doing some reading and coming back with specific questions.

However in simple form you either block the assignments out to the first person who tries to obtain them (pessimistic locking), or you throw an error after someone tries to assign over the limit (optimistic locking).

In the pessimistic case you then need ways to unblock them if the user fails to complete the transaction e.g. a timeout. A bit like on a ticket booking website it says "These tickets are being held for you for the next 10 minutes, you must complete your booking within that time else you may lose them".

And when you're down to the last few positions you are going to be turning everyone after the first away... no other way around it if you require this level of locking. (Well you could then create a waiting list, but that's another issue in itself).

Upvotes: 1

Related Questions