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?
Best Answer
The single row insert acquires an
X(exclusive) lock on the new row.The
SELECTattempts to acquire a range-shared, key shared (RangeS-S) lock.This request is reported by the
lock_acquiredExtended Event as mode =RS_S.It is reported by the Profiler event class
Lock:Acquiredas mode 13 (LCK_M_RS_S).The requested mode is combined with the existing exclusive lock mode in
Lock::CalculateGrantModeinsqlmin.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 requestedRangeS-Smode, not the resulting lock modeRangeX-X. This is counter to the limited documentation, which says: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_MODEThe mysterious
LAST_MODEis something Erik Darling has remarked on before. It is the highestmap_keyvalue in the list of lock modes exposed bysys.dm_xe_map_values:The memory structure accessed via the DMV (using
sqlmin!CMapValuesTable) is stored starting at the addresssqlmin!XeSqlPkg::g_lock_mode. Each 16-byte entry in the structure contains themap_keyand a pointer to the string returned asmap_valueby 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_valueof "LAST_MODE" instead of the expected "RX_X". Erik Darling has reported the issue on Azure Feedback.