| Bug #102722 | Unmatched second indexes are locked when index_condition_pushdown=ON | ||
|---|---|---|---|
| Submitted: | 24 Feb 2021 10:50 | Modified: | 12 Jul 2022 1:12 |
| Reporter: | Cheng Zhou | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Feb 2021 13:18]
MySQL Verification Team
Hi Mr. Zhou, Thank you for your bug report. However, this is a behaviour that is very well known for the index pushdowns. However, we do not see it properly documented in our Reference Manual. Hence, we are verifying your report as a documentation bug.
[26 Feb 2021 1:28]
Cheng Zhou
Well, I guess it would be better to unlock those unmatched second index records properly. In our production environment, we executed 'select ... for update limit 50000' in a transaction, but more than 1000000 second indexes were locked, which caused severe lock conflicts.
[26 Feb 2021 13:04]
MySQL Verification Team
Hi Mr. Zhou, We do agree with you , which is why this report will get a wider discussion .......
[23 Jul 2021 14:12]
Jakub Lopuszanski
Posted by developer:
The testcase from the original report expressed as MTR:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
show variables like '%isolation%';
create table ts(a int primary key, b int, c int, d int, index(b,c));
insert into ts
values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8
,8,8,8),(9,9,9,9);
--let $i=0
while($i<2){
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
explain select b,c,d from ts where b>=5 and b<8 and c=7 for update;
begin;
select b,c,d from ts where b>=5 and b<8 and c=7 for update;
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
commit;
SET @@optimizer_switch='index_condition_pushdown=off';
--inc $i
}
DROP TABLE ts;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
[26 Jul 2021 15:32]
Jakub Lopuszanski
Posted by developer: Just calling the row_unlock_for_mysql(prebuilt,trx) in the case reported in this issue, will not be enough, because this function returns early if we didn't lock the clustered index (just the secondary index). It does so because it doesn't know how to establish if the row was modified by current transaction or not, which it usually does by looking at the clustered index record's TRX_ID field. My current idea is to somehow use the hint from the caller, who should know if the row was or wasn't modified, but I need to think it through and test it.
[27 Jul 2021 11:47]
MySQL Verification Team
Hi Jakub, Thank you for your contribution. We do not see how could this be achieved without locking the clustered index in InnoDB. The only possibility is to have a TIMESTAMP that changes on any write. But, we can not make a special algorithm for some small part of cases.
[27 Jul 2021 13:00]
Jakub Lopuszanski
Posted by developer:
I'm sorry, the MTR I've shown above is wrong: while it looks like it sets the GLOBAL TRANSACTION ISOLATION LEVEL to READ COMMITTED, it doesn't apply to the current connection itself, which still uses REPEATABLE READ.
A (hopefully) proper version of the test is:
create table ts(a int primary key, b int, c int, d int, index(b,c));
insert into ts
values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8
,8,8,8),(9,9,9,9);
ANALYZE TABLE ts;
--let $i=0
while($i<2){
SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
explain select b,c,d from ts where b>=5 and b<8 and c=7 for update;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
select b,c,d from ts where b>=5 and b<8 and c=7 for update;
select INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
commit;
SET @@optimizer_switch='index_condition_pushdown=off';
--inc $i
}
DROP TABLE ts;
[12 Jul 2022 1:12]
Jon Stephens
Documented fix as follows in the MySQL 8.0.28 as follows:
With index_condition_pushdown=ON and
transaction_isolation='READ-COMMITTED', locks on secondary
indexes were not released until the transaction was committed or
rolled back, even though the secondary indexes were unmatched.
Closed.
Description: When index_condition_pushdown=ON and transaction_isolation = 'READ-COMMITTED', record locks on second indexes will not be unlocked until the transaction is committed or rollbacked, even though these second indexes are unmatched. How to repeat: mysql> show variables like '%isolation%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.01 sec) 1. create table ts(a int primary key, b int, c int, d int, index(b,c)); 2. insert into ts values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6),(7,7,7,7),(8,8,8,8),(9,9,9,9); 3. set index_condition_pushdown=OFF; 4. begin; 5. mysql> select b,c,d from ts where b>=5 and b<8 and c=7 for update; +------+------+------+ | b | c | d | +------+------+------+ | 7 | 7 | 7 | +------+------+------+ 1 row in set (0.00 sec) 6. mysql> select ENGINE,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +--------+------------+-----------+---------------+-------------+-----------+ | ENGINE | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+------------+-----------+---------------+-------------+-----------+ | INNODB | NULL | TABLE | IX | GRANTED | NULL | | INNODB | b | RECORD | X,REC_NOT_GAP | GRANTED | 7, 7, 7 | | INNODB | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 7 | +--------+------------+-----------+---------------+-------------+-----------+ 3 rows in set (0.00 sec) 7. commit; 8. set index_condition_pushdown=ON; 9. mysql> explain select b,c,d from ts where b>=5 and b<8 and c=7 for update; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | ts | NULL | range | b | b | 10 | NULL | 2 | 11.11 | Using index condition | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) 10. begin; 11. mysql> select b,c,d from ts where b>=5 and b<8 and c=7 for update; +------+------+------+ | b | c | d | +------+------+------+ | 7 | 7 | 7 | +------+------+------+ 1 row in set (0.00 sec) 12. mysql> select ENGINE,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +--------+------------+-----------+---------------+-------------+-----------+ | ENGINE | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+------------+-----------+---------------+-------------+-----------+ | INNODB | NULL | TABLE | IX | GRANTED | NULL | | INNODB | b | RECORD | X,REC_NOT_GAP | GRANTED | 6, 6, 6 | | INNODB | b | RECORD | X,REC_NOT_GAP | GRANTED | 7, 7, 7 | | INNODB | b | RECORD | X,REC_NOT_GAP | GRANTED | 8, 8, 8 | | INNODB | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 7 | +--------+------------+-----------+---------------+-------------+-----------+ 5 rows in set (0.00 sec) Unmatched index records (6,6,6),(8,8,8) are still locked. Suggested fix: in 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; } The condition (did_semi_consistent_read) maybe change to (trx->allow_semi_consistent() && prebuilt->select_lock_type != LOCK_NONE) .