使用UPDLOCK和HOLDLOCK的查询未生成预期锁的问题排查

Hey there! Let's dive into why your UPDLOCK + HOLDLOCK query isn't generating the expected locks, using your table1 structure as a reference. I'll walk through the most common pitfalls and how to fix them.

1. You're Not Targeting Specific Rows (Or No Rows Match Your Filter) #

SQL Server only applies row-level locks if your query actually hits existing rows. If your query lacks a WHERE clause, or the clause doesn't match any records, you won't get the row locks you're expecting. For example:

• ❌ This won't lock anything meaningful (if the table is empty or no rows match):

  SELECT * FROM table1 WITH (UPDLOCK, HOLDLOCK);
  

• ✅ This will target and lock the row with id=1 (assuming it exists):

  SELECT * FROM table1 WITH (UPDLOCK, HOLDLOCK) WHERE id = 1;
  

2. You're Not Running the Query Inside an Explicit Transaction #

UPDLOCK and HOLDLOCK only hold locks for the duration of a transaction. If you run the query without wrapping it in BEGIN TRANSACTION, the locks are released immediately after the query finishes—you won't have time to test blocking scenarios.

Do this instead in your first session:

BEGIN TRANSACTION;
-- Lock the target row
SELECT name FROM table1 WITH (UPDLOCK, HOLDLOCK) WHERE id = 1;
-- Leave the transaction open (don't COMMIT/ROLLBACK yet!)

Now you can open a second session to test blocked reads/writes.

3. Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation Is Enabled #

If your database has either of these isolation levels turned on, other sessions might read versioned data instead of being blocked. This makes it look like your locks aren't working, but they actually are.

Check your database settings with this query:

SELECT 
  name, 
  is_read_committed_snapshot_on, 
  snapshot_isolation_state 
FROM sys.databases WHERE name = 'YourDatabaseName';

To test blocking in this case, force the second session to use an isolation level that doesn't read snapshots:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- This will block until the first transaction releases locks
SELECT * FROM table1 WHERE id = 1;

4. You're Verifying Locks the Wrong Way #

Just running a SELECT in another session might not show blocking (thanks to isolation levels). To confirm locks exist, use SQL Server's system views to inspect active locks:

SELECT 
  resource_type, 
  resource_associated_entity_id,
  request_mode, 
  request_status, 
  session_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('YourDatabaseName');

You should see an UPDLOCK entry tied to your target row in table1.

Let's put this all together with your table:

1. Session 1 (Lock Holder):

   USE YourDatabaseName;
   BEGIN TRANSACTION;
   -- Lock the row with id=1
   SELECT name FROM table1 WITH (UPDLOCK, HOLDLOCK) WHERE id = 1;
   -- Keep this transaction open!
   

2. Session 2 (Test Blocking):

   USE YourDatabaseName;
   -- This UPDATE will hang until Session 1 commits/rollbacks
   UPDATE table1 SET name = 'Test Update' WHERE id = 1;
   

3. Verify Locks (Session 3):
   Run the sys.dm_tran_locks query above—you'll see the UPDLOCK on your row.
4. Clean Up:
   Go back to Session 1 and run ROLLBACK TRANSACTION; to release the locks.

If you follow these steps, you should see the blocking behavior you're expecting. Let me know if you still run into issues!

内容的提问来源于stack exchange，提问作者contactmatt