Description:
When impossible WHERE noticed after reading const tables in SELECT ... FOR UPDATE queries, locks of examined rows are not released immediately.
The level of isolation is READ-COMMITTED.
How to repeat:
[ Preparation ]
======
create table t1 (
id int,
val int,
unique key ukey (id)
);
insert into t1 values (1, 0), (2, 99), (3, 99), (4, 99);
======
[ Client A ]
======
-- transaction A, impossible WHERE
set @@transaction_isolation='READ-COMMITTED';
begin;
select * from t1 where id = 1 and val = 99 for update;
======
[ Client B ]
======
-- transaction B, timeout
set @@transaction_isolation='READ-COMMITTED';
begin;
select * from t1 where id = 1 and val = 0 for update;
======
If we alter `unique key ukey (id)` to `key ukey (id)`, it temporarily fixes the problem, no timeout occurs.
Suggested fix:
mysql> explain select * from t1 where id = 1 and val = 99 for update;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
======
JOIN::optimize()
{
...
if (make_join_query_block(this, where_cond)) {
if (thd->is_error()) return true;
zero_result_cause = "Impossible WHERE noticed after reading const tables";
create_access_paths_for_zero_rows();
goto setup_subq_exit;
}
...
}
======
In the case above, the following code is never executed. So, the row locks remains.
======
FilterIterator::Read()
{
...
if (!matched) {
m_source->UnlockRow();
continue;
}
...
}
======
Suggestion:
Unlock the row in `optimize()` for this case, because the `execute()` method is skipped.
Description: When impossible WHERE noticed after reading const tables in SELECT ... FOR UPDATE queries, locks of examined rows are not released immediately. The level of isolation is READ-COMMITTED. How to repeat: [ Preparation ] ====== create table t1 ( id int, val int, unique key ukey (id) ); insert into t1 values (1, 0), (2, 99), (3, 99), (4, 99); ====== [ Client A ] ====== -- transaction A, impossible WHERE set @@transaction_isolation='READ-COMMITTED'; begin; select * from t1 where id = 1 and val = 99 for update; ====== [ Client B ] ====== -- transaction B, timeout set @@transaction_isolation='READ-COMMITTED'; begin; select * from t1 where id = 1 and val = 0 for update; ====== If we alter `unique key ukey (id)` to `key ukey (id)`, it temporarily fixes the problem, no timeout occurs. Suggested fix: mysql> explain select * from t1 where id = 1 and val = 99 for update; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set, 1 warning (0.01 sec) ====== JOIN::optimize() { ... if (make_join_query_block(this, where_cond)) { if (thd->is_error()) return true; zero_result_cause = "Impossible WHERE noticed after reading const tables"; create_access_paths_for_zero_rows(); goto setup_subq_exit; } ... } ====== In the case above, the following code is never executed. So, the row locks remains. ====== FilterIterator::Read() { ... if (!matched) { m_source->UnlockRow(); continue; } ... } ====== Suggestion: Unlock the row in `optimize()` for this case, because the `execute()` method is skipped.