All,

As you know, our update and delete operations are cursor based, i.e.

DELETE FROM T WHERE <condition>

is executed similarly to:

FOR SELECT * FROM T WHERE <condition> AS CURSOR C DO
DELETE FROM T WHERE CURRENT OF C;

There's some time interval between fetch and update/delete when this row
could be modified (here and below: either updated or deleted) by someone.
When an actual update/delete happens, VIO checks the concurrent row
modifications and report an update conflict, if any. So far so good. But
there are cases when this record can be modified by our own transaction as
well.

First one is a SORT plan. It forces the rows to be fetched and sorted
externally. Then they're re-fetched from the sort buffer. After N rows are
sorted but before the 1st one is fetched from the buffer, we may modify the
original rows. This is not a problem for reads, because we consider the
buffer data being actual. But this is a problem for writes, as VIO receives
not actual (read: wrong) RPB and cannot report any conflict, because all
modifications are done in the same transaction. This causes to the
update_in_place() call which corrupts the row itself and/or the transaction
undo log. To avoid this issue, a RPB_s_refetch stream flag has been added
long time ago to force a RPB refetch before VIO. This flag is set in rse.cpp
for every row going to the sort buffer and every row to be modified is
refetched in advance. What is expected in the case of the modified RPB?

1) there were no modifications - everything is fine
2) there was an update and we update/delete this record - okay, but a "lost
update" phenomena happens
3) there was a delete and we update/delete this record - "no record to
fetch" error is thrown

Everything is fine here, IMO.

But there are other cases that cause the described corruption issues. All
modifications in pre-triggers are dangerous (NEW context assignments
excluded). Alex has found some cases when it happens and solved it in v1.5
with the RPB chain tracking logic, causing RPB_s_refetch to be set if our
row (subject of VIO operation) is modified in pre-triggers. Recently I've
found some other related cases and fixed them in HEAD. Example: a row is
being updated by the client, but it's deleted in the pre-trigger => database
corruption happens.

Another example is PSQL:

FOR SELECT * FROM T WHERE <condition> AS CURSOR C DO
BEGIN
DELETE FROM T WHERE CURRENT OF C;
UPDATE T SET <assignments> WHERE CURRENT OF C;
END

Result is the same corruption issue. Obviously, this could be emulated via
the isc_dsql_set_cursor() and isc_dsql_execute() calls from the client side.
AFAIU, this was possible via BLR since 1985.

SUSPEND issued after the fetch but before the modification also opens this
can of worm. Perhaps there are other possible reasons for this bug to
happen.

The question is how to avoid all these cases in common. A dumb solution is
to set RPB_s_refresh always after we have successfully fetched the row for
modification. But this is damn ineffective, causing 5-40% of performance
degradation, depending on the update logic and number of backversions. I
hope we could use the existing RPB chain tracking code. In this case the
fetched RPB must be pushed on stack right after RSE_get_record()
successfully returns and popped after the blr_for compound statement is
executed. Since it's unknown at the RSB level whether we're going to modify
the fetched rows, it looks that some new BLR code and appropriate RSB flag
is required (like Nickolay did for the WITH LOCK feature).

Anyone has better ideas?


Dmitry