Question 187390 in Mysql Question posted in
The official MySQL documentation can be found here.

Why are these concurrent transactions causing a deadlock? (mysql 8.0 innoDB)

Let’s say we have the following table

CREATE DATABASE IF NOT EXISTS humans;
USE humans;

CREATE TABLE IF NOT EXISTS address (
    last_name VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    PRIMARY KEY (last_name)
);


INSERT INTO address values ("x", "abcd");
INSERT INTO address values ("y", "asdf");


CREATE TABLE IF NOT EXISTS names (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (first_name, last_name),
    FOREIGN KEY (last_name) REFERENCES address(last_name)
);

I am adding records to the names table, but before adding it, I am deleting all the records and then re-creating them (just to reproduce the deadlock)

Start 2 separate transactions. transaction-1

START transaction;
DELETE FROM names where last_name="x";
<do not commit or rollback>

transaction-2

START transaction
DELETE FROM names where last_name="y";
<do not commit or rollback>

And then in transaction-1

INSERT INTO names VALUES ("a", "x");

and in transaction-2

INSERT INTO names VALUES  ("b", "y");

This results in a deadlock.

I am not sure why though. IIUC, innoDB locks rows of the table, and not the entire table. Both the transactions are deleting separate records and adding separate records. So why is there a deadlock?

Here are more details

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table names;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| names | CREATE TABLE `names` (
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Here is the deadlock from SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-06-13 09:46:39 0x700005d8d000
*** (1) TRANSACTION:
TRANSACTION 23728, ACTIVE 305 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1177, OS thread handle 123145414819840, query id 307296 localhost root update
INSERT INTO names VALUES ("a", "x")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 23729, ACTIVE 302 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1178, OS thread handle 123145415884800, query id 307297 localhost root update
INSERT INTO names VALUES  ("b", "y")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
Tags:

2

Answers


  1. 0

    Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

    mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE;
+-------+
| value |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)
    Login or Signup to reply.
  2. 0

    Without an index on last_name, the DELETEs must search the entire table. No, the PK that you have does not help.

    Adding INDEX(last_name) may solve your problem. More efficient would be (see @danblack), to change to PRIMARY KEY(last_name, first_name) unless there is some reason to need the locality of reference of first_name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search