You are here

Home

InnoDB deadlock con SELECT? Non è possibile! O forse sì?

Introduzione

Due punti per iniziare:

  1. Un deadlock è uno stato in cui due transazioni diverse non sono più in grado di continuare a lavorare perché ciascuna transazione detiene un blocco di cui l'altra transazione avrebbe bisogno. Poiché entrambe le transazioni stanno aspettando che l'altra transazione rilasci il proprio blocco, nessuna delle due rilascerà il proprio blocco. E questo durerebbe fino a dopodomani. Per evitare ciò, l'istanza di MariaDB interviene e uccide la transazione che ha svolto meno lavoro. L'applicazione riceve quindi un messaggio di errore di deadlock del tipo:
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  2. Il mantra generale nell'ecosistema MariaDB è che una SELECT non causa blocchi (eccezione: FOR UPDATE o LOCK IN SHARE MODE) e quindi non può essere parte di un deadlock.

Il problema

Un cliente di lunga data si rivolge al team di DBA remoti di FromDual con la richiesta di spiegare una situazione di deadlock:

Salve team FromDual,
Ho bisogno della vostra competenza in materia di deadlock.
Quando vi farebbe comodo?


La situazione è la seguente: La transazione 1 consiste in una semplice INSERT. La transazione 2 consiste in una SELECT. Questo non dovrebbe causare un deadlock!

Per prima cosa controlliamo i seguenti punti:

  • Tutte le tabelle interessate da queste query sono indicizzate correttamente? Sì, lo sono. Le query funzionano tutte perfettamente!
  • La query SELECT fa forse parte di una transazione più ampia (NON una transazione con auto-commit) e quindi non è la causa effettiva del deadlock? No, non lo è. Si tratta di transazioni con auto-commit.

E adesso? Cosa c'è da dire per chiarire: La SELECT viene inviata con una cadenza molto alta, cioè circa ogni 5 ms!

È chiaro che l'INSERT crea dei blocchi. Viene anche visualizzato. Ma perché il comando SELECT genera dei blocchi? Anche questi vengono visualizzati!

Cerchiamo quindi di scomporre il problema in singoli passaggi.

Modo per risolvere

La query si presenta come segue:

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

Se impacchettiamo questa query in una transazione esplicita, possiamo anche vedere i lock:

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

e in una seconda connessione:

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

Purtroppo non è possibile vedere di che tipo di blocco (IS) si tratta perché la vista INNODB_LOCKS è vuota.

La soluzione

Se facciamo lo stesso tentativo con delle SELECT "normali":

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

o

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

NON vediamo nessun blocco:

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

Sembra quindi che il costrutto SET @id = (...) causi questo blocco IS. Il cliente riscrive la sua applicazione e poco dopo riceviamo il seguente messaggio:

Salve team FromDual,
Il vostro consiglio è stato azzeccato.
Nessun blocco da venerdì a pranzo.
Grazie e buon fine settimana.


Ulteriori domande chiarificatrici

MySQL 8.0 si comporta allo stesso modo? Sì, esattamente lo stesso.

Addendum

Il mio caro collega Matthias mi ha dato un'altra idea: che dire delle Stored Procedures e delle Stored Functions di MariaDB?

I due test sono qui:

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 |
+-------------------+------------------+-----------------+

e qui:

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 |
+-------------------+------------------+-----------------+

Tradotto da: deepl.com

tags: 
deadlock
select