Bug #18184 SELECT ... FOR UPDATE does not work..
Submitted: 13 Mar 2006 9:40 Modified: 17 Aug 2006 8:44
Reporter: Johan Andersson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.0.19 (but can be all?!) OS:Linux (Linux)
Assigned to: Martin Skold CPU Architecture:Any

[13 Mar 2006 9:40] Johan Andersson 
Description:
SELECT ... FOR UPDATE does not lock rows as expected.

How to repeat:
2 mysql servers (A and B) connected to Cluster:

create table t2 (a integer, b integer, primary key(a)) engine=ndb;
set autocommit=0 

A:
set autocommit=0 
begin;
select * from t2 where a=1 for update;

B:
set autocommit=0 
begin;
select * from t2 where a=1 for update;

Result: No rows are locked. B should wait for A to commit/abort

--------------

Also tried:

A:
set autocommit=0 
begin;
select * from t2 force index(PRIMARY) where a=1 for update;

B:
set autocommit=0 
begin;
select * from t2 force index(PRIMARY) where a=1 for update;

Result: No rows are locked. B should wait for A to commit/abort

Suggested fix:
-
[13 Mar 2006 14:33] Johan Andersson 
A new case then:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  `c` int(11) default NULL,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1,55,2),(1,88,2),(1,44,2),(1,111,2),(1,22,2),(2,44,1),(2,11,1),(2,22,1),(2,33,1),(1,66,2),(1,33,2),(1,77,2),(1,11,2),(2,55,1),(1,222,2);

MySQL server A:
begin; select * from t1 where a=1  for update;  

MySQL server B (or another connection):
begin; select * from t1 where a=1  for update;  

Does not lock the matching tuples.

MySQL server A:
begin; select * from t1 where a=1  and b=11 for update;  

MySQL server B (or another connection):
begin; select * from t1 where a=1 and b=11 for update;  

Does lock the row. So PK access works.
But locking based on part of PK does not work. Is this a bug, or is just due to the fact how locking and scans work in cluster?
[13 Mar 2006 15:01] Jonas Oreland 
Selecting with part of primary key, is scan.
"Select for update" together with scan works poorly (in 4.1, 5.0 & 5.1)
[8 Jun 2006 14:16] 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/7399
[12 Jun 2006 7:41] 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/7514
[12 Jun 2006 8:26] 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/7517
[12 Jun 2006 9:16] Martin Skold 
Another bug was opened:
Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans
[12 Jun 2006 12:27] 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/7534
[13 Jun 2006 23:37] Jon Stephens 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.21/5.0.23/5.1.12 changelogs. Closed.
[8 Aug 2006 8:19] Hartmut Holzgraefe 
This is still not fixed in 5.0.24 for the "only one row in the table" case,
or maybe in general for the "enough rows match the WHERE condition so that
a table scan makes more sense than an index scan" case
[8 Aug 2006 10:22] 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/10154

ChangeSet@1.2499, 2006-08-08 12:22:23+02:00, mskold@mysql.com +3 -0
  bug #18184  SELECT ... FOR UPDATE does not work..: Added lockTuple call in close_scan
[9 Aug 2006 7:28] 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/10185

ChangeSet@1.2248, 2006-08-09 09:28:25+02:00, mskold@mysql.com +1 -0
  bug #18184  SELECT ... FOR UPDATE does not work..: Updated results
[9 Aug 2006 14:12] 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/10206

ChangeSet@1.2247, 2006-08-09 16:12:24+02:00, mskold@mysql.com +1 -0
  bug #18184  SELECT ... FOR UPDATE does not work..: Updated result file
[14 Aug 2006 15:14] 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/10371

ChangeSet@1.2275, 2006-08-14 17:14:31+02:00, mskold@mysql.com +1 -0
  bug #18184  SELECT ... FOR UPDATE does not work..: Updated result file
[15 Aug 2006 13:51] 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/10479

ChangeSet@1.2281, 2006-08-15 15:49:31+02:00, mskold@mysql.com +1 -0
  bug #18184  SELECT ... FOR UPDATE does not work..: Updated test result file
[16 Aug 2006 7:39] Martin Skold 
Pushed to 4.1.22, 5.0.25, 5.1.12
[17 Aug 2006 8:44] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.
[19 Mar 2009 11:31] Pavel Strashkin 
I have latest MySQL release (5.0.77) on FreeBSD and seems like a bug was not fixed (or i have problem with understanding how SELECT ... FOR UPDATE work).

What we have:
- empty table "test"
- engine "innodb"

1. Create InnoDB table "test":
create table `test` (a integer, b integer, primary key(a)) engine=innodb;

2. Test SELECT ... FOR UPDATE from 2 sessions for empty table `test`:
Session #1:
start transaction;
select * from `test` where a=1 for update;

Session #2;
start transaction;
select * from `test` where a=1 for update;

Result: there is no lock on Session #2
Question: Is SELECT ... FOR UPDATE work with empty tables?

P.S. if on Session #2 execute INSERT INTO `test` VALUES(1,1) then lock work.