You are here

Home

InnoDB deadlock sur SELECT? Pas possible! Ou bien si?

Introduction

Deux points pour commencer:

  1. Un deadlock est un état dans lequel deux transactions différentes ne sont plus en mesure de continuer à travailler, car chaque transaction détient un verrou dont l'autre transaction aurait besoin. Comme les deux transactions attendent que l'autre libère son verrou, aucune des deux transactions ne libérera son verrou. Et cela durerait jusqu'à la saint-glinglin. Pour éviter cela, l'instance MariaDB intervient et tue rapidement la transaction qui a fait le moins de travail. L'application reçoit alors un message d'erreur de type "deadlock":
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  2. Dans l'écosystème MariaDB, il est généralement admis qu'un SELECT ne provoque pas de verrouillage (exception : FOR UPDATE ou LOCK IN SHARE MODE) et ne peut donc pas faire partie d'un deadlock.

Le problème

Un client de longue date se présente à l'équipe DBA à distance de FromDual en lui demandant d'expliquer une situation de deadlock:

Bonjour l'équipe FromDual,
J'ai à nouveau besoin de votre expertise sur le thème des deadlocks.
Quand cela vous conviendrait-il ?


La situation est la suivante: La transaction 1 consiste en un simple INSERT. La transaction 2 consiste en un SELECT. Cela ne devrait PAS provoquer de blocage.

Nous vérifions d'abord les points suivants:

  • Toutes les tables concernées par ces requêtes sont-elles correctement indexées? Oui, elles le sont. Les requêtes fonctionnent toutes parfaitement!
  • Les requêtes SELECT font-elles éventuellement partie d'une transaction d'envergure (transaction NON auto-commit) et ne sont-elles donc pas la cause réelle du blocage? Non, ce n'est pas le cas. Il s'agit de transactions autocommit.

Que faire maintenant? Ce qu'il faut encore préciser: Le SELECT est lancé à une cadence très élevée, c'est-à-dire environ toutes les 5 ms!

Il est évident que l'INSERT génère des verrous. C'est d'ailleurs affiché. Mais pourquoi l'ordre SELECT génère-t-il des verrous? Ceux-ci sont également affichés!

Nous essayons donc de résoudre le problème en plusieurs étapes.

Méthode de résolution

La requête se présente comme suit:

SQL> SET @id = (SELECT id FROM test WHERE id = 3);

Si nous plaçons cette requête dans une transaction explicite, nous pouvons même voir les verrous :

SQL> START TRANSACTION;
SQL> SET @id = (SELECT id FROM test WHERE id = 3);

et dans une deuxième connection:

SQL> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 0
                 trx_state: RUNNING
               trx_started: 2023-11-19 15:27:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 3765
                 trx_query: NULL
       trx_operation_state:
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

Malheureusement, nous ne voyons pas de quel type de verrou (IS) il s'agit, car la vue INNODB_LOCKS est vide.

La solution

Si nous faisons la même tentative avec des SELECT "normaux":

SQL> START TRANSACTION; SELECT id FROM test WHERE id = 3;

ou

SQL> START TRANSACTION; SELECT id INTO @id FROM test WHERE id = 3;

nous ne voyons AUCUN verrouillage:

SQL> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 0
                 trx_state: RUNNING
               trx_started: 2023-11-19 15:31:35
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 3765
                 trx_query: NULL
       trx_operation_state:
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

Il semble donc que la construction SET @id = (...) provoque ce verrou IS. Le client réécrit son application et peu après, nous recevons le message suivant:

Bonjour l'équipe FromDual,
Votre conseil était le bon.
Plus de deadlocks depuis vendredi midi.
Merci et bon week-end.


Autres questions résolues

MySQL 8.0 se comporte de la même manière ? Oui, exactement de la même manière.

Addendum

Mon cher collègue Matthias m'a donné une autre idée de suite: qu'en est-il des procédures stockées et des fonctions stockées de MariaDB?

Les deux tests ici:

DELIMITER //

CREATE OR REPLACE PROCEDURE locktestsp (INOUT id INT)
BEGIN
  SELECT id INTO id FROM test WHERE id = id LIMIT 1;
END;
//

DELIMITER ;

SET @id = 3;
START TRANSACTION;
CALL locktestsp(@id);
SELECT @id;

SELECT trx_tables_locked, trx_lock_structs, trx_rows_locked FROM information_schema.INNODB_TRX;
+-------------------+------------------+-----------------+
| trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-------------------+------------------+-----------------+
|                 0 |                0 |               0 |
+-------------------+------------------+-----------------+

et ici:

DELIMITER //

CREATE OR REPLACE FUNCTION locktestsf (IN id INT)
RETURNS CHAR(50) DETERMINISTIC
BEGIN
  SELECT id INTO id FROM test WHERE id = id LIMIT 1;
  RETURN id;
END;
//

DELIMITER ;

START TRANSACTION;
SELECT locktestsf(3);

 SELECT trx_tables_locked, trx_lock_structs, trx_rows_locked FROM information_schema.INNODB_TRX;
+-------------------+------------------+-----------------+
| trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-------------------+------------------+-----------------+
|                 0 |                0 |               0 |
+-------------------+------------------+-----------------+

Traduit par deepl.com

tags: 
deadlock
select