Thanks for confirming that - was hoping that might have been READ UNCOMMITTED 🙂

As you would expect, as soon as you insert the data in your audit table the check query should block until the transaction is committed. Even with snapshot read committed I'd still expect to see a consistent set of results as per the uncommitted state and no blocking. Every test i have been able to perform has behaved as expected.

Its unlikely, but could it be a timing thing, that the initial RangeS-S KEY locks are not enough...maybe try adding a hint to force taking an exclusive lock, or be more aggressive with more restrictive locks? Not recommending that as a solution though 🙂

Are you getting errors from the catch statement? table variables arent effected by transactions, so theres an error scenario where the data being inserted/delete might not have the Range-S locks in place, although I still dont really see whats going on.

It could be your queries are using different a locking strategy and what you are seeing are the side effect known as phantom rows which can happen when reading from a table at anything other than SERIALIZABLE isolation levels.

It might be worth trying to capture the locks on both sessions to see whats happening.

Sorry I cant be more definitive.

Jamie