| Bug #93464 | select xxx for update statement conflict insert statement | ||
|---|---|---|---|
| Submitted: | 4 Dec 2018 8:28 | Modified: | 4 Dec 2018 13:49 |
| Reporter: | hongyu dong (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Dec 2018 8:28]
hongyu dong
[4 Dec 2018 13:49]
MySQL Verification Team
Hi, Thank you for your report. However, this is not a bug. This is a behaviour that is fully explained in our Reference Manual. Those are the locks that DML statements, like INSERT, do require. Read-only queries, like SELECT, do require to set shared locks, so that we do not have a case with dirty reads. That is all fully explained in our Reference Manual and is similar to the behaviour of other transactional engines.
[5 Dec 2018 9:33]
Jakub Lopuszanski
Correct me if I'm wrong, but my impression is that the OP is trying to get our attention to the fact, that 1 is strictly smaller than 3, so it is not in the range of the SELECT which is from 3 to 5 inclusive, and thus it is somewhat unexpected that INSERT has to wait.
In particular, it might be argued, that the row with id=3 could be latched in X,REC_NOT_GAP mode, instead of X mode (where X mode is actually REC + GAP), as there is no (obvious) point in locking the gap before row id=3, which is what X does.
I think that what happens in this particular scenario, is that the query is performed as a fullscan, so it really reads everything, and thus has to lock everything it has read.
The reason I believe this, is because I've conducted following experiment, which adds two more rows to the original bug report:
mysql> CREATE TABLE `t1` (
-> `id` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> INSERT INTO t1 VALUES (0),(3),(5),(10);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+
| id |
+----+
| 0 |
| 3 |
| 5 |
| 10 |
+----+
4 rows in set (0.00 sec)
mysql> EXPLAIN select * from t1 where id>=3 and id <= 5 for update;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 4 | 50.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` >= 3) and (`test`.`t1`.`id` <= 5)) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id>=3 and id <= 5 for update;
+----+
| id |
+----+
| 3 |
| 5 |
+----+
2 rows in set (0.00 sec)
mysql> select engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
| engine_transaction_id | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
| 1597 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 1597 | t1 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 1597 | t1 | PRIMARY | RECORD | X | GRANTED | 0 |
| 1597 | t1 | PRIMARY | RECORD | X | GRANTED | 3 |
| 1597 | t1 | PRIMARY | RECORD | X | GRANTED | 5 |
| 1597 | t1 | PRIMARY | RECORD | X | GRANTED | 10 |
+-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+
6 rows in set (0.00 sec)
As you can see above, the EXPLAIN says it will visit 4 rows, and will filter half of them, and indeed, when you execute the query, all the rows (including 0,10 and supremum) get locked.
It is the same story, if we change the query to:
select * from t1 where id between 3 and 5 for update;
However, if one adds a FORCE INDEX(PRIMARY) hint, the situation looks differently:
mysql> begin; select * from t1 FORCE INDEX(PRIMARY) where id>=3 and id <= 5 for update;
Query OK, 0 rows affected (0.00 sec)
+----+
| id |
+----+
| 3 |
| 5 |
+----+
2 rows in set (0.00 sec)
mysql> select engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_transaction_id | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 1599 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 1599 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| 1599 | t1 | PRIMARY | RECORD | X | GRANTED | 5 |
| 1599 | t1 | PRIMARY | RECORD | X | GRANTED | 10 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
(It still could be argued, that it is a bit silly to look at the record after 5, which is 10, given that we already saw a row with id=5, the range ends at 5, and this is primary key, so no duplicates are allowed. But at least this time we don't look at 0 and supremum)
