Transações¶

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.

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.

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;

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.

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 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.

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

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

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

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;

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.

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.

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.

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.

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.

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;

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;

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;

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.

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 vê as alterações feitas por instruções anteriores executadas dentro da mesma transação, mesmo que essas alterações ainda não estejam confirmadas.

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:

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.

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);

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 "";
$$;

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;

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  |
+----+-------------+

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);

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 "";
$$;

Chame o procedimento armazenado:

begin transaction;
call update_data();
rollback;

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

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

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. |
+----------------------------+

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);

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 "";
$$;

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 "";
$$;

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;

-- 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 |
+----+------------+

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;

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      |
+----+---------------+

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;

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. |
+----------------------------------------------------------+

Chame o procedimento armazenado sem forçar um erro:

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