You are here

Home » Blogs » oli's blog

InnoDB Deadlock bei SELECT? Nicht möglich! Oder doch?

Sun, 2023-11-19 16:18 — oli

Einleitung

Kurz vorab zwei Punkte:

  1. Ein Deadlock ist eine Zustand, in welchem 2 unterschiedliche Transaktionen nicht mehr in der Lage sind weiter zu arbeiten, weil jede Transaktion jeweils einen Lock hält, welche die andere Transaktion gerade bräuchte. Weil jetzt beide Transaktionen jeweils darauf warten, bis die andere Transaktion ihren Lock wieder frei gibt, wird keine von beiden Transaktionen ihre jeweiligen Locks wieder frei geben. Und das würde bis zum Sankt-Nimmerleins-Tag andauern. Um das zu vermeiden schreitet die MariaDB Instanz ein und killt kurzerhand diejenige Transaktion, die weniger Arbeit geleistet hat. Die Applikation kriegt darauf hin eine Deadlock Fehlermeldung vom Typ:
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  2. Im MariaDB Ökosystem gilt allgemein das Mantra, dass ein SELECT keine Locks verursacht (Ausnahme: FOR UPDATE oder LOCK IN SHARE MODE) und somit auch nicht Teil eines Deadlocks sein kann.

Das Problem

Ein langjähriger Kunde kommt zum FromDual remote-DBA Team mit der Bitte, eine Deadlock-Situation zu erklären:

Hallo FromDual Team,
ich brauche mal wieder euer Fachwissen zum Thema Deadlocks.
Wann würde es Euch passen?


Die Situation ist folgende: Transaktion 1 besteht aus einem simplen INSERT. Transaktion 2 besteht aus einem SELECT. Das dürfte eigentlich KEINEN Deadlock verursachen!

Zuerst prüfen wir folgende Punkte ab:

  • Sind alle Tabellen, die durch diese Abfragen betroffen sind, sauber indexiert? Jawohl sind sie. Die Queries laufen alle perfekt!
  • Ist die SELECT Abfragen eventuell Teil einer grösseren Transaktion (NICHT Auto-Commit Transaktion) und daher nicht die eigentlich Ursache für den Deadlock? Nein, ist sie nicht. Es handelt sich um Auto-Commit Transaktionen.

Was nun? Was man zur Erläuterung noch sagen muss: Das SELECT wird mit einer sehr hohen Kadenz, also so ca. alle 5 ms abgesetzt!

Dass der INSERT Locks erzeugt ist klar. Wird ja auch angezeigt. Aber warum erzeugt der SELECT Befehl Locks? Diese werden ebenfalls angezeigt!

Also versuchen wir das Problem in Einzelschritte runter zu brechen.

Der Lösungsweg

Das Query sieht wie folgt aus:

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

Wenn wir dieses Query in eine explizite Transaktion packen, können wir die Locks sogar sehen:

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

und in einer zweiten Session:

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

Leider sehen wir nicht welche Art von Lock (IS) es ist, da die View INNODB_LOCKS leer ist.

Die Lösung

Wenn wir den selben Versuch mit "normalen" SELECTs machen:

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

oder

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

sehen wir KEINE Locks:

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

Es scheint also so zu sein, dass das Konstrukt SET @id = (...) diesen IS Lock verursacht. Der Kunde schreibt seine Applikation um und kurz darauf erhalten wir folgende Meldung:

Hallo FromDual Team,
Euer Tipp war goldrichtig.
Seit Freitag Mittag keine Deadlocks mehr.
Danke und schönes Wochenende.


Weitere geklärte Fragen

MySQL 8.0 verhält sich gleich? Ja, genau gleich.

Taxonomy upgrade extras: 
deadlock
select

Comments

Mein lieber Kollege Matthias hat mich noch auf eine Folgeidee gebracht: Wie sieht das Ganze aus mit MariaDB Stored Procedures und Stored Functions?

Die beiden Tests hier:

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

und hier:

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

olicomment Sat, 2023-11-25 12:27