L'objectif de ce TME est de mettre en place des situations de concurrence entre transactions et de comprendre les mécanismes de contrôle de concurrence.

Voir l'extrait de la documentation Derby DB sur la gestion des transactions

Ne pas oublier de consulter les questions fréquentes en bas de cette page

Télécharger l'archive db-derby-10.14.2.0-bin.zip et la décompresser dans votre répertoire de travail et aller dans le dossier db-derby-…. dans toutes les instructions qui suivent, ajuster le numéro de version de db-derby selon votre installation: db-derby-10.14.2.0-bin ou db-derby-10.15.2.0.

• Windows: dans le dossier bin de db-derby, cliquer sur startNetworkServer.bat puis sur le client ij.bat.
• Linux: voir dans le dossier db-derby les commandes bin/startNetworkServer et bin/ij

La BD utilisée pour ce TME est Java DB, la version implémentée par Oracle de la BD open source Derby DB d'Apache.

Vous pouvez également suivre les instructions d'installation de Derby sur la page suivante: Derby Install Software

Étape1. Connaître votre version actuelle de Java JDK:

     java -version

Si Java n'est pas installé le télécharger à l'adresse suivante, sinon passer à l'étape 2: https://www.oracle.com/java/technologies/javase-downloads.html

Étape2. Télécharger Apache Derby à cette adresse http://db.apache.org/derby/derby_downloads.html (choisir une version binaire correspondante à votre version de Java déterminée à l'Étape 1, en principe https://downloads.apache.org/db/derby/db-derby-10.14.2.0/db-derby-10.14.2.0-bin.zip). Les exemples suivants supposent que vous avez téléchargé la version une des distributions suivantes appartenant à la version 10.15.2.0 (adapter ces exemples à votre propre version qui a été téléchargée):

Windows: db-derby-10.15.2.0-bin.zip
UNIX, Linux, Mac: db-derby-10.15.2.0-bin.tar.gz

Créer un répertoire pour le tme, télécharger et désarchiver derby dans ce répertoire:

WINDOWS:
mkdir C:\tme-transactions
copy db-derby-10.15.2.0-bin.zip C:\tme-transactions
cd C:\tme-transactions
unzip db-derby-10.15.2.0-bin.zip

UNIX:
mkdir ~/tme-transactions
cp db-derby-10.15.2.0-bin.tar.gz ~/tme-transactions
cd ~/tme-transactions
tar xzvf db-derby-10.15.2.0-bin.tar.gz

On va utiliser pour ce TME la table Account qui contient les comptes bancaires des différents clients. Pour chaque compte bancaire, on connaît son n° (acctID), le nom du client (cname) et le solde (balance):

• Account (acctID INTEGER PRIMARY KEY, cname VARCHAR(10), balance INTEGER)

Lancer le serveur Java DB (à exécuter une seule fois au début du TME):

• ouvrir un terminal et aller dans le répertoire de travail que vous avez créé pour ce TME (~/tme-transactions/). Exécuter la commande suivante dans le terminal:

     db-derby-10.15.2.0-bin/bin/startNetworkServer &

Si JAVA_HOME n'est pas défini le définir comme indiqué à l'étape 2 et lancer ensuite le serveur.

Remarque: pour arrêter le serveur à la fin du TME, utiliser l'option shutdown, voir les questions fréquentes en bas de cette page.

Créer la base de données et la table Account:

• Se connecter à Java DB. Dans le terminal, exécuter la commande suivante :

     db-derby-10.15.2.0-bin/bin/ij 

Si JAVA_HOME n'est pas défini le définir comme indiqué à l'étape 2.

• Une fois connecté à Java DB, exécuter la commande suivante afin de créer la BD :

    ij> CONNECT 'jdbc:derby://localhost:1527/accountdb;create=true';  

Cela aura pour effet la création d'un répertoire nommé accountdb dans votre répertoire de travail. Rmq: en cas d'erreur de connexion, vérifier que le serveur JavaDB est bien démarré (cf. 1ère étape de ce TME).

• Toujours dans Java DB, exécuter les commandes suivantes afin de créer la table Account:

    ij> autocommit off; 
    ij> CREATE TABLE Account (acctID INTEGER NOT NULL PRIMARY KEY,
                              cname VARCHAR(10) NOT NULL,
                              balance INTEGER NOT NULL);
    ij> DESCRIBE Account; 
    ij> Select * from Account;
    ij> commit;

• Quitter l'environnement Java DB:

    ij> exit; 

Lancer deux clients Java DB qui seront associés à deux transactions:

• Ouvrir 2 fenêtres de terminal juxtaposées et dans chacune aller dans le répertoire de travail du TME. Le premier terminal sera dénommé par la suite T1 car il sera associé à la première transaction, le deuxième sera dénommé T2 car il sera associé à la deuxième transaction.
• Dans chaque terminal:
  • Se connecter à Java DB avec la commande suivante:

     db-derby-10.15.2.0-bin/bin/ij

Si JAVA_HOME n'est pas défini le définir comme indiqué à l'étape 2.

• Une fois connecté à Java DB, exécuter la commande suivante afin de se connecter la BD:

    ij> CONNECT 'jdbc:derby://localhost:1527/accountdb;';  

• Toujours dans Java DB, désactiver le mode autocommit (mode par défaut dans lequel commit est exécuté après chaque instruction):

     ij> autocommit off;

Question 1)

• Dans le terminal T1 exécuter les instructions suivantes et expliquer le résultat observé:

ij> SELECT * FROM Account;
ij> INSERT INTO Account (acctID, cname, balance) VALUES (101, 'Client A', 1000);
ij> INSERT INTO Account (acctID, cname, balance) VALUES (202, 'Client B', 2000);
ij> SELECT * FROM Account;
ij> ROLLBACK;
ij> SELECT * FROM Account;

Question 2)

• Dans le terminal T1 exécuter les instructions des deux étapes suivantes et expliquer le résultat observé entre ces deux étapes:

Étape 1:

ij> INSERT INTO Account (acctID, cname, balance) VALUES (101, 'Client A', 1000);
ij> INSERT INTO Account (acctID, cname, balance) VALUES (202, 'Client B', 2000);
ij> SELECT * FROM Account;
ij> exit;

Étape 2:

Dans le terminal T1, se connecter à nouveau à Java DB:

 db-derby-10.15.2.0-bin/bin/ij
 
 ij> CONNECT 'jdbc:derby://localhost:1527/accountdb;';  
 ij> autocommit off;

Une fois connecté lire le contenu de la table Account:

ij> SELECT * FROM Account;

Question 3)

Quelle commande faut-il ajouter à l'étape 1 pour que les 2 lignes qui ont été insérées dans la table Account soient affichées à l'étape 2?

Étape d'initialisation:

Avant chacune des questions suivantes exécuter les instructions suivantes:

• Finir les transactions précédentes; dans T1 et T2 exécuter:

ij > commit;

• Remettre la table Account dans l'état initial; dans T1 exécuter:

ij> DELETE FROM Account;
ij> INSERT INTO Account (acctID, cname, balance) VALUES (101, 'Client A', 1000);
ij> INSERT INTO Account (acctID, cname, balance) VALUES (202, 'Client B', 2000);
ij> INSERT INTO Account (acctID, cname, balance) VALUES (303, 'Client C', 3000);
ij> SELECT * FROM Account;

• Choisir le niveau d'isolation correspondant; dans T1 et T2 exécuter:

ij> SET isolation <niveau-isolation>;

Où <niveau-isolation> est l'un parmi les niveaux suivants (voir les niveaux SQL-92 et le Tableau 6, page 78 dans la documentation Derby DB):

• READ UNCOMMITTED
• READ COMMITTED (par défaut)
• REPEATABLE READ (sera illustré par le mode RS en ij>)
• SERIALIZABLE

Opérations:

Le compte du 'Client A' correspondra au granule A, le compte du 'Client B' au granule B, le compte du 'Client C' au granule C. Chacune des deux transactions T1 et T2 peut effectuer des opérations parmi les opérations suivantes:

  L(g) : SELECT * FROM Account WHERE acctID = @g;
  E(g, v) : UPDATE Account SET balance = balance + v WHERE acctID=@g; 

L(g) représente la lecture d'un granule g parmi A, B ou C et @g est la valeur de acctID correspondante. E(g,v) représente une écriture du granule g en ajoutant la valeur v (la valeur v peut être positive ou négative). À noter qu'en réalité UPDATE réalise aussi une lecture qu'on va ignorer car UPDATE demande un verrou exclusif et pas de verrou partagé). Par exemple, L(A) et E(B,-100) correspondent aux instructions suivantes:

 SELECT * FROM Account WHERE acctID = 101;
 UPDATE Account SET balance = balance - 100 WHERE acctID = 202;

Pour chaque opération on va noter aussi la transaction qui l'exécute, par exemple L1(A) pour la lecture du compte 101 par T1. On va noter également par C1 et C2 les opérations commit réalisées par T1 et T2 et par R1 et R2 les opérations rollback réalisées par T1 et T2.

Pour cet exercice le niveau d'isolation des transactions sera SERIALIZABLE (voir tableau page 83 dans la documentation Derby DB):

• Les instructions SELECT demandent des verrous partagés
• UPDATE, DELETE et INSERT demandent des verrous exclusifs

Le verrouillage est strict, les verrous sont relâchés après commit ou rollback (voir la description “Shared Locks” et “Exclusive Locks” pour le niveau SERIALIZABLE, page 81 dans la documentation Derby DB).

Pour chacune des questions suivantes:

• tester si les exécutions proposées sont possibles
• écrivez les exécutions effectives obtenues en insérant les demandes de verrouillage et déverouillage pour chaque transaction (écrire par exemple +VP1(g) et -VX2(g) pour un verouillage et un déverouillge du granule g par T1 et T2)
• donnez l'ordre en série équivalent et les valeurs finales dans la table Account.

Première partie: Test du verrouillage

Question 1)

Vérifiez qu'une lecture faite par T1 ne bloque pas une lecture faite par T2

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• L1(A) L2(A) C1 C2

Question 2)

Vérifiez qu'une lecture faite par T1 bloque une écriture faite par T2. Quelle sera l'instruction de T1 qui va débloquer T2?

• L1(A) E2(A,100) C1 C2

Question 3)

Vérifiez qu'une écriture faite par T1 bloque une lecture faite par T2.

• E1(A, 100) L2(A) C1 C2

Question 4)

Vérifiez qu'une écriture faite par T1 bloque une écriture faite par T2.

• E1(A, 100) E2(A, 100) C1 C2

Question 5)

On suppose maintenant que T1 et T2 travaillent sur des granules différentes (T1 sur A et T2 sur B par exemple). Reprendre l'exécution de la question 4 précédente en changeant le granule pour T2. T2 est-elle toujours bloquée? Pour quelle raison?

Deuxième partie: Verrouillage en deux phases

Exécutions sans interblocage

Question 6)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• L1(A) E2(A,-500) E1(A, 100) C1 C2

Question 7)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• L1(B) E2(A, -500) E1(A,100) C1 C2

Question 8 (question TD)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• L2(B) E2(B, -70) L2(C) E2(C, 70) L1(A) E1(A,-150) L1(B) E1(B,150) C1 C2

Exécutions avec interblocage

Vérifiez que lorsqu'un interblocage se produit, Java DB choisit d'abandonner la transaction qui détient le moins de verrous (en supposant aisi que ce sera la transaction qui aura effectué le moins de travail avant l'interblocage).

Question 9)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• E1(A, -150) E2(B, -500) E1(C, 70) E1(B,-500) E2(A, -150) C1 C2

Question 10)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE

Donnez une exécution concurrente de T1 et T2 effectuant des opérations sur 2 granules différents, au moins deux de ces opérations étant des lectures. Cette exécution doit produire un interblocage.

Question 11 (question TD)

• Exécutez l'étape d'initialisation avec le niveau SERIALIZABLE
• L1(A) E1(A,-150) L1(B) L2(B) E2(B,-70) L2(C) E1(B,150) E2(C,70) C1 C2

• Lire les transparents de cours sur les niveaux d'isolation SQL-92 et les anomalies des transactions.

Le niveau d'isolation SERIALIZABLE étant restrictif, l'utilisation des verrous pourrait mettre en attente des transactions dont l'exécution ne pose pas de problème pour la cohérence de la BD. Afin d'augmenter la concurrence et diminuer le temps d'attente d'autres niveaux d'isolation peuvent être utilisés. Cet exercice permet d'étudier les anomalies qui peuvent être rencontrées dans ce cas.

Question 1 (Lecture sale)

Une transaction peut lire des données sales (non validées). Cette anomalie peut être observée en utilisant le niveau READ UNCOMMITTED.

a) Pour l'exécution suivante, testez que T2 peut lire le compte du Client A qui est modifié par T1, alors que T1 n'a pas encore validé ses modifications. Que peut-on conclure sur la fiabilité de T2? Existe-t-il des verrous?

  * Exécutez l'étape d'initialisation avec le niveau READ UNCOMMITTED
  * E1(A, 100) L1(A) L2(A) C2 R1 L1(A) C1

b) Testez la même exécution en changeant d'abord le niveau d'isolation à READ COMMITTED pendant l'étape d'initialisation. T2 voit-elle toujours l'écriture faite par T1? Existe-t-il des verrous? Quel est leur type et avant quelles opérations sont-ils demandés?

Question 2 (Lecture non réproductible)

Le niveau d'isolation sera READ COMMITTED, une transaction pourra lire seulement des données validées (on ne peut donc plus avoir des lectures sales). Il peut cependant y avoir des lectures non réproductibles: la relecture de la même ligne dans la même transaction peut montrer un résultat différent.

a) Pour l'exécution suivante, testez que les deux lectures successives du compte appartenant au Client A par T1 affichent deux valeurs différentes. Quels verrous ont été utilisés? Avant quelles opérations ont-ils été demandés et après quelles opérations sont-ils relâchés?

  * Exécutez l'étape d'initialisation avec le niveau READ COMMITTED
  * L1(A) E2(A, 100) C2 L1(A) C1

b) Testez la même exécution en changeant d'abord le niveau d'isolation à RS (REPEATABLE READ) pendant l'étape d'initialisation. T1 voit-elle toujours deux valeurs différentes du compte? Quels verrous ont été utilisés? Avant quelles opérations ont-ils été demandés et après quelles opérations sont-ils relâchés?

Question 3 (Lecture fantôme)

Le niveau d'isolation sera RS (REPEATABLE READ). Ce niveau empêche les lectures sales et non réproductible. Il peut y avoir cependant le problème lié aux lectures fantômes: pour une transaction, si on relance la même requête à deux instants différents, l'ensemble des lignes résultat aux deux instants peut être différent car de nouveaux enregistrements ont pu être ajoutés/supprimés dans/de la base par une autre transaction, ce qui modifie le résultat. Ceci est différent de la lecture non réproductible dans le sens où les enregistrements lus précédemment n’ont pas changé, mais des enregistrements peuvent être introduits/supprimés du résultat de la requête.

a) Pour l'exécution suivante, testez que les deux lectures successives de la table Account effectuées par T1 affichent un ensemble de résultats différent.

  * Exécutez l'étape d'initialisation avec le niveau REPEATABLE READ
  * L1 E2 C2 L1 C1

où L1 représente la lecture de toute la table Account réalisée par T1:

SELECT * FROM Account;

E2 représente l'insertion d'une ligne correspondante au compte d'un 'Client D' réalisée par T2:

INSERT INTO Account VALUES(404, 'Client D', 4000);

b) Testez la même exécution en changeant d'abord le niveau d'isolation à SERIALIZABLE pendant l'étape d'initialisation. Le niveau SERIALIZABLE empêche les lectures sales, non réproductibles ainsi que les lectures fantôme.

Tester les exécutions correspondantes aux Questions 1 à 11 de l'exercice 2 pour les niveaux d'isolation: READ UNCOMMITTED, READ COMMITED et REPEATABLE READ.

• Comment quitter Java DB?

 exit;

• Comment arrêter Le serveur Java DB?

     db-derby-10.15.2.0-bin/bin/stopNetworkServer &

Si JAVA_HOME n'est pas défini le définir comme indiqué à l'étape 2.