Transações com Tabelas Otimizadas para Memória

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este artigo descreve todos os aspetos das transações que são específicas para tabelas com otimização de memória e procedimentos armazenados compilados nativamente.

Os níveis de isolamento de transação no SQL Server se aplicam de forma diferente a tabelas com otimização de memória versus tabelas baseadas em disco, e os mecanismos subjacentes são diferentes. A compreensão das diferenças ajuda o programador a projetar um sistema de alto rendimento. O objetivo da integridade da transação é compartilhado em todos os casos.

Para condições de erro específicas para transações em tabelas com otimização de memória, vá para a seção Deteção de conflitos e lógica de novas tentativas.

Para obter informações gerais, consulte SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Pessimista versus otimista

As diferenças funcionais devem-se a abordagens pessimistas versus otimistas da integridade das transações. As tabelas com otimização de memória usam a abordagem otimista:

• A abordagem pessimista usa bloqueios para bloquear potenciais conflitos antes que eles ocorram. Os bloqueios são feitos quando a instrução é executada e liberados quando a transação é cometida.

• A abordagem otimista deteta conflitos à medida que ocorrem e executa verificações de validação no momento da confirmação.

  • O erro 1205, um deadlock, não pode ocorrer para uma tabela com otimização de memória.

A abordagem otimista é menos sobrecarga e geralmente é mais eficiente, em parte porque os conflitos de transação são incomuns na maioria dos aplicativos. A principal diferença funcional entre as abordagens pessimista e otimista é que, se ocorrer um conflito, na abordagem pessimista você espera, enquanto na abordagem otimista uma das transações falha e precisa ser repetida pelo cliente. As diferenças funcionais são maiores quando o nível de isolamento REPEATABLE READ está em vigor, e são as maiores para o nível SERIALIZABLE.

Modos de iniciação de transação

O SQL Server tem os seguintes modos para iniciar transações:

• Autocommit - O início de uma consulta simples ou instrução DML abre implicitamente uma transação e o final da instrução confirma implicitamente a transação. A confirmação automática é o padrão.

  • No modo de confirmação automática, geralmente não é necessário codificar uma dica de tabela sobre o nível de isolamento de transação na tabela otimizada para memória na cláusula FROM.

• Explícito - O seu Transact-SQL contém o código BEGIN TRANSACTION, juntamente com um possível COMMIT TRANSACTION. Duas ou mais declarações podem ser encaixadas na mesma transação.

  • No modo explícito, você deve usar a opção de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ou codificar uma dica de tabela sobre o nível de isolamento de transação na tabela com otimização de memória na cláusula FROM.

• Implícito - Quando SET IMPLICIT_TRANSACTION ON está em vigor. Talvez um nome melhor tivesse sido IMPLICIT_BEGIN_TRANSACTION, porque tudo o que essa opção faz é executar implicitamente o equivalente a uma transação BEGIN explícita antes de cada instrução UPDATE se 0 = @@trancount. Portanto, cabe ao seu código T-SQL eventualmente emitir uma COMMIT TRANSACTION explícita.

• ATOMIC BLOCK - Todas as instruções em blocos ATOMIC são sempre executadas como parte de uma única transação. Ou as ações do bloco atômico como um todo são comprometidas com o sucesso, ou as ações são todas revertidas quando ocorre uma falha. Cada procedimento armazenado compilado nativamente exige um bloco ATOMIC.

Exemplo de código com modo explícito

O seguinte script de Transact-SQL interpretado usa:

• Uma transação explícita.
• Uma tabela com otimização de memória, chamada dbo. Order_mo.
• O contexto de nível de isolamento de transação READ COMMITTED.

Portanto, é necessário ter uma sugestão de tabela na tabela otimizada para memória. A sugestão deve corresponder a SNAPSHOT ou a um nível ainda mais isolante. No caso do exemplo de código, a sugestão é usar WITH (SNAPSHOT). Se essa dica for removida, o script sofrerá um erro 41368, para o qual uma nova tentativa automatizada seria inadequada:

Erro 41368

O acesso a tabelas otimizadas de memória usando o nível de isolamento READ COMMITTED é suportado apenas para transações de confirmação automática. Não é suportado para transações explícitas ou implícitas. Forneça um nível de isolamento suportado para a tabela otimizada para memória, usando um indicador de tabela, como WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

A necessidade da dica WITH (SNAPSHOT) pode ser evitada através do uso da opção MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOTde banco de dados. Quando essa opção é definida como ON, o acesso a uma tabela com otimização de memória em um nível de isolamento baixo é automaticamente elevado para o isolamento SNAPSHOT.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Controle de versão de linha

As tabelas com otimização de memória usam um sistema de versionamento de linhas altamente sofisticado que torna a abordagem otimista eficiente, mesmo no nível de isolamento mais estrito de SERIALIZABLE. Para obter detalhes, consulte Introdução às Tabelas Memória-Otimizadas.

As tabelas baseadas em disco têm indiretamente um sistema de versionamento de linha quando READ_COMMITTED_SNAPSHOT ou o nível de isolamento SNAPSHOT está em vigor. Este sistema é baseado em tempdb, enquanto as estruturas de dados otimizadas em memória têm versionamento de linhas integrado, para máxima eficiência.

Níveis de isolamento

A tabela a seguir lista os níveis possíveis de isolamento de transação, em sequência do menor isolamento para o maior. Para obter detalhes sobre conflitos que podem ocorrer e repetir a lógica para lidar com esses conflitos, consulte Deteção de conflitos e lógica de novas tentativas.

Fases e tempo de vida da transação

Quando uma tabela com otimização de memória está envolvida, o tempo de vida de uma transação progride pelas fases, conforme exibido na imagem a seguir:

Seguem-se descrições das fases.

Processamento regular: Fase 1 (de 3)

• Esta fase é composta pela execução de todas as consultas e das instruções DML presentes na consulta.
• Durante esta fase, as instruções visualizam a versão das tabelas otimizadas para memória a partir da hora lógica de início da transação.

Validação: Fase 2 (de 3)

• A fase de validação começa com a atribuição da hora de término, marcando assim a transação como logicamente concluída. Essa conclusão torna todas as alterações da transação visíveis para outras transações que dependem dessa transação. As transações dependentes não podem ser confirmadas até que esta transação tenha sido confirmada com êxito. Além disso, as transações que mantêm essas dependências não têm permissão para retornar conjuntos de resultados ao cliente, para garantir que o cliente veja apenas os dados que foram confirmados com êxito no banco de dados.
• Esta fase compreende a leitura repetível e a validação serializável. Para validação de leitura repetível, ele verifica se alguma das linhas lidas pela transação foi atualizada desde então. Para validação serializável, ele verifica se alguma linha foi inserida em qualquer intervalo de dados verificado por essa transação. De acordo com a tabela em Níveis e Conflitos de Isolamento, tanto a leitura repetível quanto a validação serializável podem ocorrer ao usar o isolamento por instantâneo, para validar a consistência de restrições de chaves exclusivas e estrangeiras.

Processamento de confirmação: Fase 3 (de 3)

• Durante a fase de confirmação, as alterações feitas em tabelas duráveis são gravadas no log e o log é gravado no disco. Em seguida, o controle é retornado ao cliente.
• Após a conclusão do processamento de confirmação, todas as transações dependentes são notificadas de que podem ser confirmadas.

Como sempre, você deve tentar manter suas unidades transacionais de trabalho tão mínimas e breves quanto for válido para suas necessidades de dados.

Deteção de conflitos e lógica de repetição

Há dois tipos de condições de erro relacionadas à transação que fazem com que uma transação falhe e seja revertida. Na maioria dos casos, uma vez que tal falha ocorre, a transação precisa ser repetida, semelhante a quando ocorre um impasse.

• Conflitos entre transações simultâneas. Estes são conflitos de atualização e falhas de validação, e podem ser devido a violações de nível de isolamento de transação ou violações de restrição.
• Falhas de dependência. Estes resultam de transações das quais depende não conseguirem ser concluídas, ou do número de dependências ter aumentado demasiado.

A seguir estão as condições de erro que podem fazer com que as transações falhem quando acessam tabelas com otimização de memória.

Lógica de repetição

Quando uma transação falha devido a qualquer uma das condições acima mencionadas, a transação deve ser repetida.

A lógica de repetição pode ser implementada no lado do cliente ou servidor. A recomendação geral é implementar a lógica de tentativas no lado do cliente, pois é mais eficiente e permite lidar com conjuntos de resultados retornados pela transação antes que ocorra a falha.

Repetir exemplo de código T-SQL

A lógica de repetição do lado do servidor usando T-SQL só deve ser usada para transações que não retornam conjuntos de resultados para o cliente. Caso contrário, novas tentativas podem potencialmente resultar em conjuntos de resultados adicionais além daqueles previstos sendo devolvidos ao cliente.

O seguinte script T-SQL interpretado ilustra como a lógica de repetição pode ser aplicada aos erros relacionados a conflitos de transação envolvendo tabelas otimizadas para memória.

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry INT = 10;

    WHILE (@retry > 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0;  -- //Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry -= 1;

            IF (@retry > 0 AND
                ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)
                )
            BEGIN
                IF XACT_STATE() = -1
                    ROLLBACK TRANSACTION;

                WAITFOR DELAY '00:00:00.001';
            END
            ELSE
            BEGIN
                PRINT 'Suffered an error for which Retry is inappropriate.';
                THROW;
            END
        END CATCH

    END -- //While loop
END;
GO

--  EXECUTE usp_update_salesorder_dates;

Transação entre contêineres

Uma transação é chamada de transação entre contêineres se:

• Acede a uma tabela otimizada para memória a partir do Transact-SQL interpretado;
• Executa um proc nativo quando uma transação já está aberta (XACT_STATE() = 1).

O termo "cross-container" deriva do fato de que a transação é executada nos dois contêineres de gerenciamento de transações, um para tabelas baseadas em disco e outro para tabelas otimizadas para memória.

Em uma única transação entre contêineres, diferentes níveis de isolamento podem ser usados para acessar tabelas baseadas em disco e otimizadas para memória. Essa diferença é expressa por meio de dicas explícitas de tabela, como WITH (SERIALIZABLE) ou por meio da opção de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, que eleva implicitamente o nível de isolamento da tabela com otimização de memória para instantâneo se o TRANSACTION ISOLATION LEVEL estiver configurado como READ COMMITTED ou READ UNCOMMITTED.

No seguinte exemplo de código Transact-SQL:

• A tabela baseada em disco, Table_D1, é acessada usando o nível de isolamento READ COMMITTED.
• A tabela otimizada para memória Table_MO7 é acessada usando o nível de isolamento SERIALIZABLE. Table_MO6 não tem um nível de isolamento associado específico, uma vez que as inserções são sempre consistentes e executadas essencialmente sob isolamento serializável.

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
go

BEGIN TRANSACTION;

    -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.

    SELECT * FROM Table_D1;


    -- Table_MO6 and Table_MO7 are memory-optimized tables.
    -- Table_MO7 is accessed using SERIALIZABLE isolation,
    --   while Table_MO6 does not have a specific isolation level.

    INSERT Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

Limitações

• Não há suporte para transações entre bancos de dados para tabelas com otimização de memória. Se uma transação acessar uma tabela com otimização de memória, a transação não poderá acessar nenhum outro banco de dados, exceto para:

  • banco de dados tempdb.
  • Modo de leitura apenas da base de dados principal.

• Não há suporte para transações distribuídas: quando BEGIN DISTRIBUTED TRANSACTION é usado, a transação não pode acessar uma tabela com otimização de memória.

Procedimentos armazenados compilados nativamente

• Em um processo nativo, o bloco ATOMIC deve especificar o nível de isolamento da transação para todo o bloco, como:

  • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• Nenhuma declaração explícita de controle de transação é permitida no corpo de um proc nativo. START TRANSACTION, ROLLBACK TRANSACTION e assim por diante, não são permitidos.

• Para obter mais informações sobre o controle de transações com blocos ATOMIC , consulte Atomic Blocks

Outros links de transação

• SET IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Níveis de isolamento baseados em controle de versão de linha no Mecanismo de Banco de Dados

• Controle a durabilidade da transação