ALLOW_SNAPSHOT_ISOLATION e READ_COMMITTED_SNAPSHOT

Seu entendimento está correto. Fica um pouco confuso.

Kim Tripp (um dos programadores do SQL Server e parte integrante do SQLSkills) passa exatamente pelo que você afirmou nos vídeos do MCM no Snapshot Isolation ( link direto para download MP4 (544MB) ). Avance rápido para 41:45 no vídeo para chegar à parte em que ela responde à sua pergunta.

Se você usar ALLOW_SNAPSHOT_ISOLATIONcertifique-se de usar SET TRANSACTION ISOLATION LEVEL SNAPSHOTem seu código, caso contrário você não obterá nenhum dos benefícios.

Se você definir SET READ_COMMITTED_SNAPSHOT ON, não será necessário modificar nenhum código. O MS SQL Server aplica automaticamente o isolamento de instantâneo para essa tabela.

Eu não testei para ver o que acontece se você solicitar um nível de isolamento diferente em seu código, suspeito que ele substituirá essa opção, mas teste-o primeiro.

Uma rápida olhada na sobrecarga de desempenho usando o isolamento de instantâneo.

Bom artigo sobre como o isolamento de instantâneo pode alterar o comportamento esperado do seu aplicativo . Ele mostra exemplos de como uma instrução de atualização e uma instrução de seleção podem retornar resultados totalmente diferentes e inesperados.

OK, voltei para casa e testei. Aqui fica a observação.

CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
    ID tinyint,
    Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO

SELECT
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO

Primeiro teste com ambas as configurações confirmadas como OFF.

Consulta 1

USE TEST;

BEGIN TRAN
UPDATE TABLE1
SET Details = 'Update'
WHERE ID = 1;

--COMMIT;
--ROLLBACK;
GO

Consulta 2

USE TEST;

SELECT ID, Details
FROM TABLE1
WHERE ID = 1;
GO

Neste teste, a consulta 2 está aguardando a confirmação da consulta 1, dm_tran_locks DMV mostra esse bloqueio exclusivo na TABELA1 incorrido pela consulta 1.

USE TEST;

SELECT
    DB_NAME(tl.resource_database_id) AS DBName,
    resource_type,
    OBJECT_NAME(resource_associated_entity_id) AS tbl_name,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks tl
WHERE 
    resource_database_id = db_id('TEST')
    AND resource_type = 'OBJECT'

Segundo teste , reverta a transação anterior, defina READ_COMMITTED_SNAPSHOT como LIGADO, mas deixe ALLOW_SNAPSHOT_ISOLATION DESLIGADO.

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

Execute a consulta 1 e execute a consulta 2. DMV mostra que a consulta 1 incorre em bloqueio exclusivo, mas a consulta 2 retorna detalhes com 'Original' sem que a consulta 1 confirme a transação. Parece que o controle de versão de linha READ_COMMITTED está em vigor.

Adicionar SET TRANSACTION ISOLATION LEVEL SNAPSHOT;na consulta 1 e na consulta 2 e executar a consulta 1 ou a consulta 2 retorna erro - Falha na transação de isolamento de instantâneo ao acessar o banco de dados 'TEST' porque o isolamento de instantâneo não é permitido neste banco de dados. Use ALTER DATABASE para permitir o isolamento de instantâneo.

Terceiro teste , reversão da transação anterior. Defina READ_COMMITTED_SNAPSHOT OFF e ALLOW_SNAPSHOT_ISOLATION ON.

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE TEST
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Execute a consulta 1 e, em seguida, a consulta 2. A DMV mostra o bloqueio exclusivo incorrido pela consulta 1. A consulta 2 parece estar aguardando a conclusão da consulta 1. Ativar ALLOW_SNAPSHOT_ISOLATION não parece habilitar o controle de versão de linha READ COMMITTED.

Adicionando SET TRANSACTION ISOLATION LEVEL SNAPSHOT;à consulta 1 e à consulta 2. Execute a consulta 1 e, em seguida, a consulta 2. Enquanto o DMV mostra que a consulta 1 incorre em bloqueio exclusivo, a consulta 2 retorna os detalhes com 'Original'. O isolamento de instantâneo parece estar em vigor.

A observação do teste mostra que READ_COMMITTED_SNAPSHOTele mesmo habilita/desabilita o versionamento de linha READ COMMITTED independentemente da ALLOW_SNAPSHOT_ISOLATIONconfiguração e vice-versa.

Seu entendimento está correto. Eu gosto da definição curta, limpa e simples daqui :

Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT está ATIVA, as transações que definem o nível de isolamento de leitura confirmada usam o controle de versão de linha.

Quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION está ATIVA, as transações podem definir o nível de isolamento do instantâneo.

Parece que muito mal-entendido vem da própria MS. Por exemplo, aqui eles dizem:

Se você definir a opção de banco de dados READ_COMMITTED_SNAPSHOT como ON, o mecanismo de banco de dados usará controle de versão de linha e isolamento de instantâneo como padrão, em vez de usar bloqueios para proteger os dados.

Mas o mencionado "isolamento instantâneo" não é igual ao comportamento da transação para a qual set transaction isolation level snapshoté aplicado.

Quanto à diferença, uma boa explicação está aqui .

Provavelmente seria melhor se READ_COMMITTED_SNAPSHOT fosse nomeado como READ_COMMITTED_ROW_VERSIONING ou algo assim. :)

Eu gosto deste resumo da Microsoft :

Definir a opção READ_COMMITTED_SNAPSHOT ON permite o acesso a linhas com versão no nível de isolamento READ COMMITTED padrão. Se a opção READ_COMMITTED_SNAPSHOT estiver definida como OFF, você deverá definir explicitamente o nível de isolamento do instantâneo para cada sessão para acessar as linhas com versão.