Isolement sérialisable - Amazon Redshift

Isolement sérialisable

Certaines applications nécessitent non seulement une interrogation et un chargement simultanés, mais également la possibilité d'écrire sur plusieurs tables ou sur la même table simultanément. Dans ce contexte, simultanément entraîne un chevauchement, non planifié pour s'exécuter exactement au même moment. Deux transactions sont considérées comme simultanées si la seconde démarre avant que la première ne soit validée. Les opérations simultanées peuvent provenir de différentes séances contrôlées par le même utilisateur ou par différents utilisateurs.

Note

Amazon Redshift prend en charge un comportement de validation automatique par défaut dans lequel chaque commande SQL exécutée séparément est validée individuellement. Si vous placez un ensemble de commandes dans un bloc de transaction (défini par les instructions BEGIN et FIN), le bloc est validé comme une seule transaction et, par conséquent, vous pouvez le restaurer si nécessaire. Les exceptions à ce comportement sont les commandes TRUNCATE et VACUUM, qui valident automatiquement toutes les modifications en attente effectuées dans la transaction actuelle.

Certains clients SQL émettent automatiquement des commandes BEGIN et COMMIT, de sorte que le client contrôle si un groupe d'instructions est exécuté en tant que transaction ou si chacune des instructions est exécutée indépendamment. Consultez la documentation relative à de l'interface que vous utilisez. Par exemple, lors de l'utilisation du pilote JDBC Amazon Redshift, un PreparedStatement JDBC avec une chaîne de requête qui contient plusieurs commandes SQL (séparées par des points-virgules) exécute toutes les instructions en tant qu'une seule transaction. En revanche, si vous utilisez SQL Workbench/J et définissez AUTO COMMIT ON, alors si vous exécutez plusieurs instructions, chaque instruction s'exécute indépendamment.

Les opérations d'écriture simultanées sont prises en charge dans Amazon Redshift de manière protective, à l'aide de verrous d'écriture sur les tables et du principe d'isolement sérialisable. L'isolement sérialisable préserve l'illusion qu'une transaction s'exécutant sur une table est la seule transaction qui soit en cours d'exécution sur cette table. Par exemple, les deux transactions T1 et T2 s'exécutant simultanément doivent produire les mêmes résultats comme conséquence de l'une des conditions suivantes :

  • T1 et T2 sont exécutées en série dans cet ordre.

  • T2 et T1 sont exécutées en série dans cet ordre.

Les transactions simultanées sont invisibles les unes aux autres ; elles ne peuvent pas détecter les modifications de chacune d'elles. Chaque transaction simultanée crée un instantané de la base de données au début de la transaction. Un instantané de base de données est créé au sein d'une transaction sur la première occurrence de la plupart des instructions SELECT, des commandes DML telles que COPY, DELETE, INSERT, UPDATE et TRUNCATE, et des commandes DDL suivantes :

  • ALTER TABLE (pour ajouter ou supprimer des colonnes)

  • CREATE TABLE

  • DROP TABLE

  • TRUNCATE TABLE

Si une exécution en série des transactions simultanées produit les mêmes résultats que leur exécution simultanée, ces transactions sont considérées comme « sérialisables » et peuvent être exécutées en toute sécurité. Si aucune exécution en série de ces transactions ne peut produire les mêmes résultats, la transaction qui exécute une instruction qui pourrait interrompre la mise en série est arrêtée et annulée.

Les tables catalogue système (PG) et autres tables système Amazon Redshift (STL et STV) ne sont pas verrouillées dans une transaction. Par conséquent, les modifications apportées aux objets de base de données qui proviennent d'opérations DDL et TRUNCATE sont visibles lors de la validation de transactions simultanées.

Par exemple, supposons que cette table A existe dans la base de données lorsque deux transactions simultanées, T1 et T2, démarrent. Supposons que T2 renvoie une liste de tables en les sélectionnant dans la table de catalogue PG_TABLES. Ensuite, T1 supprime la table A et valide la suppression, puis T2 affiche les tables à nouveau. La table A n'apparaît plus dans la liste. Si T2 essaie d'interroger la table supprimée, Amazon Redshift renvoie une erreur indiquant que la relation n'existe pas. La requête de catalogue qui renvoie la liste des tables à T2 ou vérifie que la table A existe n'est pas soumise aux mêmes règles d'isolement que les opérations réalisées sur les tables de l'utilisateur.

Les transactions pour les mises à jour de ces tables s'exécutent dans un mode d'isolement validé en lecture. Les tables de catalogue préfixées par PG ne prennent pas en charge l'isolement d'instantané.

Isolement sérialisable pour les tables système et les tables de catalogue

Un instantané de base de données est également créé dans une transaction pour toute requête SELECT qui fait référence à une table créée par l'utilisateur ou à une table système Amazon Redshift (STL ou STV). Les requêtes SELECT qui ne font référence à aucune table ne créent pas d'instantané de base de données de transaction. Les instructions INSERT, DELETE et UPDATE qui fonctionnent uniquement sur les tables catalogue système (PG) ne créent pas non plus d'instantané de base de données de transaction.

Comment corriger les erreurs d'isolement sérialisable

ERROR:1023 DETAIL : Violation d'isolement sérialisable sur une table dans Redshift

Lorsqu'Amazon Redshift détecte une erreur d'isolement sérialisable, le message d'erreur suivant s'affiche.

ERROR:1023 DETAIL: Serializable isolation violation on table in Redshift

Pour corriger une erreur d'isolement sérialisable, vous pouvez essayer l'une des méthodes suivantes :

  • Réessayez la transaction annulée.

    Amazon Redshift a détecté qu'une application simultanée n'est pas sérialisable. Il suggère des lacunes dans la logique de l'application, qui peuvent généralement être contournées en réessayant la transaction qui a rencontré l'erreur. Si le problème persiste, essayez l'une des autres méthodes.

  • Déplacez les opérations qui ne doivent pas se trouver dans la même transaction atomique hors de la transaction.

    Cette méthode s'applique lorsque des opérations individuelles à l'intérieur de deux transactions se comparent entre elles d'une façon pouvant affecter le résultat de l'autre transaction. Par exemple, les deux séances suivantes lancent chacune une transaction.

    Session1_Redshift=# begin;
    Session2_Redshift=# begin;

    Le résultat d'une instruction SELECT dans chaque transaction peut être affecté par une instruction INSERT dans l'autre. En d'autres termes, supposons que vous exécutez les instructions suivantes en série, dans n'importe quel ordre. Dans chaque cas, le résultat est l'une des instructions SELECT renvoyant une ligne de plus que si les transactions avaient été exécutées de manière simultanée. Il n'existe aucun ordre dans lequel les opérations peuvent s'exécuter en série et produire le même résultat que si elles étaient exécutées de manière simultanée. Ainsi, la dernière opération exécutée entraîne une erreur d'isolement sérialisable.

    Session1_Redshift=# select * from tab1; Session1_Redshift=# insert into tab2 values (1);
    Session2_Redshift=# insert into tab1 values (1); Session2_Redshift=# select * from tab2;

    Dans de nombreux cas, le résultat de l'instruction SELECT n'est pas important. En d'autres termes, l'atomicité des opérations dans les transactions n'est pas important. Dans ces cas-là, déplacez les instructions SELECT hors de leurs transactions, comme illustré dans les exemples suivants.

    Session1_Redshift=# begin; Session1_Redshift=# insert into tab1 values (1) Session1_Redshift=# end; Session1_Redshift=# select * from tab2;
    Session2_Redshift # select * from tab1; Session2_Redshift=# begin; Session2_Redshift=# insert into tab2 values (1) Session2_Redshift=# end;

    Dans ces exemples, il n'y a aucune référence croisée dans les transactions. Les deux instructions INSERT n'ont aucun effet l'une sur l'autre. Dans ces exemples, il existe au moins un ordre dans lequel les transactions peuvent s'exécuter en série et produire le même résultat que si elle étaient exécutées de manière simultanée. Cela signifie que les transactions sont sérialisables.

  • Forcez la sérialisation en verrouillant toutes les tables de chaque séance.

    La commande LOCK bloque les opérations pouvant entraîner des erreurs d'isolement sérialisable. Lorsque vous utilisez la commande LOCK, veillez à procéder comme suit :

    • Verrouillez toutes les tables affectées par la transaction, notamment celles affectées par les instructions SELECT en lecture seule à l'intérieur de la transaction.

    • Verrouillez les tables dans le même ordre, quel que soit l'ordre dans lequel ces opérations sont exécutées.

    • Verrouillez toutes les tables au début de la transaction, avant d'exécuter la moindre opération.

ERROR:1018 DETAIL : La relation n'existe pas

Lorsque vous exécutez des opérations Amazon Redshift simultanément dans différentes séances, un message d'erreur similaire au suivant s'affiche.

ERROR: 1018 DETAIL: Relation does not exist.

Les transactions dans Amazon Redshift suivent l'isolement des instantanés. Lorsqu'une transaction a démarré, Amazon Redshift prend un instantané de la base de données. Pour tout le cycle de vie de la transaction, la transaction est exécutée sur l'état de la base de données tel qu'il est reflété dans l'instantané. Si la transaction effectue la lecture à partir d'une table qui n'existe pas dans l'instantané, elle renvoie le message d'erreur 1018 affiché précédemment. Même lorsqu'une autre transaction simultanée crée une table après que la transaction a pris l'instantané, la transaction ne peut pas effectuer la lecture à partir de la table créée.

Pour corriger cette erreur d'isolement de sérialisation, vous pouvez essayer de faire démarrer la transaction à un moment où vous savez que la table existe.

Si la table est créée par une autre transaction, ce moment commence au moins après que cette transaction a été validée. Assurez-vous également qu'aucune transaction simultanée qui aurait pu supprimer la table n'a été validée.

session1 = # BEGIN; session1 = # DROP TABLE A; session1 = # COMMIT;
session2 = # BEGIN;
session3 = # BEGIN; session3 = # CREATE TABLE A (id INT); session3 = # COMMIT;
session2 = # SELECT * FROM A;

La dernière opération exécutée en tant qu'opération de lecture par session2 entraîne une erreur d'isolement sérialisable. Cette erreur se produit lorsqu'une session2 prend un instantané et que la table a déjà été supprimée par une session1 validée. En d'autres termes, même si une session3 simultanée a créé la table, la session2 ne voit pas la table car elle ne se trouve pas dans l'instantané.

Pour résoudre cette erreur, vous pouvez réorganiser les séances comme suit.

session1 = # BEGIN; session1 = # DROP TABLE A; session1 = # COMMIT;
session3 = # BEGIN; session3 = # CREATE TABLE A (id INT); session3 = # COMMIT;
session2 = # BEGIN; session2 = # SELECT * FROM A;

Lorsque la session2 prend son instantané, la session3 a déjà été validée et la table se trouve dans la base de données. La session2 peut effectuer la lecture à partir de la table sans aucune erreur.