Bases de données
R. Charrier / G. Simon / B. Sadeg

<<<<< Transactions pgSQL : comprendre le fonctionnement d'une transaction >>>>>

    •  

    • Savoir manipuler des transactions sous Postgres

     


  • On suppose que la base de données Produit-Achat-Client est installé



  • Définitions
    • Définition 1 : Une transaction est un ensemble de requêtes et d'actions qui permet de passer d'un état cohérent de la base de données à un autre état cohérent.
    • Définition 2 : Dans la base, du point de vue d'un client réalisant une suite d'opérations, il est souhaitable que les différentes opérations de cette séquence apparaissent comme une opération unique (donc que les différentes opérations apparaissent comme étant exécutées simultanément). Une séquence d'instructions possédant cette propriété est dite atomique et est appelée transaction (atomique).
        Pour la plupart des questions, utiliser deux terminaux avec dans chacun une connexion différente à Ces deux terminaux seront nommés xterm1 et xterm2 dans la suite.
  • Question 1 : connexions multiples sans transaction explicite
    • Dans le xterm1, taper la commande suivante : 
      INSERT INTO produit (np, lib, coul, qs) VALUES(50, 'feuilles','blanche', 100000);
    • Dans chacun des deux xterms, taper la commande suivante : 
      SELECT * FROM PRODUIT;
      		 La modification de la table produit est visible dans les deux cas

  •  Question 2 : Validation de transaction par commit

    Tant qu'une transaction explicite n'est pas terminée (par "commit" ou "rollback"), les données ne sont pas réellement écrites dans la base de données.
    • étape 1 -> dans le xterm 1 :
      • SELECT * from produit;
      
BEGIN transaction;
      
INSERT INTO produit (np, lib, coul, qs) VALUES(50, 'feuilles','blanche', 100000);
SELECT * from produit;
      		 la modification est visible lors du deuxième select
    • étape 2 -> dans le xterm2 : 
      SELECT * from produit;
      		   la modification de la table produit n'apparaît pas. 
    • étape 3 -> retour au xterm1 : 

      COMMIT transaction;

      		  le commit valide toutes les requêtes depuis le début de la transaction.
       les modifications deviennent donc persistantes dans la base de données.
    • étape 4 -> à nouveau dans le xterm2 : 
      SELECT * from produit;
      		   la modification de la table produit est bien visible maintenant.
  • Question 3 : Annulation de transaction par rollback
           Recommencer le même scénario que dans la question 2 mais en remplaçant COMMIT par ROLLBACK à l'étape 3.
           Que constatez-vous ?

  • Question 4 : Ensemble de mises-à-jour cohérentes

       Dans cette question, on ne travaille qu'avec une seule connexion sur la base de données.
        Soient les tables achat(np, ncli, qa) et produit(np, lib, couleur, qs)
        Si un nouvel achat est effectué (exemple : le client 101 a acheté 55 articles du produit 3), il faudra faire deux actions :
        1) insérer cet achat dans la table Achat.
        2) décrémenter le stock du produit 3 de la quantité achetée dans la table produit (ici  55).

        Cette contrainte avait été précédemment traitée par l'introduction d'un trigger qu'il faut SUPPRIMER pour ce tp.
        Pour une raison de cohérence des données, on ne peut pas faire la première action sans être certain que la deuxième action sera faite !
        Les deux requêtes SQL doivent donc être considérées par le SGBD comme une seule opération. Pour ce faire, une transaction va être utilisée.

        question 4.1 : écrire la transaction permettant de réaliser un nouvel achat (vous prendrez un exemple cohérent avec vos données).
        question 4.2 : tant que le commit n'a pas été fait, quelle est la quantité en stock pour les autres utilisateurs ?

Dans les questions 5,6 et 7, on va de nouveau travailler avec deux connexions dans deux xterms différents. Mais on va cette fois manipuler deux transactions en parallèle.
  • Question 5 : Transactions concurrentes - insert+select
transaction 1 (xterm1)
 transaction 2   (xterm2)
begin transaction;
 begin transaction;
insert into client(ncli,nom) values (120,'WOOLDRIDGE');
 ... on attend...
select * from client;
 ... on  attend ...
... on attend...  select * from client;
commit transaction;
 ... on attend ...
... on attend ... select * from client;
... on attend ...
 commit transaction;

Que constatez-vous ?
  • Question 6 : Transactions concurrentes - 2 update sur le même tuple
transaction 1 (xterm1)
 transaction 2   (xterm2)
begin transaction;
 begin transaction;
update client set nom = 'JENNINGS' where ncli = 120;
 ... on attend...
select * from client;
 ... on  attend ...
... on attend...  select * from client;
... on attend ...
 update client set nom = 'SYCARA' where ncli = 120;
commit transaction; ... on attend ...
select * from client;
 ... on attend ...
... on attend ...
 select * from client;
... on attend ...
 commit transaction;
... on attend ...
 select * from client;

Que constatez-vous ?
  • Question 7 : Transactions concurrentes - Etreinte fatale
transaction 1 (xterm1)
 transaction 2   (xterm2)
begin transaction;
 begin transaction;
update produit set qs = 2000 where np = 50;
 ... on attend...
... on attend ...
 update produit set qs = 100 where np = 10;
... on attend...  select * from produit;
select * from produit;
 ... on attend ...
update produit set qs = 500 where np = 10; ... on attend ...
... on attend ...
 update produit set qs = 5000 where np = 50;
select * from produit;
 ... on attend ...
... on  attend ...
 commit transaction;
... on attend ...
 select * from produit;
commit transaction; ... on attend ...
select * from produit;
 ... on attend ...
... on attend;
 select * from produit;

Que constatez-vous ?