1

Is there a race condition if two processes both execute this query at (nearly) the same time?

delete
    table
from
    table with (readpast)
    join (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    ) as selected on
        table.notPK = selected.notPK

Assume there are a variety of duplicate notPK's in table, that id is (of course) an identity and primary key, and that there is no shortage of records in this table. Also assume the default isolation level (read committed).

In other words, is there a chance that one of those processes will not get to delete any records from table even when there's no shortage of records there?

In other words, when does SQL Server apply row locks on the records that will get deleted by the query--before or after they've been SELECTed in that subquery?


I mentioned this in a comment, but the design is to process a First-In-First-Out queue that contains duplicates. In the real world notPK uniquely identifies a unit so in the ideal world it would be the primary key, but here in the real world there are duplicate entries in the queue for each unit. So when one unit gets dequeued then all the duplicates need to be dequeued as well, because each unit should be processed only up to once.

So the question I'm wondering is whether this query guarantees that a unit (and its duplicates) will always be deleted (when there are records in the table), or if it is sometimes possible for zero records to be deleted. For example, does it do this?

  1. Read a notPK
  2. Get references to all records with that notPK and that aren't locked, and place locks on them
  3. Delete those records
  4. Release the locks

...or does it do this?

  1. Read a notPK and get references for all records with that notPK and place locks on all of those records, all in an atomic operation
  2. Delete those records
  3. Release the locks

My gut feeling is that it does the former. Therefore two processes could both read the same notPK, but only one of those processes would "win" the race and acquire locks to delete. The other would end up deleting zero records.

Is that correct?


Here's the same query written a little different with the help of @SlavaMurygin:

delete
    table
from
    table with (readpast)
where
    notPK in (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    )
  • 1
    use notPK rather than * and that is one strange delete – paparazzo Aug 11 '16 at 0:21
  • That might depend on the isolation level in effect. – mustaccio Aug 11 '16 at 0:43
  • @Paparazzi The idea is a FIFO-ish queue with duplicates. So in the ideal world notPK would be the PK, but it's not and I can't control that. So dequeuing and processing one notPK should count as dequeuing the duplicates, and a notPK should only be processed once. I'm open to design suggestions, but I think the question would still stand. P.S. changed to select notPK – Matt Thomas Aug 11 '16 at 2:28
  • @mustaccio I clarified that I'm thinking of READ COMMITTED – Matt Thomas Aug 11 '16 at 2:31
  • 1
    I believe the plan should be the same, but I'd say that one is just easier to comprehend:delete table from table with (readpast) WHERE notPK in (select top (1) notPK from table with (readpast) order by table.id) – Slava Murygin Aug 13 '16 at 14:58
0

It appears from empirical evidence that a race condition does exist.

I've seen in the logs of an application running a query such as this where two threads each got to delete a record at the same time, and those records had the same notPK. If a race condition did not exist then only one thread would have gotten to delete a record.

Note that when I asked the above question I didn't think that it would be possible for both threads to each dequeue a notPK; I thought it would only be possible for one thread to dequeue any particular notPK.

Since I observed that there is at least this one kind of race condition, then it must also be possible for a process to fail to dequeue anything even when there is no shortage of records in the queue.

I'm not sure how to concisely put something here that readers can take and validate on their own, so I guess everyone will just have to trust me on this.

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.