O problema

Eu tenho um par de consultas que, sob isolamento serializável, causam um bloqueio RX-X. No entanto, quando uso Extended Events para assistir a aquisição de bloqueio, a aquisição de bloqueio RX-X nunca aparece, apenas é liberada. De onde isso vem?

A reprodução

Segue minha tabela:

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')

Aqui está o meu lote de problemas:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

INSERT dbo.LockTest
VALUES ('bleh')

SELECT *
FROM dbo.LockTest
WHERE ID = SCOPE_IDENTITY()

--ROLLBACK

Eu verifico os bloqueios mantidos por esta sessão e vejo RX-X:

SELECT resource_type, request_mode, request_status, resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = 72 --change SPID!

Mas também tenho um Evento Estendido em lock_acquirede lock_released. Eu o filtro no associado_object_id apropriado... não há RX-X.

Após executar o rollback, vejo o RX-X (LAST_MODE) liberado, mesmo que nunca tenha sido adquirido.

O que eu tentei

• Olhei para todos os bloqueios em Eventos Estendidos - sem filtragem. Nenhum bloqueio RX-X adquirido.

• Eu também tentei Profiler: mesmos resultados (exceto, é claro, que obtém o nome certo ... sem "LAST_MODE").

• Eu executei o XE para escalonamentos de bloqueio - não está lá.

• Não há XE especificamente para conversões, mas pude confirmar que pelo menos a conversão de bloqueio de U para X é capturada porlock_acquired

Também digno de nota é o RI-N que é adquirido, mas nunca lançado. Minha hipótese atual é que o RX-X é um bloqueio de conversão, conforme descrito aqui . Há bloqueios de intervalo de chaves sobrepostos no meu lote que parecem se qualificar para conversão, mas o bloqueio RX-X não está na tabela de conversão.

De onde vem esse bloqueio e por que não é captado pelos Eventos Estendidos?

Estou lutando contra o NOLOCK no meu ambiente atual. Um argumento que ouvi é que a sobrecarga do bloqueio diminui a velocidade de uma consulta. Então, eu criei um teste para ver o quanto essa sobrecarga pode ser.

Descobri que o NOLOCK realmente diminui a velocidade da minha varredura.

No começo eu estava encantado, mas agora estou apenas confuso. Meu teste é inválido de alguma forma? O NOLOCK não deveria permitir uma varredura um pouco mais rápida? O que está acontecendo aqui?

Aqui está meu roteiro:

USE TestDB
GO

--Create a five-million row table
DROP TABLE IF EXISTS dbo.JustAnotherTable
GO

CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )

INSERT dbo.JustAnotherTable
SELECT TOP 5000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
CROSS JOIN sys.all_objects a3

/********************************************/
-----Testing. Run each multiple times--------
/********************************************/
--How fast is a plain select? (I get about 587ms)
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

----------------------------------------------
--Now how fast is it with NOLOCK? About 640ms for me
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

O que eu tentei que não funcionou:

• Executando em servidores diferentes (mesmos resultados, os servidores eram 2016-SP1 e 2016-SP2, ambos silenciosos)
• Executando em dbfiddle.uk em versões diferentes (ruidoso, mas provavelmente os mesmos resultados)
• SET ISOLATION LEVEL em vez de dicas (mesmos resultados)
• Desativando o escalonamento de bloqueio na tabela (mesmos resultados)
• Examinando o tempo de execução real da verificação no plano de consulta real (mesmos resultados)
• Dica de recompilação (mesmos resultados)
• Grupo de arquivos somente leitura (mesmos resultados)

A exploração mais promissora vem da remoção da variável trash e do uso de uma consulta sem resultados. Inicialmente, isso mostrou o NOLOCK um pouco mais rápido, mas quando mostrei a demo ao meu chefe, o NOLOCK voltou a ser mais lento.

O que há no NOLOCK que retarda uma varredura com atribuição de variável?