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 

Answer 2

I wrote a test and looks like UPDLOCK does the job! Thank you everyone for the inputs.

    CREATE TABLE lock_test(
ID INT NOT NULL,
is_active INT NOT NULL,
x_id INT NOT NULL,
audit_id INT,
status varchar(25)
)

INSERT INTO lock_test(ID, is_active, x_id, status)
VALUES (1, 1, 9, 'inprogress')

In query tab 1

BEGIN TRANSACTION tran1
  BEGIN TRY
        IF EXISTS (select * from lock_test with(updlock) where x_id = 9 and is_active = 1 and status = 'inprogress')
        BEGIN
            UPDATE lock_test SET is_active = 0 where x_id = 9 and is_active = 1 and status = 'inprogress'
            WAITFOR DELAY '00:00:20'
            INSERT INTO lock_test(ID, is_active, x_id, status) VALUES (2, 1, 9, 'done')
            COMMIT TRANSACTION tran1
        END 
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION tran1
  END CATCH

In query tab2

BEGIN TRANSACTION tran1
  BEGIN TRY
        IF EXISTS (select * from lock_test with(updlock) where x_id = 9 and is_active = 1 and status = 'inprogress')
        BEGIN
            UPDATE lock_test SET is_active = 0 where x_id = 9 and is_active = 1 and status = 'inprogress'
            INSERT INTO lock_test(ID, is_active, x_id, status) VALUES (2, 1, 9, 'done')
            COMMIT TRANSACTION tran1
        END 
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION tran1
  END CATCH

Result

To reproduce the problem in question, Remove with(updlock) from both selects and follow the steps mentioned above.

Problematic result:

References:

• https://learn.microsoft.com/en-us/answers/questions/99285/what-is-the-usage-of-updlock-in-select-and-update
• https://learn.microsoft.com/en-us/answers/questions/99453/what-is-the-difference-between-rowlock-updlock-and
• https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15&WT.mc_id=DP-MVP-36772
• https://learn.microsoft.com/en-us/answers/questions/150070/sql-server-when-to-use-rowlock-updlock-etc
• https://learn.microsoft.com/en-us/answers/questions/91746/how-update-lock-works
• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

Answer 3

Azure works differently than "default mode" SQL Server here. Because Snapshot Isolation is normally on, reads will not block during an open write transaction even at the "READ COMMITTED" isolation level -- Azure will "take a snapshot" of the old data instead. If you want to avoid this behavior, you can either turn off snapshot isolation ("READ_COMMITTED_SNAPSHOT OFF"), upgrade to an update lock (as per the other answer), or set the transaction isolation level to serializable just for this one transaction.

However, the better solution is to avoid this contortion entirely. To do this without a multi-statement explicit transaction, use the "OUTPUT" clause on the update (which is atomic) to return the Audit ID you wish to insert. If your INSERT contains the where clause "..WHERE Active=1", it can't go wrong. An example of using OUTPUT to audit rows in atomic fashion like this can be found at:

SQLServerCentral: The Output Clause

Answer 4

For step #2 UPDLOCK does not work for SELECT statements.
Correction: It acctually does work when both SELECTs have UPDLOCK.
You could use an exclusive lock, so that other SELECTs (without any hint) will wait for the transaction to finish:

SELECT id FROM tableA 
  WITH (PAGLOCK,XLOCK)
  WHERE x_id = 1 AND is_active = 1 AND status = 'inprogress'

Note: On Azure SQL the default isolation level (READ COMMITTED) behaves differently than on SQL Server, and does not wait on reads even when the lock is exclusive. On Azure SQL to wait in SELECTs on locked records, you should set SET TRANSACTION ISOLATION LEVEL REPEATABLE READ or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

It would be much simpler if you could avoid SELECT before UPDATE with something like this:

BEGIN TRAN
DECLARE @id int
UPDATE tableA 
  SET is_active = 0, @id=id
  WHERE x_id = 1 AND is_active = 1 AND status = 'inprogress'
IF @@ROWCOUNT>0 -- if found then also insert audit record
  INSERT INTO tableA (x_id, is_active, audit_id, status)
    VALUES (1, 1/*true*/, @id, 'inprogress')
COMMIT TRAN