Comprendre le verrouillage MySQL : vérifier l’état des verrous, libérer les verrous en toute sécurité et prévenir les interblocages (5.6–8.0)

1. Introduction

MySQL est largement utilisé comme système de gestion de bases de données, mais lorsque plusieurs requêtes tentent d’accéder aux mêmes données, un mécanisme de verrouillage est déclenché. Les verrous sont essentiels pour maintenir la cohérence des données ; toutefois, une mauvaise gestion peut entraîner des impasses et une dégradation des performances.

Dans cet article, nous expliquerons les concepts fondamentaux du verrouillage dans MySQL et fournirons des conseils détaillés sur comment vérifier l’état des verrous, comment libérer les verrous et comment prévenir les impasses.

Ce que vous apprendrez dans cet article

• Les types de verrous MySQL et leur impact
• Comment vérifier les verrous selon la version de MySQL
• Procédures sûres pour libérer les verrous
• Conseils pratiques pour prévenir les impasses

Commençons par expliquer les concepts de base du verrouillage MySQL.

2. Concepts de base du verrouillage MySQL

Dans une base de données, un « verrou » est un mécanisme qui restreint l’accès afin de préserver l’intégrité des données lorsque plusieurs transactions tentent de les modifier simultanément. Si les verrous ne sont pas gérés correctement, une dégradation des performances et des impasses peuvent survenir.

2.1 Types principaux de verrous

Dans MySQL, il existe plusieurs types de verrous en fonction du niveau de protection des données requis.

Verrou de ligne

• Verrouille uniquement les lignes spécifiques, minimisant l’impact sur les autres transactions.
• Pris en charge uniquement par le moteur de stockage InnoDB.
• Déclenché lors de l’utilisation de SELECT ... FOR UPDATE ou SELECT ... LOCK IN SHARE MODE.

Verrou de table

• Verrouille la table entière, empêchant l’exécution simultanée de plusieurs requêtes.
• Couramment utilisé avec le moteur de stockage MyISAM.
• Déclenché lors de l’utilisation de l’instruction LOCK TABLES.

Verrou d’intention

• Verrou utilisé pour coordonner les verrous de ligne et les verrous de table afin d’éviter les conflits.
• Utilisé uniquement dans InnoDB et géré automatiquement.

Impasse

• État où plusieurs transactions attendent les verrous des autres.
• Si les transactions ne sont pas conçues correctement, le traitement peut s’arrêter complètement.

2.2 Exemples d’occurrence de verrous

Examinons des requêtes SQL spécifiques pour comprendre comment les verrous se produisent.

Exemple de verrou de ligne

L’exécution du SQL suivant verrouillera une ligne spécifique.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Si une autre session tente de mettre à jour la même ligne, elle entrera dans un état d’attente de verrou (contention de verrou).

Exemple de verrou de table

Pour verrouiller une table entière, utilisez la commande suivante.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Tant que ce verrou n’est pas libéré, les autres utilisateurs ne peuvent pas modifier les données de la table products.

Exemple d’impasse

Ce qui suit montre un scénario typique d’impasse.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

Dans cette situation, chaque transaction attend que l’autre libère son verrou, ce qui entraîne une impasse.

3. Comment vérifier l’état des verrous MySQL (par version)

Pour déterminer si des verrous se produisent, vous devez exécuter les commandes appropriées à votre version de MySQL.

3.1 Comment vérifier les verrous dans MySQL 5.6 et antérieur

Dans MySQL 5.6 et antérieur, vous pouvez vérifier les informations de verrouillage avec SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

L’exécution de cette commande affiche des informations détaillées sur les verrous actuellement actifs.

3.2 Comment vérifier les verrous dans MySQL 5.7

Dans MySQL 5.7 et versions ultérieures, la méthode la plus simple consiste à utiliser la table sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

En interrogeant cette table, vous pouvez identifier quelles transactions attendent des verrous.

3.3 Comment vérifier les verrous dans MySQL 8.0 et versions ultérieures

Dans MySQL 8.0 et versions ultérieures, vous pouvez récupérer des informations de verrouillage plus détaillées en utilisant performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Pour identifier la session détenant le verrou, utilisez le SQL suivant :

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

Cela vous permet de localiser le processus responsable du verrou.

4. Comment libérer les verrous MySQL (risques expliqués)

Si un verrou se produit dans MySQL et n’est pas géré correctement, le traitement peut se bloquer et les performances de la base de données peuvent se dégrader.
Dans cette section, nous expliquerons comment libérer les verrous et les risques associés.

4.1 Identifier la session détenant le verrou

Avant de libérer un verrou, vous devez identifier quelle session le détient. Utilisez le SQL suivant pour vérifier les sessions subissant des attentes de verrou :

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Cette requête répertorie les sessions qui attendent un verrou.

Dans MySQL 8.0 et versions ultérieures, vous pouvez obtenir des informations détaillées sur les verrous en utilisant :

SELECT * FROM performance_schema.data_locks;

4.2 Libérer les verrous avec la commande KILL

Une fois que vous avez identifié la session détenant le verrou, vous pouvez le libérer en terminant de force le processus.

1. Vérifier le processus détenant le verrou

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Terminer la session en utilisant la commande KILL

KILL <process_id>;

Par exemple, pour terminer un processus avec ID=12345, exécutez :

KILL 12345;

⚠️ Risques de la commande KILL

• Les transactions terminées de force sont annulées
• Par exemple, les modifications apportées par une instruction UPDATE interrompue peuvent être perdues.
• Cela peut provoquer des erreurs d’application
• Si vous devez fréquemment utiliser KILL, vous devriez revoir la conception de votre application.

4.3 Libérer les verrous avec ROLLBACK (méthode plus sûre)

Avant d’utiliser la commande KILL, si possible, essayez de terminer manuellement la transaction à l’origine du verrou.

1. Tout d’abord, vérifiez les transactions en cours

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Si vous trouvez la transaction problématique, exécutez ROLLBACK

ROLLBACK;

Cette méthode vous permet de libérer le verrou tout en maintenant la cohérence des données.

4.4 Automatiser la gestion des verrous avec SET innodb_lock_wait_timeout

Au lieu de libérer manuellement les verrous, vous pouvez configurer un délai d’attente de verrou afin que la transaction expire automatiquement si le verrou n’est pas libéré dans un délai spécifié.

SET innodb_lock_wait_timeout = 10;

Avec ce paramètre, si le verrou n’est pas libéré dans les 10 secondes, MySQL renvoie une erreur et termine automatiquement la transaction.

5. Notes importantes et bonnes pratiques pour les verrous MySQL

Une gestion appropriée des verrous aide à réduire le risque de blocages (deadlocks) et la dégradation des performances. Voici les meilleures pratiques pour minimiser les verrous et les gérer efficacement.

5.1 Comment prévenir les deadlocks

Pour prévenir les deadlocks, gardez les points suivants à l’esprit :

1. Standardiser l’ordre d’exécution des transactions

• Par exemple, lors de la mise à jour de plusieurs tables, toujours les mettre à jour dans le même ordre .
• Exemple :

  -- OK: Always update in the order orders → customers
  BEGIN;
  UPDATE orders SET status = 'shipped' WHERE order_id = 1;
  UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
  COMMIT;
  

× NG : Des ordres d’exécution différents peuvent provoquer des deadlocks

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Session 2 (un interblocage peut se produire si exécuté dans l'ordre inverse)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

• Commit or roll back as quickly as possible
• Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

• Creating proper indexes helps avoid unnecessary locks .
• Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .

  CREATE INDEX idx_customer_id ON orders (customer_id);
  

6. Summary

• MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
• The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
• Be cautious when releasing locks!
• Try ROLLBACK before using the KILL command.
• Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
• To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

• A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

• A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

• A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

• A4. The following methods are effective:
• Standardize transaction execution order
• Keep transactions short
• Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

• A5.
• Design proper indexes to reduce unnecessary locks
• Keep transactions short to minimize lock duration
• Avoid full table locks (LOCK TABLES)
• Use read replicas to distribute read workloads