I'm just trying to understand a bit more about the behaviour of HOLDLOCK in the scenario where multiple transactions are running concurrently.
The transaction is basically doing the following:
- Start Transaction
- SELECT a row from TableA with the last sequence ID associated with a reference (HOLDLOCK applied here)
- Increment sequence ID by 1
- INSERT a new row with the reference and next sequence ID
- COMMIT
This is working exactly as expected, however I'm just trying to gain an understanding of the order of execution if multiple transactions are waiting on an existing lock on the affected rows on TableA. I.e. if the order is guaranteed (e.g. FIFO).
Just for additional information the transaction currently runs within a SNAPSHOT isolation level.
I've spent a few hours scouring through Microsoft documentation and web searches and can't find anything that definitively answers my question.
If anyone has any general pointers/references it would be greatly appreciated.
HOLDLOCKis an alias forSERIALIZABLE, so you get pessmistic locking and it will hold the lock until the end of the transaction. FIFO is not completely guaranteed, see techcommunity.microsoft.com/t5/sql-server-support-blog/… What exactly are you asking, and can you give the exact code used (along with tables and index definitions), rather than a hand-wavy "basically".