Hi!

In my attempts to convert my old Firebird 1.5 application to Firebird
2.1 I ran into something which I take to be a transaction-isolation
problem, though I can't figure it out.

I have a table EXAMPLE with a number of fields, of which there three are
relevant to my issue:
- CREATED_BY (integer) = ID of user who created the record
- MODIFIED_BY (integer) = ID of user who last modified the record
- MODIFIED_ON (timestamp) = timestamp of the last modification

There is also a BEFORE UPDATE trigger which maintains these fields and
makes sure two users don't modify the same record at the same time. It
works like this:
1. When a process wants to update a record, it first reads its
MODIFIED_BY a MODIFIED_ON.
2. Then it performs everything it needs to do to generate an update script
3. Within that update script, it sets CREATED_BY to current user's ID
and MODIFIED_BY and MODIFIED_ON to the values read in step 1
4. The BEFORE UPDATE trigger first compares NEW.MODIFIED_BY to
OLD.MODIFIED_BY and NEW.MODIFIED_ON to OLD.MODIFIED_ON, and either fires
an exception (if they differ) or sets NEW.MODIFIED_BY to NEW.CREATED_BY,
NEW.MODIFIED_ON to 'now' AND NEW.CREATED_BY to OLD.CREATED_BY
5. End result is that CREATED_BY remains unchanged and MODIFIED_BY and
MODIFIED_ON are updated to current user and timestamp

My problem is that with Firebird 2.1 (but not Firebird 1.5), this
sequence of events fails:

1. START TRANSACTION
2. SELECT modified_by, modified_on FROM example WHERE key=<key> INTO
<mod.by>, <mod.on>
-- <mod.by> and <mod.on> contain some legacy data
3. UPDATE example SET created_by=<current_user>, modified_by=<mod.by>,
modified_on=<mod.on> ... WHERE key=<key>
4. BEFORE UPDATE trigger fires
5. SELECT modified_by, modified_on FROM example WHERE key=<key> INTO
<mod.by>, <mod.on>
-- at this moment, <mod.by> contains <current user> and <mod.on>
contains timestamp of step 4, as expected
6. UPDATE example SET created_by=<current_user>, modified_by=<mod.by>,
modified_on=<mod.on> ... WHERE key=<key>
7. BEFORE UPDATE trigger fires
8. COMMIT

In step 7, an exception is generated, meaning that
NEW.MODIFIED_BY<>OLD.MODIFIED_BY or NEW.MODIFIED_ON<>OLD.MODIFIED_ON. I
take it to mean that the UPDATE in step 6 is isolated from the
transaction of the UPDATE in step 3 and SELECT in step 5 (both of which
happen in the same transaction).

Curiously enough, even if I insert a COMMIT/START TRANSACTION between
steps 4 and 5, I _STILL_ get an exception in step 7.

Any ideas of where I should look for the cause of the problem?

Thanks,

Pepak