5

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

  • There's a constraint-only solution for storing intervals of time with no overlaps by Alex Kuznetsov, if you'd like to take a look. – Andriy M Nov 9 '16 at 7:26
  • 1
    Wow @AndriyM, that is a bit complicated but sort of elegant. For the case at hand it might make inserting rows tricky if you're trying to insert into a hole in the range. – mendosi Nov 9 '16 at 10:40
0

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.

  • What guarantees that the trigger query wont suffer the same concurrency problems? – victor Nov 9 '16 at 20:34
  • @victor This would prevent inconsistent data, put it wouldn't prevent multiple users from attempting to modify the data at the same time. One of them will fail and should know that they failed. – mendosi Nov 9 '16 at 20:44
0

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.

  • I thought about a check constraint too, but how would you design this check constraint to get the desired effect? – mendosi Nov 9 '16 at 6:37
  • @mendosi Good point I should have said a unique index clustered or not. It is late and I'm answering on my phone while watching the US election results – Erik Nov 9 '16 at 6:48
  • 1
    How woul you add a unique constraint to something that is computed? Remember that this is not a simple comparison of the start and end of the interval. How can I store the interval itself so that I can make it unique – victor Nov 9 '16 at 12:21
  • @victor An indexed view might be able to do the trick. I would need to have the table schema to try and write the view though and have a chance to ingest some caffeine. This whole plan might be unworkable though. I'm not at my best while watching politics late at night – Erik Nov 9 '16 at 13:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.