MySQL Bugs: #4595: UPDATE multiple rows with ordered index fails

Bug #4595	UPDATE multiple rows with ordered index fails
Submitted:	18 Jul 2004 9:10	Modified:	10 Aug 2004 16:18
Reporter:	Johan Andersson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	mysql 4.1 bk src	OS:	Linux (RH9)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
I did a test creating a table (see "how to repeat") and created an ordered index on column 'b'.
Tried to update the indexed column 'b'
The result was not what I expected.

How to repeat:

mysql> create table t1(a integer not null primary key, b integer not null) type=ndb;
Query OK, 0 rows affected, 1 warning (0.68 sec)

populate with data:
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 10 |
| 2 | 10 |
| 4 | 10 |
| 5 | 10 |
| 3 | 10 |
| 1 | 10 |
+---+----+
8 rows in set (0.00 sec)

mysql> update t1 set b=b+1 where b=10;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 11 |
| 2 | 11 |
| 4 | 11 |
| 5 | 11 |
| 3 | 11 |
| 1 | 11 |
+---+----+
8 rows in set (0.00 sec)

mysql> create index t1_b on t1(b);
Query OK, 8 rows affected (1.98 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> update t1 set b=b+1 where b=11;
Query OK, 8 rows affected (0.02 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 11 |
| 2 | 11 |
| 4 | 11 |
| 5 | 11 |
| 3 | 11 |
| 1 | 13 |
+---+----+
8 rows in set (0.00 sec)

This is really wrong.....

The problem seems to be that select for update over an
ordered index works quite different from a full table scan.
Here a scan is first made and then a pk_read is done with
a consequennt update. With a full table scan an update
is done after each next_result.
The bug is that ndbcluster::extra(HA_EXTRA_RETRIEVE_ALL_COLS)
is called before the ordered index scan, but this is ignored in the pk_read.
The fix is to change pk_read to retrieve all columns, including the primary
key.

This solution will however not work well until we fix
range scan locks. This is a different problem,
but the example might not give transaction consistent
results (since the ordered index scan does not keep
locks). In a full table scan we do take over of read locks.

Great to see that a patch is pending. Please check the following (I am going on vacation)  and that expected result is according to  read commit consistency (using two mysql clients):

CLIENT A:
mysql> create table t1(a integer not null primary key, b integer not null)
type=ndb;
Query OK, 0 rows affected, 1 warning (0.68 sec)

populate with data:
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 10 |
| 2 | 10 |
| 4 | 10 |
| 5 | 10 |
| 3 | 10 |
| 1 | 10 |
+---+----+
8 rows in set (0.00 sec)

CLIENT A:

mysql> create index t1_b_idx on t1(b);

mysql> begin;
mysql> update t1 set b=b+1 where b=10;

CLIENT B:
mysql> select * from t1;

CLIENT A:
mysql> commit;

Please check the result and if that complies with read_committed

Last test also verified.