Transactions

Une transaction est une séquence d’instructions SQL qui sont validées ou annulées en tant qu’unité.

Dans ce chapitre :

Introduction

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 à INSERT, UPDATE, DELETE, MERGE et TRUNCATE.

  • Le terme « instruction de requête » fait référence à 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.

Note

Les transactions explicites ne doivent contenir que des instructions DML et des instructions d’interrogation. Les instructions DDL valident implicitement des transactions actives (pour plus de détails, voir la section DDL).

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.

DDL

Chaque instruction DDL s’exécute comme une transaction distincte.

Si une instruction DDL est exécutée alors qu’une transaction est active, l’instruction DDL :

  1. Valide implicitement la transaction active.

  2. Exécute l’instruction DDL comme une transaction distincte.

Comme une instruction DDL est une transaction propre, vous ne pouvez pas annuler une instruction DDL ; la transaction contenant le DDL est terminée avant que vous puissiez exécuter le ROLLBACK explicite.

S une instruction DDL est immédiatement suivie d’une instruction DML ou de requête, alors cette instruction DML ou de requête lance implicitement une nouvelle transaction.

AUTOCOMMIT

Snowflake prend en charge un paramètre AUTOCOMMIT . Le paramètre par défaut pour AUTOCOMMIT est activé.

Alors que AUTOCOMMIT est activé :

  • Chaque instruction en dehors d’une transaction explicite est traitée comme si elle se trouvait à l’intérieur de sa propre transaction implicite à instruction unique. En d’autres termes, cette instruction est automatiquement validée si elle réussit, et automatiquement annulée si elle échoue.

    Les instructions à l’intérieur d’une transaction explicite ne sont pas affectées par AUTOCOMMIT. Par exemple, les instructions à l’intérieur d’un BEGIN TRANSACTION ... ROLLBACK explicite sont annulées même si AUTOCOMMIT est TRUE.

Alors que AUTOCOMMIT est désactivé :

  • Une BEGIN TRANSACTION implicite est exécutée à :

    • La première instruction ou requête DML après la fin d’une transaction. Cela est vrai indépendamment de ce qui a mis fin à la transaction précédente (par exemple, instruction DDL, ou validation ou annulation explicite).

    • La première instruction DML ou instruction d’interrogation après avoir désactivé AUTOCOMMIT.

  • Un COMMIT implicite est exécuté à la suite (si une transaction est déjà active) :

    • De l’exécution d’une instruction DDL.

    • L’exécution d’une instruction ALTER SESSION SET AUTOCOMMIT, que la nouvelle valeur soit TRUE ou FALSE, et que la nouvelle valeur soit différente ou non de la précédente. Par exemple, même si vous réglez AUTOCOMMIT sur FALSE alors qu’elle est déjà FALSE, un COMMIT implicite est exécuté.

  • Un ROLLBACK implicite est exécuté à la suite (si une transaction est déjà active) :

    • De la fin d’une session.

    • La fin d’une procédure stockée.

      Que la transaction active de la procédure stockée ait été lancée explicitement ou implicitement, Snowflake annule la transaction active et émet un message d’erreur.

Prudence

Ne pas modifier les paramètres AUTOCOMMIT à l’intérieur d’une procédure stockée. Vous obtiendrez un message d’erreur.

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;

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.

Procédures et transactions stockées

En général, les règles décrites dans les sections précédentes s’appliquent également aux procédures stockées. Cette section fournit des informations supplémentaires spécifiques aux procédures stockées.

Une transaction peut être à l’intérieur d’une procédure stockée, ou une procédure stockée peut être à l’intérieur d’une transaction ; cependant, une transaction ne peut pas être en partie à l’intérieur et en partie à l’extérieur d’une procédure stockée, ni démarrée dans une procédure stockée et terminée dans une autre procédure stockée.

Par exemple :

  • Vous ne pouvez pas commencer une transaction avant d’appeler la procédure stockée, puis terminer la transaction à l’intérieur de la procédure stockée. Si vous essayez de faire cela, Snowflake signale une erreur similaire à Modifying a transaction that has started at a different scope is not allowed.

  • Vous ne pouvez pas commencer une transaction à l’intérieur de la procédure stockée, puis terminer la transaction au retour de la procédure. Si une transaction est lancée à l’intérieur d’une procédure stockée et qu’elle est toujours active lorsque la procédure stockée se termine, une erreur se produit et la transaction est annulée.

Ces règles s’appliquent également aux procédures stockées imbriquées. Si la procédure A appelle la procédure B, alors B ne peut pas terminer une transaction commencée dans A ou vice-versa. Chaque BEGIN TRANSACTION dans A doit avoir un COMMIT correspondant (ou ROLLBACK) dans A, et chaque BEGIN TRANSACTION dans B doit avoir un COMMIT correspondant (ou ROLLBACK) dans B.

Si une procédure stockée contient une transaction, cette transaction ne peut contenir qu’une partie ou la totalité du corps de la procédure stockée. Par exemple, dans la procédure stockée suivante, seules certaines instructions font partie d’une transaction. (Cet exemple, et plusieurs exemples ultérieurs, utilisent un pseudo-code pour plus de simplicité).

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

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

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.

Utilisation d’une procédure stockée dans une transaction

Dans le cas le plus simple, une procédure stockée est considérée comme faisant partie d’une transaction si les conditions suivantes sont remplies :

  • Un BEGIN TRANSACTION est exécuté avant que la procédure stockée ne soit appelée.

  • Un COMMIT (ou ROLLBACK) correspondant est exécuté après la fin de la procédure stockée.

  • Le corps de la procédure stockée ne contient pas de BEGIN TRANSACTION explicite ou implicite ou COMMIT (ou ROLLBACK).

La procédure stockée à l’intérieur de la transaction suit les règles de la transaction de clôture :

  • Si la transaction est validée, alors toutes les instructions à l’intérieur de la procédure sont validées.

  • Si la transaction est annulée, toutes les instructions de la procédure sont annulées.

Le pseudo-code suivant montre une procédure stockée appelée entièrement à l’intérieur d’une transaction :

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

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

Cela équivaut à exécuter la séquence d’instructions suivante :

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

Utilisation d’une transaction dans une procédure stockée

Vous pouvez exécuter zéro, une ou plusieurs transactions à l’intérieur d’une procédure stockée. Le pseudo-code suivant montre un exemple de deux transactions dans une procédure stockée :

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

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

La procédure stockée pourrait être appelée comme indiqué ci-dessous :

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

Cela équivaut à exécuter la séquence suivante :

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;

Dans ce code, quatre transactions distinctes sont exécutées. Chaque transaction soit commence et se termine en dehors de la procédure, soit commence et se termine à l’intérieur de la procédure. Aucune transaction n’est fractionnée à travers une procédure - en partie à l’intérieur et en partie à l’extérieur de la procédure stockée. Aucune transaction n’est imbriquée dans une autre transaction.

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

Note

Les termes « interne » et « externe » sont couramment utilisés pour décrire des opérations imbriquées, telles que les appels de procédures stockées imbriquées. Cependant, les transactions dans Snowflake ne sont pas vraiment « imbriquées » ; par conséquent, pour réduire la confusion lors de la référence aux transactions, ce document utilise fréquemment les termes « délimité » et « délimitant » plutôt que « interne » ou « externe ».

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.

Illustration of two stored procedures, each with its own scoped 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.

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

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.

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

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;

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

Prudence

Le chevauchement de transactions scopées peut entraîner un blocage si elles manipulent le même objet de base de données (par exemple, une table). Les transactions scopées ne doivent être utilisées qu’en cas de nécessité.

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;

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.

Niveau d’isolation

READ COMMITTED est actuellement le seul niveau d’isolation pris en charge pour les tables.

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.

Verrouillage des ressources

Les opérations transactionnelles acquièrent des verrous sur une ressource, telle qu’une table, lors de la modification de cette ressource. Les verrous empêchent les autres instructions de modifier la ressource jusqu’à ce que le verrou soit supprimé.

Les directives suivantes s’appliquent dans la plupart des situations :

  • Les opérations COMMIT (y compris AUTOCOMMIT et COMMIT explicite) verrouillent des ressources, mais généralement de manière brève.

  • Les instructions UPDATE, DELETE et MERGE détiennent des verrous qui les empêchent généralement de s’exécuter en parallèle avec d’autres instructions UPDATE, DELETE et MERGE.

  • La plupart des instructions INSERT et COPY n’écrivent que de nouvelles partitions. Ces instructions peuvent souvent être exécutées en parallèle avec d’autres opérations INSERT et COPY, et parfois en parallèle avec une instruction UPDATE, DELETE ou MERGE.

Les verrous détenus par une instruction sont supprimés lors de l’opération COMMIT ou ROLLBACK de la transaction.

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

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.

Commandes et fonctions de transactions

Snowflake fournit les commandes SQL suivantes pour accepter les transactions :

En outre, Snowflake fournit les fonctions contextuelles suivantes pour obtenir des informations sur les transactions d’une session :

Annulation de transactions

Si une transaction est en cours d’exécution dans une session et que la session se déconnecte brusquement, empêchant la validation ou l’annulation de la transaction, la transaction est laissée dans un état détaché, y compris tout verrouillage que la transaction contient sur les ressources. Si cela se produit, vous devrez peut-être annuler la transaction.

Pour interrompre une transaction en cours, l’utilisateur qui a lancé la transaction ou un administrateur de compte peut appeler la fonction système, SYSTEM$ABORT_TRANSACTION.

Si la transaction n’est pas interrompue par l’utilisateur :

  • Si elle empêche une autre transaction d’acquérir un verrou sur la même table et si elle est inactive pendant 5 minutes, elle est automatiquement interrompue et annulée.

  • Si elle n’empêche pas d’autres transactions de modifier la même table et si elle est plus ancienne de 4 heures, elle est automatiquement interrompue et annulée.

Meilleures pratiques

  • Une transaction doit contenir des instructions qui sont liées et qui doivent réussir ou échouer ensemble, par exemple, le retrait d’argent d’un compte et le dépôt de ce même argent sur un autre compte. En cas d’annulation, le payeur ou le bénéficiaire se retrouve avec l’argent ; l’argent ne « disparaît » jamais (il est retiré d’un compte mais n’est jamais déposé sur l’autre compte).

    En général, une transaction ne doit contenir que des instructions connexes. Rendre une instruction moins granulaire signifie que lorsqu’une transaction est annulée, elle peut annuler un travail utile qui n’avait pas besoin d’être annulé.

  • Les transactions plus importantes peuvent améliorer les performances dans certains cas.

    Bien que le point précédent ait souligné l’importance de ne regrouper que les instructions qui doivent réellement être validées ou annulées en tant que groupe, des transactions plus importantes peuvent parfois être utiles. Dans Snowflake, comme dans la plupart des bases de données, la gestion des transactions consomme des ressources. Par exemple, l’insertion de 10 lignes dans une transaction est généralement plus rapide et moins coûteuse que l’insertion d’une ligne dans chacune des 10 transactions séparées. La combinaison de plusieurs instructions en une seule opération peut améliorer les performances.

  • Des transactions trop importantes peuvent réduire le parallélisme ou accroître les blocages. Si vous décidez de regrouper des instructions non liées pour améliorer les performances (comme décrit au point précédent), gardez à l’esprit qu’une transaction peut acquérir des verrous sur les ressources, ce qui peut retarder d’autres requêtes ou entraîner des blocages.

  • Snowflake recommande de garder AUTOCOMMIT activé et d’utiliser autant que possible des transactions explicites. L’utilisation de transactions explicites permet aux lecteurs humains de voir plus facilement où commencent et où finissent les transactions. Ceci, combiné avec AUTOCOMMIT, rend votre code moins susceptible de subir des annulations involontaires, par exemple à la fin d’une procédure stockée.

  • Évitez de modifier AUTOCOMMIT simplement pour lancer implicitement une nouvelle transaction. Utilisez plutôt BEGIN TRANSACTION pour rendre plus évident le point de départ d’une nouvelle transaction.

  • Évitez d’exécuter plusieurs instructions BEGIN TRANSACTION à la suite. Les instructions BEGIN TRANSACTION supplémentaires rendent plus difficile de voir où une transaction commence réellement, et rendent plus difficile le couplage des commandes COMMIT/ROLLBACK avec les commandes BEGIN TRANSACTION correspondantes.

ID de transaction

Chaque transaction Snowflake se voit attribuer une heure de début unique (avec une précision d’une milliseconde), qui sert d’ID pour la transaction. Pour obtenir quelques exemples d’heures de début de transactions, voir les Exemples pour BEGIN TRANSACTION.

Exemples

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 INSERTs qui sont dans la procédure stockée, mais en dehors de la transaction de la procédure stockée, sont conservés.

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

Valider le niveau intermédiaire de trois niveaux

Cet exemple contient 3 transactions. Cet exemple valide le niveau « intermédiaire » (la transaction délimitée par la transaction la plus extérieure et délimitant la transaction la plus intérieure). Cela annule les transactions les plus extérieures et les plus intérieures.

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;

Il en résulte que seules les lignes de la transaction intermédiaire (12, 21 et 23) sont validées. Les lignes de la transaction extérieure et de la transaction intérieure ne sont pas validées.

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

Annuler le niveau intermédiaire de trois niveaux

Cet exemple contient 3 transactions. Cet exemple annule le niveau « intermédiaire » (la transaction délimitée par la transaction la plus extérieure et délimitant la transaction la plus intérieure). Cela valide les transactions les plus extérieures et les plus intérieures.

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;

Il en résulte que toutes les lignes à l’exception de celles se trouvant dans la transaction intermédiaire (12, 21 et 23) sont validées.

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