les transactions en BDD

Quand on développe un programme P accédant à une base de données, on suppose que :

P s’exécutera indépendamment de tout autre programme ou utilisateur
l’exécution de P se déroulera toujours intégralement.

Or les bases de données constituent des ressources accessibles simultanément à plusieurs utilisateurs pour rechercher, créer, modifier ou détruire des informations :

les accès simultanés à une même ressource sont dits concurrents => contrôle de concurrence

Il existe une multitude de raison qu’un programme ne se déroule pas intégralement (arrêt des serveurs, erreurs de code, violation de contrainte …) => reprise sur panne

http://sql.bdpedia.fr/transactions.html

Définition des points de sauvegardes.(commit)
Blocages des autres utilisateurs. Le contrôle de concurrence => verrouillage de certaines resources (tables blocs, n-uplets)
Choix d’un niveau d’isolation

les bases

DELETE UPDATE INSERT : ce sont des instructions transactionnelles atomiques (elles fonctionnent complètement ou pas du tout)

Le concept de transaction sur un compte (bancaire …)

on désire :
- retirer 1000€ sur le compte de ref 111111
- rajouter 1000€ sur le compte de ref 222222
- enregistrer l’information : ‘M Dupond’ a déplacé de l’argent (1000€) de son compte 111111 à son compte 222222

UPDATE sb_accounts SET balance = balance - 1000 WHERE account_no = 111111 ; 
UPDATE ca_accounts SET balance = balance + 1000 WHERE account_no = 222222 ; 
INSERT INTO journal VALUES (100896, 'Transfert de M Dupond ', '2017-01-01', 111111, 222222, 1000); 
COMMIT WORK;

Si pour une raison quelconque entre les 2 “UPDATE” la session est stoppée, l’argent serait perdu !

documentation : “doc developpez.com” “doc postgreSQL”

Tester les instructions ci dessous :

DROP TABLE IF EXISTS Client;

CREATE TABLE Client (
    id_client INTEGER  NOT NULL,
    nom varchar(30)  NOT NULL,
    nb_places_reservees INTEGER  NOT NULL,
    solde INTEGER  NOT NULL,
    PRIMARY KEY (id_client)
);

INSERT into Client VALUES (1, 'Philippe', 0, 2000);
INSERT into Client VALUES (2, 'Julie', 0, 350);
INSERT into Client VALUES (3, 'alex', 0, 350);

SELECT @@autocommit;
UPDATE Client SET nom="ALEXANDRE" WHERE id_client=3;



SET autocommit=0;  -- Les "COMMIT" ne sont plus automatiques

SELECT * FROM Client WHERE id_client=3;

START TRANSACTION;
UPDATE Client SET nom="alex1" WHERE id_client=3;
SELECT * FROM Client WHERE id_client=3;

ROLLBACK;
SELECT * FROM Client WHERE id_client=3;

UPDATE Client SET nom="alex2" WHERE id_client=3;
SELECT * FROM Client WHERE id_client=3;

-- se connecter depuis un autre terminal sur cette base de données (nouvelle session) ; qu'elle est la valeur du champ nom ?

COMMIT;
SELECT * FROM Client WHERE id_client=3;

ROLLBACK;
SELECT * FROM Client WHERE id_client=3;

l’instruction “START TRANSACTION;” est elle obligatoire pour faire une transaction (revenir en arrière avant le COMMIT) ?
lorsqu’une transaction est démarrée, peut on voir les changements depuis une autre session avant le “COMMIT” ?

Reprendre le script précédent, démarrer la transaction, modifier la valeur du nom.
Ouvrir un autre terminal sur la même base de données : une autre session, exécuter l’instruction ci-dessous

SELECT @@autocommit;
SELECT * FROM Client WHERE id_client=3;
UPDATE Client SET nom="alex ***** 1" WHERE id_client=3;
SELECT * FROM Client WHERE id_client=3;

terminer la première transaction, … conclusion

CONCLUSION : Propriétés des transactions : ACIDité [Harder&Reuter, ACM CS 15(4), 1983]

Atomicité : Toutes les MAJ sont exécutées ou aucune
Cohérence : Passer d’un état cohérent à un autre (pas géré par le système)
Isolation : La transaction s’effectue comme si elle était seule
Durabilité : Une fois une transactions validée, son effet ne peut pas être perdu suite à une panne quelconque.

https://makina-corpus.com/devops/bien-debuter-avec-les-transactions-sql
http://sys.bdpedia.fr/transactions.html https://sgbd.developpez.com/tutoriels/cours-complet-bdd-sql/?page=concurrence-et-reprise#LXI

https://zestedesavoir.com/tutoriels/730/administrez-vos-bases-de-donnees-avec-mysql/952_securiser-et-automatiser-ses-actions/3952_transactions/

exercice transaction

DROP TABLE IF EXISTS Spectacle, Journal;


CREATE TABLE Spectacle (
    id_spectacle INTEGER  NOT NULL,
    titre VARCHAR(30)  NOT NULL,
    nb_places_offertes INTEGER  NOT NULL CHECK(nb_places_offertes>=0),
    nb_places_libres INTEGER  NOT NULL CHECK(nb_places_libres>=0),
    tarif DECIMAL(10,2)  NOT NULL,
    PRIMARY KEY (id_spectacle)
);

CREATE TABLE Journal (
    id INTEGER  AUTO_INCREMENT,
    libelle_transaction VARCHAR(30)  NOT NULL,
    client_id INTEGER,
    nb_places INTEGER,
    spectacle_id INTEGER,
    tarif DECIMAL(19,4),
    PRIMARY KEY (id)
);

SET autocommit = 0;
DELETE FROM Client;
DELETE FROM Spectacle;

INSERT into Client VALUES (1, 'Philippe', 0, 2000);
INSERT into Client VALUES (2, 'Julie', 0, 350);

INSERT into Spectacle VALUES (1, 'Ben hur', 250, 50, 50);
INSERT into Spectacle VALUES (2, 'Tartuffe', 120, 30, 30);

COMMIT;

faire un premier script (transaction le client “1” achete 20 places de ‘Ben hur’)
- décrémenter le nombre de places libres de 20 dans la table “Spectacle”
- incrémenter de 20 la colonne “nb_places_reservees” dans la table “Client”
- calculer le prix des places
- écrire dans la table “Journal” : ‘achat de place’, l’identifiant du client et du spectacle ainsi que les prix des places

utiliser les variables en début de script (nb_place, spectacle_id et client_id)

SET @nb_place=20;
SET @spectacle_id=1;
SET @client_id=1;

SET autocommit = 0;
START TRANSACTION;

SELECT @nb_place,@spectacle_id,@client_id;

-- à compléter
-- à compléter

-- à compléter

-- à compléter


SELECT * FROM Spectacle;
SELECT * FROM Client;
SELECT * FROM Journal;

COMMIT;

SELECT * FROM Spectacle;
SELECT * FROM Client;
SELECT * FROM Journal;

REMARQUE : lors du COMMIT toutes les opérations valides sont exécutées => programme pour vérifier l’intégrité des données

http://sql.bdpedia.fr/transactions.html#s1-transactions

les verrous

LOCK TABLES nom_table [AS alias_table]  [READ  | WRITE]   [,....];

En utilisant READ : verrou en lecture, les autres sessions pourront que lire les données des tables verrouillées, mais ne pourront pas les modifier (SELECT uniquement)
En utilisant WRITE : verrou en écriture, les autres sessions pourront ni lire ni modifié les données des tables verrouillées.

Conséquence de l’utilisation de verrou :

Accès uniquement au table verrouillée
Lorsqu’une session MySQL pose un verrou sur un élément de la base de données, cela veut dire qu’il restreint, voire interdit, l’accès à cet élément aux autres sessions MySQL qui voudraient y accéder.

session 1 (terminal)

LOCK TABLES Client   READ,              -- On pose un verrou de lecture sur Client
            Spectacle  WRITE;           -- et un verrou d'écriture sur Spectacle

exécuter les 3 requêtes ci-dessous dans 2 terminaux

session 1 et Session 2 (terminal)

SELECT * FROM Client;
SELECT * FROM Journal;
SELECT * FROM Spectacle;

session 1 et Session 2 (terminal)

UPDATE Client SET nb_places_reservees = 1  WHERE id_client=1;
UPDATE Spectacle SET nb_places_libres = 2 WHERE id_spectacle=1;

session 1 (terminal)

UNLOCK TABLES  ;

Verrous de TABLE et verrous de ligne Il est possible de poser un verrou sur une TABLE entière, ou seulement sur une ou plusieurs lignes d’une TABLE. Étant donné qu’un verrou empêche l’accès d’autres sessions, il est en général plus intéressant de poser un verrou sur la plus petite partie de la base possible.

Cette notion d’accès simultané aux données par plusieurs sessions différentes s’appelle la concurrence. Plus la concurrence est possible, donc plus le nombre de sessions pouvant accéder aux données simultanément est grand, mieux c’est. En effet, prenons l’exemple d’un site web. En général, on préfère permettre à plusieurs utilisateurs de surfer en même temps, sans devoir attendre entre chaque action de pouvoir accéder aux informations chacun à son tour. Or, chaque utilisateur crée une session chaque fois qu’il se connecte à la base de données (pour lire les informations ou les modifier). Préférez donc (autant que possible) les verrous de ligne aux verrous de TABLE !

Conséquences pour les autres sessions

Si une session a obtenu un verrou de lecture sur une TABLE, les autres sessions :

peuvent lire les données de la TABLE ;
peuvent également acquérir un verrou de lecture sur cette TABLE ;
ne peuvent pas modifier les données, ni acquérir un verrou d’écriture sur cette TABLE.

Si par contre une session a obtenu un verrou d’écriture, les autres sessions ne peuvent absolument pas accéder à cette TABLE tant que ce verrou existe.

Verrou mortel

SET autocommit = 0;
START TRANSACTION;
SELECT "P1";
SELECT SLEEP(15);
UPDATE Client SET nom="alexandre 1" WHERE id_client=1;
SELECT SLEEP(15);
UPDATE Spectacle SET titre="Ben hur 1" WHERE id_spectacle=1; 

-- lancer l'autre script dans un terminal
SELECT "P1";
COMMIT;

SET autocommit = 0;
START TRANSACTION;
SELECT "P2";
UPDATE Spectacle SET titre="Ben hur 2" WHERE id_spectacle=1;
SELECT SLEEP(15);
UPDATE Client SET nom="alexandre 2" WHERE id_client=1;
SELECT "P2";
COMMIT;

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; doc

SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_on_timeout';

interblocage (deadlocks)

autres moyens de verrouillage

SELECT * FROM Spectacle WHERE id_spectacle = 1 LOCK IN SHARE MODE;
UPDATE Spectacle SET nb_places_libres=5 WHERE id_spectacle = 1;

: pose un verrou partagé sur la prochaine clé sur tous les index que la lecture rencontre.

SELECT * FROM Spectacle WHERE id_spectacle = 1 FOR UPDATE;
UPDATE Spectacle SET nb_places_libres=10 WHERE id_spectacle = 1;

: pose un verrou exclusif sur la prochaine clé sur tous les index que la lecture rencontre.

http://samisd2003.free.fr/WinLAMP/MYSQL/innodb-locking-reads.html

niveau d’isolation transaction

niveau 0 : READ-UNCOMMITTED ; autorise la lecture sale

niveau 1 : READ-COMMITTED ; interdit la lecture sale

niveau 2 : REPEATABLE-READ (niveau par défaut) ; idem, et rend la session aveugle aux modifications (mêmes validées) faites par les autres sessions (“lecture cohérente” ou consistent read)

niveau 3 : SERIALIZABLE : idem, et fait comme si les transactions étaient validées les unes après les autres et non en même temps

article sur MS SQLserver

SELECT @@TX_ISOLATION;

# Et voici les commandes pour régler le niveau d'isolation
SET session transaction isolation level read uncommitted;
SET session transaction isolation level read committed;
SET session transaction isolation level repeatable read;
SET session transaction isolation level serializable;

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html
https://stackoverflow.com/questions/6121917/automatic-rollback-if-commit-transaction-is-not-reached

MySQL 8+ SELECT @@transaction_isolation; -- session
SELECT @@global.transaction_isolation; -- global

https://mysql.developpez.com/faq/?page=Transactions
https://www.canal-u.tv/chaines/inria/transactions-et-concurrence/degres-d-isolation-dans-les-sgbd

article sympa :

https://medium.com/neoxia/deepdive-voyage-au-pays-des-lock-mysql-innodb-2bb043f9739

procédure et fonction

https://fr.wikibooks.org/wiki/MySQL/Proc%C3%A9dures_stock%C3%A9es

gestion d’une erreur

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Problème: pas assez de places disponibles.';

DROP PROCEDURE IF EXISTS sp_commande_spectacle ;

DELIMITER //

CREATE PROCEDURE sp_commande_spectacle(IN p_nb_places INT, IN p_client_id INT, IN p_spectacle_id INT)

BEGIN

START TRANSACTION;

-- à compléter
-- à compléter

-- à compléter

-- à compléter


COMMIT;

END; //

DELIMITER ;

SELECT * FROM Spectacle;
SELECT * FROM Client;
SELECT * FROM Journal;
CALL sp_commande_spectacle(25,1,1);
SELECT * FROM Spectacle;
SELECT * FROM Client;
SELECT * FROM Journal;

https://stackoverflow.com/questions/6121917/automatic-rollback-if-commit-transaction-is-not-reached
https://khanrahim.wordpress.com/2010/05/16/transaction-with-stored-procedure-in-mysql-server/

DECLARE exit handler for sqlexception
  BEGIN
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
 ROLLBACK;
END;

CREATE DEFINER=##@% PROCEDURE sp_commande_spectacle(IN nb_places INT, IN client_id INT, IN spectacle_id INT)

DROP PROCEDURE IF EXISTS sp_commande_spectacle ;

DELIMITER //

CREATE PROCEDURE sp_commande_spectacle(IN p_nb_places INT, IN p_client_id INT, IN p_spectacle_id INT)

BEGIN

DECLARE exit handler for sqlexception
  BEGIN
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SQLError occured. Procedure error ROLLBACK';
END;

DECLARE exit handler for sqlwarning
 BEGIN
 ROLLBACK;
 SIGNAL SQLSTATE '35000' SET MESSAGE_TEXT = 'SQLwarning occured. Procedure warning ROLLBACK';
END;

START TRANSACTION;

-- à compléter
-- à compléter

-- à compléter

-- à compléter

COMMIT;

END; //

DELIMITER ;

trigger

Dans MySQL, un trigger (ou déclencheur) est une commande SQL définie par l’utilisateur qui est automatiquement invoquée lors d’une opération INSERT, DELETE ou UPDATE. Le code trigger est associé à une table et est détruit une fois que la table est supprimée.

un trigger, c’est quoi ?https://www.w3resource.com/mysql/mysql-triggers.php
présentation (voir article)

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

exemple :

DELIMITER #

CREATE TRIGGER trigger_CLIENT_insert_before
BEFORE INSERT
ON Client
FOR EACH ROW
BEGIN
IF NEW.nom='' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Le nom ne peut pas etre une chaine vide';
END IF;    
END; #

DELIMITER ;

autre solution

IF NEW.nom='' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Le nom ne peut pas etre une chaine vide';
ELSE
    SET NEW.nom=LOWER(NEW.nom);
END IF;

pour tester

SHOW TRIGGERS;
SELECT * FROM Client;
INSERT into Client VALUES (3, 'SEBastien', 0, 350);
INSERT into Client VALUES (4, '', 0, 350);
SELECT * FROM Client;


DROP TRIGGER trigger_CLIENT_insert_before;
DELETE FROM Client WHERE id_client>2;

autre exemple

exercice : trigger ; fonctionnalité : lors d’une mise à jour de client mettre le nom en MAJUSCULE

DROP TABLE IF EXISTS log_client_update;
CREATE TABLE log_client_update (
    id INTEGER  AUTO_INCREMENT,
    client_id INTEGER,
    updated_date  DATETIME,
    updated_by VARCHAR(255),
    PRIMARY KEY (id)
);

DROP TRIGGER Client_after_update;
DELIMITER //

CREATE TRIGGER Client_after_update
AFTER UPDATE
   ON Client FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the INSERT into table
   SELECT USER() INTO vUser;

   -- Insert record into audit table
-- à compléter
-- à compléter


END; //

DELIMITER ;

UPDATE Client SET nom='SEBastien' , nb_places_reservees = nb_places_reservees + 1
WHERE id_client=1;
SELECT * FROM Client;
SELECT * FROM log_client_update;

IF NEW.nom='' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Le nom ne peut pas etre une chaine vide';
ELSE
-- à compléter
-- à compléter
END IF;

https://www.orleans-informatique.com/info/sio/exercices/TP%20N%C2%B05%20-%20SQL%20et%20triggers.pdf

https://www.orleans-informatique.com/etudiants/informatique/bts-sio/sql-conception-base-donnees

exemples