Verrous

• Transactions
• Sommaire
• Requêtes préparées

Complément indispensable des transactions, les verrous permettent de sécuriser les requêtes en bloquant ponctuellement et partiellement l'accès aux données.

Il s'agit d'un gros chapitre, avec beaucoup d'informations. Il y a par conséquent un maximum d'exemples pour vous aider à comprendre le comportement des verrous selon les situations.

Au sommaire de ce chapitre :

• Qu'est-ce qu'un verrou ?
• Quel est le comportement par défaut de MySQL par rapport aux verrous ?
• Quand et comment poser un verrou de table ?
• Quand et comment poser un verrou de ligne ?
• Comment modifier le comportement par défaut de MySQL ?

• Principe
• Syntaxe et utilisation : verrous de table
• Syntaxe et utilisation : verrous de ligne
• Niveaux d'isolation

Principe

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.

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.

Par exemple, si l'on travaille avec les chiens de la table Animal.

• On peut poser un verrou sur toute la table Animal. Dans ce cas, les autres sessions n'auront pas accès à cette table, tant que le verrou sera posé. Qu'elles veuillent en utiliser les chiens, les chats, ou autre, tout leur sera refusé.
• On peut aussi poser un verrou uniquement sur les lignes de la table qui contiennent des chiens. De cette manière, les autres sessions pourront accéder aux chats, aux perroquets, etc. Elles pourront toujours travailler, tant qu'elles n'utilisent pas les chiens.

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 !

Avertissements

Les informations données dans ce chapitre concernent exclusivement MySQL, et en particulier les tables utilisant les moteurs MyISAM ou InnoDB (selon le type de verrou utilisé). En effet, les verrous sont implémentés différemment selon les SGDB, et même selon le moteur de table en ce qui concerne MySQL. Si le principe général reste toujours le même, certains comportements et certaines options peuvent différer d'une implémentation à l'autre. N'hésitez pas à vous renseigner plus avant.

Par ailleurs, je vous présente ici les principes généraux et les principales options, mais il faut savoir qu'il y a énormément à dire sur les verrous, et que j'ai donc dû faire un sérieux tri des informations avant de rédiger ce chapitre. À nouveau, en cas de doute, ou si vous avez besoin d'informations précises, je vous conseille vraiment de consulter la documentation officielle (si possible en anglais, car elle est infiniment plus complète qu'en français).

Enfin, dernier avertissement : de nombreux changements dans l'implémentation des verrous sont advenus lors du développement des dernières versions de MySQL. Aussi, la différence entre les verrous dans la version 5.0 et la version 5.5 est assez importante. Tout ce que je présente dans ce chapitre concerne la version 5.5. Vérifiez bien votre version, et si vous consultez la documentation officielle, prenez bien celle qui concerne votre propre version.

Modification de notre base de données

Nous allons ajouter deux tables à notre base de données, afin d'illustrer au mieux l'intérêt et l'utilisation des verrous : une table Client, qui contiendra les coordonnées des clients de notre élevage, et une table Adoption, qui contiendra les renseignements concernant les adoptions faites par nos clients. Dorénavant, certains animaux présents dans notre table Animal ne seront plus disponibles, car ils auront été adoptés. Nous les garderons cependant dans notre base de données. Avant toute adoption, il nous faudra donc vérifier la disponibilité de l'animal.

Voici les requêtes à effectuer pour faire ces changements.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

-- Table Client
CREATE TABLE Client (
  id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(60) NOT NULL,
  adresse VARCHAR(200),
  code_postal VARCHAR(6),
  ville VARCHAR(60),
  pays VARCHAR(60),
  email VARBINARY(100),
  PRIMARY KEY (id),
  UNIQUE INDEX ind_uni_email (email)
) ENGINE = InnoDB;

-- Table Adoption
CREATE TABLE Adoption (
  client_id SMALLINT UNSIGNED NOT NULL,
  animal_id SMALLINT UNSIGNED NOT NULL,
  date_reservation DATE NOT NULL,
  date_adoption DATE,
        prix DECIMAL(7,2) UNSIGNED NOT NULL,
  paye TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (client_id, animal_id),
  CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client(id),
  CONSTRAINT fk_adoption_animal_id FOREIGN KEY (animal_id) REFERENCES Animal(id),
  UNIQUE INDEX ind_uni_animal_id (animal_id)
) ENGINE = InnoDB;

-- Insertion de quelques clients
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Jean', 'Dupont', 'Rue du Centre, 5', '45810', 'Houtsiplou', 'France', 'jean.dupont@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Marie', 'Boudur', 'Place de la Gare, 2', '35840', 'Troudumonde', 'France', 'marie.boudur@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Fleur', 'Trachon', 'Rue haute, 54b', '3250', 'Belville', 'Belgique', 'fleurtrachon@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Julien', 'Van Piperseel',  NULL, NULL, NULL, NULL, 'jeanvp@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Johan', 'Nouvel',  NULL, NULL, NULL, NULL, 'johanetpirlouit@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Frank', 'Germain',  NULL, NULL, NULL, NULL, 'francoisgermain@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Maximilien', 'Antoine', 'Rue Moineau, 123', '4580', 'Trocoul', 'Belgique', 'max.antoine@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Hector', 'Di Paolo', NULL, NULL, NULL, NULL, 'hectordipao@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Anaelle', 'Corduro',  NULL, NULL, NULL, NULL, 'ana.corduro@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Eline', 'Faluche', 'Avenue circulaire, 7', '45870', 'Garduche', 'France', 'elinefaluche@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Carine', 'Penni', 'Boulevard Haussman, 85', '1514', 'Plasse', 'Suisse', 'cpenni@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Virginie', 'Broussaille', 'Rue du Fleuve, 18', '45810', 'Houtsiplou', 'France', 'vibrousaille@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Hannah', 'Durant', 'Rue des Pendus, 66', '1514', 'Plasse', 'Suisse', 'hhdurant@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Elodie', 'Delfour', 'Rue de Flore, 1', '3250', 'Belville', 'Belgique', 'e.delfour@email.com');
INSERT INTO Client (prenom, nom, adresse, code_postal, ville, pays, email) VALUES ('Joel', 'Kestau', NULL, NULL, NULL, NULL, 'joel.kestau@email.com');

-- Insertion de quelques adoptions
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (1, 39, '2008-08-17', '2008-08-17', 735.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (1, 40, '2008-08-17', '2008-08-17', 735.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (2, 18, '2008-06-04', '2008-06-04', 485.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (3, 27, '2009-11-17', '2009-11-17', 200.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (4, 26, '2007-02-21', '2007-02-21', 485.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (4, 41, '2007-02-21', '2007-02-21', 835.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (5, 21, '2009-03-08', '2009-03-08', 200.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (6, 16, '2010-01-27', '2010-01-27', 200.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (7, 5, '2011-04-05', '2011-04-05', 150.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (8, 42, '2008-08-16', '2008-08-16', 735.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (9, 55, '2011-02-13', '2011-02-13', 140.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (9, 54, '2011-02-13', '2011-02-13', 140.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (10, 49, '2010-08-17', '2010-08-17', 140.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (11, 62, '2011-03-01', '2011-03-01', 630.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (12, 69, '2007-09-20', '2007-09-20', 10.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (13, 57, '2012-01-10', '2012-01-10', 700.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (14, 58, '2012-02-25', '2012-02-25', 700.00,  1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (15, 30, '2008-08-17', '2008-08-17', 735.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (11, 32, '2008-08-17', '2010-03-09', 140.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (9, 33, '2007-02-11', '2007-02-11', 835.00, 1);
INSERT INTO Adoption (client_id, animal_id, date_reservation, date_adoption, prix, paye) VALUES (2, 3, '2011-03-12', '2011-03-12', 835.00, 1);

La table Adoption ne contient pas de colonne id auto-incrémentée. Par contre, on a bien défini une clé primaire, mais une clé primaire composite (sur plusieurs colonnes). En effet, une adoption est définie par un client adoptant un animal. Il n'est pas nécessaire d'ajouter une colonne supplémentaire pour définir individuellement chaque ligne ; le couple (client_id, animal_id) fait très bien l'affaire (il est composé de deux SMALLINT, donc les recherches sur cette clé seront rapides). Notez que nous définissons également un index UNIQUE sur la colonne animal_id. Par conséquent, on aurait même pu définir directement animal_id comme étant la clé primaire. Je trouvais cependant plus logique d'inclure le client dans la définition d'une adoption. C'est un choix plutôt arbitraire, qui a surtout comme avantage de vous montrer un exemple de clé composite.

Syntaxe et utilisation : verrous de table

Les verrous de table sont les seuls supportés par MyISAM. Ils sont d'ailleurs principalement utilisés pour pallier en partie l'absence de transactions dans MyISAM. Les tables InnoDB peuvent également utiliser ce type de verrou.

Pour verrouiller une table, il faut utiliser la commande LOCK TABLES :

1

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

• En utilisant READ, un verrou de lecture sera posé ; c'est-à-dire que les autres sessions pourront toujours lire les données des tables verrouillées, mais ne pourront plus les modifier.
• En utilisant WRITE, un verrou d'écriture sera posé. Les autres sessions ne pourront plus ni lire ni modifier les données des tables verrouillées.

Pour déverrouiller les tables, on utilise UNLOCK TABLES. Cela déverrouille toutes les tables verrouillées. Il n'est pas possible de préciser les tables à déverrouiller. Tous les verrous de table d'une session sont relâchés en même temps.

Session ayant obtenu le verrou

Lorsqu'une session acquiert un ou plusieurs verrous de table, cela a plusieurs conséquences pour cette session :

• elle ne peut plus accéder qu'aux tables sur lesquelles elle a posé un verrou ;
• elle ne peut accéder à ces tables qu'en utilisant les noms qu'elle a donnés lors du verrouillage (soit le nom de la table, soit le/les alias donné(s)) ;
• s'il s'agit d'un verrou de lecture (READ), elle peut uniquement lire les données, pas les modifier.

Exemples : on pose deux verrous, l'un READ, l'autre WRITE, l'un en donnant un alias au nom de la table, l'autre sans.

1
2

LOCK TABLES Espece READ,              -- On pose un verrou de lecture sur Espece
            Adoption AS adopt WRITE;  -- et un verrou d'écriture sur Adoption avec l'alias adopt

Voyons maintenant le résultat de ces différentes requêtes.

1. Sélection dans Espece, sans alias.

1

SELECT id, nom_courant FROM Espece;

Pas de problème, on a bien un verrou sur Espece, sans alias.

2. Sélection dans Espece, avec alias.

1
2

SELECT id, nom_courant 
FROM Espece AS table_espece;

1

ERROR 1100 (HY000): Table 'table_espece' was not locked with LOCK TABLES

Par contre, si l'on essaye d'utiliser un alias, cela ne fonctionne pas. Le verrou est posé sur Espece, pas sur Espece AS table_espece.

3. Modification dans Espece, sans alias.

1
2
3

UPDATE Espece
SET description = 'Petit piaf bruyant' 
WHERE id = 4;

1

ERROR 1099 (HY000): Table 'Espece' was locked with a READ lock and can't be updated

Avec ou sans alias, impossible de modifier la table Espece, puisque le verrou que l'on possède dessus est un verrou de lecture.

4. Sélection dans Adoption, sans alias.

1
2

SELECT client_id, animal_id 
FROM Adoption;

1

ERROR 1100 (HY000): Table 'Adoption' was not locked with LOCK TABLES

Cette fois, c'est le contraire, sans alias, ça ne passe pas.

5. Sélection dans Adoption, avec alias.

1
2
3

SELECT client_id, animal_id 
FROM Adoption AS adopt
WHERE client_id = 4;

6. Modification dans Adoption, sans alias.

1
2
3

UPDATE Adoption
SET paye = 0 
WHERE client_id = 10 AND animal_id = 49;

1

ERROR 1100 (HY000): Table 'Adoption' was not locked with LOCK TABLES

Idem pour la modification, l'alias est indispensable.

7. Modification dans Adoption, avec alias.

1
2
3

UPDATE Adoption AS adopt
SET paye = 0
WHERE client_id = 10 AND animal_id = 49;

1

Query OK, 1 row affected (0.03 sec)

Il faut donc penser à acquérir tous les verrous nécessaires aux requêtes à exécuter. De plus, il faut les obtenir en une seule requête LOCK TABLES. En effet, LOCK TABLES commence par enlever tous les verrous de table de la session avant d'en acquérir de nouveaux. Il est bien entendu possible de poser plusieurs verrous sur la même table en une seule requête afin de verrouiller son nom ainsi qu'un ou plusieurs alias.

Exemples : on pose un verrou de lecture sur Adoption, puis avec une seconde requête, on pose deux verrous de lecture sur la table Espece, l'un avec alias, l'autre sans.

1
2
3
4

UNLOCK TABLES; -- On relâche d'abord les deux verrous précédents

LOCK TABLES Adoption READ;
LOCK TABLES Espece READ, Espece AS table_espece READ;

Une fois ces deux requêtes effectuées, nous aurons donc bien deux verrous de lecture sur la table Espece : un avec son nom, l'autre avec un alias. Par contre, le verrou sur Adoption n'existera plus puisqu'il aura été relâché par l'exécution de la seconde requête LOCK TABLES.

1. Sélection dans Espece, sans alias.

1

SELECT id, nom_courant FROM Espece;

2. Sélection dans Espece, avec alias.

1

SELECT id, nom_courant FROM Espece AS table_espece;

Avec ou sans alias, on peut sélectionner les données de la table Espece, puisque l'on a un verrou sur Espece et sur Espece AS table_espece.

3. Sélection dans Espece, avec mauvais alias.

1

SELECT id, nom_courant FROM Espece AS table_esp;

1

ERROR 1100 (HY000): Table 'table_esp' was not locked with LOCK TABLES

Bien entendu, cela ne fonctionne que pour l'alias que l'on a donné lors du verrouillage.

4. Sélection dans Adoption, sans alias.

1

SELECT * FROM Adoption;

1

ERROR 1100 (HY000): Table 'Adoption' was not locked with LOCK TABLES

Le verrou sur Adoption a été relâché lorsque l'on a posé les verrous sur Espece. On ne peut donc pas lire les données d'Adoption (avec ou sans alias).

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.

Exemples : ouvrez un deuxième client MySQL et connectez-vous à votre base de données, afin d'avoir deux sessions ouvertes.

1. Sélection sur des tables verrouillées à partir d'une autre session.

Session 1 :

1
2

LOCK TABLES Client READ,        -- Verrou de lecture sur Client 
            Adoption WRITE;     -- Verrou d'écriture sur Adoption

Session 2 :

1
2
3

SELECT id, nom, prenom, ville, email 
FROM Client 
WHERE ville = 'Houtsiplou';

La sélection sur Client se fait sans problème.

Session 2 :

1
2
3

SELECT * 
FROM Adoption 
WHERE client_id = 4;

Par contre, la sélection sur Adoption ne passe pas. La session se bloque, jusqu'à ce que la session 1 déverrouille les tables avec UNLOCK TABLES.

2. Modification sur des tables verrouillées à partir d'une autre session.

Reverrouillez les tables avec la session 1 :

1
2

LOCK TABLES Client READ,       -- Verrou de lecture sur Client 
            Adoption WRITE;    -- Verrou d'écriture sur Adoption

Session 2 :

1
2
3

UPDATE Client
SET pays = 'Suisse'
WHERE id = 5;

La modification sur Client, contrairement à la sélection, est bloquée jusqu'au déverrouillage. Déverrouillez puis verrouillez à nouveau avec la session 1.

Session 2:

1
2
3

UPDATE Adoption
SET paye = 1
WHERE client_id = 3;

Bien entendu, la modification sur la table Adoption attend également que les verrous soient relâchés par la session 1.

En ce qui concerne la pose de verrous de table par les autres sessions, faites vos propres tests, mais simplement : si une session peut lire les données d'une table, elle peut également poser un verrou de lecture. Si une session peut modifier les données d'une table, elle peut également poser un verrou d'écriture.

Interaction avec les transactions

Si l'on utilise des tables MyISAM, il n'y a évidemment aucune précaution particulière à prendre par rapport aux transactions lorsqu'on utilise des verrous de table (les tables MyISAM étant non-transactionnelles). Par contre, si on utilise des tables InnoDB, il convient d'être prudent. En effet :

• START TRANSACTION ôte les verrous de table ;
• les commandes LOCK TABLES et UNLOCK TABLES provoquent une validation implicite si elles sont exécutées à l'intérieur d'une transaction.

Pour utiliser à la fois les transactions et les verrous de table, il faut renoncer à démarrer explicitement les transactions, et donc utiliser le mode non-autocommit. Lorsque l'on est dans ce mode, il est facile de contourner la validation implicite provoquée par LOCK TABLES et UNLOCK TABLES : il suffit d'appeler LOCK TABLES avant toute modification de données, et de commiter/annuler les modifications avant d'exécuter UNLOCK TABLES.

Exemple :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SET autocommit = 0;
LOCK TABLES Adoption WRITE; -- La validation implicite ne commite rien puisque aucun changement n'a été fait

UPDATE Adoption SET date_adoption = NOW() WHERE client_id = 9 AND animal_id = 54;
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE client_id = 9;

ROLLBACK;
UNLOCK TABLES; -- On a annulé les changements juste avant donc la validation implicite n'a aucune conséquence
SELECT client_id, animal_id, date_adoption FROM Adoption WHERE client_id = 9;
SET autocommit = 1;

Syntaxe et utilisation : verrous de ligne

Comme les verrous de table, les verrous de ligne peuvent être de deux types :

• Les verrous partagés : permettent aux autres sessions de lire les données, mais pas de les modifier (équivalents aux verrous de table de lecture) ;
• Les verrous exclusifs : ne permettent ni la lecture ni la modification des données (équivalents aux verrous d'écriture).

Requêtes de modification, insertion et suppression

• Les requêtes de modification et suppression des données posent automatiquement un verrou exclusif sur les lignes concernées, à savoir les lignes sélectionnées par la clause WHERE, ou toutes les lignes s'il n'y a pas de clause WHERE (ou s'il n'y a pas d'index, sur les colonnes utilisées comme nous verrons plus loin).
• Les requêtes d'insertion quant à elles posent un verrou exclusif sur la ligne insérée.

Requêtes de sélection

Les requêtes de sélection, par défaut, ne posent pas de verrous. Il faut donc en poser explicitement au besoin.

Verrou partagé

Pour poser un verrou partagé, on utilise LOCK IN SHARE MODE à la fin de la requête SELECT.

1

SELECT * FROM Animal WHERE espece_id = 5 LOCK IN SHARE MODE;

Cette requête pose donc un verrou partagé sur les lignes de la table Animal pour lesquelles espece_id vaut 5.

Ce verrou signifie en fait, pour les autres sessions : "Je suis en train de lire ces données. Vous pouvez venir les lire aussi, mais pas les modifier tant que je n'ai pas terminé.".

Verrou exclusif

Pour poser un verrou exclusif, on utilise FOR UPDATE à la fin de la requête SELECT.

1

SELECT * FROM Animal WHERE espece_id = 5 FOR UPDATE;

Ce verrou signifie aux autres sessions : "Je suis en train de lire ces données dans le but probable de faire une modification. Ne les lisez pas avant que j'aie fini (et bien sûr, ne les modifiez pas).".

Transactions et fin d'un verrou de ligne

Les verrous de ligne ne sont donc pas posés par des commandes spécifiques, mais par des requêtes de sélection, insertion ou modification. Ces verrous existent donc uniquement tant que la requête qui les a posés interagit avec les données.

Par conséquent, ce type de verrou s'utilise en conjonction avec les transactions. En effet, hors transaction, dès qu'une requête est lancée, elle est effectuée et les éventuelles modifications des données sont immédiatement validées. Par contre, dans le cas d'une requête faite dans une transaction, les changements ne sont pas validés tant que la transaction n'a pas été commitée. Donc, à partir du moment où une requête a été exécutée dans une transaction, et jusqu'à la fin de la transaction (COMMIT ou ROLLBACK), la requête a potentiellement un effet sur les données. C'est à ce moment-là (quand une requête a été exécutée mais pas validée ou annulée) qu'il est intéressant de verrouiller les données qui vont potentiellement être modifiées (ou supprimées) par la transaction.

Un verrou de ligne est donc lié à la transaction dans laquelle il est posé. Dès que l'on fait un COMMIT ou un ROLLBACK de la transaction, le verrou est levé.

Exemples

Verrou posé par une requête de modification

Session 1 :

1
2
3
4

START TRANSACTION;

UPDATE Client SET pays = 'Suisse'
WHERE id = 8;           -- un verrou exclusif sera posé sur la ligne avec id = 8

Session 2:

1
2
3
4
5
6
7
8

START TRANSACTION;

SELECT * FROM Client
WHERE id = 8;      -- pas de verrou

SELECT * FROM Client 
WHERE id = 8 
LOCK IN SHARE MODE; -- on essaye de poser un verrou partagé

La première session a donc posé un verrou exclusif automatiquement en faisant un UPDATE.

La seconde session fait d'abord une simple sélection, sans poser de verrou. Pas de problème, la requête passe.

Oui, c'est normal et c'est important de comprendre pourquoi. En fait, lorsqu'une session démarre une transaction, elle prend en quelque sorte une photo des tables dans leur état actuel (les modifications non commitées n'étant pas visibles). La transaction va alors travailler sur la base de cette photo, tant qu'on ne lui demande pas d'aller vérifier que les données n'ont pas changé. Donc le SELECT ne voit pas les changements, et ne se heurte pas au verrou, puisque celui-ci est posé sur les lignes de la table, et non pas sur la photo de cette table que détient la session.

On lui demande de poser un verrou ! Lorsqu'une session pose un verrou sur une table, elle est obligée de travailler vraiment avec la table, et pas sur sa photo. Elle va donc aller chercher les dernières infos disponibles, et actualiser sa photo par la même occasion. On le voit bien avec la seconde requête, qui tente de poser un verrou partagé (qui vise donc uniquement la lecture). Elle va d'abord chercher les lignes les plus à jour et tombe sur le verrou posé par la première session ; elle se retrouve alors bloquée jusqu'à ce que la première session ôte le verrou exclusif.

Session 1 :

1

COMMIT;

En committant les changements de la session 1, le verrou exclusif posé par la requête de modification est relâché. La session 2 est donc libre de poser à son tour un verrou partagé.

On peut également essayer la même manœuvre, avec cette fois-ci un UPDATE plutôt qu'un SELECT ... LOCK IN SHARE MODE (donc une requête qui va tenter de poser un verrou exclusif plutôt qu'un verrou partagé).

Session 1 :

1
2
3
4

START TRANSACTION;

UPDATE Adoption SET paye = 0
WHERE client_id = 11;

Session 2 :

1
2
3
4

START TRANSACTION;

UPDATE Adoption SET paye = 1
WHERE animal_id = 32; -- l'animal 32 a été adopté par le client 11

Comme prévu, la seconde session est bloquée, jusqu'à ce que la première session termine sa transaction. Validez la transaction de la première session, puis de la seconde. Le comportement sera le même si la deuxième session fait un DELETE sur les lignes verrouillées, ou un SELECT ... FOR UPDATE.

Verrou posé par une requête d'insertion

Session 1 :

1
2
3
4

START TRANSACTION;

INSERT INTO Adoption (client_id, animal_id, date_reservation, prix)
VALUES (12, 75, NOW(), 10.00);

Session 2 :

1
2
3
4
5
6
7

SELECT * FROM Adoption
WHERE client_id > 13
LOCK IN SHARE MODE;

SELECT * FROM Adoption
WHERE client_id < 13
LOCK IN SHARE MODE;

La première session insère une adoption pour le client 12 et pose un verrou exclusif sur cette ligne. La seconde session fait deux requêtes SELECT en posant un verrou partagé : l'une qui sélectionne les adoptions des clients avec un id supérieur à 13 ; l'autre qui sélectionne les adoptions des clients avec un id inférieur à 13. Seule la seconde requête SELECT se heurte au verrou posé par la première session, puisqu'elle tente de récupérer notamment les adoptions du client 12, dont une est verrouillée.

Dès que la session 1 commite l'insertion, la sélection se fait dans la session 2.

Session 1 :

1

COMMIT;

Verrou posé par une requête de sélection

Voyons d'abord le comportement d'un verrou partagé, posé par SELECT ... LOCK IN SHARE MODE.

Session 1 :

1
2
3
4
5

START TRANSACTION;

SELECT * FROM Client
WHERE id < 5
LOCK IN SHARE MODE;

Session 2 :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

START TRANSACTION;

SELECT * FROM Client 
WHERE id BETWEEN 3 AND 8;

SELECT * FROM Client
WHERE id BETWEEN 3 AND 8
LOCK IN SHARE MODE;

SELECT * FROM Client
WHERE id BETWEEN 3 AND 8
FOR UPDATE;

La première session pose un verrou partagé sur les clients 1, 2, 3 et 4. La seconde session fait trois requêtes de sélection. Toutes les trois concernent les clients 3 à 8 (dont les deux premiers sont verrouillés).

• Requête 1 : ne pose aucun verrou (travaille sur une "photo" de la table et pas sur les vraies données) donc s'effectue sans souci.
• Requête 2 : pose un verrou partagé, ce qui est faisable sur une ligne verrouillée par un verrou partagé. Elle s'effectue également.
• Requête 3 : tente de poser un verrou exclusif, ce qui lui est refusé.

Bien entendu, des requêtes UPDATE ou DELETE (posant des verrous exclusifs) faites par la deuxième session se verraient, elles aussi, bloquées.

Terminez les transactions des deux sessions (par un rollback ou un commit).

Quant aux requêtes SELECT ... FOR UPDATE posant un verrou exclusif, elles provoqueront exactement les mêmes effets qu'une requête UPDATE ou DELETE (après tout, un verrou exclusif, c'est un verrou exclusif).

Session 1 :

1
2
3
4
5

START TRANSACTION;

SELECT * FROM Client
WHERE id < 5
FOR UPDATE;

Session 2 :

1
2
3
4
5
6
7
8

START TRANSACTION;

SELECT * FROM Client 
WHERE id BETWEEN 3 AND 8;

SELECT * FROM Client
WHERE id BETWEEN 3 AND 8
LOCK IN SHARE MODE;

Cette fois-ci, même la requête SELECT ... LOCK IN SHARE MODE de la seconde session est bloquée (comme le serait une requête SELECT ... FOR UPDATE, ou une requête UPDATE, ou une requête DELETE).

En résumé

• On pose un verrou partagé lorsqu'on fait une requête dans le but de lire des données.
• On pose un verrou exclusif lorsqu'on fait une requête dans le but (immédiat ou non) de modifier des données.
• Un verrou partagé sur les lignes x va permettre aux autres sessions d'obtenir également un verrou partagé sur les lignes x, mais pas d'obtenir un verrou exclusif.
• Un verrou exclusif sur les lignes x va empêcher les autres sessions d'obtenir un verrou sur les lignes x, qu'il soit partagé ou exclusif.

Rôle des index

Tentons une nouvelle expérience.

Session 1 :

1
2
3
4

START TRANSACTION;
UPDATE Animal 
SET commentaires = CONCAT_WS(' ', 'Animal fondateur.', commentaires) -- On ajoute une phrase de commentaire
WHERE date_naissance < '2007-01-01';                                 -- à tous les animaux nés avant 2007

Session 2 :

1
2
3
4

START TRANSACTION;
UPDATE Animal 
SET commentaires = 'Aveugle'                  -- On modifie les commentaires
WHERE date_naissance = '2008-03-10 13:40:00'; -- De l'animal né le 10 mars 2008 à 13h40

Dans la session 1, on fait un UPDATE sur les animaux nés avant 2007. On s'attend donc à pouvoir utiliser les animaux nés après dans une autre session, puisque InnoDB pose des verrous sur les lignes et pas sur toute la table. Pourtant, la session 2 semble bloquée lorsque l'on fait un UPDATE sur un animal né en 2008. Faites un rollback sur la session 1 ; ceci débloque la session 2. Annulez également la requête de cette session.

Le sous-titre vous a évidemment soufflé la réponse : la différence se trouve au niveau des index. Voyons donc ça ! Voici une commande qui va vous afficher les index présents sur la table Animal :

1

SHOW INDEX FROM Animal;

Nous avons donc des index sur les colonnes suivantes : id, nom, mere_id, pere_id, espece_id et race_id. Mais aucun index sur la colonne date_naissance.

Il semblerait donc que lorsque l'on pose un verrou, avec dans la clause WHERE de la requête une colonne indexée (espece_id), le verrou est bien posé uniquement sur les lignes pour lesquelles espece_id vaut la valeur recherchée. Par contre, si dans la clause WHERE on utilise une colonne non-indexée (date_naissance), MySQL n'est pas capable de déterminer quelles lignes doivent être bloquées, donc on se retrouve avec toutes les lignes bloquées.

C'est très simple ! Vous savez que lorsqu'une colonne est indexée (que ce soit un index simple, unique, ou une clé primaire ou étrangère), MySQL stocke les valeurs de cette colonne en les triant. Du coup, lors d'une recherche sur l'index, pas besoin de parcourir toutes les lignes, il peut utiliser des algorithmes de recherche performants et trouver facilement les lignes concernées. S'il n'y a pas d'index par contre, toutes les lignes doivent être parcourues chaque fois que la recherche est faite, et il n'y a donc pas moyen de verrouiller simplement une partie de l'index (donc une partie des lignes). Dans ce cas, MySQL verrouille toutes les lignes.

Cela fait une bonne raison de plus de mettre des index sur les colonnes qui servent fréquemment dans vos clauses WHERE !

Encore une petite expérience pour illustrer le rôle des index dans le verrouillage de lignes :

Session 1 :

1
2
3
4

START TRANSACTION;
UPDATE Animal  -- Modification de tous les rats
SET commentaires = CONCAT_WS(' ', 'Très intelligent.', commentaires)
WHERE espece_id = 5;

Session 2 :

1
2
3
4
5
6
7

START TRANSACTION;
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 34; -- Modification de l'animal 34 (un chat)
UPDATE Animal
SET commentaires = 'Aveugle'
WHERE id = 72; -- Modification de l'animal 72 (un rat)

La session 1 se sert de l'index sur espece_id pour verrouiller les lignes contenant des rats bruns. Pendant ce temps, la session 2 veut modifier deux animaux : un chat et un rat, en se basant sur leur id. La modification du chat se fait sans problème, par contre, la modification du rat est bloquée, tant que la transaction de la session 1 est ouverte. Faites un rollback des deux transactions.

On peut conclure de cette expérience que, bien que MySQL utilise les index pour verrouiller les lignes, il n'est pas nécessaire d'utiliser le même index pour avoir des accès concurrents.

Lignes fantômes et index de clé suivante

Dans une session, démarrons une transaction et sélectionnons toutes les adoptions faites par les clients dont l'id dépasse 13, avec un verrou exclusif.

Session 1 :

1
2
3

START TRANSACTION;

SELECT * FROM Adoption WHERE client_id > 13 FOR UPDATE; -- ne pas oublier le FOR UPDATE pour poser le verrou

La requête va poser un verrou exclusif sur toutes les lignes dont client_id vaut 14 ou plus.

Imaginons maintenant qu'une seconde session démarre une transaction à ce moment-là, insère et commite une ligne dans Adoption pour le client 15. Si, par la suite, la première session refait la même requête de sélection avec verrou exclusif, elle va faire apparaître une troisième ligne de résultat : l'adoption nouvellement insérée (étant donné que pour poser le verrou, la session va aller chercher les données les plus à jour, prenant en compte le commit de la seconde session).

Cette ligne nouvellement apparue malgré les verrous est une "ligne fantôme".

Pour pallier ce problème, qui est contraire au principe d'isolation, les verrous posés par des requêtes de lecture, de modification et de suppression sont des verrous dits "de clé suivante" ; ils empêchent l'insertion d'une ligne dans les espaces entre les lignes verrouillées, ainsi que dans l'espace juste après les lignes verrouillées.

Nous avons vu que les verrous se basent sur les index pour verrouiller uniquement les lignes nécessaires. Voici un petit schéma qui vous expliquera ce qu'est cet "index de clé suivante".

On peut représenter l'index sur client_id de la table Adoption de la manière suivante (je ne mets que les client_id < 10) :

Si l'on insère une adoption avec 4 pour client_id, l'index va être réorganisé de la manière suivante :

Mais, si l'on pose un verrou de clé suivante sur l'index, sur les lignes dont client_id vaut 4, on va alors verrouiller les lignes, les espaces entre les lignes et les espaces juste après. Ceci va bloquer l'insertion de la nouvelle ligne :

Démonstration

On a toujours un verrou exclusif (grâce à notre SELECT ... FOR UPDATE) sur les client_id supérieurs à 14 dans la session 1 (sinon, reposez-le).

Session 2 :

1
2
3
4

START TRANSACTION;

INSERT INTO Adoption (client_id, animal_id, date_reservation, prix) 
VALUES (15, 61, NOW(), 735.00);

L'insertion est bloquée ! Pas de risque de voir apparaître une ligne fantôme. Annulez les deux transactions.

Exception

Si la clause WHERE concerne un index UNIQUE (cela inclut bien sûr les clés primaires) et recherche une seule valeur (exemple : WHERE id = 4), alors seule la ligne concernée (si elle existe) est verrouillée, et pas l'espace juste après dans l'index. Forcément, s'il s'agit d'un index UNIQUE, l'insertion d'une nouvelle valeur ne changera rien : WHERE id = 4 ne renverra jamais qu'une seule ligne.

Pourquoi poser un verrou exclusif avec une requête SELECT ?

Après tout, une requête SELECT ne fait jamais que lire des données. Que personne ne puisse les modifier pendant qu'on est en train de les lire, c'est tout à fait compréhensible. Mais pourquoi carrément interdire aux autres de les lire aussi ?

Tout simplement parce que certaines données sont lues dans le but prévisible et avoué de les modifier immédiatement après.

L'exemple typique est la vérification de stock dans un magasin (ou dans un élevage d'animaux). Un client arrive et veut adopter un chat, on vérifie donc les chats disponibles pour l'adoption, en posant un verrou partagé :

Session 1 :

1
2
3
4
5
6
7
8
9

START TRANSACTION;

SELECT Animal.id, Animal.nom, Animal.date_naissance, Race.nom as race, COALESCE(Race.prix, Espece.prix) as prix
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id             -- Jointure externe, on ne veut pas que les chats de race
WHERE Espece.nom_courant = 'Chat'                      -- Uniquement les chats...
AND Animal.id NOT IN (SELECT animal_id FROM Adoption)  -- ... qui n'ont pas encore été adoptés
LOCK IN SHARE MODE;

Si, pendant que le premier client fait son choix, un second client arrive, qui veut adopter un chat Maine Coon, il va également chercher la liste des chats disponibles. Et vu qu'on travaille pour l'instant en verrous partagés, il va pouvoir l'obtenir.

Session 2 :

1
2
3
4
5
6
7
8
9

START TRANSACTION;

SELECT Animal.id, Animal.nom, Animal.date_naissance, Race.nom as race, COALESCE(Race.prix, Espece.prix) as prix
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
INNER JOIN Race ON Animal.race_id = Race.id            -- Jointure interne cette fois
WHERE Race.nom = 'Maine Coon'                          -- Uniquement les Maine Coon...
AND Animal.id NOT IN (SELECT animal_id FROM Adoption)  -- ... qui n'ont pas encore été adoptés
LOCK IN SHARE MODE;

C'est alors que le premier client, M. Dupont, décide de craquer pour Bagherra.

1
2
3
4
5
6

INSERT INTO Adoption (client_id, animal_id, date_reservation, prix, paye)
SELECT id, 8, NOW(), 735.00, 1
FROM Client
WHERE email = 'jean.dupont@email.com';

COMMIT;

Et M. Durant jette également son dévolu sur Bagherra (qui est décidément très très mignon) !

1
2
3
4
5

INSERT INTO Client (nom, prenom, email)
VALUES ('Durant', 'Philippe', 'phidu@email.com');

INSERT INTO Adoption (client_id, animal_id, date_reservation, prix, paye)
VALUES (LAST_INSERT_ID(), 8, NOW(), 735.00, 0);

L'insertion dans Client fonctionne mais l'insertion dans Adoption pose problème :

1

ERROR 1062 (23000): Duplicate entry '8' for key 'ind_uni_animal_id'

Et pour cause : Bagherra vient d'être adopté, à l'instant. Furieux, M. Durant s'en va, et l'élevage a perdu un client. Il ne reste plus qu'à annuler sa transaction.

C'est pour éviter ce genre de situation qu'il vaut parfois mieux mettre un verrou exclusif sur une sélection. Si l'on sait que cette sélection sert à déterminer quels changements vont être faits, ce n'est pas la peine de laisser quelqu'un d'autre lire des informations qui cesseront d'être justes incessamment sous peu.

Niveaux d'isolation

Nous avons vu que par défaut :

• lorsque l'on démarre une transaction, la session prend une photo des tables, et travaille uniquement sur cette photo (donc sur des données potentiellement périmées) tant qu'elle ne pose pas un verrou ;
• les requêtes SELECT ne posent pas de verrous si l'on ne le demande pas explicitement ;
• les requêtes SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE, DELETE et UPDATE posent un verrou de clé suivante (sauf dans le cas d'une recherche sur index unique, avec une valeur unique).

Ce comportement est défini par le niveau d'isolation des transactions.

Syntaxe

Pour définir le niveau d'isolation des transactions, on utilise la requête suivante :

1

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED  | REPEATABLE READ  | SERIALIZABLE }

• Le mot-clé GLOBAL définit le niveau d'isolation pour toutes les sessions MySQL qui seront créées dans le futur. Les sessions existantes ne sont pas affectées.
• SESSION définit le niveau d'isolation pour la session courante.
• Si l'on ne précise ni GLOBAL, ni SESSION, le niveau d'isolation défini ne concernera que la prochaine transaction que l'on ouvrira dans la session courante.

Les différents niveaux

REPEATABLE READ

Il s'agit du niveau par défaut, celui avec lequel vous travaillez depuis le début. Repeatable read signifie "lecture répétable", c'est-à-dire que si l'on fait plusieurs requêtes de sélection (non-verrouillantes) de suite, elles donneront toujours le même résultat, quels que soient les changements effectués par d'autres sessions. Si l'on pense à bien utiliser les verrous là où c'est nécessaire, c'est un niveau d'isolation tout à fait suffisant.

READ COMMITTED

Avec ce niveau d'isolation, chaque requête SELECT (non-verrouillante) va reprendre une "photo" à jour de la base de données, même si plusieurs SELECT se font dans la même transaction. Ainsi, un SELECT verra toujours les derniers changements commités, même s'ils ont été faits dans une autre session, après le début de la transaction.

READ UNCOMMITTED

Le niveau READ UNCOMMITTED fonctionne comme READ COMMITTED, si ce n'est qu'il autorise la "lecture sale". C'est-à-dire qu'une session sera capable de lire des changements encore non commités par d'autres sessions.

Exemple

Session 1 :

1
2
3
4
5

START TRANSACTION;

UPDATE Race 
SET prix = 0 
WHERE id = 7;

Session 2 :

1
2
3
4

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

SELECT id, nom, espece_id, prix FROM Race;

La modification faite par la session 1 n'a pas été commitée. Elle ne sera donc potentiellement jamais validée, auquel cas, elle n'affectera jamais les données. Pourtant, la session 2 voit ce changement de données non-commitées. "Lecture sale" n'a pas une connotation négative par hasard, bien entendu ! Aussi, évitez de travailler avec ce niveau d'isolation. Annulez la modification de données réalisée par la session 1 et terminez la transaction de la seconde session.

SERIALIZABLE

Ce niveau d'isolation se comporte comme REPEATABLE READ, sauf que lorsque le mode autocommit est désactivé, tous les SELECT simples sont implicitement convertis en SELECT ... LOCK IN SHARE MODE.

En résumé

• Les verrous permettent de restreindre, voire interdire l'accès, à une partie des données.
• Les verrous de table peuvent s'utiliser sur des tables transactionnelles et non-transactionnelles, contrairement aux verrous de ligne qui ne sont disponibles que pour des tables transactionnelles.
• Les verrous de lecture (tables) et partagés (lignes) permettent aux autres sessions de lire les données verrouillées, mais pas de les modifier. Les verrous d'écriture (tables) et exclusif (lignes) par contre, ne permettent aux autres sessions ni de lire, ni de modifier les données verrouillées.
• Les verrous de ligne s'utilisent avec les transactions, et dépendent des index.
• Les requêtes de suppression, modification et insertion posent automatiquement un verrou de ligne exclusif de clé suivante sur les lignes concernées par la requête. Les requêtes de sélection par contre, ne posent pas de verrou par défaut, il faut en poser un explicitement.
• Le comportement par défaut des verrous de ligne est défini par le niveau d'isolation des transactions, qui est modifiable.

• Transactions
• Sommaire
• Requêtes préparées