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 (confirmadas) ou desfeitas (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 confirmada, ou criar uma transação externa que confirme uma transação interna 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 multithreading.

Terminologia¶

Neste tópico:

• O termo DDL inclui instruções CTAS (CREATE TABLE AS SELECT), bem como outras instruções DDL que definem objetos de banco de dados.

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

• O termo instrução de consulta refere-se a declaraçõ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. No entanto, os usuários devem evitar instruções BEGIN TRANSACTION extras, pois elas tornam muito mais difícil para os leitores humanos emparelhar uma instrução COMMIT (ou ROLLBACK) com a instrução BEGIN TRANSACTION correspondente.

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 uma BEGIN TRANSACTION ou COMMIT/ROLLBACK explícita. 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 iniciar dependem 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 encerrada por uma COMMIT ou ROLLBACK explícita.

• Uma transação iniciada explicitamente pode ser encerrada por uma COMMIT ou ROLLBACK implícita.

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, ainda será possível confirmar a transação, em vez de revertê-la.

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 serão aplicadas.

Por exemplo, considere o código a seguir, que insere dois valores válidos e um valor inválido em uma tabela:

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 com falha forem executadas, a saída da instrução SELECT final incluirá as linhas com valores inteiro 1 e 2, mesmo que uma das outras instruções na transação tenha falhado.

Transações e multithreading¶

Embora várias sessões não possam compartilhar a mesma transação, vários threads que usam uma única conexão compartilham a mesma sessão e, portanto, compartilham a mesma transação. Esse comportamento pode levar a resultados inesperados, como um thread revertendo o trabalho que foi feito em outro thread.

Essa situação pode ocorrer quando um aplicativo cliente usa um driver Snowflake (como o driver Snowflake JDBC) ou um conector (como o Snowflake Connector para Python) com vários threads. Se dois ou mais threads compartilham a mesma conexã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 my_procedure()
  ...
  AS
  $$
    statement X;
    statement Y;
  $$;

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

BEGIN TRANSACTION;
statement W;
statement X;
statement Y;
statement Z;
COMMIT;

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 próximo exemplo, 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 fechado.

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

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 desabilitado, tenha 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. Essa 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 a BEGIN TRANSACTION quanto o COMMIT dentro do procedimento armazenado, conforme 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;

Neste exemplo, o único valor 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 retorna um erro porque a CALL para inner_sp2() falhou.

2. Como a CALL par a inner_sp2() falhou e como essa instrução CALL estava em outer_sp1(), o procedimento armazenado outer_sp1() em si 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 para valores osp1_delta e osp1_omega nunca são executadas.

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

Parâmetros de tempo limite de bloqueio¶

Dois parâmetros controlam o tempo limite para bloqueios: LOCK_TIMEOUT e HYBRID_TABLE_LOCK_TIMEOUT.

ALTER SESSION SET LOCK_TIMEOUT=7200;
SHOW PARAMETERS LIKE 'lock_timeout';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------+
| key          | value | default | level   | description                                                                   | type   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  | NUMBER |
|              |       |         |         | 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.                                                                 |        |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------+

ALTER SESSION SET HYBRID_TABLE_LOCK_TIMEOUT=600;
SHOW PARAMETERS LIKE 'hybrid_table_lock_timeout';

+---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------|
| key                       | value | default | level   | description                                                                    | type   |
|---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------+
| HYBRID_TABLE_LOCK_TIMEOUT | 600   | 3600    | SESSION | Number of seconds to wait while trying to acquire locks, before timing out and | NUMBER |
|                           |       |         |         | aborting the statement. A value of 0 turns off lock waiting i.e. the statement |        |
|                           |       |         |         | must acquire the lock immediately or abort.                                    |        |
+---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------+

Deadlocks¶

Deadlocks podem ocorrer quando transações simultâneas aguardam recursos que estão bloqueados entre si.

Observe as seguintes regras:

• Deadlocks não podem ocorrer enquanto instruções de consulta de confirmação automática estiverem sendo executadas simultaneamente. Isso é verdadeiro tanto para tabelas padrão quanto para tabelas híbridas porque as instruções SELECT são sempre somente leitura.

• Deadlocks não podem ocorrer com operações DML de autocommit em tabelas padrão, mas podem ocorrer com operações DML de autocommit em tabelas híbridas.

• Deadlocks podem ocorrer quando transações são iniciadas explicitamente e múltiplas instruções são executadas em cada transação. O Snowflake detecta deadlocks e escolhe a instrução mais recente que faz parte do deadlock como 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.

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:

• E bloqueia outra transação de adquirir um bloqueio na mesma tabela e fica ocioso por 5 minutos, é automaticamente anulado e revertido.

• E não bloqueia outras transações de modificar a mesma tabela e se tiver mais de 4 horas, ela será automaticamente anulada e revertida.

• E lê ou grava em tabelas híbridas e fica ociosa por 5 minutos, é automaticamente anulada e revertida, independentemente de bloquear outras transações de modificar a mesma tabela.

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.

Consulte também Visibilidade de transação e bloqueio para tabelas híbridas.

Monitoramento de transações e bloqueios¶

É possível usar o comando SHOW TRANSACTIONS para retornar uma lista de transações executadas pelo usuário atual (em todas as sessões desse usuário) ou por todos os usuários em todas as sessões da conta. O exemplo a seguir é para as sessões do usuário atual.

SHOW TRANSACTIONS;

+---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------+
|                  id | user    |         session | name                                 | started_on                    | state   | scope |
|---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------|
| 1721165674582000000 | CALIBAN | 186457423713330 | 551f494d-90ed-438d-b32b-1161396c3a22 | 2024-07-16 14:34:34.582 -0700 | running |     0 |
| 1721165584820000000 | CALIBAN | 186457423749354 | a092aa44-9a0a-4955-9659-123b35c0efeb | 2024-07-16 14:33:04.820 -0700 | running |     0 |
+---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------+

Cada transação do Snowflake recebe um ID de transação exclusivo. O valor id é um inteiro assinado de 64 bits (longo). O intervalo de valores é de -9.223.372.036.854.775.808 (-2 ⁶³) a 9.223.372.036.854.775.807 (2 ⁶³ – 1).

Também é possível usar a função CURRENT_TRANSACTION para retornar o ID de transação da transação atualmente em execução na sessão.

SELECT CURRENT_TRANSACTION();

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1721161383427000000   |
+-----------------------+

Se você souber o ID da transação que deseja monitorar, poderá usar o comando DESCRIBE TRANSACTION para retornar detalhes sobre a transação, enquanto ela ainda estiver em execução ou depois de ter sido confirmada ou anulada. Por exemplo:

DESCRIBE TRANSACTION 1721161383427000000;

+---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------+
|                  id | user    |        session | name                                 | started_on                    | state     | ended_on                      |
|---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------|
| 1721161383427000000 | CALIBAN | 10363774361222 | 7db0ec5c-2e5d-47be-ac37-66cbf905668b | 2024-07-16 13:23:03.427 -0700 | committed | 2024-07-16 13:24:14.402 -0700 |
+---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------+

Visibilidade de transação e bloqueio para tabelas híbridas¶

Ao analisar a saída de comandos e exibições para transações que acessam tabelas híbridas ou bloqueios em linhas de tabelas híbridas, observe o seguinte comportamento:

• As transações são listadas somente se estiverem bloqueando outras transações ou se estiverem bloqueadas.

• Tenha em mente que as transações que acessam tabelas híbridas mantêm bloqueios em nível de linha (tipo ROW). Se duas transações acessam linhas diferentes na mesma tabela, elas não bloqueiam uma à outra.

• As transações são listadas somente se uma transação bloqueada estiver bloqueada por mais de 5 segundos.

• Quando uma transação não está mais bloqueada, ela ainda pode aparecer na saída, mas por não mais que por 15 segundos.

Da mesma forma, na saída de SHOW LOCKS, as seguintes regras se aplicam:

• Um bloqueio é listado somente se uma transação detém o bloqueio e a outra transação está bloqueada naquele bloqueio específico.

• Na coluna type, os bloqueios de tabela híbrida mostram ROW.

• A coluna resource sempre mostra o ID da transação de bloqueio. (A transação bloqueada é bloqueada pela transação com este ID.)

• Em muitos casos, consultas em tabelas híbridas não geram IDs de consulta. Consulte Tabelas híbridas.

Por exemplo:

SHOW LOCKS;

+---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------+
| resource            | type |         transaction | transaction_started_on        | status  | acquired_on | query_id                             |
|---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------|
| 1721165584820000000 | ROW  | 1721165584820000000 | 2024-07-16 14:33:04.820 -0700 | HOLDING | NULL        |                                      |
| 1721165584820000000 | ROW  | 1721165674582000000 | 2024-07-16 14:34:34.582 -0700 | WAITING | NULL        | 01b5b715-0002-852b-0000-a99500665352 |
+---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------+

Em Exibição LOCK_WAIT_HISTORY, a saída se comporta da seguinte forma:

• As colunas requested_at e acquired_at definem quando os bloqueios em nível de linha foram solicitados e adquiridos, sujeitos às regras gerais para relatar atividade de transação com tabelas híbridas.

• As colunas lock_type e object_name mostram o valor Row.

• As colunas schema_id e schema_name estão sempre vazias (0 e NULL, respectivamente).

• A coluna object_id sempre mostra o ID do objeto de bloqueio.

• A coluna blocker_queries é uma matriz JSON com exatamente um elemento, que mostra a transação de bloqueio.

• Mesmo que várias transações sejam bloqueadas na mesma linha, elas serão mostradas como várias linhas na saída.

Por exemplo:

SELECT query_id, object_name, transaction_id, blocker_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.LOCK_WAIT_HISTORY
  WHERE requested_at >= DATEADD('hours', -48, CURRENT_TIMESTAMP()) LIMIT 1;

+--------------------------------------+-------------+---------------------+---------------------------------------------------------+
| QUERY_ID                             | OBJECT_NAME |      TRANSACTION_ID | BLOCKER_QUERIES                                         |
|--------------------------------------+-------------+---------------------+---------------------------------------------------------|
| 01b5b715-0002-852b-0000-a99500665352 | Row         | 1721165674582000000 | [                                                       |
|                                      |             |                     |   {                                                     |
|                                      |             |                     |     "is_snowflake": false,                              |
|                                      |             |                     |     "query_id": "01b5b70d-0002-8513-0000-a9950065d43e", |
|                                      |             |                     |     "transaction_id": 1721165584820000000               |
|                                      |             |                     |   }                                                     |
|                                      |             |                     | ]                                                       |
+--------------------------------------+-------------+---------------------+---------------------------------------------------------+

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

Como usar o 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 |
+-----------+