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 above 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.

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, or create an Azure Feedback item.

LAST_MODE

The mysterious LAST_MODE is something Erik Darling has remarked on before. It is the highest map_key value in the list of lock modes exposed by sys.dm_xe_map_values:

SELECT
 DXMV.map_key,
 DXMV.map_value
FROM sys.dm_xe_map_values AS DXMV
WHERE 
 DXMV.[name] = N'lock_mode'
ORDER BY
 DXMV.map_key;

╔═════════╦═══════════╗
║ map_key ║ map_value ║
╠═════════╬═══════════╣
║ 0 ║ NL ║
║ 1 ║ SCH_S ║
║ 2 ║ SCH_M ║
║ 3 ║ S ║
║ 4 ║ U ║
║ 5 ║ X ║
║ 6 ║ IS ║
║ 7 ║ IU ║
║ 8 ║ IX ║
║ 9 ║ SIU ║
║ 10 ║ SIX ║
║ 11 ║ UIX ║
║ 12 ║ BU ║
║ 13 ║ RS_S ║
║ 14 ║ RS_U ║
║ 15 ║ RI_NL ║
║ 16 ║ RI_S ║
║ 17 ║ RI_U ║
║ 18 ║ RI_X ║
║ 19 ║ RX_S ║
║ 20 ║ RX_U ║
║ 21 ║ LAST_MODE ║
╚═════════╩═══════════╝

The memory structure accessed via the DMV (using sqlmin!CMapValuesTable) is stored starting at the address sqlmin!XeSqlPkg::g_lock_mode. Each 16-byte entry in the structure contains the map_key and a pointer to the string returned as map_value by the streaming TVF.

The strings are stored exactly as shown in the table above (though not in that order). It seems to be an error that entry 21 has a map_value of "LAST_MODE" instead of the expected "RX_X".