How does mysql lock work for compound unique index?

Question

I have read the mysql docs regarding various kinds of locks and I don't have clear understanding why I'm having locks and deadlocks in my specific situation.

We have a join-table with unique compound index:

mysql> SHOW CREATE TABLE workflow_run_assigned_users \G
*************************** 1. row ***************************
       Table: workflow_run_assigned_users
Create Table: CREATE TABLE `workflow_run_assigned_users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `workflow_run_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_workflow_run_assigned_users_on_user_id_and_workflow_run_id` (`user_id`,`workflow_run_id`),
  KEY `index_workflow_run_assigned_users_on_workflow_run_id` (`workflow_run_id`),
  KEY `index_workflow_run_assigned_users_on_user_id` (`user_id`),
  CONSTRAINT `fk_rails_77cc0a4937` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rails_9202262959` FOREIGN KEY (`workflow_run_id`) REFERENCES `workflow_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

We have a job that for some event recalculates which users are associated with specific workflow_run and writes that data inside transaction, eg:

BEGIN;

# delete previous data
DELETE FROM workflow_run_assigned_users WHERE id = XXX;

# insert new data
INSERT INTO `workflow_run_assigned_users` (`workflow_run_id`,`user_id`,`created_at`,`updated_at`) VALUES (A, B, C, D) ON DUPLICATE KEY UPDATE `workflow_run_id`=`workflow_run_id`;

COMMIT;

Those jobs are concurrent and we started experiencing deadlocks, even when different transactions operate with records containing non-conflicting data (both user_id and workflow_run_id). Here is an example that I succeed to reproduce in the parallel consoles:

mysql1>  select * from workflow_run_assigned_users where workflow_run_id = 6310;
+-----+-----------------+---------+----------------------------+----------------------------+
| id  | workflow_run_id | user_id | created_at                 | updated_at                 |
+-----+-----------------+---------+----------------------------+----------------------------+
| 111 |            6310 |    2026 | 2023-04-23 10:26:54.343125 | 2023-04-23 10:26:54.343125 |
| 112 |            6310 |    2025 | 2023-04-23 10:26:54.343125 | 2023-04-23 10:26:54.343125 |
+-----+-----------------+---------+----------------------------+----------------------------+
2 rows in set (0.00 sec)

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql1> DELETE FROM workflow_run_assigned_users WHERE id = 111;
Query OK, 1 row affected (0.00 sec)

then in second client:

mysql2> select * from workflow_run_assigned_users where workflow_run_id = 10570;
+-----+-----------------+---------+----------------------------+----------------------------+
| id  | workflow_run_id | user_id | created_at                 | updated_at                 |
+-----+-----------------+---------+----------------------------+----------------------------+
| 113 |           10570 |    2025 | 2023-04-23 10:26:59.773823 | 2023-04-23 10:26:59.773823 |
+-----+-----------------+---------+----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql2> DELETE FROM workflow_run_assigned_users WHERE id = 113;                                                                                                                                            
Query OK, 1 row affected (0.01 sec)

mysql2> INSERT INTO `workflow_run_assigned_users` (`workflow_run_id`,`user_id`,`created_at`,`updated_at`) VALUES (10570, 2025, '2023-04-16 10:27:21.227936', '2023-04-16 10:27:21.227936') ON DUPLICATE KEY UPDATE `workflow_run_id`=`workflow_run_id`;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is what we can see regarding the locks in the third console, while second awaits the lock:

mysql> SELECT * FROM performance_schema.data_locks WHERE object_name = 'workflow_run_assigned_users' \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736800:774749:140693935777056
ENGINE_TRANSACTION_ID: 21941932
            THREAD_ID: 1094
             EVENT_ID: 294
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140693935777056
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736800:772939:4:16:140693943223840
ENGINE_TRANSACTION_ID: 21941932
            THREAD_ID: 1094
             EVENT_ID: 294
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140693943223840
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 113
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736800:772939:7:8:140693943225216
ENGINE_TRANSACTION_ID: 21941932
            THREAD_ID: 1094
             EVENT_ID: 295
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943225216
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2025, 10570, 113
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736800:772939:7:8:140693943225560
ENGINE_TRANSACTION_ID: 21941932
            THREAD_ID: 1094
             EVENT_ID: 295
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943225560
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2025, 10570, 113
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736800:772939:7:11:140693943225904
ENGINE_TRANSACTION_ID: 21941932
            THREAD_ID: 1094
             EVENT_ID: 295
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943225904
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 2026, 6310, 111
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736008:774749:140693935776032
ENGINE_TRANSACTION_ID: 21941925
            THREAD_ID: 1093
             EVENT_ID: 288
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140693935776032
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736008:772939:4:13:140693943219232
ENGINE_TRANSACTION_ID: 21941925
            THREAD_ID: 1093
             EVENT_ID: 288
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140693943219232
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 111
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140693718736008:772939:7:11:140693943219576
ENGINE_TRANSACTION_ID: 21941925
            THREAD_ID: 1094
             EVENT_ID: 290
        OBJECT_SCHEMA: tap-qa
          OBJECT_NAME: workflow_run_assigned_users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943219576
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2026, 6310, 111
8 rows in set (0.00 sec)

How I read this output:

# first transaction holds exclusive lock on index record (2026, 6310, 111)
*************************** 8. row ***************************
ENGINE_TRANSACTION_ID: 21941925
          OBJECT_NAME: workflow_run_assigned_users
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943219576
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2026, 6310, 111

# while second is waiting in attempt to hold the same index record, even if it tried to insert totally different dat (10570, 2025, ...)
*************************** 5. row ***************************
ENGINE_TRANSACTION_ID: 21941932
          OBJECT_NAME: workflow_run_assigned_users
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
OBJECT_INSTANCE_BEGIN: 140693943225904
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 2026, 6310, 111

# there is another entry about GAP lock that first transaction holds, but I'm not sure it is matters here
ENGINE_TRANSACTION_ID: 21941932
          OBJECT_NAME: workflow_run_assigned_users
           INDEX_NAME: index_workflow_run_assigned_users_on_user_id_and_workflow_run_id
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2025, 10570, 113

So my questions:

Is it GAP or maybe Next-key lock?
It looks like that even if unique index is compound, only first column matters (in this case user_id). When I swap the order of columns in the unique index from (user_id, workflow_run_id) to (workflow_run_id, user_id), there is no lock in this test case.
Also note, that second session first deletes the row and then inserts the same data as deleted row contained. Why there were no conflict during DELETION, but we got it during INSERT?

Mysql version is 8.0.32

Does my answer here answer your question? https://stackoverflow.com/questions/68627347/why-do-i-get-a-deadlock-error-when-unique-index-is-present/68627699#68627699 — Bill Karwin, Apr 23 '23 at 14:57
@BillKarwin, thanks for your reply, but your answer does not contain explanation of why lock in my case exists. We already have pessimistic record locks (SELECT FOR UPDATE) to avoid the concurrent transactions by the same `workflow_run_id`, but this (dead)lock happens when we try to insert non-conflicting data (at the first glance). — Andrey Khataev, Apr 23 '23 at 19:45

How does mysql lock work for compound unique index?

0 Answers0