Os pontos mais delicados dos gravadores bloqueando uns aos outros sob os níveis de isolamento de instantâneo

Regras gerais sobre bloqueios e escalonamento de bloqueios:

Os bloqueios de nível de linha podem ser escalados para bloqueios de nível de tabela (ou nível de partição). Portanto, é possível que linhas não relacionadas sejam bloqueadas, sob determinadas circunstâncias.

Essas circunstâncias estão documentadas aqui . Observe também que o escalonamento de bloqueio pode ser desabilitado com a LOCK_ESCALATIONopção de tabela.

Além disso, alguns planos de consulta usam bloqueios de página, e alguns planos de consulta precisarão ler uma linha bloqueada por outra transação para determinar se ela deve ser modificada. Por exemplo update foo where someUnindexedColumn = 'someval', precisará ler todas as linhas e, portanto, será bloqueado por qualquer outro escritor.

Observe isso (que precisaremos no último parágrafo):

(i) Importante

A escolha de um nível de isolamento de transação não afeta os bloqueios adquiridos para proteger as modificações de dados. Uma transação sempre obtém um bloqueio exclusivo em todos os dados que modifica e mantém esse bloqueio até que a transação seja concluída, independentemente do nível de isolamento definido para essa transação. Para operações de leitura, os níveis de isolamento da transação definem principalmente o nível de proteção contra os efeitos das modificações feitas por outras transações.

Regras sobre SNAPSHOTo nível de isolamento :

O termo "snapshot" reflete o fato de que todas as consultas na transação veem a mesma versão, ou snapshot, do banco de dados, com base no estado do banco de dados no momento em que a transação começa. Nenhum bloqueio é adquirido nas linhas de dados subjacentes ou páginas de dados em uma transação de instantâneo, o que permite que outras transações sejam executadas sem serem bloqueadas por uma transação anterior não concluída. As transações que modificam dados não bloqueiam transações que leem dados e transações que leem dados não bloqueiam transações que gravam dados , como normalmente fariam no nível de isolamento READ COMMITTED padrão no SQL Server. Esse comportamento sem bloqueio também reduz significativamente a probabilidade de deadlocks para transações complexas.

Em suma, neste nível de isolamento:

• o versionamento de linha é usado (em vez de bloqueios) para proteger as modificações de outras transações.
• Os leitores não bloqueiam os leitores.
• Os leitores não bloqueiam os escritores.
• Escritores não bloqueiam leitores.

No entanto, a documentação NÃO menciona que "escritores não bloqueiam escritores", portanto, podemos assumir que os escritores realmente PODEM bloquear escritores no nível de isolamento INSTANTÂNEO.

Mais informações sobre como o instantâneo funciona podem ser encontradas em Bloqueio de transações e Controle de versão de linha :

O isolamento de instantâneo também usa o controle de versão de linha, que não usa bloqueios compartilhados durante as operações de leitura.
...
Quando as opções de banco de dados READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION estão LIGADAS, cópias lógicas (versões) são mantidas para todas as modificações de dados realizadas no banco de dados. Sempre que uma linha é modificada por uma transação específica, a instância do Mecanismo de Banco de Dados do SQL Server armazena uma versão da imagem confirmada anteriormente da linha em tempdb. ...

e abaixo

Comportamento ao modificar dados
... As
transações executadas sob isolamento de instantâneo adotam uma abordagem otimista para a modificação de dados adquirindo bloqueios nos dados antes de realizar a modificação apenas para impor restrições. Caso contrário, os bloqueios não são adquiridos nos dados até que os dados sejam modificados. Quando uma linha de dados atende aos critérios de atualização, a transação de instantâneo verifica se a linha de dados não foi modificada por uma transação simultânea confirmada após o início da transação de instantâneo. Se a linha de dados tiver sido modificada fora da transação de instantâneo, ocorrerá um conflito de atualizaçãoe a transação de instantâneo é encerrada. O conflito de atualização é tratado pelo Mecanismo de Banco de Dados do SQL Server e não há como desabilitar a detecção de conflito de atualização.

Este parágrafo acima e especialmente a parte em que os bloqueios são de fato adquiridos pouco antes de os dados serem modificados explicam o comportamento de bloqueio (escritores para escritores) (e considerando a nota "Importante" mencionada no topo):

Então sob SNAPSHOTisolamento:

• Digamos que a transação A leia vários dados. Nenhum bloqueio é adquirido.
• Outras transações podem ler os mesmos dados e nenhum bloqueio é adquirido.
• Quando a transação A tenta modificar alguns dados, um bloqueio é adquirido e mantido até o final da transação, que pode ser commit ou rollback.
• Outras transações ainda podem ler os dados (é claro que eles veem alguma versão anterior). Sem travamento.
• Se a transação B, no entanto, tentar modificar os dados já mantidos por algum bloqueio (da transação A não confirmada), ela tentará colocar algum bloqueio também, e B será bloqueado.
• Se a transação A for confirmada em algum ponto, seus bloqueios serão liberados, então a transação B será desbloqueada. No entanto, B agora verá um conflito de atualização e não poderá ter êxito.
• Se a transação A for revertida, seus bloqueios serão liberados e suas versões de linha removidas, de modo que a transação B será desbloqueada e suas modificações poderão prosseguir.