Transactions¶

Qu’est-ce qu’une transaction ?¶

Une transaction est une séquence d’instructions SQL qui sont traitées comme une unité atomique. Toutes les instructions de la transaction sont soit appliquées (validées), soit annulées ensemble. Les transactions de Snowflake garantissent les propriétés ACID.

Une transaction peut inclure à la fois des lectures et des écritures.

Les transactions suivent ces règles :

• Les transactions ne sont jamais « imbriquées ». Par exemple, vous ne pouvez pas créer une transaction « externe » qui annulerait une transaction « interne » qui a été validée, ni créer une transaction « externe » qui validerait une transaction « interne » qui a été annulée.

• Une transaction est associée à une seule session. Plusieurs sessions ne peuvent pas partager la même transaction. Pour plus d’informations sur le traitement des transactions dont les threads se chevauchent dans la même session, voir Transactions et multithread.

Terminologie¶

Dans cette rubrique :

• Le terme « DDL » comprend les instructions CTAS (CREATE TABLE AS SELECT ...) ainsi que d’autres instructions DDL.

• Le terme « DML » fait référence aux instructions INSERT, UPDATE, DELETE, MERGE et TRUNCATE.

• Le terme « instruction de requête » fait référence aux instructions SELECT et CALL.

Bien qu’une instruction CALL (qui appelle une procédure stockée) soit une instruction unique, la procédure stockée qu’elle appelle peut contenir plusieurs instructions. Il existe des règles spéciales pour les procédures et les transactions stockées.

Transactions explicites¶

Une transaction peut être démarrée explicitement en exécutant une instruction BEGIN. Snowflake prend en charge les synonymes BEGIN WORK et BEGIN TRANSACTION. Snowflake recommande d’utiliser BEGIN TRANSACTION.

Une transaction peut être terminée explicitement en exécutant COMMIT ou ROLLBACK. Snowflake prend en charge le synonyme COMMIT WORK pour COMMIT, et le synonyme ROLLBACK WORK pour ROLLBACK.

En général, si une transaction est déjà active, les instructions BEGIN TRANSACTION sont ignorées. Les utilisateurs devraient toutefois éviter les instructions BEGIN TRANSACTION supplémentaires, car les instructions BEGIN TRANSACTION supplémentaires rendent beaucoup plus difficile le couplage des instructions COMMIT (ou ROLLBACK) par les lecteurs humains avec la BEGIN TRANSACTION correspondante.

Une exception à cette règle concerne un appel de procédure stocké imbriqué. Pour plus de détails, voir Transactions scopées.

Transactions implicites¶

Les transactions peuvent être commencées et terminées implicitement, sans BEGIN TRANSACTION ou COMMIT/ROLLBACK explicite. Les transactions implicites se comportent de la même manière que les transactions explicites. Toutefois, les règles qui déterminent le début de la transaction implicite sont différentes des règles qui déterminent le début d’une transaction explicite.

Les règles d’arrêt et de démarrage dépendent de la nature de l’instruction : DDL ou DML ou instruction d’interrogation. Si l’instruction est une instruction DML ou d’interrogation, les règles dépendent de l’activation ou non de AUTOCOMMIT.

Mélange des débuts et des fins implicites et explicites d’une transaction¶

Pour éviter d’écrire un code confus, vous devez éviter de mélanger des débuts et des fins implicites et explicites dans la même transaction. Les éléments suivants sont légaux, mais à éviter :

• Une transaction commencée implicitement peut être terminée par un COMMIT ou un ROLLBACK explicite.

• Une transaction commencée explicitement peut être terminée par un COMMIT ou un ROLLBACK implicite.

Échec des instructions dans le cadre d’une transaction¶

Bien qu’une transaction soit validée ou annulée en tant qu’unité, cela ne veut pas dire qu’elle réussit ou échoue en tant qu’unité. Si une instruction échoue dans le cadre d’une transaction, vous pouvez toujours valider, plutôt que d’annuler, la transaction.

Lorsqu’une instruction DML ou CALL dans une transaction échoue, les modifications apportées par cette instruction échouée sont annulées. Toutefois, la transaction reste active jusqu’à ce que la transaction entière soit validée ou annulée. Si la transaction est validée, les modifications apportées par les instructions réussies sont appliquées.

Prenons par exemple le code suivant, qui insère deux valeurs valides et une valeur non valide :

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;

Si les instructions après l’échec de l’instruction INSERT sont exécutées alors la sortie de l’instruction SELECT finale comprend les lignes avec les valeurs entières 1 et 2, même si l’une des autres instructions de la transaction a échoué.

Transactions et multithread¶

Bien que plusieurs sessions ne puissent pas partager la même transaction, plusieurs threads utilisant une seule connexion partagent la même session et partagent donc la même transaction. Cela peut entraîner des résultats inattendus, tels qu’un thread annulant le travail effectué dans un autre thread.

Cette situation peut se produire lorsqu’une application client utilisant un pilote Snowflake (tel que le pilote JDBC Snowflake) ou un connecteur (tel que le connecteur Snowflake pour Python) est multithread. Si deux threads ou plus partagent la même connexion, ces threads partagent également la transaction actuelle dans cette connexion. Un BEGIN TRANSACTION, COMMIT ou ROLLBACK par un thread affecte tous les threads utilisant cette connexion partagée. Si les threads s’exécutent de manière asynchrone, les résultats peuvent être imprévisibles.

De la même façon, la modification du paramètre AUTOCOMMIT dans un thread affecte le paramètre AUTOCOMMIT dans tous les autres threads qui utilisent la même connexion.

Snowflake recommande que les programmes client multithread effectuent au moins l’une des opérations suivantes :

• Utiliser une connexion distincte pour chaque thread.

  Notez que même avec des connexions séparées, votre code peut toujours atteindre des conditions de course qui génèrent une sortie imprévisible ; par exemple, un thread peut supprimer des données avant qu’un autre thread tente de les mettre à jour.

• Exécuter les threads de manière synchrone plutôt qu’asynchrone, pour contrôler l’ordre dans lequel les étapes sont effectuées.

Transactions ne se chevauchant pas¶

Les sections ci-dessous décrivent :

• L’utilisation d’une procédure stockée à l’intérieur d’une transaction.

• L’utilisation d’une transaction dans le cadre d’une procédure stockée.

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;

Transactions scopées¶

Une procédure stockée qui contient une transaction peut être appelée à partir d’une autre transaction. Par exemple, une transaction à l’intérieur d’une procédure stockée peut inclure un appel vers une autre procédure stockée qui contient une transaction.

Snowflake ne traite pas la transaction interne comme étant imbriquée ; au contraire, la transaction interne est une transaction séparée. Snowflake les appelle « transactions scopées autonomes » (ou simplement « transactions scopées »).

Le point de départ et le point d’arrivée de chaque transaction scopée déterminent les instructions qui sont incluses dans la transaction. Le début et la fin peuvent être explicites ou implicites. Chaque instruction SQL fait partie d’une seule transaction. Un ROLLBACK ou un COMMIT délimitant ne « défait » pas un COMMIT ou un ROLLBACK délimité.

Le diagramme ci-dessous montre deux procédures stockées et deux transactions scopées. Dans cet exemple, chaque procédure stockée contient sa propre transaction indépendante. La première procédure stockée appelle la deuxième procédure stockée, de sorte que les procédures se chevauchent dans le temps - mais pas dans leur contenu. Toutes les instructions qui se trouvent dans la case grisée concernent une transaction ; toutes les autres instructions concernent une autre transaction.

Dans l’exemple suivant, les limites de la transaction sont différentes de celles de la procédure stockée ; la transaction qui commence dans la procédure stockée externe comprend certaines des instructions de la procédure stockée interne, mais pas toutes.

Dans le code ci-dessus, la deuxième procédure stockée contient certaines instructions (SP2_T1_S2 et SP2_T1_S3) qui se trouvent dans le scope de la première transaction. Seule l’instruction SP2_T2_S1, à l’intérieur de la case grisée, est dans le scope de la deuxième transaction.

L’exemple suivant montre les problèmes qui se posent si une transaction ne commence et ne se termine pas dans le cadre de la même procédure stockée. L’exemple contient le même nombre d’instructions COMMIT que d’instructions BEGIN. Cependant, les instructions BEGIN et COMMIT ne sont pas couplées correctement, de sorte que cet exemple contient deux erreurs :

• La procédure stockée externe démarre une transaction scopée, mais ne la termine pas explicitement. Par conséquent, cette transaction scopée cause une erreur à la fin de cette procédure stockée, et la transaction activée est implicitement annulée.

• La deuxième procédure stockée contient un COMMIT, mais il n’y a pas de BEGIN correspondant dans cette procédure stockée. Ce COMMIT ne valide pas la transaction ouverte commencée dans la première procédure stockée. Au lieu de cela, l’appariement incorrect de COMMIT provoque une erreur.

L’exemple suivant montre trois transactions scopées qui se chevauchent dans le temps. Dans cet exemple, la procédure stockée p1() appelle une autre procédure stockée p2() à l’intérieur d’une transaction, et p2() contient sa propre transaction, de sorte que la transaction lancée dans p2() s’exécute également indépendamment. (Cet exemple utilise un pseudo-code).

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

Dans ces trois transactions scopées :

• La transaction qui se trouve en dehors de toute procédure stockée contient les instructions A et E.

• La transaction de la procédure stockée p1() contient les instructions B et D

• La transaction dans p2() contient l’instruction C.

Les règles relatives aux transactions scopées s’appliquent également aux appels de procédure stockée récursifs. Un appel récursif est juste un type spécifique d’appel imbriqué, et suit les mêmes règles de transaction qu’un appel imbriqué.

Lorsque AUTOCOMMIT est éteint, soyez particulièrement prudent en combinant les transactions implicites et les procédures stockées. Si vous laissez accidentellement une transaction active à la fin d’une procédure stockée, la transaction est annulée.

Par exemple, l’exemple de pseudo-code suivant provoque un ROLLBACK implicite à la fin de la procédure stockée :

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


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

Dans cet exemple, la commande pour définir AUTOCOMMIT valide toute transaction active. Une nouvelle transaction n’est pas lancée immédiatement. La procédure stockée contient une instruction DML , qui lance implicitement une nouvelle transaction. Ce BEGIN TRANSACTION implicite n’a pas de COMMIT ou ROLLBACK correspondant dans la procédure stockée. Comme il y a une transaction active à la fin de la procédure stockée, cette transaction active est implicitement annulée.

Si vous souhaitez exécuter l’ensemble de la procédure stockée en une seule transaction, lancez la transaction avant d’appeler la procédure stockée, et validez la transaction après l’appel :

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


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

Dans ce cas, les BEGIN et COMMIT sont correctement couplés, et le code s’exécute sans erreur.

Comme alternative, mettez BEGIN TRANSACTION et COMMIT dans la procédure stockée, comme indiqué dans l’exemple de pseudo-code suivant.

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


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;

Si vous ne couplez pas correctement vos blocs BEGIN/COMMIT dans une transaction scopée, Snowflake signale une erreur. Cette erreur peut avoir d’autres conséquences, comme empêcher l’achèvement d’une procédure stockée ou empêcher la validation d’une transaction de clôture. Par exemple, dans l’exemple de pseudo-code suivant, certaines instructions de la procédure stockée délimitante, ainsi que la procédure stockée englobée, sont annulées :

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

Dans cet exemple, la seule valeur qui est insérée est « osp1_alpha ». Aucune des autres valeurs n’est insérée car un COMMIT n’est pas correctement associé à un BEGIN. L’erreur est traitée comme suit :

1. Lorsque la procédure inner_sp2() se termine, Snowflake détecte que BEGIN dans inner_sp2() ne correspond pas à un COMMIT (ou ROLLBACK).

   1. Snowflake annule implicitement la transaction scopée qui a commencé en inner_sp2().

   2. Snowflake émet également une erreur parce que CALL à inner_sp2() a échoué.

2. Parce que CALL de inner_sp2() a échoué, et parce que cette instruction CALL se trouvait dans outer_sp1(), la procédure stockée outer_sp1() elle-même échoue également et renvoie une erreur, au lieu de continuer.

3. Parce que outer_sp1() n’a pas fini de s’exécuter :

   • Les instructions INSERT pour les valeurs “osp1_delta” et “osp1_omega” ne sont jamais exécutées.

   • La transaction ouverte dans outer_sp1() est implicitement annulée plutôt que validée, donc l’insertion de la valeur “osp1_beta” n’est jamais validée.

Isolation READ COMMITTED¶

Dans le cas de l’isolation READ COMMITTED, une instruction ne voit que les données qui ont été validées avant le début de l’instruction. Elle ne voit jamais les données non validées.

Lorsqu’une instruction est exécutée dans une transaction à plusieurs instructions :

• Une instruction ne voit que les données qui ont été validées avant le début de l”instruction. Deux instructions successives dans la même transaction peuvent voir des données différentes si une autre transaction est validée entre l’exécution de la première instruction et la deuxième.

• Une instruction affiche les modifications apportées par les instructions précédentes exécutées dans la même transaction, même si ces modifications ne sont pas encore validées.

Délai d’attente du verrouillage¶

Une instruction bloquée obtiendra soit un verrouillage sur la ressource qu’elle attendait, soit un délai d’attente avant que la ressource devienne disponible. La durée (en secondes) de blocage d’une instruction peut être configurée en réglant le paramètre LOCK_TIMEOUT.

Par exemple, pour modifier le délai de verrouillage à 2 heures (7 200 secondes) pour la session en cours :

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

Interblocages¶

Les blocages surviennent lorsque des transactions simultanées sont en attente sur des ressources qui sont bloquées les unes par rapport aux autres.

Dans Snowflake, un blocage ne peut pas se produire lors de l’exécution simultanée d’instructions DML ou de requête de validation automatique. Toutefois, des blocages peuvent se produire avec des transactions explicitement démarrées et plusieurs instructions dans chaque transaction. Snowflake détecte les interblocages et choisit l’instruction la plus récente qui fait partie de l’interblocage en tant que victime. L’instruction est annulée, mais la transaction elle-même est laissée active et doit être validée ou annulée.

La détection des blocages peut prendre du temps.

Autorisation d’erreurs d’instructions pour annuler les transactions¶

Pour permettre à une erreur d’instruction dans une transaction d’annuler une transaction, définissez le paramètre TRANSACTION_ABORT_ON_ERROR au niveau de la session ou du compte.

Analyse des transactions bloquées avec la vue LOCK_WAIT_HISTORY¶

L” Vue LOCK_WAIT_HISTORY renvoie les détails de la transaction qui peuvent être utiles pour analyser les transactions bloquées. Chaque ligne du résultat comprend les détails d’une transaction qui attend un verrou et les détails des transactions qui détiennent ce verrou ou qui attendent ce verrou.

Par exemple, voir le scénario ci-dessous :

Dans ce scénario, les données suivantes sont renvoyées :

• La transaction B est la transaction qui attend un verrou.

• La transaction B a demandé le verrouillage à l’horodatage T1.

• La transaction A est la transaction qui détient le verrou.

• La requête 2 de la transaction A est la requête de blocage.

La requête 2 est la requête de blocage, car il s’agit de la première instruction de la transaction A (la transaction qui détient le verrou) sur laquelle la transaction B (la transaction qui attend le verrou) a commencé à attendre.

Cependant, notez qu’une requête ultérieure dans la transaction A (requête 5) a également acquis le verrou. Il est possible que des exécutions concurrentes ultérieures de ces transactions entraînent le blocage de la transaction B sur une autre requête qui acquiert le verrou dans la transaction A. Par conséquent, vous devez examiner toutes les requêtes dans la première transaction de blocage.

Exemple simple de transaction et de procédure stockée¶

Voici un exemple simple de transactions scopées. La procédure stockée contient une transaction qui insère une ligne avec la valeur 12 et qui s’annule ensuite. La transaction extérieure est validée. La sortie montre que toutes les lignes du champ d’application de la transaction externe sont conservées, tandis que la ligne du champ d’application de la transaction interne n’est pas conservée.

Notez qu’étant donné que seule une partie de la procédure stockée se trouve à l’intérieur de sa propre transaction, les valeurs insérées par les instructions INSERT qui sont dans la procédure stockée, mais en dehors de la transaction de la procédure stockée, sont conservées.

Créer deux tables :

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

Créer la procédure stockée :

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

Appeler la procédure stockée :

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

Les résultats devraient comprendre 00, 11, 13 et 09. La ligne avec ID = 12 ne doit pas être incluse. Cette ligne se trouvait dans le champ d’application de la transaction délimitée, qui a été annulée. Toutes les autres lignes étaient dans le champ d’application de la transaction extérieure, et ont été validées. Notez en particulier que les lignes avec IDs 11 et 13 étaient à l’intérieur de la procédure stockée, mais en dehors de la transaction la plus interne ; elles sont dans le champ de la transaction délimitante, et ont été validées avec celle-ci.

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

Enregistrement des informations indépendamment du succès d’une transaction¶

Voici un exemple simple et pratique de la manière d’utiliser une transaction scopée. Dans cet exemple, une transaction enregistre certaines informations ; ces informations enregistrées sont conservées, que la transaction elle-même réussisse ou échoue. Cette technique peut être utilisée pour suivre toutes les tentatives d’action, que chacune ait réussi ou non.

Créer deux tables :

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

Créer la procédure stockée :

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

Appeler la procédure stockée :

begin transaction;
call update_data();
rollback;

Le tableau des données est vide car la transaction a été annulée :

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

Toutefois, la table de journalisation n’est pas vide ; l’insertion dans la table de journalisation a été effectuée dans une transaction distincte de l’insertion dans data_table.

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

Exemples de transactions scopées et de procédures stockées¶

Les quelques exemples suivants utilisent les tables et les procédures stockées présentées ci-dessous. En transmettant les paramètres appropriés, l’appelant peut contrôler où les instructions BEGIN TRANSACTION, COMMIT et ROLLBACK sont exécutées dans les procédures stockées.

Créer les tables :

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

Cette procédure est la procédure stockée délimitante et, selon les paramètres qui lui sont transmis, elle peut créer une transaction délimitante (de clôture).

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

Cette procédure est la procédure interne stockée et, selon les paramètres qui lui sont transmis, elle peut créer une transaction délimitée.

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

Utilisation du traitement des erreurs avec les transactions dans les procédures stockées¶

Le code suivant indique un traitement d’erreur simple pour une transaction dans une procédure stockée. Si la valeur du paramètre « fail » est transmise, la procédure stockée tente de supprimer deux tables qui existent et une table qui n’existe pas, et la procédure stockée détecte l’erreur et renvoie un message d’erreur. Si la valeur du paramètre « fail » n’est pas transmise, la procédure tente de supprimer deux tables qui existent, et aboutit.

Créer les tables et la procédure stockée :

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;

Appeler la procédure stockée et forcer une erreur :

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

Appeler la procédure stockée sans forcer une erreur :

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