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: | |
| 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 |
[18 Jul 2004 9:10]
Johan Andersson
[19 Jul 2004 17:08]
Martin Skold
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.
[19 Jul 2004 17:28]
Martin Skold
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.
[22 Jul 2004 23:22]
Johan Andersson
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
[10 Aug 2004 16:18]
Martin Skold
Last test also verified.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.