Sql Locking Analysis

Open three query windows like so

select request_session_id, resource_type, request_mode, request_status
from sys.dm_tran_locks
where request_session_id in (64, 65)

Use this query to determine the locks showing

I'm using the AdventureWorks2017 database available here, instructions provided

I would like to update the first available match where the Person.AddressLine2 IS NULL and the StateProvince.Name = 'Texas'

BEGIN TRAN 
	MERGE Person.Address t
		USING (
			SELECT TOP 1 pa.*, sp.Name
			FROM Person.Address pa
			INNER JOIN Person.StateProvince sp
			ON pa.StateProvinceID = sp.StateProvinceID
			WHERE sp.Name = 'Texas'
			AND pa.AddressLine2 IS NULL
			ORDER BY pa.AddressId
		) s
		on (s.AddressId = t.AddressId)
	WHEN MATCHED
		THEN UPDATE SET
			t.AddressLine2 = s.Name
	OUTPUT
	   $action,
	   inserted.*,
	   deleted.*;
COMMIT TRAN

I'm doing the above query update to demonstrate a complicated merge statement as shown with the inner join and the order of the Person.AddressId for numeric ordering to give deterministic ordering so that the TOP 1 items should move out of the system like a Queue First In/First Out

TLDR;  I want to take a table numerically order by ids and move down the list of items in a first in order and update the record.  If the record is in use I want to skip to the next one so I will attempt to tell the row that information with the table hints (READPAST, UPDLOCK, ROWLOCK)

First find the expected rows to be affected

USE [AdventureWorks2017]

SELECT TOP 2 pa.*, sp.Name
FROM Person.Address pa
INNER JOIN Person.StateProvince sp
ON pa.StateProvinceID = sp.StateProvinceID
WHERE sp.Name = 'Texas'
AND pa.AddressLine2 IS NULL
ORDER BY pa.AddressId

In the above if I start a transaction and put an arbitrary lock on the table to hold it for 5 seconds in one query window I want it to take the record matched AddressId of 25 and hold it exclusively until the transaction finishes.  If I start another concurrent query without the arbitrary wait it should grab the next ordered id 325 in this case

Window 1

USE [AdventureWorks2017]

DECLARE @startTime DATETIME2 = SYSUTCDATETIME()

BEGIN TRAN 
	MERGE Person.Address t
		USING (
			select TOP 1 pa.*, sp.Name
			FROM Person.Address pa
			INNER JOIN Person.StateProvince sp
			ON pa.StateProvinceID = sp.StateProvinceID
			WHERE sp.Name = 'Texas'
			AND pa.AddressLine2 IS NULL
			ORDER BY pa.AddressId
		) s
		on (s.AddressId = t.AddressId)
	WHEN MATCHED
		THEN UPDATE SET
			t.AddressLine2 = s.Name
	OUTPUT
	   $action,
	   inserted.*,
	   deleted.*;
	WAITFOR DELAY '00:00:05'
COMMIT TRAN

PRINT DATEDIFF(ss, @startTime, SYSUTCDATETIME())

Window 2

USE [AdventureWorks2017]

DECLARE @startTime DATETIME2 = SYSUTCDATETIME()

BEGIN TRAN 
	MERGE Person.Address t
		USING (
			select TOP 1 pa.*, sp.Name
			FROM Person.Address pa
			INNER JOIN Person.StateProvince sp
			ON pa.StateProvinceID = sp.StateProvinceID
			WHERE sp.Name = 'Texas'
			AND pa.AddressLine2 IS NULL
			ORDER BY pa.AddressId
		) s
		on (s.AddressId = t.AddressId)
	WHEN MATCHED
		THEN UPDATE SET
			t.AddressLine2 = s.Name
	OUTPUT
	   $action,
	   inserted.*,
	   deleted.*;
	WAITFOR DELAY '00:00:05'
COMMIT TRAN

PRINT DATEDIFF(ss, @startTime, SYSUTCDATETIME())

What happens

Ok this is without any locking so it is up to SQL Server to interpret and run the transactions in order and as you can see the transactions do not collide and pick the proper order of actions to execute the delayed transaction ran and picked the first order id 25, but the second concurrent transaction 325 did not immediately pick up the next  value there was a delay as the table lock escalation was validated and reviewed and it took 3 whole seconds for it to figure this out.  This effect will only be exacerbated by multiple concurrent transactions hitting the same table and causing table locking in order to sort out the proper order.

There is a way to instruct the table locking to occur without delay and read into the next available, use a combination of locks and hints to instruct the transaction to pass over immediately even if there is a delay in concurrent transactions.

READPAST

Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.

SO that also means I need to use ROWLOCK to inform READPAST what to skip

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

I am doing an UPDATE so this means I need to add another hint UPDLOCK

UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

That means I will use the following table hints in my concurrent transactions then

READPAST, UPDLOCK, ROWLOCK

Now try this again

Reset the data

-- Reset
USE [AdventureWorks2017]
update Person.Address
set AddressLine2 = NULL
WHERE AddressLine2 = 'Texas'

Window 1

BEGIN TRAN 
	MERGE Person.Address t
		USING (
			select TOP 1 pa.*, sp.Name
			FROM Person.Address pa WITH (READPAST, UPDLOCK, ROWLOCK)
			INNER JOIN Person.StateProvince sp
			ON pa.StateProvinceID = sp.StateProvinceID
			WHERE sp.Name = 'Texas'
			AND pa.AddressLine2 IS NULL
			ORDER BY pa.AddressId
		) s
		on (s.AddressId = t.AddressId)
	WHEN MATCHED
		THEN UPDATE SET
			t.AddressLine2 = s.Name
	OUTPUT
	   $action,
	   inserted.*,
	   deleted.*;
	WAITFOR DELAY '00:00:05'
COMMIT TRAN

Window 2

BEGIN TRAN 
	MERGE Person.Address t
		USING (
			select TOP 1 pa.*, sp.Name
			FROM Person.Address pa WITH (READPAST, UPDLOCK, ROWLOCK)
			INNER JOIN Person.StateProvince sp
			ON pa.StateProvinceID = sp.StateProvinceID
			WHERE sp.Name = 'Texas'
			AND pa.AddressLine2 IS NULL
			ORDER BY pa.AddressId
		) s
		on (s.AddressId = t.AddressId)
	WHEN MATCHED
		THEN UPDATE SET
			t.AddressLine2 = s.Name
	OUTPUT
	   $action,
	   inserted.*,
	   deleted.*;

COMMIT TRAN

Results - Yes it works!

As you can see the first transaction picks up id 25 immediately and then holds it for the 5 seconds, but the key difference here is the second transaction picks up the second item 325 and runs immediately without any delay as you can see

Run the test slowed down to see locks in progress

I am changing the first WAITFOR DELAY to 15 seconds and putting 5 seconds on the second transaction

Yay!