MySQL Bugs: #18184: SELECT ... FOR UPDATE does not work..

Bug #18184	SELECT ... FOR UPDATE does not work..
Submitted:	13 Mar 2006 10:40	Modified:	17 Aug 2006 10:44
Reporter:	Johan Andersson
Status:	Closed
Category:	Server: Cluster	Severity:	S3 (Non-critical)
Version:	5.0.19 (but can be all?!)	OS:	Linux (Linux)
Assigned to:	Martin Skold	Target Version:

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:
-

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?

Selecting with part of primary key, is scan.
"Select for update" together with scan works poorly (in 4.1, 5.0 & 5.1)

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

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

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

Another bug was opened:
Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table scans

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

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.

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

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

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

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

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

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

Pushed to 4.1.22, 5.0.25, 5.1.12

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.