| En préambule: il peut être intéressant de lire les articles sur le verrouillage en 4 épisodes de Kuba Łopuszański: – épisode 1 : introduction – épisode 2 : locks – épisode 3 : locks deep dive – épisode 4 : deadlocks |
Aujourd’hui, nous allons analyser un problème de verrouillage sur MySQL / MariaDB avec INSERT IGNORE.
Un petit exemple tout d’abord pour illustrer l’utilisation de cette instruction, avec une table example telle que:
mysql> create table example (a int NOT NULL, -> b datetime NULL, -> PRIMARY KEY (a)) ENGINE=innodb ; Query OK, 0 rows affected (0.01 sec) mysql> select * from example ; +---+---------------------+ | a | b | +---+---------------------+ | 1 | 2021-02-24 17:35:12 | | 2 | 2021-02-23 17:35:26 | +---+---------------------+ 2 rows in set (0.00 sec)
OK donc le but d’INSERT IGNORE est de tenter une insertion dans une table mais ne pas générer d’erreur en cas de violation de clé unique , simplement la ligne n’est pas insérée et un warning est renvoyé au client:
mysql> insert ignore into example values -> (2,date_add(now(), interval -1 week)) ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings ; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1062 | Duplicate entry '2' for key 'example.PRIMARY' | +---------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
On va s’intéresser maintenant aux types de verrous impliqués.
Problème de verrouillage
Soit trois tables src, trg et ref telles que:
src est la table source telle que:
mysql> show create table src \G
*************************** 1. row ***************************
Table: src
Create Table: CREATE TABLE `src` (
`a` bigint NOT NULL,
`b` datetime DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
On créé un trigger AFTER UPDATE sur src pour venir insérer avec INSERT IGNORE les nouvelles lignes modifiées dans une seconde table trg:
DELIMITER // CREATE TRIGGER update_src AFTER UPDATE ON src FOR EACH ROW BEGIN INSERT IGNORE INTO trg (c,d) VALUES (NEW.a,DATE_FORMAT(NEW.b,'%Y%m')); END // DELIMITER ;
trg est la table qui reçoit les données modifiées du trigger:
mysql> show create table trg \G
*************************** 1. row ***************************
Table: trg
Create Table: CREATE TABLE `trg` (
`c` bigint NOT NULL,
`d` int DEFAULT NULL,
UNIQUE KEY `c` (`c`,`d`),
CONSTRAINT `FK_c` FOREIGN KEY (`c`) REFERENCES `ref` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Noter que la colonne d accepte les valeurs NULL, donc elle ne peut pas faire partie d’une clé primaire, on créé une clé UNIQUE sur (c,d). InnoDB ne connaît pas les heaps, donc il s’appuie sur cette clé unique pour générer un index clusterisé qu’il appelera en interne ‘GEN_CLUST_INDEX’.
Enfin, à chaque insertion dans trg, on va aller lire si la valeur de (c) est bien listée dans une table de référence ref telle que:
mysql> show create table ref \G
*************************** 1. row ***************************
Table: ref
Create Table: CREATE TABLE `ref` (
`c` bigint NOT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from ref ;
+-------+
| c |
+-------+
| 11111 |
| 22222 |
| 33333 |
| 44444 |
| 55555 |
| 66666 |
| 77777 |
| 88888 |
| 99999 |
+-------+
9 rows in set (0.00 sec)
Premier test :
Contenu de src et trg au début du test:
mysql> select * from src; select * from trg ; +-------+---------------------+ | a | b | +-------+---------------------+ | 11111 | 2021-02-24 15:41:11 | | 33333 | 2021-02-24 15:40:53 | | 55555 | 2021-02-24 16:58:49 | | 77777 | 2021-02-24 16:58:58 | +-------+---------------------+ 4 rows in set (0.00 sec) +-------+--------+ | c | d | +-------+--------+ | 11111 | 202102 | | 22222 | 202102 | | 33333 | 202102 | | 77777 | 202102 | | 88888 | 202102 | +-------+--------+ 5 rows in set (0.00 sec)
La première transaction met à jour une ligne de src :
mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=44444 where a=11111 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Pendant ce temps, une seconde transaction fait un UPDATE sur une autre ligne de src de la même manière:
mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=66666 where a=33333 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Les valeurs insérées dans la table trg par le trigger n’existent pas dans les deux cas donc les lignes sont bien insérées, après avoir été vérifier que les nouvelles valeurs (44444 et 66666) existent bien dans la table ref
Les deux transactions accèdent en modification à des enregistrements différents de src, et insèrent des lignes différentes dans trg, donc elles ne se bloquent pas mutuellement. Comme nous sommes en version 8.0, on peut utiliser la nouvelle vue performance_schema.data_locks pour inspecter les verrous posés par chaque transaction. Noter que nous n’avons pas validé ou annulé chaque transaction pour pouvoir faire cette inspection:
Pour la première transaction :
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, -> LOCK_DATA from data_locks where THREAD_ID = 49 ; +-------------+-----------+---------------+-------------+-----------+ | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-------------+-----------+---------------+-------------+-----------+ | src | TABLE | IX | GRANTED | NULL | | src | RECORD | X,REC_NOT_GAP | GRANTED | 11111 | | trg | TABLE | IX | GRANTED | NULL | | ref | TABLE | IS | GRANTED | NULL | | ref | RECORD | S,REC_NOT_GAP | GRANTED | 44444 | +-------------+-----------+---------------+-------------+-----------+ 5 rows in set (0.00 sec)
Et pour la seconde :
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, -> LOCK_DATA from data_locks where THREAD_ID = 50 ; +-------------+-----------+---------------+-------------+-----------+ | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-------------+-----------+---------------+-------------+-----------+ | src | TABLE | IX | GRANTED | NULL | | src | RECORD | X,REC_NOT_GAP | GRANTED | 33333 | | trg | TABLE | IX | GRANTED | NULL | | ref | TABLE | IS | GRANTED | NULL | | ref | RECORD | S,REC_NOT_GAP | GRANTED | 66666 | +-------------+-----------+---------------+-------------+-----------+ 5 rows in set (0.00 sec)
On voit bien que chaque transaction utilise les mêmes verrous sur des enregistrements différents, elles n’entrent donc pas en concurrence:
– Sur src : un verrou d’intention IX sur la table, pour protéger la modification de la ligne, plus un verrou exclusif sur l’enregistrement seul (X,REC_NOT_GAP). Les verrous d’intention sont compatibles entre eux, et les deux autres verrouillent des enregistrements différents.
– Sur ref: un verrou d’intention partagé IS sur la table, et un verrou en lecture sur l’enregistrement de la clé primaire (S,REC_NOT_GAP) pour satisfaire la contrainte d’intégrité référentielle.
– Enfin sur trg: on ne voit qu’un verrou d’intention sur la table. Mais où sont passés les autres verrous sur l’INSERT ?
Alors là il y a une petite subtilité. En REPEATABLE-READ, lorsqu’une ligne va être insérée, un premier verrou X,INSERT_INTENTION est posé puis un verrou X,REC_NOT_GAP sur le futur enregistrement ‘formatté‘ pour recevoir la future ligne à insérer.
X,INSERT_INTENTION est un genre de gap lock mais spécifique à la fin de la page : il couvre un pseudo-enregistrement qui représente l’espace entre la dernière valeur insérée et la fin de la page, que l’on appelle supremum. Il permet de préparer le terrain à une insertion dans l’intervalle avant la fin de page.
D’ailleurs le schéma n’est pas tout à fait juste dans le sens où il semble que les deux nouvelles lignes (44444|202102) et (66666|202102) dans trg soient insérées au milieu, ce qui n’est pas le cas dans la pratique. Elles sont insérées à la fin et chacune embarque dans son entête un pointeur vers le ‘prochain’ enregistrement dans la page, ce serait bien trop lourd de ‘déplacer’ des lignes dans la page, ça ressemble donc plutôt à ça:
Mais si je ne simplifie pas un peu on ne va pas s’en sortir …
Bref on ne voit pas ces verrous dans data_locks car ils sont dits implicites dans le sens où ils n’intéressent que la transaction qui est en cours (souvenez-vous qu’avec InnoDB, le mode d’isolation par défaut est REPEATABLE-READ). Chaque nouvelle ligne n’est visible que de la transaction qui l’a insérée.
En fait ces verrous peuvent être convertis en verrous explicites par le moniteur de verrous InnoDB s’ils sont la cause d’un blocage, et être rendus visibles aux interfaces notamment data_locks , ce qui n’est pas le cas ici donc on ne les voit pas.
Deuxième test:
Cette fois on va forcer l’insertion par le trigger d’une ligne qui existe déjà
dans trg pour voir ce qu’il se passe. Le trigger effectue un INSERT IGNORE, donc la ligne devrait ne pas être insérée. Qu’en est-il des verrous ?
A nouveau une première session modifie une ligne de src, mais cette fois avec une valeur qui existe déjà dans trg (22222):
mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=22222 where a=44444 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Notez que le warning ne remonte pas car il a été ‘digéré‘ par le trigger.
De la même manière une autre transaction modifie une autre ligne de src, cette fois avec une valeur qui n’existe pas encore dans trg:
mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=99999 where a=77777 ; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Elle tombe en erreur au bout de 50 secondes (valeur de innodb_lock_wait_timeout par défaut). Si on inspecte les verrous posés par ces 2 transactions (avant la fin des 50 secondes):
Pour la première transaction:
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA -> from data_locks where THREAD_ID = 52 ; +-------------+-----------+---------------+-------------+-------------------------------+ | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-------------+-----------+---------------+-------------+-------------------------------+ | src | TABLE | IX | GRANTED | NULL | | src | RECORD | X,REC_NOT_GAP | GRANTED | 44444 | | trg | TABLE | IX | GRANTED | NULL | | ref | TABLE | IS | GRANTED | NULL | | ref | RECORD | S,REC_NOT_GAP | GRANTED | 22222 | | trg | RECORD | S | GRANTED | 22222, 202102, 0x000000000200 | | trg | RECORD | X | GRANTED | supremum pseudo-record | +-------------+-----------+---------------+-------------+-------------------------------+ 7 rows in set (0.00 sec)
Deux nouveaux verrous sont visibles :
– un verrou S (c’est à dire S,REC_NOT_GAP + S,GAP) sur la ligne qui provoque la violation de clé unique dans trg, avec la valeur (22222|202102).
– un verrou X (c’est à dire X,REC_NOT_GAP + X,GAP) sur le supremum.
Pour la seconde transaction:
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA -> from data_locks where THREAD_ID = 53 ; +-------------+-----------+--------------------+-------------+------------------------+ | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-------------+-----------+--------------------+-------------+------------------------+ | src | TABLE | IX | GRANTED | NULL | | src | RECORD | X,REC_NOT_GAP | GRANTED | 77777 | | trg | TABLE | IX | GRANTED | NULL | | trg | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record | +-------------+-----------+--------------------+-------------+------------------------+ 4 rows in set (0.00 sec)
Elle tente de poser un verrou INSERT_INTENTION sur le gap entre la dernière valeur et la fin de la page, mais l’autre transaction possède déjà un verrou (X) sur le supremum ET le gap. Le statut de cette demande reste en WAITING jusqu’à expiration du timeout, pourtant la première transaction n’a rien inséré du tout.
En parcourant la documentation officielle on peut lire :
“If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock”
En modifiant le mode d’isolation à READ-COMMITTED, les verrous de type gap lock comme l’INSERT_INTENTION ne sont pas utilisés et la seconde transaction peut insérer sans être bloquée:
Première transaction:
mysql> set transaction_isolation='READ-COMMITTED' ; Query OK, 0 rows affected (0.00 sec) mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=22222 where a=44444 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Seconde transaction:
mysql> set transaction_isolation='READ-COMMITTED' ; Query OK, 0 rows affected (0.00 sec) mysql> start transaction ; Query OK, 0 rows affected (0.00 sec) mysql> update src set a=99999 where a=77777 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
En aparté, vous noterez que la table ref n’entre pas dans le débat ici, cela dit la vérification d’une contrainte d’intégrité déclenchée par un trigger peut rallonger encore la durée de la transaction parente, et ainsi allonger la durée de maintien des verrous. Il faut donc faire attention à ne pas trop cascader de dépendances dans une même transaction.
N’hésitez pas à partager vos expériences similaires dans les commentaires !
A bientôt ~David.
Continuez votre lecture sur le blog :
- Un trigger fait-il parti d’une transaction ? (Benjamin VESAN) [GénéralMySQLOracleSQL ServerSybase]
- Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
- Nouveautés MySQL 8.0 : Les indexes invisibles (Capdata team) [MySQL]
- Alter table rebuild (Benjamin VESAN) [SQL Server]
- Intérêt de créer des indexes cluster uniques (David Baffaleuf) [SQL Server]