InnoDB Deadlock on SELECT? Not possible! Or Is It?

Introduction

Two points in advance:

1. A deadlock is a state in which two different transactions are no longer able to continue working because each transaction holds a lock that the other transaction would need. Because both transactions are now waiting for the other transaction to release their locks, neither transaction will release their respective locks. And that would last forever. To avoid this, the MariaDB instance intervenes and kills the transaction that has done less work. The application then receives a deadlock error message of this type:
   

   ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

   
   
2. A general mantra in the MariaDB ecosystem is that a SELECT does not cause locks (exception: FOR UPDATE or LOCK IN SHARE MODE) and therefore cannot be part of a deadlock.

The problem

A long-standing customer comes to the FromDual remote DBA team with a request to explain a deadlock situation:

Hello FromDual Team,
I need your expertise on the subject of deadlocks.
When would it suit you?

The situation is as follows: Transaction 1 consists of a simple INSERT. Transaction 2 consists of a SELECT. This should NOT actually cause a deadlock!

We first check the following points

• Are all tables affected by these queries properly indexed? Yes, they are. The queries are all running perfectly!
• Is the SELECT query possibly part of a larger transaction (NOT an auto-commit transaction) and therefore not the actual cause of the deadlock? No, it is not. They are auto-commit transactions.

What now? What else needs to be said for clarification: The SELECT is sent with a very high cadence, i.e. approx. every 5 ms!

It is clear that the INSERT generates locks. It is also displayed. But why does the SELECT command generate locks? These are also displayed!

So we try to break the problem down into individual steps.

The approach

The query looks like this:

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

If we pack this query into an explicit transaction, we can even see the locks:

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

and in a second 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

Unfortunately, we cannot see what kind of lock (IS) it is, because the view INNODB_LOCKS is empty.

The solution

If we do the same experiment with "normal" SELECTs:

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

or

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

we do NOT see any 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

So it seems that the construct SET @id = (...) causes this IS lock. The customer rewrites his application and shortly afterwards we receive the following message:

Hello FromDual team,
Your tip was spot on.
No more deadlocks since Friday lunchtime.
Thank you and have a nice weekend.

Further clarified questions

MySQL 8.0 behaves the same? Yes, exactly the same.

Addendum

My dear colleague Matthias gave me a follow-up idea: What about MariaDB Stored Procedures and Stored Functions?

The two tests here:

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

and here:

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