Bug #10868 select ... for update broken for max() and perhaps other aggregates
Submitted: 25 May 2005 21:06 Modified: 8 Aug 2007 9:43
Reporter: John Muehlhausen Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:4.1.12 OS:Any (OS 10.3.9)
Assigned to: CPU Architecture:Any

[25 May 2005 21:06] John Muehlhausen
mysqladmin  Ver 8.41 Distrib 4.1.12, for apple-darwin7.9.0 on powerpc

select ... for update doesn't perform appropriate locking when using the aggregate function max().  This may be due to the fact that max() is stored somewhere other than the rows so that row locks do not need to be obtained in order to report the value, which is as it should be for efficiency, however the user expects that no new max is allowed to be created during the transaction.  This is the correct behavior on InnoDB.

How to repeat:
Create a table with the NDB engine and put something in it:

use test
create table c (var integer) type=NDB;
insert into c(var) values (1);

Start a transaction that is selecting the max of var "for update:"

start transaction;
select max(var) from c for update;

From another connection, issue an update to a new max value:

start transaction;
update c set var=2;

This is allowed to complete, which violates the "for update" from the first connection.  InnoDB does the right thing by blocking the update until the other transaction completes.

Suggested fix:

An update or insert should block if they are about to create a new max() (or min(), etc) that is locked by another transaction "select ... for update."
[29 May 2005 20:07] Martin Skold
This either requires a table lock or a consistent read for
a non-blocking implementation of highest transaction
isolation level, serializable. Both are feature requests and
will be done later.
[4 Oct 2006 17:17] John Muehlhausen
Is there any update on this issue?
[7 Oct 2006 9:47] Stewart Smith
SELECT ... FOR UPDATE for NDB was fixed in a recent release - check the release notes in the manual.
[8 Aug 2007 9:43] Hartmut Holzgraefe
duplicate, original bug got fixed