I use Azure SQL DB (Single DB, Basic, DTU, Provisioned). Isolation level is default is_read_committed_snapshot_on = 1.
Requirement is to lock a record in a table, if it is about to be updated. During the lock...
#1. The other users should be allowed to read the entire table (including the row being updated).
#2. The other users should be allowed to update other records (except the one being updated).
#3. The other users should be allowed to identify who has locked the row (i.e., a query to identify the lock, passing the Table Name and PK of the record.)
For #1, the hint SELECT with UPDLOCK does the job.
For #2, I have tried different combinations of UPDLOCK with and without ROWLOCK, READPAST. All are locking entire table (object) in IX mode and RID in U mode. So, it blocks update of other records in the table by other users. How to allow other users (sessions) to update other rows of the table?
For #3, passing the table name/SPID, and using the DMVs dm_tran_locks and dm_exec_sessions we can identify that this table is locked by this user. How can the query be fine till the row level (using PK)? (i.e., if two users have locked two different rows, how can we identify that User A has locked Row 1, and User B locked Row 2?) Is it possible?
Your help is much appreciated!
Thanks in advance!
(object) in IX mode and RID in U
A table-level IX and a RID U lock will not block other sessions from updating other rows. Double-check your indexing, especially consider adding a clustered index instead of using a heap table.
How can the query be fine till the row level (using PK)?
There's not a great way to do this. sys.dm_trans_locks.resource_description will match the %%lockres%% undocumented virtual column for the locked row. But other sessions can't read the locked row, and you'd have to scan the whole table for matches. If you really need this, you could introduce an additional table where you can insert (ID,session_id) for the session that owns the lock.