sql-server sql-server-2017
## 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:


INSERT dbo.LockTest
VALUES ('bleh')

FROM dbo.LockTest


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.

[![Extended Event output][2]][2]

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][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
Top Answer
Paul White
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.



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]:

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][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
Why does this RX-X lock not appear in Extended Events?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.