You are here
InnoDB Deadlock on SELECT? Not possible! Or Is It?
Introduction
Two points in advance:
- 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
- A general mantra in the MariaDB ecosystem is that a
SELECT
does not cause locks (exception:FOR UPDATE
orLOCK 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" SELECT
s:
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 | +-------------------+------------------+-----------------+
Translated by deepl.com
- Shinguz's blog
- Log in or register to post comments