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?
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_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
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?