Transactions

Une transaction est un ensemble d’instructions SQL, de lectures et d’écritures, qui sont traitées comme une unité. Toutes les instructions de la transaction sont soit appliquées (validées), soit annulées en tant qu’unité unique. La transaction peut inclure à la fois des lectures et des écritures. Comme les autres bases de données, les transactions Snowflake garantissent ACID des propriétés.

Dans ce chapitre :

Portée d’une transaction Snowflake

Une transaction est associée à une seule session. Plusieurs sessions ne peuvent pas partager la même transaction.

Une transaction peut être démarrée explicitement en exécutant une instruction BEGIN. Une transaction peut être démarrée implicitement dans les situations suivantes :

  • Une fois que AUTOCOMMIT a été désactivé, l’instruction DML suivante (le cas échéant) démarre implicitement une transaction.

  • Tant que AUTOCOMMIT est désactivé, la première instruction DML (le cas échéant) après COMMIT ou ROLLBACK démarre implicitement une transaction.

Une fois qu’une transaction a été lancée, elle doit être fermée en exécutant une instruction COMMIT ou ROLLBACK. Si une session avec une transaction ouverte est clôturée, la transaction ouverte est annulée.

De plus, si l’état AUTOCOMMIT a changé (de false à true et vice versa), la transaction en cours d’exécution sera COMMIT.

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

Validation automatique

Par défaut, une instruction DML exécutée sans démarrer explicitement une transaction est automatiquement validée en cas de succès ou annulée en cas d’échec à la fin de l’instruction. Ce comportement est appelé « validation automatique ». Ce comportement est contrôlé avec le paramètre AUTOCOMMIT.

Les instructions DDL sont toujours validées automatiquement, quelle que soit la configuration AUTOCOMMIT. L’exécution d’une instruction DDL dans une transaction ouverte fait ce qui suit :

  • Valide la transaction ouverte.

  • Exécute l’instruction DDL et la valide.

Les instructions CTAS (CREATE TABLE AS SELECT ...) sont traitées comme des DDL ; par conséquent, elles sont également validées automatiquement.

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 (l’annulation). Pour annuler la plupart des instructions DDL, vous devez exécuter une autre instruction DDL (par exemple, DROP (détruire) une table que vous avez créée, ALTER (modifier) une table ayant retrouvé son statut d’origine si vous l’avez ALTERed (modifiée), ou UNDROP (annuler la destruction) ou recréer un objet que vous avez détruit).

Annulation d’une instruction

Si une instruction DML exécutée dans une transaction explicitement démarrée échoue, les modifications apportées par la DML sont annulées. Toutefois, la transaction reste ouverte jusqu’à ce qu’elle soit validée ou annulée.

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

Notez que la modification de l’état du paramètre AUTOCOMMIT valide une transaction existante.

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.

Niveau d’isolation

Les transactions Snowflake prennent en charge l’isolation READ COMMITTED (lecture validée) des tables. 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 interblocages 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 interblocage ne peut pas se produire lors de l’exécution simultanée d’instructions DML de validation automatique. Toutefois, des interblocages 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 ouverte et doit être validée ou annulée.

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 est laissée ouverte, Snowflake l’annulera généralement après une période d’inactivité de quatre heures.