## 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,
CREATE CLUSTERED INDEX CX_LockTest --not unique!
--preload some rows
Here's my problem batch:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WHERE ID = SCOPE_IDENTITY()
I check locks held by this session, and see RX-X:
SELECT resource_type, request_mode, request_status, resource_description
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.
[![Extended Event output]]
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.
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`]:
FROM sys.dm_xe_map_values AS DXMV
DXMV.[name] = N'lock_mode'
║ 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". Erik Darling has [reported the issue on Azure Feedback].