Transações

Uma transação é uma sequência de instruções SQL que são confirmadas ou revertidas como uma unidade.

Neste tópico:

Introdução

O que é uma transação?

Uma transação é uma sequência de instruções SQL que são processadas como uma unidade atômica. Todas as instruções na transação são aplicadas (ou seja, confirmadas) ou desfeitas (ou seja, revertidas) juntas. As operações do Snowflake garantem as propriedades ACID.

Uma transação pode incluir tanto a leitura quanto a escrita.

As transações seguem estas regras:

  • As transações nunca são “aninhadas”. Por exemplo, não é possível criar uma transação “externa” que reverta uma transação “interna” que tenha sido confirmada, ou criar uma transação “externa” que confirme uma transação “interna” que tenha sido revertida.

  • Uma transação está associada a uma única sessão. Múltiplas sessões não podem compartilhar a mesma transação. Para obter mais informações sobre como tratar transações com threads sobrepostos na mesma sessão, consulte Transações e multithread.

Terminologia

Neste tópico:

  • O termo “DDL” inclui instruções CTAS (CREATE TABLE AS SELECT ...), bem como outras instruções DDL.

  • O termo “DML” refere-se às instruções INSERT, UPDATE, DELETE, MERGE e TRUNCATE.

  • O termo “instrução de consulta” (query statement) refere-se às instruções SELECT e CALL.

Embora uma instrução CALL (que chama um procedimento armazenado) seja uma única instrução, o procedimento armazenado que ela chama pode conter várias instruções. Existem regras especiais para transações e procedimentos armazenados.

Transações explícitas

Uma transação pode ser iniciada explicitamente por meio da execução de uma instrução BEGIN. O Snowflake oferece suporte aos sinônimos BEGIN WORK e BEGIN TRANSACTION. O Snowflake recomenda usar BEGIN TRANSACTION.

Uma transação pode ser terminada explicitamente executando COMMIT ou ROLLBACK. O Snowflake oferece suporte ao sinônimo COMMIT WORK para COMMIT, e o sinônimo ROLLBACK WORK para ROLLBACK.

Em geral, se uma transação já estiver ativa, quaisquer instruções BEGIN TRANSACTION são ignoradas. Entretanto, os usuários devem evitar instruções BEGIN TRANSACTION extras, porque instruções BEGIN TRANSACTION extras dificultam a combinação de instruções COMMIT (ou ROLLBACK) com a BEGIN TRANSACTION correspondente por leitores humanos.

Uma exceção a esta regra envolve uma chamada de um procedimento armazenado aninhado. Para obter mais detalhes, consulte Transações com escopo.

Nota

As transações explícitas devem conter apenas instruções e DML instruções de consulta. Instruções DDL confirmam implicitamente transações ativas (para obter mais detalhes, consulte a seção DDL).

Transações implícitas

As transações podem ser iniciadas e terminadas implicitamente, sem um BEGIN TRANSACTION ou COMMIT / ROLLBACK explícito. As transações implícitas se comportam da mesma forma que as transações explícitas. Entretanto, as regras que determinam quando a transação implícita começa são diferentes das regras que determinam quando uma transação explícita começa.

As regras para parar e começar dependem de se a instrução é uma instrução DDL, uma instrução DML ou uma instrução de consulta. Se a instrução for uma instrução DML ou uma instrução de consulta, as regras dependem de se AUTOCOMMIT está ativado.

DDL

Cada instrução DDL é executada como uma transação separada.

Se uma instrução DDL for executada enquanto uma transação estiver ativa, a instrução DDL:

  1. Confirma implicitamente a transação ativa.

  2. Executa a instrução DDL como uma transação separada.

Como uma instrução DDL é sua própria transação, não é possível reverter uma instrução DDL; a transação contendo a DDL é concluída antes que você possa executar um ROLLBACK explícito.

Se uma instrução DDL for seguida imediatamente por uma instrução DML ou instrução de consulta, então essa instrução DML ou de consulta inicia implicitamente uma nova transação.

AUTOCOMMIT

O Snowflake oferece suporte a um parâmetro AUTOCOMMIT. A configuração padrão para AUTOCOMMIT está ativada.

Enquanto AUTOCOMMIT estiver ativado:

  • Cada instrução fora de uma transação explícita é tratada como se estivesse dentro de sua própria transação implícita de instrução única. Em outras palavras, essa instrução é automaticamente confirmada se for bem-sucedida e automaticamente revertida se falhar.

    Instruções dentro de uma transação explícita não são afetadas pelo AUTOCOMMIT. Por exemplo, as instruções dentro de um BEGIN TRANSACTION ... ROLLBACK explícito são revertidas mesmo que AUTOCOMMIT seja TRUE.

Enquanto AUTOCOMMIT está desativado:

  • Um BEGIN TRANSACTION implícito é executado:

    • Na primeira instrução DML ou instrução de consulta após o término de uma transação. Isto é verdade independentemente do que terminou a transação anterior (por exemplo, uma instrução DDL, uma confirmação explícita ou reversão).

    • Na primeira instrução DML ou instrução de consulta após desativar AUTOCOMMIT.

  • Um COMMIT implícito é executado nos seguintes casos (se uma transação já estiver ativa):

    • Execução de uma instrução DDL.

    • Execução de uma instrução ALTER SESSION SET AUTOCOMMIT, independentemente de o novo valor ser TRUE ou FALSE e independentemente de o novo valor ser ou não diferente do valor anterior. Por exemplo, mesmo se você definir AUTOCOMMIT para FALSE quando já estiver FALSE, um COMMIT implícito é executado.

  • Um ROLLBACK implícito é executado nos seguintes casos (se uma transação já estiver ativa):

    • Término de uma sessão.

    • Término de um procedimento armazenado.

      Independentemente de a transação ativa do procedimento armazenado ter sido iniciada explícita ou implicitamente, o Snowflake reverte a transação ativa e emite uma mensagem de erro.

Cuidado

Não altere as configurações AUTOCOMMIT dentro de um procedimento armazenado. Você receberá uma mensagem de erro.

Mistura de inícios e términos implícitos e explícitos de uma transação

Para evitar a escrita de códigos confusos, você deve evitar a mistura de inícios e términos implícitos e explícitos na mesma transação. Os elementos seguintes são permitidos, mas não recomendados:

  • Uma transação iniciada implicitamente pode ser terminada por um COMMIT ou ROLLBACK explícito.

  • Uma transação iniciada explicitamente pode ser terminada por um COMMIT ou ROLLBACK implícito.

Instruções com falha dentro de uma transação

Apesar de uma transação ser confirmada ou revertida como uma unidade, isso não é o mesmo que dizer que ela apresenta sucesso ou falha como uma unidade. Se uma instrução falhar dentro de uma transação, você ainda pode confirmar, em vez de reverter, a transação.

Quando uma instrução DML ou instrução CALL em uma transação falha, as alterações feitas por essa instrução que falhou são revertidas. Entretanto, a transação permanece ativa até que toda a transação seja confirmada ou revertida. Se a transação for confirmada, as alterações feitas pelas instruções bem-sucedidas são aplicadas.

Por exemplo, considere o seguinte código, que insere dois valores válidos e um valor inválido:

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;
Copy

Se as instruções após a instrução INSERT que falhou forem executadas, então a saída da instrução SELECT final inclui as linhas com valores inteiros 1 e 2, mesmo que uma das outras instruções na transação tenha falhado.

Nota

As instruções após a instrução INSERT que falhou podem ou não ser executadas, dependendo de como as instruções são executadas e como os erros são tratados.

Por exemplo:

  • Se essas instruções estiverem dentro de um procedimento armazenado escrito em linguagem Script Snowflake, a instrução INSERT que falhou lança uma exceção.

    • Se a exceção não for tratada, então o procedimento armazenado nunca é concluído e COMMIT nunca é executado, então a transação aberta é implicitamente revertida. Nesse caso, a tabela não tem 1 nem 2.

    • Se o procedimento armazenado tratar da exceção e confirmar as instruções antes da instrução INSERT que falhou, mas não executar as instruções após a instrução INSERT que falhou, então somente a linha com o valor 1 é armazenada na tabela.

  • Se essas instruções não estiverem dentro de um procedimento armazenado, então o comportamento depende de como as instruções são executadas. Por exemplo:

    • Se as instruções forem executadas por meio de Snowsight, a execução é interrompida no primeiro erro.

    • Se as instruções forem executadas por SnowSQL usando a opção -f (nome do arquivo), então a execução não é interrompida no primeiro erro, e as instruções após o erro são executadas.

Transações e multithread

Embora múltiplas sessões não possam compartilhar a mesma transação, múltiplos threads usando uma única conexão compartilham a mesma sessão, e, por isso, compartilham a mesma transação. Isso pode levar a resultados inesperados, como um thread revertendo o trabalho feito em outro thread.

Essa situação pode ocorrer quando um aplicativo cliente usando um driver Snowflake (como o Driver Snowflake JDBC) ou conector Snowflake (como o Conector do Snowflake para Python) passa por multithread. Se dois ou mais threads compartilham a mesma conexão, então esses threads também compartilham a transação atual nessa conexão. Um BEGIN TRANSACTION, COMMIT ou ROLLBACK por um único thread afeta todos os threads usando essa conexão compartilhada. Se os threads estiverem sendo executados de forma assíncrona, os resultados podem ser imprevisíveis.

Da mesma forma, alterar a configuração AUTOCOMMIT em um thread afeta a configuração AUTOCOMMIT em todos os outros threads que usam a mesma conexão.

O Snowflake recomenda que os programas cliente com multithread façam pelo menos uma das seguintes operações:

  • Usar uma conexão separada para cada thread.

    Observe que mesmo com conexões separadas, seu código ainda pode atingir condições de curso que geram resultados imprevisíveis; por exemplo, um thread pode excluir dados antes que outro thread tente atualizá-los.

  • Executar os threads de forma síncrona em vez de assíncrona, para controlar a ordem em que as etapas são executadas.

Transações e procedimentos armazenados

Em geral, as regras descritas nas seções anteriores também se aplicam aos procedimentos armazenados. Esta seção fornece informações adicionais específicas para os procedimentos armazenados.

Uma transação pode estar dentro de um procedimento armazenado, ou um procedimento armazenado pode estar dentro de uma transação; entretanto, uma transação não pode estar parcialmente dentro e parcialmente fora de um procedimento armazenado, ou iniciada em um procedimento armazenado e finalizada em um procedimento armazenado diferente.

Por exemplo:

  • Não é possível iniciar uma transação antes de chamar o procedimento armazenado e, em seguida, concluir a transação dentro do procedimento armazenado. Se você tentar fazer isso, o Snowflake relata um erro semelhante a Modifying a transaction that has started at a different scope is not allowed.

  • Não é possível iniciar uma transação dentro do procedimento armazenado e, depois, concluir a transação após retornar do procedimento. Se uma transação é iniciada dentro de um procedimento armazenado e ainda está ativa quando o procedimento armazenado termina, então ocorre um erro e a transação é revertida.

Essas regras também se aplicam aos procedimentos armazenados aninhados. Se o procedimento A chama o procedimento B, então B não pode concluir uma transação iniciada em A ou vice-versa. Cada BEGIN TRANSACTION em A deve ter um COMMIT (ou ROLLBACK) correspondente em A, e cada BEGIN TRANSACTION em B deve ter um COMMIT (ou ROLLBACK) correspondente em B.

Se um procedimento armazenado contém uma transação, essa transação pode conter parte ou a totalidade do corpo do procedimento armazenado. Por exemplo, no procedimento armazenado a seguir, apenas algumas das instruções estão em uma transação. (Este exemplo e vários exemplos subsequentes utilizam pseudocódigo para simplificar).

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;
Copy

Transações não sobrepostas

As seções abaixo descrevem:

  • Como usar um procedimento armazenado dentro de uma transação.

  • Como usar uma transação dentro de um procedimento armazenado.

Como usar um procedimento armazenado dentro de uma transação

No caso mais simples, considera-se que um procedimento armazenado está dentro de uma transação se as seguintes condições forem satisfeitas:

  • Um BEGIN TRANSACTION é executado antes que o procedimento armazenado seja chamado.

  • O COMMIT (ou ROLLBACK) correspondente é executado após a conclusão do procedimento armazenado.

  • O corpo do procedimento armazenado não contém um BEGIN TRANSACTION ou COMMIT (ou ROLLBACK) explícito ou implícito.

O procedimento armazenado dentro da transação segue as regras da transação delimitadora:

  • Se a transação for confirmada, então todas as instruções dentro do procedimento são confirmadas.

  • Se a transação for revertida, então todas as instruções dentro do procedimento são revertidas.

O seguinte pseudocódigo mostra um procedimento armazenado chamado inteiramente dentro de uma transação:

CREATE PROCEDURE my_procedure()
...
AS
$$
    statement X;
    statement Y;
$$;

BEGIN TRANSACTION;
statement W;
CALL my_procedure();
statement Z;
COMMIT;
Copy

Isto é equivalente a executar a seguinte sequência de instruções:

begin;
statement W;
statement X;
statement Y;
statement Z;
commit;
Copy

Como usar uma transação em um procedimento armazenado

Você pode executar zero, uma ou mais transações dentro de um procedimento armazenado. O seguinte pseudocódigo mostra um exemplo de duas transações em um procedimento armazenado:

CREATE PROCEDURE p1()
...
$$
    begin transaction;
    statement C;
    statement D;
    commit;

    begin transaction;
    statement E;
    statement F;
    commit;
$$;
Copy

O procedimento armazenado poderia ser chamado como mostrado abaixo:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;
Copy

Isto é equivalente a executar a seguinte sequência:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

BEGIN TRANSACTION;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;
Copy

Neste código, quatro transações separadas são executadas. Cada transação começa e termina fora do procedimento, ou começa e termina dentro do procedimento. Nenhuma transação é dividida em um limite de procedimento: parcialmente dentro e parcialmente fora do procedimento armazenado. Nenhuma transação é aninhada em outra transação.

Transações com escopo

Um procedimento armazenado que contém uma transação pode ser chamado de dentro de outra transação. Por exemplo, uma transação dentro de um procedimento armazenado pode incluir uma chamada para outro procedimento armazenado que contenha uma transação.

O Snowflake não trata a transação interna como aninhada; em vez disso, a transação interna é uma transação separada. O Snowflake as chama de “transações autônomas com escopo” (ou simplesmente “transações com escopo”).

O ponto inicial e o ponto final de cada transação com escopo determinam quais instruções são incluídas na transação. O início e o fim podem ser explícitos ou implícitos. Cada instrução SQL é parte de apenas uma transação. Um ROLLBACK ou COMMIT delimitador não desfaz um COMMIT ou ROLLBACK delimitado.

Nota

Os termos “interno” e “externo” são comumente usados ao descrever operações aninhadas, como chamadas de procedimentos armazenados aninhados. Entretanto, as transações no Snowflake não são verdadeiramente “aninhadas”; portanto, para reduzir a confusão ao se referir às transações, este documento costuma usa os termos “delimitado” e “delimitador”, em vez de “interno” e “externo”.

O diagrama abaixo mostra dois procedimentos armazenados e duas operações com escopo. Neste exemplo, cada procedimento armazenado contém sua própria transação independente. O primeiro procedimento armazenado chama o segundo procedimento armazenado, de modo que os procedimentos se sobrepõem no tempo, no entanto, eles não se sobrepõem no conteúdo. Todas as instruções dentro da caixa interna sombreada estão em uma transação; todas as outras instruções estão em outra transação.

Illustration of two stored procedures, each with its own scoped transaction.

No exemplo seguinte, os limites da transação são diferentes dos limites do procedimento armazenado; a transação que começa no procedimento armazenado externo inclui algumas, mas não todas, as instruções no procedimento armazenado interno.

Illustration of two stored procedures and two scoped transactions, in which one transaction includes some statements from the inner stored procedure as well as all statements from the outer stored procedure.

No código acima, o segundo procedimento armazenado contém algumas instruções (SP2_T1_S2 e SP2_T1_S3) que estão no escopo da primeira transação. Somente a instrução SP2_T2_S1, dentro da caixa interna sombreada, está no escopo da segunda transação.

O exemplo seguinte demonstra os problemas que ocorrem se uma transação não começa e termina dentro do mesmo procedimento armazenado. O exemplo contém o mesmo número de instruções COMMIT e instruções BEGIN. Entretanto, as instruções BEGIN e COMMIT não estão combinadas corretamente, de modo que este exemplo contém dois erros:

  • O procedimento armazenado externo inicia uma transação com escopo, mas não a conclui explicitamente. Portanto, essa transação com escopo causa um erro no final desse procedimento armazenado, e a transação ativa é implicitamente revertida.

  • O segundo procedimento armazenado contém um COMMIT, mas não há um BEGIN correspondente nesse procedimento armazenado. Esse COMMIT não confirma a transação aberta iniciada no primeiro procedimento armazenado. Em vez disso, o COMMIT sem correspondente adequado causa um erro.

Illustration of two stored procedures that create improperly-scoped transactions.

O exemplo seguinte mostra três transações com escopo que se sobrepõem no tempo. Neste exemplo, o procedimento armazenado p1() chama outro procedimento armazenado p2() de dentro de uma transação, e p2() contém sua própria transação, portanto, a transação iniciada em p2() também é executada independentemente. (Este exemplo usa pseudocódigo).

CREATE PROCEDURE p2()
...
$$
    BEGIN TRANSACTION;
    statement C;
    COMMIT;
$$;

CREATE PROCEDURE p1()
...
$$
    BEGIN TRANSACTION;
    statement B;
    CALL p2();
    statement D;
    COMMIT;
$$;

BEGIN TRANSACTION;
statement A;
CALL p1();
statement E;
COMMIT;
Copy

Nessas três operações com escopo:

  • A transação que está fora de qualquer procedimento armazenado contém instruções A e E.

  • A transação no procedimento armazenado p1() contém instruções B e D

  • A transação em p2() contém a instrução C.

As regras para transações com escopo também se aplicam a chamadas recursivas de procedimentos armazenados. Uma chamada recursiva é apenas um tipo específico de chamada aninhada e segue as mesmas regras de transação que uma chamada aninhada.

Cuidado

A sobreposição de transações com escopo pode causar um deadlock se elas manipularem o mesmo objeto de banco de dados (por exemplo, uma tabela). As transações com escopo devem ser usadas somente quando necessário.

Quando AUTOCOMMIT estiver desativado, tenha um cuidado especial ao combinar transações implícitas e procedimentos armazenados. Se você acidentalmente deixar uma transação ativa no final de um procedimento armazenado, a transação é revertida.

Por exemplo, o seguinte exemplo de pseudocódigo causa um ROLLBACK implícito ao final do procedimento armazenado:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
COMMIT WORK;
Copy

Neste exemplo, o comando para definir AUTOCOMMIT confirma qualquer transação ativa. Uma nova transação não é iniciada imediatamente. O procedimento armazenado contém uma instrução DML, que implicitamente inicia uma nova transação. Esse BEGIN TRANSACTION implícito não tem um COMMIT ou ROLLBACK correspondente no procedimento armazenado. Como há uma transação ativa no final do procedimento armazenado, essa transação ativa é implicitamente revertida.

Se você quiser executar todo o procedimento armazenado em uma única transação, inicie a transação antes de chamar o procedimento armazenado e confirme a transação após a chamada:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
BEGIN TRANSACTION;
CALL p1;
COMMIT WORK;
Copy

Neste caso, BEGIN e COMMIT têm seus correspondentes adequados, e o código é executado sem erros.

Como alternativa, coloque tanto o BEGIN TRANSACTION quanto o COMMIT dentro do procedimento armazenado, como mostrado no exemplo de pseudocódigo a seguir:

CREATE PROCEDURE p1() ...
$$
    BEGIN TRANSACTION;
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
    COMMIT WORK;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
Copy

Se você não associa os blocos de BEGIN/COMMIT corretamente em uma transação com escopo, o Snowflake relata um erro. Esse erro pode ter outros impactos, como impedir que um procedimento armazenado seja concluído ou impedir que uma transação delimitadora seja confirmada. Por exemplo, no exemplo de pseudocódigo a seguir, algumas instruções no procedimento armazenado delimitador bem como o procedimento armazenado delimitado são revertidos:

CREATE or replace PROCEDURE outer_sp1()
...
AS
$$
    insert 'osp1_alpha';
    BEGIN WORK;
    insert 'osp1_beta';
    CALL inner_sp2();
    INSERT 'osp1_delta';
    COMMIT WORK;
    INSERT 'osp1_omega';
$$;

CREATE or replace PROCEDURE inner_sp2()
...
AS
$$
    BEGIN WORK;
    insert 'isp2';
    -- Missing COMMIT, so implicitly rolls back!
$$;

CALL outer_sp1();

SELECT * FROM st;
Copy

Neste exemplo, o único valor que é inserido é “osp1_alpha’. Nenhum dos outros valores são inseridos porque um COMMIT não está associado corretamente a um BEGIN. O erro é tratado da seguinte forma:

  1. Quando o procedimento inner_sp2() termina, o Snowflake detecta que o BEGIN em inner_sp2() não tem COMMIT (ou ROLLBACK) correspondente.

    1. O Snowflake implicitamente reverte a transação com escopo iniciada em inner_sp2().

    2. O Snowflake também emite um erro porque o CALL para inner_sp2() falhou.

  2. Como o CALL de inner_sp2() falhou, e como essa instrução CALL estava em outer_sp1(), o próprio procedimento armazenado outer_sp1() também falha e retorna um erro, em vez de continuar.

  3. Como outer_sp1() não termina a execução:

    • As instruções INSERT de valores “osp1_delta” e “osp1_omega” nunca são executadas.

    • A transação aberta em outer_sp1() é implicitamente revertida em vez de confirmada, portanto, a inserção do valor “osp1_beta” nunca é confirmada.

Nível de isolamento

READ COMMITTED é o único nível de isolamento atualmente aceito para tabelas.

Isolamento READ COMMITTED

Com o isolamento READ COMMITTED, uma instrução vê apenas os dados que foram confirmados antes do início da instrução. Ela nunca vê dados não confirmados.

Quando uma instrução é executada dentro de uma transação com várias instruções:

  • Uma instrução vê apenas os dados que foram confirmados antes do início da instrução. Duas instruções sucessivas na mesma transação podem ver dados diferentes se outra transação for confirmada entre a execução da primeira e da segunda instrução.

  • Uma instrução as alterações feitas por instruções anteriores executadas dentro da mesma transação, mesmo que essas alterações ainda não estejam confirmadas.

Bloqueio de recursos

As operações transacionais adquirem bloqueios em um recurso, como uma tabela, enquanto esse recurso está sendo modificado. Bloqueios impedem que outras instruções modifiquem o recurso até que o bloqueio seja liberado.

As seguintes diretrizes se aplicam na maioria das situações:

  • Operações COMMIT (incluindo AUTOCOMMIT e COMMITexplícito) bloqueiam recursos, mas geralmente apenas de forma breve.

  • As operações CREATE [ DYNAMIC] TABLE, CREATE STREAM e ALTER TABLE todas bloqueiam seus recursos subjacentes ao definir CHANGE_TRACKING = TRUE, mas em geral isso só é feito brevemente. Somente as operações UPDATE e DELETE DML são bloqueadas quando uma tabela é bloqueada. As operações INSERT NOT são bloqueadas.

  • As instruções UPDATE, DELETE e MERGE mantêm bloqueios que geralmente impedem que sejam executadas em paralelo com outras instruções UPDATE, DELETE e MERGE.

  • A maioria das instruções INSERT e COPY escreve apenas novas partições. Essas instruções geralmente podem ser executadas em paralelo com outras operações INSERT e COPY, e algumas vezes podem ser executadas em paralelo com uma instrução UPDATE, DELETE ou MERGE.

Os bloqueios mantidos por uma instrução são liberados em operações COMMIT ou ROLLBACK da transação.

Tempo limite de espera do bloqueio

Uma instrução bloqueada adquire um bloqueio para o recurso que estava esperando ou expira esperando que o recurso fique disponível. A duração de tempo (em segundos) do bloqueio de uma instrução pode ser configurada pela definição do parâmetro LOCK_TIMEOUT.

Por exemplo, para mudar o tempo limite do bloqueio para 2 horas (7200 segundos) para a sessão atual:

ALTER SESSION SET LOCK_TIMEOUT=7200;

SHOW PARAMETERS LIKE 'lock%';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
| key          | value | default | level   | description                                                                   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  |
|              |       |         |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the      |
|              |       |         |         | statement must acquire the lock immediately or abort. If multiple resources   |
|              |       |         |         | need to be locked by the statement, the timeout applies separately to each    |
|              |       |         |         | lock attempt.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
Copy

Deadlocks

Deadlocks ocorrem quando transações simultâneas estão esperando recursos que estão bloqueados entre si.

No Snowflake, não pode ocorrer um deadlock durante a execução simultânea de instruções DML ou instruções de consulta de confirmação automática. Entretanto, podem ocorrer deadlocks com transações explicitamente iniciadas e instruções múltiplas em cada transação. O Snowflake detecta os deadlocks e escolhe a instrução mais recente que faz parte do deadlock como a vítima. A instrução é revertida, mas a transação em si permanece ativa e deve ser confirmada ou revertida.

A detecção de bloqueios pode demorar.

Permissão de erros de instrução para anular transações

Para permitir que um erro de instrução dentro de uma transação anule a transação, defina o parâmetro TRANSACTION_ABORT_ON_ERROR no nível da sessão ou da conta.

Análise de transações bloqueadas com a exibição LOCK_WAIT_HISTORY

O Exibição LOCK_WAIT_HISTORY retorna detalhes de transações que podem ser úteis na análise de transações bloqueadas. Cada linha na saída inclui os detalhes de uma transação que está esperando por um bloqueio e os detalhes das transações que estão detendo este bloqueio ou esperando por ele.

Por exemplo, veja o cenário abaixo:

Example of blocked and blocker transactions.

Neste cenário, os seguintes dados são retornados:

  • A transação B é a transação que está esperando por um bloqueio.

  • A transação B solicitou o bloqueio no carimbo de data/hora T1.

  • A transação A é a transação que detém o bloqueio.

  • A consulta 2 na Transação A é a consulta do bloqueador.

A consulta 2 é a consulta do bloqueador porque é a primeira instrução na Transação A (a transação que detém o bloqueio) em que a Transação B (a transação que aguarda o bloqueio) começou a esperar.

No entanto, observe que uma consulta posterior na Transação A (consulta 5) também adquiriu o bloqueio. É possível que execuções simultâneas posteriores destas transações possam fazer com que a Transação B bloqueie em uma consulta diferente que adquira o bloqueio na Transação A. Portanto, você deve investigar todas as consultas na primeira transação do bloqueador.

Inspeção de uma instrução de longo prazo

  1. Consultar o Exibição QUERY_HISTORY do Account Usage para transações que esperaram por bloqueios nas últimas 24 horas:

    SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time,
           compilation_time, execution_time, transaction_blocked_time
      FROM snowflake.account_usage.query_history
      WHERE start_time >= dateadd('hours', -24, current_timestamp())
      AND transaction_blocked_time > 0
      ORDER BY transaction_blocked_time DESC;
    
    Copy

    Reveja os resultados da consulta e anote as IDs das consultas com valores altos TRANSACTION_BLOCKED_TIME.

  2. Para encontrar transações de bloqueio para as consultas identificadas na etapa anterior, consulte a exibição LOCK_WAIT_HISTORY para linhas com essas IDs de consulta:

    SELECT object_name, lock_type, transaction_id, blocker_queries
      FROM snowflake.account_usage.lock_wait_history
      WHERE query_id = '<query_id>';
    
    Copy

    Pode haver várias consultas na coluna blocker_queries nos resultados. Observe o transaction_id de cada consulta de bloqueador na saída.

  3. Consulte a exibição QUERY_HISTORY para cada transação na saída blocker_queries:

    SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time, compilation_time, execution_time
      FROM snowflake.account_usage.query_history
      WHERE transaction_id = '<transaction_id>';
    
    Copy

    Investigue os resultados da consulta. Se uma instrução na transação foi uma declaração DML e operou no recurso bloqueado, ela pode ter adquirido o bloqueio em algum momento durante a transação.

Comandos e funções da transação

O Snowflake fornece os seguintes comandos SQL para oferecer suporte às transações:

Além disso, o Snowflake fornece as seguintes funções de contexto para obter informações sobre as transações em uma sessão:

Anulação de transações

Se uma transação estiver em execução em uma sessão e a sessão se desconectar abruptamente, impedindo que a transação seja confirmada ou revertida, a transação é deixada em um estado de suspensão, incluindo os bloqueios que a transação está mantendo para os recursos. Se isso acontecer, pode ser necessário anular a transação.

Para anular uma transação em execução, o usuário que iniciou a transação ou um administrador de conta pode chamar a função do sistema SYSTEM$ABORT_TRANSACTION.

Se a transação não for anulada pelo usuário:

  • Se ela impedir outra transação de adquirir um bloqueio na mesma tabela e estiver ociosa por 5 minutos, ela é automaticamente anulada e revertida.

  • Se ela não impedir outras transações de modificar a mesma tabela e tiver mais que 4 horas, ela é automaticamente anulada e revertida.

Práticas recomendadas

  • Uma transação deve conter instruções relacionadas e que apresentam sucesso ou falha em conjunto, por exemplo, a retirada de dinheiro de uma conta e o depósito desse mesmo dinheiro em outra conta. Se ocorrer uma reversão, a pessoa que envia ou recebe fica com o dinheiro; o dinheiro nunca “desaparece” (é retirado de uma conta, mas nunca depositado na outra conta).

    Em geral, uma transação deve conter apenas instruções relacionadas. Fazer uma instrução menos granular significa que, quando uma transação é revertida, ela pode reverter um trabalho útil que na verdade não precisava ser revertido.

  • As transações maiores podem melhorar o desempenho em alguns casos.

    Embora o argumento anterior enfatize a importância de agrupar apenas instruções que realmente precisam ser confirmadas ou revertidas como um grupo, às vezes transações maiores podem ser úteis. No Snowflake, como na maioria dos bancos de dados, o gerenciamento das transações consome recursos. Por exemplo, inserir linhas 10 em uma transação é geralmente mais rápido e mais barato do que inserir uma linha em 10 transações separadas. A combinação de múltiplas instruções em uma única transação pode melhorar o desempenho.

  • Transações excessivamente grandes podem reduzir o paralelismo ou aumentar os deadlocks. Se você decidir agrupar instruções não relacionadas para melhorar o desempenho (como descrito no argumento anterior), tenha em mente que uma transação pode adquirir bloqueios para recursos, o que pode atrasar outras consultas ou resultar em deadlocks.

  • O Snowflake recomenda manter o AUTOCOMMIT habilitado e usar transações explícitas o máximo possível. O uso de transações explícitas permite que os leitores humanos vejam onde as transações começam e terminam com mais facilidade. Isso, combinado com o AUTOCOMMIT, torna menos provável que seu código apresente reversões involuntárias, por exemplo, ao final de um procedimento armazenado.

  • Evite alterar o AUTOCOMMIT apenas para iniciar uma nova transação implicitamente. Em vez disso, use BEGIN TRANSACTION para tornar mais óbvio onde uma nova transação começa.

  • Evite executar mais de uma instrução BEGIN TRANSACTION em uma linha. Instruções BEGIN TRANSACTION extras dificultam a identificação do início de uma transação e dificultam a associação de comandos COMMIT/ROLLBACK com os comandos BEGIN TRANSACTION correspondentes.

ID de transação

A cada transação do Snowflake é atribuída uma ID única de transação. A ID de transação é um número inteiro assinado de 64 bits (de comprimento). O intervalo de valores é de -9.223.372.036.854.775.808 (-2 63) a 9.223.372.036.854.775.807 (2 63 – 1).

Exemplos

Exemplo simples de transação com escopo e procedimento armazenado

Este é um exemplo simples de transações com escopo. O procedimento armazenado contém uma transação que insere uma linha com o valor 12 e depois é revertida. A transação externa é confirmada. A saída mostra que todas as linhas no escopo da transação externa são mantidas, enquanto que a linha no escopo da transação interna não é mantida.

Observe que como apenas parte do procedimento armazenado está dentro de sua própria transação, os valores inseridos pelas instruções INSERT que estão no procedimento armazenado, mas fora da transação do procedimento armazenado, são mantidos.

Crie duas tabelas:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
Copy

Crie o procedimento armazenado:

create procedure sp1()
returns varchar
language javascript
AS
$$
    // This is part of the outer transaction that started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

Chame o procedimento armazenado:

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1();
insert into tracker_1 values (09, 'outer_zulu');
commit;
Copy

Os resultados devem incluir 00, 11, 13 e 09. A linha com ID = 12 não deve ser incluída. Esta linha estava no escopo da transação delimitada, que foi revertida. Todas as outras linhas estavam no escopo da transação externa e foram confirmadas. Observe em particular que as linhas com IDs 11 e 13 estavam dentro do procedimento armazenado, mas fora da transação mais interna; elas estão no escopo da transação delimitadora e foram confirmadas com isso.

select id, name FROM tracker_1
union all
select id, name FROM tracker_2
order by id;
+----+-------------+
| ID | NAME        |
|----+-------------|
|  0 | outer_alpha |
|  9 | outer_zulu  |
| 11 | p1_alpha    |
| 13 | p1_charlie  |
+----+-------------+
Copy

Registro de informações independentemente do sucesso de uma transação

Este é um exemplo simples e prático de como utilizar uma transação com escopo. Neste exemplo, uma transação registra certas informações; essas informações registradas são preservadas independentemente do sucesso ou falha da transação. Essa técnica pode ser usada para rastrear todas as tentativas de ação, cada uma delas tendo sucesso ou não.

Crie duas tabelas:

create table data_table (id integer);
create table log_table (message varchar);
Copy

Crie o procedimento armazenado:

create procedure log_message(MESSAGE VARCHAR)
returns varchar
language javascript
AS
$$
    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into log_table values ('" + MESSAGE + "')"}
        );
    snowflake.execute (
        {sqlText: "commit"}
        );

    // Dummy value.
    return "";
$$;

create procedure update_data()
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into data_table (id) values (17)"}
        );
    snowflake.execute (
        {sqlText: "call log_message('You should see this saved.')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // Dummy value.
    return "";
$$;
Copy

Chame o procedimento armazenado:

begin transaction;
call update_data();
rollback;
Copy

A tabela de dados está vazia porque a transação foi revertida:

select * from data_table;
+----+
| ID |
|----|
+----+
Copy

Entretanto, a tabela de registro não está vazia; a inserção na tabela de registro foi feita em uma transação separada da inserção em data_table.

select * from log_table;
+----------------------------+
| MESSAGE                    |
|----------------------------|
| You should see this saved. |
+----------------------------+
Copy

Exemplos de transações com escopo e procedimentos armazenados

Os próximos exemplos utilizam as tabelas e procedimentos armazenados mostrados abaixo. Ao passar parâmetros apropriados, o chamador pode controlar onde as instruções BEGIN TRANSACTION, COMMIT e ROLLBACK são executadas dentro dos procedimentos armazenados.

Criar as tabelas:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
create table tracker_3 (id integer, name varchar);
Copy

Este procedimento é o procedimento armazenado delimitador e, dependendo dos parâmetros passados a ele, pode criar uma transação delimitadora.

create procedure sp1_outer(
    USE_BEGIN varchar,
    USE_INNER_BEGIN varchar,
    USE_INNER_COMMIT_OR_ROLLBACK varchar,
    USE_COMMIT_OR_ROLLBACK varchar
    )
returns varchar
language javascript
AS
$$
    // This should be part of the outer transaction started before this
    // stored procedure was called. This should be committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    // Call (and optionally begin/commit-or-rollback) an inner stored proc...
    var command = "call sp2_inner('";
    command = command.concat(USE_INNER_BEGIN);
    command = command.concat("', '");
    command = command.concat(USE_INNER_COMMIT_OR_ROLLBACK);
    command = command.concat( "')" );
    snowflake.execute (
        {sqlText: command}
        );
    if (USE_COMMIT_OR_ROLLBACK != '') {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

Este procedimento é o procedimento armazenado interno e, dependendo dos parâmetros passados a ele, pode criar uma transação delimitada.

create procedure sp2_inner(
    USE_BEGIN varchar,
    USE_COMMIT_OR_ROLLBACK varchar)
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (21, 'p2_alpha')"}
        );

    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_3 values (22, 'p2_bravo')"}
        );
    if (USE_COMMIT_OR_ROLLBACK != '')  {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    snowflake.execute (
        {sqlText: "insert into tracker_2 values (23, 'p2_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

Confirmação do nível intermediário de três níveis

Este exemplo contém 3 transações. Este exemplo confirma o nível “intermediário” (a transação delimitada pela transação mais externa e delimitadora da transação mais interna). Isto reverte as transações mais externas e mais internas.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'rollback', 'commit');
insert into tracker_1 values (09, 'outer_charlie');
rollback;
Copy

O resultado é que somente as linhas na transação intermediária (12, 21 e 23) são confirmadas. As linhas na transação externa e na transação interna não são confirmadas.

-- Should return only 12, 21, 23.
select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+------------+
| ID | NAME       |
|----+------------|
| 12 | p1_bravo   |
| 21 | p2_alpha   |
| 23 | p2_charlie |
+----+------------+
Copy

Reversão do nível intermediário de três níveis

Este exemplo contém 3 transações. Este exemplo reverte o nível “intermediário” (a transação delimitada pela transação mais externa e delimitadora da transação mais interna). Isso confirma as transações mais externas e mais internas.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'commit', 'rollback');
insert into tracker_1 values (09, 'outer_charlie');
commit;
Copy

O resultado é que todas as linhas, exceto as linhas na transação intermediária (12, 21 e 23), são confirmadas.

select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+---------------+
| ID | NAME          |
|----+---------------|
|  0 | outer_alpha   |
|  9 | outer_charlie |
| 11 | p1_alpha      |
| 13 | p1_charlie    |
| 22 | p2_bravo      |
+----+---------------+
Copy

Uso do tratamento de erros com transações em procedimentos armazenados

O código a seguir mostra um simples tratamento de erros para uma transação em um procedimento armazenado. Se o valor de parâmetro “fail” for passado, o procedimento armazenado tenta excluir de duas tabelas que existem e uma tabela que não existe, e o procedimento armazenado captura o erro, retornando uma mensagem de erro. Se o valor de parâmetro “fail” não for passado, o procedimento armazenado tenta excluir de duas tabelas que existem e obtém sucesso.

Crie as tabelas e o procedimento armazenado:

begin transaction;

create table parent(id integer);
create table child (child_id integer, parent_ID integer);

-- ----------------------------------------------------- --
-- Wrap multiple related statements in a transaction,
-- and use try/catch to commit or roll back.
-- ----------------------------------------------------- --
-- Create the procedure
create or replace procedure cleanup(FORCE_FAILURE varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "begin transaction;"} );
  try {
      snowflake.execute( {sqlText: "delete from child where parent_id = 1;"} );
      snowflake.execute( {sqlText: "delete from parent where id = 1;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "delete from no_such_table;"} );
          }
      snowflake.execute( {sqlText: "commit;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "rollback;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

commit;
Copy

Chame o procedimento armazenado e force um erro:

call cleanup('fail');
+----------------------------------------------------------+
| CLEANUP                                                  |
|----------------------------------------------------------|
| Failed: SQL compilation error:                           |
| Object 'NO_SUCH_TABLE' does not exist or not authorized. |
+----------------------------------------------------------+
Copy

Chame o procedimento armazenado sem forçar um erro:

call cleanup('do not fail');
+-----------+
| CLEANUP   |
|-----------|
| Succeeded |
+-----------+
Copy