Lock Acquisition Order in MYSQL

22 views Asked by At

I need a clear picture about lock acquisition order when performing certain queries. Below is the example, I tried.

+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| TestA | CREATE TABLE `TestA` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+

Within a transaction, I perform,

delete from TestA where id in (7,1);

My doubt is when getting record locks, will it get based on the order given in the list(7,1) or based on the table scan(1,7). Or is there any other factors determining this lock acquisition order?

Also, I checked data_locks in performance_schema and the result was;

+--------+----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                   | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 5254385560:563294:5198889928     |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | NULL       |            5198889928 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 5254385560:562138:4:2:5201207320 |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | PRIMARY    |            5201207320 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| INNODB | 5254385560:562138:4:5:5201207320 |               9915434 |      5974 |      695 | test          | testa       | NULL           | NULL              | PRIMARY    |            5201207320 | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+--------+----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

From this table, should I conclude the locking order was 1,7? Or will it vary in some cases?

0

There are 0 answers