| Bug #100117 | index range scan locks one extra record larger than the upper boundry | ||
|---|---|---|---|
| Submitted: | 6 Jul 2020 2:46 | Modified: | 9 Jul 2020 11:48 |
| Reporter: | Cheng Zhou | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Jul 2020 2:46]
Cheng Zhou
[6 Jul 2020 12:19]
MySQL Verification Team
Hi Mr. Zhou, Thank you for your bug report. However , I do not think that it is a bug. Your table has so few rows, that no index can be used. Also, with non-unique index, it can not be known where the range finishes. If you manage to repeat the same behaviour with unique index, which is used (as verified by EXPLAIN), then this could be considered as a feature request.
[6 Jul 2020 17:19]
Cheng Zhou
1.set autocommit=1;
2.create table t (a int primary key, b int, c int, d int, unique index(c));
3.create procedure sp(a int, b int)
begin
declare var int;
set var=a;
while var<b do
insert into t values(var,var,var,var);
set var=var+20;
end while;
end //
4. call sp(0, 100000);
5.
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 5000 |
+----------+
6.
mysql> explain select b,c,d from t where b=90 and c>=85 and c<95 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: c
key: c
key_len: 5
ref: NULL
rows: 1
filtered: 10.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
7. mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
8.mysql> select b,c,d from t where b=90 and c>=85 and c<95 for update;
Empty set (0.00 sec)
But record(100,100,100,100) is still locked:
mysql> select OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| t | NULL | TABLE | IX | GRANTED | NULL |
| t | c | RECORD | X,REC_NOT_GAP | GRANTED | 100, 100 |
+-------------+------------+-----------+---------------+-------------+-----------+
[7 Jul 2020 0:42]
Cheng Zhou
mysql> explain select c,d from t where c>=85 and c<95 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: c
key: c
key_len: 5
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
mysql> select c,d from t where c>=85 and c<95 for update\G
Empty set (0.00 sec)
Record (100,100,100,100) is still locked:
mysql> select OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| t | NULL | TABLE | IX | GRANTED | NULL |
| t | c | RECORD | X,REC_NOT_GAP | GRANTED | 100, 100 |
+-------------+------------+-----------+---------------+-------------+-----------+
[7 Jul 2020 12:16]
MySQL Verification Team
Hi Mr. Zhou, I was probably not precise enough. In order to try and evade last record, the entire filtering condition has to be covered by an unique index. Even then, this report would then make a fine feature request.
[8 Jul 2020 10:59]
Cheng Zhou
When using ICP (as verified by EXPLAIN, using index condition), one record out of range would not be unlocked.
In function row_search_mvcc():
switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {
case ICP_NO_MATCH:
if (did_semi_consistent_read) {
row_unlock_for_mysql(prebuilt, TRUE);
}
goto next_rec;
case ICP_OUT_OF_RANGE:
err = DB_RECORD_NOT_FOUND;
goto idx_cond_failed;
case ICP_MATCH:
break;
}
when taking case ICP_OUT_OF_RANGE, the record out of range would not be unlocked by InnoDB or Server.
[8 Jul 2020 11:19]
Cheng Zhou
The code fragment above could be modified like this:
switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {
case ICP_NO_MATCH:
if (did_semi_consistent_read) {
row_unlock_for_mysql(prebuilt, TRUE);
}
goto next_rec;
case ICP_OUT_OF_RANGE:
if (trx->allow_semi_consistent() &&
prebuilt->select_lock_type != LOCK_NONE) {
row_unlock_for_mysql(prebuilt, TRUE);
}
err = DB_RECORD_NOT_FOUND;
goto idx_cond_failed;
case ICP_MATCH:
break;
}
[8 Jul 2020 11:38]
Cheng Zhou
I'm not sure whether the IF condition in case ICP_NO_MATCH need be modified or not. Maybe something like if (trx->allow_semi_consistent() && prebuilt->select_lock_type != LOCK_NONE) ?
[8 Jul 2020 12:25]
Cheng Zhou
The suggests above does not work well ...
[8 Jul 2020 13:45]
MySQL Verification Team
Hi, Please provide us with the feedback that we requested. We need to know when the entire filtering condition has to be covered by a unique index, whether you still got one more row locked. Or not.
[8 Jul 2020 13:58]
Cheng Zhou
Still got one more row locked.
[8 Jul 2020 13:59]
MySQL Verification Team
Hi, Please, can you provide us with a test case.
[8 Jul 2020 16:10]
Cheng Zhou
1.set optimizer_switch='index_condition_pushdown=on';
2.create table t(a int primary key, b int, c int, d int, unique index(c));
3.insert into t values(10,10,10,10),(20,20,20,20),(30,30,30,30),(50,50,50,50),(60,60,60,60);
4. mysql> explain select d from t where c>35 and c<45 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: c
key: c
key_len: 5
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
5. set autocommit=0;
6. mysql> select d from t where c>35 and c<45 for update;
Empty set (0.00 sec)
second index record (50,50) is locked:
mysql> select object_name,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| object_name | lock_type | lock_mode | lock_status | lock_data |
+-------------+-----------+---------------+-------------+-----------+
| t | TABLE | IX | GRANTED | NULL |
| t | RECORD | X,REC_NOT_GAP | GRANTED | 50, 50 |
+-------------+-----------+---------------+-------------+-----------+
[9 Jul 2020 11:49]
MySQL Verification Team
Hi Mr. Zhou, Thank you for the test case. I managed to repeat it. This is now a verified feature request.
