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

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

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

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