Handle concurrency when insertion depends on reading

Question

[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?

Answer 1

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

A phantom is a row that matches the search criteria but is not initially seen.

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.

Answer 2

If I understand correctly, you are doing two separate statements. First check to see if insert is okay, then second do the insert.

I wonder if a trigger might be a solution to your woes. I haven't any experience with creating triggers specifically in Azure, but try something like this:

Create Trigger tr_insertTimeRange On [Table1] For Insert As
Begin
    If 1 < (
        Select Count(*)
          From [Table1] As t
          Join Inserted As i
            On t.attemptEnd >= i.attemptStart
            Or t.attemptStart <= i.attemptEnd)
    Begin
        Raiserror ('Error!', 12, 1);
        Rollback Transaction;
    End
End
Go

After the data is inserted, it counts how many rows in the table overlap the time range of the inserted row. If there are more than 1 (remember, the row has already been inserted so it will overlap) then the transaction is rolled back.

Answer 3

You can always put in a unique index (clustered or not) on the table to prevent the overlap. This would enforce the business logic by ensuring that only one can succeed.

Another option would be to set the transaction level to serializable which will prevent the problem but probably excessively bottleneck the app.