Bug #93464 select xxx for update statement conflict insert statement
Submitted: 4 Dec 2018 8:28 Modified: 4 Dec 2018 13:49
Reporter: donghongyu dong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[4 Dec 2018 8:28] donghongyu dong
Description:
hello:
It is hard to understand that there is a lock problem, as follows:

Table definition:
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table rows:
mysql> select * from t1;
+----+
| id |
+----+
|  3 |
|  5 |
+----+
2 rows in set (0.00 sec)

session1: begin;select * from t1 where id>=3 and id <= 5 for update;
session2:begin;insert into t1 values (1); -- Blocking

Transaction isolation level:REPEATABLE-READ 

Id is the primary key, the record with the insertion value of 1 should not conflict with the statement in session1, because there is no phantom read in this case.

The information when the lock occurs is as follows:
+----------------+-----------------------+-----------+---------------+-------------+------------+-----------+------------------------+-------------+------------------------+
| ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA              |
+----------------+-----------------------+-----------+---------------+-------------+------------+-----------+------------------------+-------------+------------------------+
| 28305:1096     |                 28305 |        89 | dhy           | t1          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
| 28305:39:4:4   |                 28305 |        89 | dhy           | t1          | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 3                      |
| 28304:1096     |                 28304 |        97 | dhy           | t1          | NULL       | TABLE     | IX                     | GRANTED     | NULL                   |
| 28304:39:4:1   |                 28304 |        97 | dhy           | t1          | PRIMARY    | RECORD    | X                      | GRANTED     | supremum pseudo-record |
| 28304:39:4:3   |                 28304 |        97 | dhy           | t1          | PRIMARY    | RECORD    | X                      | GRANTED     | 5                      |
| 28304:39:4:4   |                 28304 |        97 | dhy           | t1          | PRIMARY    | RECORD    | X                      | GRANTED     | 3                      |
+----------------+-----------------------+-----------+---------------+-------------+------------+-----------+------------------------+-------------+------------------------+

session2 request X,GAP,INSERT_INTENTION lock  WAITING session1 id = 3 X lock

How to repeat:
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t1 values (3),(5);

session1: begin;select * from t1 where id>=3 and id <= 5 for update;
session2:begin;insert into t1 values (1);
[4 Dec 2018 13:49] Sinisa Milivojevic
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)