SSI/fr

Documentation de la «Serializable Snapshot Isolation» (Isolation par Instantanés Sérialisables, ou SSI) dans PostgreSQL, comparée à la «Snapshot Isolation» (Isolation par Instantanés, ou SI). Celles-ci correspondent respectivement aux niveaux d'isolation de transaction SERIALIZABLE et REPEATABLE READ dans PostgreSQL, à partir de la version 9.1.
== Aperçu ==
Avec de vraies transactions sérialisables, si vous pouvez prouver que votre transaction fera ce qui est prévu si il n'y a aucune transaction concurrente, elle fera ce qui est prévu quelles que soient les autres transactions sérialisables qui s'exécuteront en même temps qu'elle, ou sera annulée pour erreur de sérialisation.
Ce document montre les problèmes qui peuvent se produire avec certaines combinaisons de transactions au niveau d'isolation de transaction REPEATABLE READ, et comment elles sont évitées avec le niveau d'isolation SERIALIZABLE, à partir de PostgreSQL 9.1.
Ce document est destiné au programmeur d'applications ou à l'administrateur de bases de données. Pour les détails sur l'implémentation de SSI, voyez la page de Wiki Serializable. Pour plus d'informations sur comment utiliser ce niveau d'isolation, voyez la documentation PostgreSQL courante.
== Exemples ==
Dans les environnements qui évitent de protéger leur intégrité en mettant en place des verrous bloquants, il sera fréquent que la base soit configurée (dans postgresql.conf) avec:
 default_transaction_isolation = 'serializable'
Pour cette raison, tous les exemples ont été effectués avec ce paramétrage, ce qui a évité de polluer les exemples en se contentant d'un simple begin plutôt que de déclarer explicitement le niveau d'isolation pour chaque transaction.
=== Write Skew Simple (Écriture Faussée Simple?) ===
Quand deux transactions concurrentes déterminent chacune ce qu'elles écrivent en lisant des données qui se chevauchent avec des données que l'autre modifie, on peut se retrouver dans un état qui ne devrait pas apparaître si une des deux s'était exécutée avant l'autre. C'est un phénomène connu sous le nom de write skew, et c'est la forme la plus simple de défaut de sérialisation contre laquelle SSI vous protège.
Quand il y a write skew dans SSI, les deux transactions se déroulent jusqu'à ce que l'une valide. La première à valider gagne, et l'autre transaction est annulée. La règle du "le premier à valider gagne" garantit que du travail peut avoir lieu sur la base et que la transaction qui est annulée puisse être tentée à nouveau immédiatement.
----
==== Noir et Blanc ====
Dans ce cas, il y a des enregistrement avec une colonne couleur contenant 'blanc' ou 'noir'. Deux utilisateurs essayent simultanément de convertir tous les enregistrements vers une couleur unique, mais chacun dans une direction opposée. Un veut tout passer tous les blancs en noir, et l'autre tous les noirs en blanc.
L'exemple peut être mis en place avec ces ordres: 
 create table points
 (
 id int not null primary key,
 couleur text not null
 );
 insert into points
 with x(id) as (select generate_series(1,10))
 select id, case when id % 2 = 1 then 'noir'
 else 'blanc' end from x;
 Exemple Noir et Blanc
session 1	session 2
 begin;
 update points set couleur = 'noir'
 where couleur = 'blanc';	
 	
 commit;
 ERROR: could not serialize access
 due to read/write dependencies
 among transactions
 DETAIL: Cancelled on identification
 as a pivot, during commit attempt.
 HINT: The transaction might succeed if retried.
Une erreur de sérialisation. On annule et on réessaye.
 rollback;
 begin;
 update points set couleur = 'noir'
 where couleur = 'blanc';
 commit;
Il n'y a pas de transaction concurrente pour gêner.
 select * from points order by id;
 id | couleur
 ----+-------
 1 | noir
 2 | noir
 3 | noir
 4 | noir
 5 | noir
 6 | noir
 7 | noir
 8 | noir
 9 | noir
 10 | noir
 (10 rows)
La transaction s'est exécutée seule, après l'autre.	
----
==== Données en intersection ====
Cet exemple est tiré de la documentation PostgreSQL. Deux transactions concurrentes lisent des données, et chacune utilise ces données pour mettre à jour l'ensemble lu par l'autre. Un exemple simple, même si un peu artificiel, de données faussées.
L'exemple peut être mis en place avec ces ordres:
 CREATE TABLE mytab
 (
 class int NOT NULL,
 value int NOT NULL
 );
 INSERT INTO mytab VALUES
 (1, 10), (1, 20), (2, 100), (2, 200);
 Exemple de données en intersection
session 1	session 2
 BEGIN;
 SELECT SUM(value) FROM mytab WHERE class = 1;
 sum
 -----
 30
 (1 row)
 INSERT INTO mytab VALUES (2, 30);	
 	
 BEGIN;
 SELECT SUM(value) FROM mytab WHERE class = 2;
 sum
 -----
 300
 (1 row)
 INSERT INTO mytab VALUES (1, 300);
Chaque transaction a modifié ce que l'autre transaction aurait lu. Si les deux étaient autorisées à valider, le comportement sérialisable ne serait plus respecté, parce que si elles avaient été exécutées une seule à la fois, une des transactions aurait vu l'INSERT que l'autre a validé. Nous attendons qu'une des transactions ait validé avant d'annuler quoi que ce soit, toutefois, pour garantir que des traitements soient effectués et éviter que le système ne s'effondre.
 COMMIT;
 COMMIT;
 ERROR: could not serialize access
 due to read/write dependencies
 among transactions
 DETAIL: Cancelled on identification
 as a pivot, during commit attempt.
 HINT: The transaction might succeed if retried.
Donc, maintenant nous annulons la transaction en échec et nous la réessayons depuis le début.
 ROLLBACK;
 BEGIN;
 SELECT SUM(value) FROM mytab WHERE class = 1;
 sum
 -----
 330
 (1 row)
 INSERT INTO mytab VALUES (2, 330);
 COMMIT;
Cela réussit, et le résultat est cohérent avec une exécution sérialisée des transactions.
 SELECT * FROM mytab;
 class | value
 -------+-------
 1 | 10
 1 | 20
 2 | 100
 2 | 200
 1 | 300
 2 | 330
 (6 rows)	
----
==== Protection contre le Découvert ====
Le cas hypothétique est celui d'une banque qui autorise ses clients à retirer de l'argent jusqu'au total de tout ce qu'ils ont sur tous leurs comptes. La banque transfèrera ensuite automatiquement les fonds au besoin pour terminer la journée avec un solde positif sur chaque compte. À l'intérieur d'une seule transaction, on vérifie que la somme de tous les comptes dépasse la somme requise.
Quelqu'un essaye d'être malin et de piéger la banque en soumettant deux retraits de 900$ sur deux comptes ayant chacun 500$ de solde simultanément. Au niveau d'isolation de transaction REPEATABLE READ, cela pourrait marcher; mais si le niveau d'isolation de transaction SERIALIZABLE est utilisé, SSI détectera une "structure dangereuse" dans le schéma de lecture/écriture et rejettera une des deux transactions.
Cet exemple peut être mis en place avec ces ordres:
 create table compte
 (
 nom text not null,
 type text not null,
 solde money not null default '0.00'::money,
 primary key (nom, type)
 );
 insert into compte values
 ('kevin','epargne', 500),
 ('kevin','courant', 500);
 
 Exemple de Protection contre le Découvert
session 1	session 2
 begin;
 select type, solde from compte
 where nom = 'kevin';
 type | solde
 -----------+---------
 epargne | $500.00
 courant | $500.00
 (2 rows)
Le total est de $1000, un retrait de $900 est donc permis.	
 	
 update compte
 set solde = solde - 900::money
 where nom = 'kevin' and type = 'epargne';
Jusqu'ici tout va bien.	
 	
 update compte
 set solde = solde - 900::money
 where nom = 'kevin' and type = 'courant';
Maintenant nous avons un problème. Cela ne peut co-exister avec l'activité de l'autre transaction. Nous n'annulons pas encore, parce que la transaction échouerait avec les mêmes conflits si on la réessayait. Le premier à valider va gagner, et l'autre échouera quand elle essayera de continuer après cela.
 commit;
Celle ci a validé la première. Son travail est enregistré.	
 	
=== Trois Transactions ou Plus ===
Des anomalies de sérialisation peuvent résulter de motifs plus complexes d'accès, impliquant trois transactions ou plus.
----
==== Couleurs Primaires ====
C'est similaire à l'exemple "Blanc et Noir" précédent, à la différence que nous utilisons les trois couleurs primaires. Une transaction essaye de passer le rouge à jaune, la suivante le jaune au bleu, et la troisième le bleu au rouge. Si ces transactions étaient exécutées une seule à la fois, on aurait à la fin de l'exécution deux des trois couleurs, en fonction de l'ordre d'exécution. Si deux d'entre elles sont exécutées simultanément, celle essayant de lire les enregistrements mis à jour par l'autre semblera s'exécuter première, puisqu'elle ne verra pas le travail de l'autre transaction, il n'y a donc pas de problème dans ce cas. Que l'autre transaction soit exécutée avant ou après cela, les résultats sont cohérents avec un ordre d'exécution sérialisé.
Si les trois s'exécutent en même temps, il y a un cycle dans l'ordre apparent d'exécution. Une transaction Repeatable Read ne détecterait pas cela, et la table aurait toujours trois couleurs. Une transaction Sérialisable détectera le problème et annulera une des transactions avec une erreur de sérialisation.
L'exemple peut être mis en place avec ces ordres:
 create table points
 (
 id int not null primary key,
 couleur text not null
 );
 insert into points
 with x(id) as (select generate_series(1,9000))
 select id, case when id % 3 = 1 then 'rouge'
 when id % 3 = 2 then 'jaune'
 else 'blue' end from x;
 create index points_couleur on points (couleur);
 analyze points;
 Primary Colors Example
session 1	session 2	session 3
 begin;
 update points set couleur = 'jaune'
 where couleur = 'rouge';	 	
 	
 begin;
 update points set couleur = 'blue'
 where couleur = 'jaune';	
 	 	
 begin;
 update points set couleur = 'rouge'
 where couleur = 'blue';
À ce point, au moins une des trois transactions est condamnée. Pour garantir que les traitement progressent, on attend qu'une valide. Le commit va réussir, ce qui non seulement garantit que les traitements progressent, mais qu'une tentative de reprendre une transaction échouée n'échouera pas sur la même combinaison de transactions.
 commit;
Le premier commit gagne. La session 2 doit échouer à ce point, parce que durant le commit il a été déterminé qu'elle a les plus grandes chances de réussir si réessayée immédiatement.
 select couleur, count(*) from points
 group by couleur
 order by couleur;
 couleur | count
 ----------+-------
 blue | 3000
 jaune | 6000
 (2 rows)
Cela semble avoir été exécuté avant les autres mises à jour.	 	
 	 	
 	 	
Un point intéressant est que si la session 2 avait tenté de valider après la session 1 et avant la session 3, elle aurait tout de même échoué, et une re-tentative aurait aussi réussi, mais le comportement de la transaction de la session 3 n'est pas déterministe. Elle pourrait avoir réussi, ou avoir reçu une erreur de sérialisation et avoir nécessité d'être rejouée.
C'est parce que le verrouillage de prédicat utilisé par le mécanisme de détection de conflit s'appuie sur les pages et enregistrement effectivement accédés, et il y a un facteur aléatoire utilisé lors de l'insertion des entrées d'index qui ont des clés égales, afin de réduire la contention; donc même avec des séquences d'évènements identiques il est toujours possible de voir des différences sur où les erreurs de sérialisation se produisent. C'est pour cela qu'il est important, quand on s'appuie sur les transactions sérialisables pour gérer la concurrence, d'avoir un système généralisé permettant d'identifier les erreurs de sérialisation et de rejouer les transactions depuis leur début.
Il convient aussi de noter que si la session 2 avait validé la seconde tentative de transaction avant que la session 3 ait validé sa transaction, toute requête ultérieure qui aurait vu des enregistrements mis à jour de jaune à bleu (et validés) aurait, de façon déterministe, fait échouer la transaction de la session 3, parce que ces enregistrements ne seraient pas des enregistrements que la session 3 verraient comme bleu et mettraient à jour à rouge. Pour que la transaction 3 réussisse, elle doit pouvoir être considérée comme ayant été exécutée avant la transaction validée de la session 2. Par conséquent, exposer un état dans lequel le travail de la transaction de la session 2 est visible, mais pas le travail de la transaction de la session 3 signifie que la transaction de la session 3 doit échouer. L'acte d' observer un état récemment modifié de la base peut entraîner des erreurs de sérialisation. Cela sera exploré plus avant dans d'autres exemples.
=== Mettre en place des règles métier dans des triggers ===
Si toutes les transactions sont sérialisables, des règles métier peuvent être vérifiées par des triggers sans les problèmes associés avec les autres niveaux d'isolation de transactions. Quand une contrainte déclarative fonctionne, elle sera en règle générale plus rapide, plus simple à implémenter et à maintenir, et moins sujette à bug - les triggers ne devront donc être utilisés comme suit que quand une contrainte déclarative ne fonctionnera pas.
----
==== Contraintes similaires à de l'unicité ====
Imaginons que vous vouliez quelque chose de similaire à une contrainte unique, mais en un peu plus compliqué. Pour cet exemple, nous voulons l'unicité des six premiers caractères de la colonne texte.
Cet exemple peut être mis en place avec les ordres suivants:
 create table t (id int not null, val text not null);
 with x (n) as (select generate_series(1,10000))
 insert into t select x.n, md5(x.n::text) from x;
 alter table t add primary key(id);
 create index t_val on t (val);
 vacuum analyze t;
 create function t_func()
 returns trigger
 language plpgsql as $$
 declare
 st text;
 begin
 st := substring(new.val from 1 for 6);
 if tg_op = 'UPDATE' and substring(old.val from 1 for 6) = st then
 return new;
 end if;
 if exists (select * from t where val between st and st || 'z') then
 raise exception 't.val pas unique sur les six premiers caractères: "%"', st;
 end if;
 return new;
 end;
 $$;
 create trigger t_trig
 before insert or update on t
 for each row execute procedure t_func();
Pour vérifier que le trigger fait bien respecter la règle métier quand il n'y a pas de problème de concurrence, sur une connexion unique:
 insert into t values (-1, 'this old dog');
 insert into t values (-2, 'this old cat');
 ERROR: t.val pas unique sur les six premiers caractères: "this o"
Essayons maintenant avec deux sessions concurrentes.
 Exemple de contrainte similaire à de l'unicité
session 1	session 2
 begin;
 insert into t values (-3, 'the river flows');	
 	
 begin;
 insert into t values (-4, 'the right stuff');
Cela fonctionne pour le moment, parce que le travail de l'autre transaction n'est pas visible de cette transaction, mais les deux transactions ne peuvent pas valider sans violer la règle métier.
 commit;
Le premier à valider gagne. La transaction est garantie.
Un commit ici échouerait, ainsi que n'importe quel autre ordre qu'on tenterait d'exécuter dans cette transaction condamnée.
 select * from t where id < 0;
 ERROR: could not serialize access
 due to read/write dependencies
 among transactions
 DETAIL: Canceled on identification as a pivot,
 during conflict out checking.
 HINT: The transaction might succeed if retried.
Comme il s'agit d'une erreur de sérialisation, la transaction devrait être réessayée.
 rollback;
 begin;
 insert into t values (-3, 'the river flows');
Lors de la nouvelle tentative, nous recevons une erreur plus utile à l'utilisateur.
 ERROR: t.val pas unique sur les six premiers caractères: "the ri"	
----
==== Contraintes similaires à des clés étrangères ====
Quelquefois deux tables doivent avoir un lien très similaire à une relation de clé étrangère, mais il y a des critères supplémentaires qui rendrait la clé étrangère insuffisante à traiter la vérification d'intégrité nécessaire. Dans cet exemple un table project contient une référence à la clé d'une table person dans sa propre colonne project_manager, mais une personne quelconque ne suffira pas; la personne spécifiée doit être un gestionnaire de projet.
On peut mettre en place cet exemple avec les ordres suivants:
 create table person
 (
 person_id int not null primary key,
 person_name text not null,
 is_project_manager boolean not null
 );
 create table project
 (
 project_id int not null primary key,
 project_name text not null,
 project_manager int not null
 );
 create index project_manager
 on project (project_manager);
 
 create function person_func()
 returns trigger
 language plpgsql as $$
 begin
 if tg_op = 'DELETE' and old.is_project_manager then
 if exists (select * from project
 where project_manager = old.person_id) then
 raise exception
 'une personne ne peut être supprimée tant quelle est responsable dun projet';
 end if;
 end if;
 if tg_op = 'UPDATE' then
 if new.person_id is distinct from old.person_id then
 raise exception 'il est interdit de modifier person_id';
 end if;
 if old.is_project_manager and not new.is_project_manager then
 if exists (select * from project
 where project_manager = old.person_id) then
 raise exception
 'une personne doit rester gestionnaire de projet tant quelle est responsable dun projet';
 end if;
 end if;
 end if;
 if tg_op = 'DELETE' then
 return old;
 else
 return new;
 end if;
 end;
 $$;
 create trigger person_trig
 before update or delete on person
 for each row execute procedure person_func();
 
 create function project_func()
 returns trigger
 language plpgsql as $$
 begin
 if tg_op = 'INSERT'
 or (tg_op = 'UPDATE' and new.project_manager <> old.project_manager) then
 if not exists (select * from person
 where person_id = new.project_manager
 and is_project_manager) then
 raise exception
 'project_manager doit être défini en tant que gestionnaire de projet dans la table person';
 end if;
 end if;
 return new;
 end;
 $$;
 create trigger project_trig
 before insert or update on project
 for each row execute procedure project_func();
 
 insert into person values (1, 'Kevin Grittner', true);
 insert into person values (2, 'Peter Parker', true);
 insert into project values (101, 'parallel processing', 1);
 Exemple de contrainte similaire à une contrainte de clé étrangère
session 1	session 2
Une personne est mise à jour pour ne plus être un gestionnaire de projet.
 begin;
 update person
 set is_project_manager = false
 where person_id = 2;	
 	
En même temps, un projet est mis à jour afin que de rendre cette personne responsable de ce projet.
 begin;
 update project
 set project_manager = 2
 where project_id = 101;
Il n'est pas possible de valider les deux. Le premier à valider gagne.
 commit;
L'affectation de la personne au projet valide d'abord, ce qui entraîne que l'autre transaction doit maintenant échouer. Si l'autre transaction s'était exécuté à un autre niveau d'isolation, les deux transactions auraient validé, entraînant une violation des règles métier.
 commit;
 ERROR: could not serialize access
 due to read/write dependencies
 among transactions
 DETAIL: Cancelled on identification
 as a pivot, during commit attempt.
 HINT: The transaction might succeed if retried.
A serialization failure. We roll back and try again.
 rollback;
 begin;
 update person
 set is_project_manager = false
 where person_id = 2;
 ERROR: une personne doit rester gestionnaire de 
 projet tant qu'elle est responsable d'un projet
Lors de la seconde tentative, nous récupérons un message intelligible.	
=== Transactions en Lecture Seule ===
Bien qu'une transaction en lecture seule ne puisse contribuer à une anomalie qui persiste dans la base, dans le mode Repeatable Read, elle peut "voir" un état qui n'est pas cohérent avec l'exécution sérialisée (une à la fois) des transactions. Une transaction Serializable implémentée avec SSI ne verra jamais ces anomalies transitoires.
----
==== Rapport de Dépôt ====
Une classe générale de problèmes invoquant des transactions en lecture seule est le traitement par lots, où une table contrôle quel lot (batch) est actuellement la cible des insertions. Un lot est fermé en mettant à jour la table de contrôle, point à partir duquel le lot est considéré comme "verrouillé" contre tout changement ultérieur, et le traitement de ce lot se produit.
Ce genre de problématique peut être trouvé de façon concrète dans le traitement de reçus. Des reçus peuvent être ajoutés à un lot identifié par une date de dépôt, ou (si plus d'un dépôt par jour est possible) un numéro de lot de reçu abstrait. Un un point durant la journée, alors que la banque est toujours ouverte, le lot est fermé, un rapport de l'argent reçu est imprimé, et l'argent est emmené à la banque pour y être déposé.
L'exemple peut être mis en place avec ces ordres:
 create table control
 (
 deposit_no int not null
 );
 insert into control values (1);
 create table receipt
 (
 receipt_no serial primary key,
 deposit_no int not null,
 payee text not null,
 amount money not null
 );
 insert into receipt
 (deposit_no, payee, amount)
 values ((select deposit_no from control), 'Crosby', '100');
 insert into receipt
 (deposit_no, payee, amount)
 values ((select deposit_no from control), 'Stills', '200');
 insert into receipt
 (deposit_no, payee, amount)
 values ((select deposit_no from control), 'Nash', '300');
 Exemple de Rapport de Dépôt
session 1	session 2
Au comptoir de réception, un autre reçu est ajouté au lot courant.
 begin; -- T1
 insert into receipt
 (deposit_no, payee, amount)
 values
 (
 (select deposit_no from control),
 'Young', '100'
 );
Cette transaction peut voir son propre insert, mais il n'est pas visible pour les autres transactions jusqu'à sa validation.
 select * from receipt;
 receipt_no | deposit_no | payee | amount 
 ------------+------------+--------+---------
 1 | 1 | Crosby | $100.00
 2 | 1 | Stills | $200.00
 3 | 1 | Nash | $300.00
 4 | 1 | Young | $100.00
 (4 rows)	
 	
 commit;
 ERROR: could not serialize access
 due to read/write dependencies
 among transactions
 DETAIL: Cancelled on identification
 as a pivot, during commit attempt.
 HINT: The transaction might succeed if retried.
OK, let's retry.
 rollback;
 begin; -- T1 retry
 insert into receipt
 (deposit_no, payee, amount)
 values
 (
 (select deposit_no from control),
 'Young', '100'
 );
À quoi ressemble la table reçu maintenant?
 select * from receipt;
 receipt_no | deposit_no | payee | amount 
 ------------+------------+--------+---------
 1 | 1 | Crosby | $100.00
 2 | 1 | Stills | $200.00
 3 | 1 | Nash | $300.00
 5 | 2 | Young | $100.00
 (4 rows)
Le reçu est maintenant dans le nouveau lot, rendant le rapport de dépôt de T3 correct!
 commit;
Plus de problème maintenant.	
 	
 commit;
Cela n'aurait posé aucun problème à n'importe quel moment après le SELECT de T3.
Category:Français