## 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!
```
[![dm_tran_locks][1]][1]
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][2]][2]
After executing the rollback, I see RX-X (LAST_MODE) released, even though it was never acquired.
[![LAST_MODE][3]][3]
## 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][4]. 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?**
[1]: https://i.stack.imgur.com/pewku.png
[2]: https://i.stack.imgur.com/q1EpO.png
[3]: https://i.stack.imgur.com/LDp97.png
[4]: https://docs.microsoft.com/en-us/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014#conversion-locks
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][1], 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][2]. It is the highest `map_key` value in the list of lock modes exposed by [`sys.dm_xe_map_values`][3]:
```
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;
```
```none
╔═════════╦═══════════╗
║ 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][4].
[1]: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class
[2]: https://www.brentozar.com/archive/2018/09/quirks-when-working-with-extended-events-to-track-locks/
[3]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-xe-map-values-transact-sql?view=sql-server-2017
[4]: https://feedback.azure.com/forums/908035-sql-server/suggestions/35166271-last-mode-should-map-to-lck-m-rx-x-in-the-lock-mod
