Forrest (imported from SE)
## 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 "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?**

Top Answer
Paul White (imported from SE)
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].


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.