A strange deadlock: it seems that a delete locks multiple secondary indexes, a bug of MySQL?

Question

Recently I got an unexpected deadlock with the below 3 delete (MySQL 5.7.12, isolation level: Repeatable Read)

Session 1

begin;
delete from fruit where age = 99;

Session 2

begin;
delete from fruit where name = 'lg4xdzvn2bth';

Session 1

delete from fruit where name = 'lg4xdzvn2bth';

Session 2 got the deadlock here, how surprising! anybody know why? It seems have something to do with the first delete in Session 1.

However, AFAIK, the delete.. where age = .. should lock on i_age secondary index, which have nothing to do with the index i_name, right?

Deadlock report

2021-06-23 17:59:52 0x700003e01000
*** (1) TRANSACTION:
TRANSACTION 221465, ACTIVE 5 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145367605248, query id 9710 localhost root updating
delete from fruit where name = 'lg4xdzvn2bth'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221465 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
 1: len 8; hex 80000000000038a3; asc       8 ;;

*** (2) TRANSACTION:
TRANSACTION 221464, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 25 row lock(s), undo log entries 11
MySQL thread id 2, OS thread handle 123145367326720, query id 9711 localhost root updating
delete from fruit where name = 'lg4xdzvn2bth'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221464 lock_mode X locks rec but not gap
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
 1: len 8; hex 80000000000038a3; asc       8 ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221464 lock_mode X waiting
Record lock, heap no 428 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
 1: len 8; hex 80000000000038a2; asc       8 ;;

Table

 CREATE TABLE `fruit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_age` (`age`),
  KEY `i_name` (`name`)
) ENGINE=InnoDB;

We could use the below procedure to generate data

    delimiter ;;
    CREATE PROCEDURE `init_data`()
    BEGIN
        DECLARE i int DEFAULT 1;
        WHILE i < 500 DO
            INSERT INTO fruit (`name`, `age`) VALUES 
            (substring(MD5(RAND()),1,20), i / 10 + 1),
            (substring(MD5(RAND()),1,20), i / 10 + 1),
            (substring(MD5(RAND()),1,20), i / 10 + 1);
            SET i = i + 1;
        END WHILE;
        insert into fruit (name, age) values ('lg4xdzvn2bth', 56), ('lg4xdzvn2bth', 99);
    END ;;
    delimiter ;


call init_data();

---

Updated

To make it clear, I repeat it on MySQL 8.0, so we could use performance_schema.data_locks to see the lock details.

Session 1

delete from fruit where age = 99;

+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
| thread_id | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
|        47 | fruit       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | supremum pseudo-record |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | 99, 16500              |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | 99, 16501              |
|        47 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16500                  |
|        47 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16501                  |

However, we do not see the delete hold locks on other secondary index i_name.

Session 2:

delete from fruit where name = 'lg4xdzvn2bth';

+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
| thread_id | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
|        80 | fruit       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|        80 | fruit       | i_name     | RECORD    | X             | GRANTED     | 'lg4xdzvn2bth', 16499  |
|        80 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16499                  |
|        80 | fruit       | i_name     | RECORD    | X             | WAITING     | 'lg4xdzvn2bth', 16500  |  <-----
|        80 | fruit       | i_name     | RECORD    | X,REC_NOT_GAP | GRANTED     | 'lg4xdzvn2bth', 16500  |  <-----

Look at the last 2 rows, a strange thing is that session 2 hold a record lock (not gap), but waiting a next key lock of the same index record, so weird! The index record 'lg4xdzvn2bth', 16500 should be hold by session 1, right? a bug here?

Answer 1

ypercubeᵀᴹ: When you delete a row you need to delete the reference to the row in all secondary indexes. Both transactions want to delete the row with name = 'lg4xdzvn2bth' and age=99.

Tran 2 was second to request that so it was waiting. But now Tran 1 wants to delete all the rows with name = 'lg4xdzvn2bth' as well, which tran 2 has already locks on.

So they are both waiting on each other locks. Deadlock.