0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zaidasp
  • 23
  • 2
  • 1
    Have you considered setting a constraint to stop the value being greater than 10? Considering that while the `UPDATE` is running there will be a lock, the second `UPDATE` will have to wait for the other to complete. Then, the second `UPDATE` will fail due to the constraint. – Thom A Dec 11 '18 at 19:40
  • There are two basic approaches. 1) As, Larnu pointed out, you can create a database constraint, but that is not a very dynamic option and there can still be concurrency issues. 2) You can lock the table until the transaction is complete; this would likely be the way I would go if it was needed. Check this out: https://stackoverflow.com/questions/25273157/t-sql-lock-a-table-manually-for-some-minutes – UnhandledExcepSean Dec 11 '18 at 19:43
  • Do you mean that when the first Update is executing to check if the value has reached 10 and if so to add a lock to that department so future updates can not perform their update? – zaidasp Dec 11 '18 at 19:45
  • Don't set `=10`, use `+=1`. – Ivan Starostin Dec 11 '18 at 19:47
  • @DaleBurrell I did but this is not a simply concurrency that can be solved by optimistic or pessimistic concurrency since Im checking against another column and software is storing the value dynamically. Anyways thanks – zaidasp Dec 11 '18 at 19:48
  • Thank you everyone for the suggestions. Locking the table would be problematic since I have 500 people signing up at the same time and this would disconnect many of them – zaidasp Dec 11 '18 at 19:49
  • @DaleBurrell True but the problem arises when those two tickets are the last two that are available. Am I right or Am I missing something? – zaidasp Dec 11 '18 at 19:51
  • Thank you @DaleBurrell I really appreciate it :) – zaidasp Dec 11 '18 at 19:59

1 Answers1

1

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).

Dale K
  • 25,246
  • 15
  • 42
  • 71