The Problem

I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it is only released. Where does it come from?

The Repro

Here's my table:

CREATE TABLE dbo.LockTest (
ID int identity,
Junk char(4)
)

CREATE CLUSTERED INDEX CX_LockTest --not unique!
ON dbo.LockTest(ID)

--preload some rows
INSERT dbo.LockTest
VALUES ('data'),('data'),('data')

Here's my problem batch:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

INSERT dbo.LockTest
VALUES ('bleh')

SELECT *
FROM dbo.LockTest
WHERE ID = SCOPE_IDENTITY()

--ROLLBACK

I check locks held by this session, and see RX-X:

SELECT resource_type, request_mode, request_status, resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = 72 --change SPID!

But I also have an Extended Event on lock_acquired and lock_released. I filter it on the appropriate associated_object_id...there's no RX-X.

After executing the rollback, I see RX-X (LAST_MODE) released, even though it was never acquired.

What I've Tried

• I looked at all locks in Extended Events - no filtering. No RX-X locks acquired.




• I also tried Profiler: same results (except of course it gets the name right...no "LAST_MODE").




• I ran the XE for lock escalations - it's not there.




• There's no XE specifically for conversions, but I was able to confirm that at least the U to X lock conversion is captured by lock_acquired

Also of note is the RI-N that gets acquired but never released. My current hypothesis is that the RX-X is a conversion lock, as described here. There are overlapping key-range locks in my batch that look like they should qualify for conversion, but the RX-X lock isn't in the conversion table.

Where is this lock coming from, and why isn't it picked up by Extended Events?

The single row insert acquires an X (exclusive) lock on the new row.

The SELECT attempts to acquire a range-shared, key shared (RangeS-S) lock.

This request is reported by the lock_acquired Extended Event as mode = RS_S.

It is reported by the Profiler event class Lock:Acquired as mode 13 (LCK_M_RS_S).

The requested mode is combined with the existing exclusive lock mode in Lock::CalculateGrantMode in sqlmin.dll. There is no combined mode of range-shared, key exclusive (RangeS-X) so the outcome of the calculation is range-exclusive, key exclusive (RangeX-X), which happens to be mode 15.

The grant mode calculation is performed just before the extended event is generated by lck_ProduceExtendedEvent<XeSqlPkg::lock_acquired>. Nevertheless, both Profiler and Extended Events log the requested RangeS-S mode, not the resulting lock mode RangeX-X. This is counter to the limited documentation, which says:

Mode | int | Resulting mode after the lock was acquired.

The mode column of the extended event has no documentation at all, and the description in the meta data is blank. Perhaps Microsoft themselves weren't even sure of the behaviour.

The mysterious LAST_MODE is something Erik Darling has remarked on before.

I have often thought it would be more useful if lock events reported both the requested and resulting modes, but that is not what we have. The current arrangement makes it pretty much impossible to track and match up lock acquisition and release.

There might be a good reason for reporting locks this way. If it doesn't meet your needs, you could open a support case with Microsoft.