Transações com tabelas com otimização de memória

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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

Os níveis de isolamento de transação no SQL Server são aplicados de maneira diferente para tabelas com otimização de memória versus tabelas baseadas em disco rígido e os mecanismos subjacentes são diferentes. Uma compreensão das diferenças ajuda o programador a criar um sistema de alta taxa de transferência. O objetivo da integridade da transação é pertinente a todos os casos.

Para obter condições de erro específicas de transações em tabelas com otimização de memória, vá para a seção Detecção de conflito e lógica de repetição.

Para saber mais gerais, veja SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Pessimista versus otimista

As diferenças funcionais são devido a diferenças nas abordagens, pessimista ou otimista, à integridade da transação. As tabelas com otimização de memória usam a abordagem otimista:

• a abordagem pessimista usa travas para bloquear os conflitos em potencial antes de ocorrerem. Bloqueios são usados quando a instrução é executada e liberados quando a transação é confirmada.

• Uma abordagem otimista detecta conflitos conforme ocorrem e executa verificações de validação no momento da confirmação.

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

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

Modos de iniciação da transação

O SQL Server tem os seguintes modos de iniciação de transação:

• Confirmação automática – O início de uma consulta simples ou instrução DML abre implicitamente uma transação e o término da instrução implicitamente confirma 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 da transação na tabela com otimização de memória na cláusula FROM.

• Explícita - o Transact-SQL contém o código BEGIN TRANSACTION, juntamente com uma eventual COMMIT TRANSACTION. Duas ou mais instruções podem ser agrupadas na mesma transação.

  • No modo explícito, é necessário usar a opção de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ou um código de uma dica de tabela sobre o nível de isolamento da transação na tabela com otimização de memória da cláusula FROM.

• Implícita - quando SET IMPLICIT_TRANSACTION ON está em vigor. Talvez um nome melhor teria sido IMPLICIT_BEGIN_TRANSACTION, porque essas opções implicitamente realizam o equivalente a uma BEGIN TRANSACTION antes de cada instrução UPDATE se 0 = @@trancount. Portanto, depende do seu código T-SQL eventualmente emitir uma COMMIT TRANSACTION explícita.

• ATOMIC BLOCK – Todas as instruções nos blocos ATOMIC sempre são executadas como parte de uma única transação. As ações do bloco atômico como um todo serão confirmadas em caso de êxito ou as ações serão todas revertidas quando ocorrer uma falha. Cada procedimento armazenado originalmente compilado requer um bloco ATOMIC.

Exemplo de código com o modo Explicit

O script Transact-SQL interpretado a seguir usa:

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

Portanto, é necessário ter uma dica de tabela na tabela com otimização de memória. A dica deve ser para um SNAPSHOT ou nível com ainda mais isolamento. No caso do exemplo de código, a dica é WITH (SNAPSHOT). Se essa dica for removida, o script deverá sofrer um erro 41368, para o qual uma repetição automática seria inadequada:

Erro 41368

Há suporte para o acesso às tabelas com otimização de memória usando o nível de isolamento READ COMMITTED somente em transações de confirmação automática. Ele não tem suporte para transações implícitas ou explícitas. Forneça um nível de isolamento com suporte para a tabela com otimização de memória usando uma dica 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;

Pode-se evitar a necessidade da dica WITH (SNAPSHOT) com o uso da opção de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. Quando essa opção é definida como ON, o acesso a uma tabela com otimização de memória em um nível de isolamento inferior é elevado automaticamente 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 controle de versão de linha sofisticado que torna o nível de isolamento eficiente, até mesmo o nível de isolamento SERIALIZABLE, mais restrito. Para obter detalhes, veja Introdução às tabelas com otimização de memória.

As tabelas baseadas em disco indiretamente têm um sistema de controle de versão de linha quando READ_COMMITTED_SNAPSHOT ou SNAPSHOT está em vigor. Esse sistema baseia-se no tempdb, enquanto as estruturas de dados com otimização de memória têm controle de versão de linha embutido, para máxima eficiência.

Níveis de isolamento

A tabela a seguir lista os possíveis níveis de isolamento da transação, na sequência de isolamento do menor para o maior. Para obter detalhes sobre os conflitos que podem ocorrer e a lógica de repetição para lidar com esses conflitos, veja Detecção de conflito e lógica de repetição.

Tempo de vida e fases da transação

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

Seguem as descrições das fases.

Processamento regular: fase 1 (de 3)

• Essa fase é composta pela execução de todas as consultas e instruções DML na consulta.
• Durante essa fase, as instruções consideram a versão das tabelas com otimização de memória a partir da hora de início lógica 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 a transação como logicamente completa. Essa conclusão torna todas as alterações da transação visíveis para outras transações, que obtêm uma dependência nessa transação. As transações dependentes não têm permissão para confirmar até que essa transação seja confirmada com êxito. Além disso, as transações que mantêm essas dependências não poderão retornar conjuntos de resultados para o cliente, a fim de garantir que o cliente veja apenas os dados que foram confirmados com êxito no banco de dados.
• Essa fase compreende as validações de leitura repetida e serializável. Para a validação de leitura repetida, ela verifica se qualquer uma das linhas lidas pela transação foi atualizada desde então. Para a validação serializável, ela verifica se nenhuma linha foi inserida em nenhum intervalo de dados verificado por essa transação. De acordo com a tabela em Conflitos e níveis de isolamento, as validações de leitura repetida e serializável podem ocorrer ao usar o isolamento de instantâneo, para validar a consistência de restrições de chaves estrangeira e exclusiva.

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

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

Como sempre, você deve tentar manter suas unidades de trabalho transacionais mínimas e breves, conforme forem válidas para suas necessidades de dados.

Detecção de conflito e lógica de repetição

Há dois tipos de condições de erro relacionadas à transação que causam a falha e reversão de uma transação. Na maioria dos casos, quando ocorre uma falha desse tipo, a transação precisa ser repetida, de forma semelhante a quando ocorre um deadlock.

• Conflitos entre transações simultâneas. Esses são os conflitos de atualização e as falhas de validação e podem ocorrer devido a violações no nível de isolamento da transação ou a violações de restrição.
• Falhas de dependência. Isso é resultante da falha das transações dependentes ao serem confirmadas ou do aumento excessivo do número de dependências.

Veja abaixo as condições de erro que podem causar falhas nas transações ao acessarem as tabelas com otimização de memória.

Lógica de repetição

Quando uma transação falha devido a alguma das condições mencionadas acima, 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 repetição no lado do cliente, pois é mais eficiente, e permite lidar com os conjuntos de resultados retornados pela transação antes que ocorra a falha.

Repita o exemplo de código T-SQL

A lógica de repetição do lado do servidor usando o T-SQL deve ser usada somente em transações que não retornam os conjuntos de resultados ao cliente. Caso contrário, as tentativas podem possivelmente resultar em conjuntos de resultados adicionais, além daqueles antecipados que são retornados ao cliente.

O script T-SQL interpretado a seguir ilustra a aparência de uma lógica de repetição para os erros associados com conflitos de transações que envolvem tabelas com otimização de 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:

• Acessar uma tabela com otimização de memória de um Transact-SQL interpretado; ou
• Execute um procedimento nativo quando uma transação já estiver aberta (XACT_STATE() = 1).

O termo “entre contêineres” deriva do fato de que a transação é executada nos dois contêineres de gerenciamento de transações: um para as tabelas baseadas em disco e outro para as tabelas com otimização de memória.

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

No exemplo de código Transact-SQL a seguir:

• A tabela baseada em disco, Table_D1, é acessada usando o nível de isolamento READ COMMITTED.
• A tabela com otimização de memória Table_MO7 é acessada com o nível de isolamento SERIALIZABLE. Table_MO6 não tem um nível de isolamento associado específico, já que as inserções sempre são consistentes e executadas basicamente no 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

• As transações entre bancos de dados não têm suporte 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.
  • Somente leitura do banco de dados mestre.

• 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 declarar o nível de isolamento de transação para o bloco inteiro, por exemplo:

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

• Nenhuma instrução explícita de controle de transação é permitida dentro do corpo de um processo nativo. BEGIN TRANSACTION, ROLLBACK TRANSACTION, e assim por diante, são todos não permitidos.

• Para obter mais informações sobre o controle de transações com blocos ATOMIC, veja Blocos Atômicos

Outros links de transação

• SET IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Níveis de isolamento com base no controle de versão de linha no Mecanismo de Banco de Dados

• Controlar a durabilidade da transação