AzureSql/SQL Server - Concurrent transactions, rowlock, updlock and holdlock

Question

I have a transaction where

1. Transaction start
2. I run an update on a row with a given id that is marked currently active and mark it inactive
3. Add a new row with a audit_id from the above updated row
4. Commit the transaction.

However, when two concurrent transactions ran, ended up with a scenario where #2 happened twice on the same row and #3 got executed twice resulting in two extra rows being inserted instead of just 1.

TLDR; No two rows should have same audit ids. If transactions ran as intended, row 3 should have never appeared.

id x_id is_active audit_id. status
1.  9     false.    null     inprogress
2.  9     true       1        done
3.  9     true       1        done

How do I avoid this?

I was looking at ROWLOCKS, UPDATELOCKS and HOLDLOCKs

For the above scenario, looks like I need a HOLDLOCK..

1. Transaction begin
2. Select id from tableA where x_id = 1 and is_active = true and status = inprogress with (HOLDLOCK)
3. Update is_active = false where id = (id from #2)
4. Insert into tableA x_id=1, is_active = true, audit_id = (id from #2), status = done
5. Commit transaction

Is the above right? According to what I understand, if I use UPDLOCK, say in another concurrent transaction #2 was already executed, both transactions will end up executing #3, not at once but one after the other in overlapping transactions and I'll end up with two rows having same audit_id and status = done.

Answer 1

With READ COMMITTED SNAPSHOT on you need to opt-in to a lock when reading. Otherwise you'll simply read the "last-known-good" version of the row.

So use UPDLOCK, and add HOLDLOCK if you also need to lock empty ranges (ie where the row doesn't exist and you intend to insert it).

EG

Select id 
from tableA with (UPDLOCK,HOLDLOCK)
where x_id = 1 
  and is_active = true 
  and status = inprogress