Bug #20390 | SELECT FOR UPDATE does not release locks of untouched rows in full table scans | ||
---|---|---|---|
Submitted: | 12 Jun 2006 8:40 | Modified: | 29 Nov 2007 17:46 |
Reporter: | Martin Skold | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.0, 5.1 | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | bfsm_2006_12_07, rt_q1_2007 |
[12 Jun 2006 8:40]
Martin Skold
[12 Jun 2006 8:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7518
[12 Jun 2006 9:11]
Martin Skold
Incorrect change of status, just added reference to this bug when disabling another test for Bug #18184 SELECT ... FOR UPDATE does not work..
[12 Jun 2006 13:52]
Hakan Küçükyılmaz
I also experienced this with latest 5.1-bk, change set: 1.2176.5.1
[12 Jun 2006 16:48]
Hakan Küçükyılmaz
Reproduction on 5.1: -- connection1: use test; drop table if exists t1; set autocommit=0; set tx_isolation = 'READ-UNCOMMITTED'; create table t1(a integer not null primary key, b varchar(5), c integer, key(c)) engine innodb; insert into t1 values(1, 'one', 1); insert into t1 values(2, 'two', 2); insert into t1 values(3, 'three', 3); commit; begin; select * from t1 where b = 'one' or b = 'three' for update; -- connection2: use test; set autocommit=0; set tx_isolation = 'READ-UNCOMMITTED'; begin; select * from t1 where a = 2 for update; -- connection2 should hang now until connection1 commits or gets timeout -- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[7 Dec 2006 22:35]
Konstantin Osipov
Martin, we can perhaps find and fix a regression for this obvious test case, but this code has never worked properly. E.g. here's what a comment for unlock_row() in 4.1 says: This row failed selection, release lock on it. XXX: There is no table handler in MySQL which makes use of this call. It's kept from Gemini times. A lot of new code was added recently (i. e. subselects) without having it in mind. */ info->file->unlock_row();
[27 Dec 2006 15:42]
Dmitry Lenev
Hello, Martin, Hakan! Please note that in 4.1 InnoDB does not implement handler::unlock_row() method and therefore statements that perform full table scan won't unlock rows which don't match where clause (for documentation see http://dev.mysql.com/doc/refman/4.1/en/innodb-locks-set.html). In 5.0 we have non-empty ha_innobase::unlock_row() implementation, but it still does nothing unless --innodb_locks_unsafe_for_binlog is enabled (which is disabled by default). This means that test cases provided are not supposed to work for InnoDB tables in 4.1 and 5.0 (unless --innodb_locks_unsafe_for_binlog is enabled). AFAIU the same is true for 5.1. But you are right there is regression between 4.1 and 5.0 (demonstrated by the same test case but for NDB tables) which of course should be fixed.
[27 Dec 2006 20:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/17420 ChangeSet@1.2321, 2006-12-27 23:21:31+03:00, dlenev@mockturtle.local +3 -0 Proposed fix for bug#20390 "SELECT FOR UPDATE does not release locks of untouched rows in full table scans". SELECT ... FOR UPDATE/LOCK IN SHARE MODE statements as well as UPDATE/DELETE statements which were executed using full table scan were not releasing locks on rows which didn't satisfy WHERE condition. This bug surfaced in 5.0 and affected NDB tables. (InnoDB tables intentionally don't support such unlocking in default mode). This problem occured because code implementing join didn't call handler::unlock_row() for rows which didn't satisfy part of condition attached to this particular table/level of nested loop. So we solve the problem adding this call. Note that we already had this call in place in 4.1 but it was lost (actually misplaced) when we have introduced nested joins. Also note that additional QA should be requested once this patch is pushed as interaction between handler::unlock_row() and many recent MySQL features such as subqueries, unions, views is not tested enough. QQ marks question for reviewers.
[15 Jan 2007 9:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/18105 ChangeSet@1.2320, 2007-01-15 12:32:38+03:00, dlenev@mockturtle.local +3 -0 Fix for bug#20390 "SELECT FOR UPDATE does not release locks of untouched rows in full table scans". SELECT ... FOR UPDATE/LOCK IN SHARE MODE statements as well as UPDATE/DELETE statements which were executed using full table scan were not releasing locks on rows which didn't satisfy WHERE condition. This bug surfaced in 5.0 and affected NDB tables. (InnoDB tables intentionally don't support such unlocking in default mode). This problem occured because code implementing join didn't call handler::unlock_row() for rows which didn't satisfy part of condition attached to this particular table/level of nested loop. So we solve the problem adding this call. Note that we already had this call in place in 4.1 but it was lost (actually not quite correctly placed) when we have introduced nested joins. Also note that additional QA should be requested once this patch is pushed as interaction between handler::unlock_row() and many recent MySQL features such as subqueries, unions, views is not tested enough.
[17 Jan 2007 17:57]
Konstantin Osipov
Pushed into 5.0.36, 5.1.15
[23 Jan 2007 19:36]
Paul DuBois
Noted in 5.0.36, 5.1.15 changelogs. Moving report to QA testing since QA was requested.
[9 Oct 2007 17:38]
Hartmut Holzgraefe
Reopening as documentation issue as i can't find any reference to this limitations in the manual anywhere (neither in http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html nor within the cluster limitation pages)
[29 Nov 2007 17:46]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Added to the innodb-locks-set page for 5.0 and up: For SELECT ... FOR UPDATE, 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 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 to the rows in the original table is lost and those rows are not unlocked until later. (Note that in a case such as this, a FOR UPDATE clause is unnecessary anyway because the result set is non-updatable.)