[Short]
I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.
How can I solve this kind of concurrency in Azure SQL Database V12?
[Detailed]
The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:
SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart
Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.
How can I solve this kind of concurrency in Azure SQL Database V12?
Best Answer
The problem is that there could be values present at the end of A's transaction - the ones inserted by B - that were not present when A first checked. This is known as a phantom
The way to prevent phantoms is to use the Serializable isolation level. This is the only isolation level which prevents phantoms.
There are other ways to serialize a workload without using that isolation level. One is to obtain an exclusive lock on the whole table using TABLOCKX. When used within an explicit transaction (BEGIN TRANSACTION) this will take an exclusive lock on the table and hold it until the transaction is committed or rolled back. Therefore B will not be able to insert until A has committed. The table lock affects all work, however, including simple queries which would not induce interval overlaps.
A finer-grained approach would be to use sp_getapplock. This allows your code to generate its own lock with its own semantics. It acts as a MUTEX. When workload A starts it acquires this applock. It can then go on to preform the check and insert confident it alone has rights to do so. If workload B were to start concurrently it would attempt to acquire the applock and be blocked because that applock is owned by A. You have to be careful that all work that could be subject to the overlapping interval problem must start by acquiring this custom applock and finish by releasing it. Writes that do not suffer from the interval overlap problem and all reads can proceed without acquiring the applock or being blocked by it.