• Artigo

Níveis de isolamento no Mecanismo de Banco de Dados

As transações especificam um nível de isolamento que define o grau em que uma transação deve ser isolada contra modificações de recursos ou de dados feitas por outras transações. Os níveis de isolamento são descritos em termos de quais efeitos colaterais de simultaneidade são permitidos, como leituras sujas ou leituras fantasma.

Níveis de isolamento da transação controlam:

  • Se são feitos bloqueios quando os dados são lidos, e que tipo de bloqueio é solicitado.

  • Por quanto tempo os bloqueios de leitura são mantidos.

  • Se uma linha de referência de operação de leitura foi modificada por outra transação:

    • Bloqueia até que o bloqueio exclusivo na linha seja liberado.

    • Recupera a versão confirmada da linha existente no momento em que a instrução ou transação foi iniciada.

    • Lê a modificação de dados não confirmados.

Escolhendo um nível de isolamento da transação não afeta os bloqueios obtidos para proteger as modificações de dados. Uma transação sempre obtém um bloqueio exclusivo em quaisquer dados que modifica e mantém tal bloqueio até que a transação seja concluída, sem considerar o conjunto de níveis de isolamento para a transação em questão. Para operações de leitura, níveis de isolamento da transação definem principalmente o nível de proteção dos efeitos das modificações feitas por outras transações.

Um nível de isolamento inferior aumenta a capacidade de muitos usuários acessarem dados ao mesmo tempo, mas aumenta o número de efeitos de simultaneidade (como leituras sujas ou atualizações perdidas) que os usuários podem encontrar. Inversamente, um nível de isolamento mais alto reduz os tipos de efeito de simultaneidade que os usuários podem encontrar, mas requer mais recursos do sistema e aumenta as chances de uma transação bloquear outra. Escolher o nível de isolamento apropriado depende de equilibrar os requisitos de integridade de dados do aplicativo em relação à sobrecarga de cada nível de isolamento. O nível de isolamento mais alto, serializável, garante que uma transação recuperará exatamente os mesmos dados toda vez que repetir uma operação de leitura, mas faz isto executando um nível de bloqueio que provavelmente causará impacto em outros usuários em sistemas multiusuários. O mais baixo nível de isolamento, leitura de dados não confirmados, pode recuperar dados que foram modificados mas não foram confirmados por outras transações. Todos os efeitos colaterais de simultaneidade podem acontecer em leitura não confirmada, mas não há nenhum bloqueio de leitura ou controle de versão, assim a sobrecarga é minimizada.

Níveis de isolamento do Mecanismo de Banco de Dados

O padrão ISO define os seguintes níveis de isolamento, todos têm suporte pelo Mecanismo de banco de dados do SQL Server:

  • Leitura não confirmada (o mais baixo nível onde transações só estão isoladas o bastante para assegurar que dados corruptos fisicamente não são sejam lidos)

  • Leitura confirmada (Mecanismo de Banco de Dados nível padrão)

  • Leitura repetida

  • Serializável (o nível mais alto, onde as transações estão completamente isoladas uma da outra)

Observação importanteImportante

Pode haver falha em operações e transações DDL em tabelas replicadas quando o nível de isolamento serializável é solicitado. Isso ocorre porque as consultas de replicação usam dicas que podem ser incompatíveis com o nível de isolamento serializável.

O SQL Server também oferece suporte a dois níveis de isolamento de transação que usam controle de versão de linha. É uma implementação nova de isolamento de leitura confirmada e é um nível de isolamento da transação novo, instantâneo.

  • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT estiver definida como ON, o isolamento de leitura confirmada usará o controle de versão de linha para fornecer consistência de leitura no nível da instrução. Operações de leitura só requerem bloqueios de nível de tabela SCH-S e nenhum bloqueio de página ou linha. Quando a opção do banco de dados READ_COMMITTED_SNAPSHOT estiver defnida como OFF, que é a configuração padrão, o isolamento de leitura confirmada se comportará conforme versões anteriores do SQL Server. Ambas as implementações satisfazem a definição de ANSI de isolamento de leitura confirmada.

  • O nível de isolamento do instantâneo usa controle de versão de linha para fornecer consistência de leitura em nível de transação. Operações de leitura não requerem bloqueios de página ou linha; apenas bloqueios de tabela SCH-S são necessários. Ao ler linhas modificadas por outra transação, elas recuperam a versão da linha que existia na inicialização da transação. É possível usar o Isolamento do instantâneo em um banco de dados quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION estiver definida como ON. Por padrão, essa opção é definida como OFF para bancos de dados de usuários.

ObservaçãoObservação

O SQL Server não tem suporte para controle de versão de metadados. Por isso, há restrições nas operações de DDL que podem ser executadas em uma transação explícita que está sendo executada sob isolamento do instantâneo. As instruções de DDL a seguir não são permitidas sob isolamento do instantâneo depois de uma instrução BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME ou qualquer instrução CRL (Common Language Runtime) DDL. Essas instruções são permitidas quando você está usando isolamento do instantâneo em transações implícitas. Uma transação implícita, por definição, é uma instrução única que torna possível impor semânticas de isolamento do instantâneo, até mesmo com instruções DDL. Violações desse princípio podem causar o erro 3961: “Falha na transação de isolamento do instantâneo no banco de dados '%. * ls' porque o objeto acessado pela instrução foi modificado por uma instrução de DDL em outra transação simultânea desde o início dessa transação. Isso não é permitido porque os metadados não têm controle de versão. Uma atualização simultânea para metadados poderia gerar inconsistências se misturada com isolamento do instantâneo."

A tabela a seguir mostra os efeitos colaterais de simultaneidade habilitados por níveis de isolamento diferentes.

Nível de isolamento

Leitura suja

Leitura não repetível

Fantasma

Leitura não confirmada

Sim

Sim

Sim

Leitura confirmada

Não

Sim

Sim

Leitura repetível

Não

Não

Sim

Instantâneo

Não

Não

Não

Serializável

Não

Não

Não

Para obter mais informações sobre os tipos específicos de bloqueio ou controle de versão de linha controlado para cada nível de isolamento da transação, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).