There are many instances when you run some query and realize that you need to use optimistic or pessimistic approach to effect the concurrency. When you use Always on Availability group, you should be aware that any lock hints such as updlock,xlock or other lock hints will be ignored when you run the command in the active secondary. I think it makes sense because all you can do in secondary is READONLY. So, why bother with lock hints when all you can do is READ , READ and READ.

 

To prove,  I used a small Demo. I have two SQL server instances and one of the database is configured with AG

 

Step 1.  Create a temple and insert some hundred odd records

create table testlock (I int)

--After create , insert 100 records

insert into testlock values('1')

go 100

 

Step 2 : Open a transaction

            begin  Tran

        select * from testlock with (holdlock,updlock)

 

step 3 : run sys.dm_tran_Locks and check the  request mode column for the session

 

Step 4: Run the same command as mentioned in the step 2 in an active secondary. When you check sys.dm_tran_locks  in secondary

 

 

You will observe the difference in  the output.

1. In primary, the query completes successfully, however it held a lock at the object level and the request mode is X meaning exclusive. So, the lock manager honored the holdlock and updlock hint

2. But in secondary, the query completes and when you check the sys.dm_tran_locks , you will realize that there is no entry in the sys.dm_tran_locks table for the specific session. Select query takes shared lock and it is released as soon as the page is read. So, holdlock and updlock lock hint were ignored and the query behaved as a normal and usual select command.