Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.6.2, 10.11.11
-
Linux/Docker
Description
Given the following database structure:
CREATE DATABASE `test`; |
USE `test`; |
CREATE TABLE `booking` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=1;
|
|
|
CREATE TABLE `ticket` ( |
`ticket_id` bigint(20) NOT NULL AUTO_INCREMENT, |
`is_valid` tinyint(1) NOT NULL DEFAULT 1, |
`booking_id` int(11) DEFAULT NULL, |
PRIMARY KEY (`ticket_id`), |
KEY `fk_ticket_2_booking` (`booking_id`), |
CONSTRAINT `fk_ticket_2_booking` FOREIGN KEY (`booking_id`) REFERENCES `booking` (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=1;
|
|
|
INSERT INTO booking VALUES(); |
INSERT INTO booking VALUES(); |
|
|
/* Without these two the lock is "X, supremum presudo-record", without - it's "X,GAP" */
|
INSERT INTO ticket (booking_id) VALUE(1); |
INSERT INTO ticket (booking_id) VALUE(2); |
Now, the expected locks occur in the following situation (all connections here and below are SET autocommit=0):
Conn 1: `UPDATE ticket SET is_valid = 0 WHERE booking_id = 1;`
Conn 2: `UPDATE ticket SET is_valid = 0 WHERE booking_id = 2;`
Conn 1: `INSERT INTO ticket (booking_id) VALUE(1);`
Conn 1 is now blocked by the following lock:
lock_mode: X
|
lock_type: RECORD
|
lock_table: `test`.`ticket`
|
lock_index: fk_ticket_2_booking
|
lock_space: 9
|
lock_page: 4
|
lock_rec: 1
|
lock_data: supremum pseudo-record
|
If Conn 2 attempts to do `INSERT INTO ticket (booking_id) VALUE(2);` deadlock occurs.
Full locks for both TXs.
> SELECT t.*, l.* FROM INNODB_TRX t LEFT OUTER JOIN INNODB_LOCKS l ON t.trx_id = l.lock_trx_id \G
|
|
|
1. row
|
|
|
trx_id: 29
|
trx_state: LOCK WAIT
|
trx_started: 2025-05-16 19:59:37
|
trx_requested_lock_id: 29:9:4:1
|
trx_wait_started: 2025-05-16 20:07:20
|
trx_weight: 9
|
trx_mysql_thread_id: 3
|
trx_query: INSERT INTO ticket (booking_id) VALUES(1)
|
trx_operation_state: inserting
|
trx_tables_in_use: 1
|
trx_tables_locked: 2
|
trx_lock_structs: 7
|
trx_lock_memory_bytes: 1128
|
trx_rows_locked: 5
|
trx_rows_modified: 2
|
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
|
lock_id: 29:9:4:1
|
lock_trx_id: 29
|
lock_mode: X
|
lock_type: RECORD
|
lock_table: `test`.`ticket`
|
lock_index: fk_ticket_2_booking
|
lock_space: 9
|
lock_page: 4
|
lock_rec: 1
|
lock_data: supremum pseudo-record
|
|
|
2. row
|
|
|
trx_id: 34
|
trx_state: RUNNING
|
trx_started: 2025-05-16 20:04:19
|
trx_requested_lock_id: NULL
|
trx_wait_started: NULL
|
trx_weight: 2
|
trx_mysql_thread_id: 4
|
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
|
lock_id: NULL
|
lock_trx_id: NULL
|
lock_mode: NULL
|
lock_type: NULL
|
lock_table: NULL
|
lock_index: NULL
|
lock_space: NULL
|
lock_page: NULL
|
lock_rec: NULL
|
lock_data: NULL
|
2 rows in set (0.001 sec)
|
So far so good.
If we were to add a few tickets, let's say Ticket 1/Booking 1 and Ticket 2/Booking 2 to the ticket table and then ran:
Conn 1: `UPDATE ticket SET is_valid = 0 WHERE ticket_id IN (1);`
Conn 2: `UPDATE ticket SET is_valid = 0 WHERE ticket_id IN (2);`
There would be NO X gap lock on `fk_ticket_2_booking`. Also an expected result.
Here's where the bug
seems to happen:
What about the following?
`UPDATE ticket SET is_valid WHERE ticket_id IN (SELECT ticket_id FROM ticket WHERE booking_id = 1);`
This unexpectedly behaves lock-wise exactly like `UPDATE ticket SET is_valid=0 WHERE booking_id = 1;`.
In my opinion it should not.
The `ticket_id` IN-operation in the UPDATE should rightfully take the PK row locks but not the booking_id FK X gap locks, as the booking_id is not actually compared anywhere in the UPDATE query, only in the SELECT subquery.
Looking at the analysis both `UPDATE ... WHERE booking_id = X` AND `UPDATE ... WHERE ticket_id IN (<subq>)` produce distinctly different results:
> ANALYZE FORMAT=JSON UPDATE ticket SET is_valid = 0 WHERE booking_id = 1 \G
|
|
|
1. row
|
|
|
ANALYZE: {
|
"query_optimization": {
|
"r_total_time_ms": 0.072844233
|
},
|
"query_block": {
|
"select_id": 1,
|
"r_total_time_ms": 0.040248096,
|
"table": {
|
"update": 1,
|
"table_name": "ticket",
|
"access_type": "range",
|
"possible_keys": ["fk_ticket_2_booking"],
|
"key": "fk_ticket_2_booking",
|
"key_length": "5",
|
"used_key_parts": ["booking_id"],
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_filtered": 100,
|
"r_total_time_ms": 0.027836258,
|
"r_engine_stats": {
|
"pages_accessed": 4,
|
"old_rows_read": 1
|
},
|
"attached_condition": "ticket.booking_id = 1",
|
"r_filtered": 100
|
}
|
}
|
}
|
1 row in set (0.001 sec)
|
vs
> ANALYZE FORMAT=JSON UPDATE ticket SET is_valid = 0 WHERE ticket_id IN (SELECT ticket_id FROM ticket WHERE booking_id = 1) \G
|
|
|
1. row
|
ANALYZE: {
|
"query_optimization": {
|
"r_total_time_ms": 0.148751258
|
},
|
"query_block": {
|
"select_id": 1,
|
"cost": 0.003506245,
|
"r_loops": 1,
|
"r_total_time_ms": 0.045560282,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "ticket",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "fk_ticket_2_booking"],
|
"key": "fk_ticket_2_booking",
|
"key_length": "5",
|
"used_key_parts": ["booking_id"],
|
"ref": ["const"],
|
"loops": 1,
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"cost": 0.001792605,
|
"r_table_time_ms": 0.015093036,
|
"r_other_time_ms": 0.004980802,
|
"r_engine_stats": {
|
"pages_accessed": 3,
|
"old_rows_read": 1
|
},
|
"filtered": 100,
|
"r_total_filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "ticket",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["ticket_id"],
|
"ref": ["test.ticket.ticket_id"],
|
"loops": 1,
|
"r_loops": 1,
|
"r_table_loops": 2,
|
"rows": 1,
|
"r_rows": 1,
|
"cost": 0.00171364,
|
"r_table_time_ms": 0.002460275,
|
"r_other_time_ms": 0.018848721,
|
"r_engine_stats": {
|
"pages_accessed": 2
|
},
|
"filtered": 100,
|
"r_total_filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
]
|
}
|
}
|
1 row in set (0.001 sec)
|
You can clearly see that the optimizer does not eliminate the SELECT subquery by folding it into the UPDATE query WHERE clause itself. Yet the gap lock is somehow acquired as if the UPDATE was filtering on the FK index itself.
====
To summarize:
I believe that an update filtering on a primary key using a subquery SELECT on a foreign key should not behave any differently with respect to an exclusive gap lock on that foreign key index from an UPDATE on the primary key where the list of the primary keys was provided directly rather than via a subquery.
After all, if within the same transaction I was to do via the client:
ids := SELECT ticket_id FROM ticket WHERE booking_id = 1; |
UPDATE ticket SET is_valid = 0 WHERE ticket_id IN (<ids>); |
then no x gap lock on the FK would be acquired. The subquery embedded in the UPDATE shouldn't produce one either.