When performing an UPDATE with selection criteria, our storage engine is
encountering an infinite loop condition. Specifically, when an update
modifies a record such that the record is now sorted after its initial
position in the index, our storage engine repeatedly finds the new value
and updates that record.
For example, the following statements will cause an infinite loop, assuming
"test" is read through index "i".
CREATE TABLE test (i int, index(i));
INSERT INTO test VALUES (4),(5);
UPDATE test SET i = i + 2;
This occurs because our storage engine's index read cursor tracks position
relative to the key value, not the actual row sequence/row ID. When rows
are updated, they are repositioned in the index according to their new
value.
The following diagram shows the cycle of read/update operations along with
the position of the read cursor (>) and the updated row (*). Keep in mind
that throughout this sequence the two rows keep their original row IDs;
these are the same two rows that are being updated over and over.
R U R U R U ...
------------------------------
>
4 >4 >
5 5 5 >5 >
*6 6 6 >6 >
*7 7 7
*8
We need a way to detect that this is happening and prevent it. We could
keep a history of updated row IDs and stop when an already modified row was
encountered, but it is not hard to imagine a pathological case that would
make this a bad idea.
It would be better if we could identify the potential for this behavior
before MySQL began the operation and restrict the use of any index that
covers a field that will be updated. Is there any way to do this well?
Thank you,
Tim Clark
