Bug #54094 SELECT FOR UPDATE does not release locks for rows do not qualify in the result
Submitted: 31 May 2010 4:56 Modified: 1 Jun 2010 3:20
Reporter: Jingqi Xu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.42 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2010 4:56] Jingqi Xu
Description:
SELECT FOR UPDATE does not release locks for rows that do not qualify for inclusion in the result set.

How to repeat:
5.1.42-log MySQL Community Server (GPL)
InnoDB Plugin 1.0.6

select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

create table test(id int primary key, name varchar(10)) ENGINE=InnoDB;
insert into test values(1, '1'), (2, '2'), (3, '3');

Session A:
start transaction;
select * from test where id in (1, 2) and name = '2' for update;

Session B:
start transaction;
update test set name = 'b' where id = 1; # Blocked

According to the document, the update statement should not be blocked.
[31 May 2010 7:49] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have no index on name field, so "A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows. " and "For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution. " See http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html for details
[31 May 2010 7:53] Marko Mäkelä
This looks like a duplicate feature request, for Bug #3300. The semi-consistent reads are enabled if innodb_locks_unsafe_for_binlog is set or the transaction isolation level is READ UNCOMMITTED or READ COMMITTED.
[31 May 2010 8:47] Jingqi Xu
Sorry for the inconvenience.
I have read the documents many times. Probably I did not grasp the point.
In my point of view, it does not matter whether there is an index on name field because the execution plan below shows that MySQL uses the primary key to scan rows. If so, two rows are scanned by InnoDB and one row is filtered out by MySQL server. So "A SELECT ... FOR UPDATE does not need to read the latest available
data and set exclusive lock on row which id is 1;

mysql> explain select * from test where id in (1, 2) and name = '2';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
[1 Jun 2010 3:20] Jingqi Xu
According to http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html,
1 For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set.
2 UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. 

But I don't see any difference with respect to lock releasing。