Race condition in DELETE with nested query?

Question

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
    )

Answer 1

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.