Hello,

I'm trying to understand interactions between transactions with
different isolation levels.

My environment is Firebird Super Server 2.5 (v. 2.5.2) on Linux
Ubuntu 14.04

I'm testing by opening two consoles and running isql-fb on both consoles.

On the first one I start a SNAPSHOT transaction ( lets call it
transaction A ).

Afterwards, on the second console, I start another transaction with a
READ COMMITTED RECORD_VERSION isolation level ( lets call it transaction
B ).

In transaction B, I update a record X and commit the transaction.

In the same second console I start a third transaction, also READ
COMMITTED RECORD_VERSION ( lets call it transaction C ).

In transaction C, I update record X, but neither commit it nor roll it
back ( transaction C remains active ).

On the first console, in transaction A, I update record X and it
obviously waits for a commit or a rollback of transaction C.

Back on the second console, in transaction C, I roll it back.

Not surprisingly, on the first console, transaction A ends waiting with
a collision error, because although transaction C rolled back,
transaction B, that had started after transaction A, had made an update
and committed it. As the isolation level of transaction A was SNAPSHOT,
it finds a collision and so an error is risen.

So we have the following situation:

If transaction C ends rolling back (as in the example), transaction A
raises an error because of the collision with the previous transaction
that touched and committed the record ( transaction B ).

If transaction C ends committing, transaction A also raises an error
because of the collision with transaction C.

Whichever way transaction C ends ( committing or rolling back ), the
waiting update in transaction A is aborted with an error message because
of a collision.

The question is which was the point of waiting in transaction A for the
end of transaction C ? Why didn't it raise an error in the same moment I
tried to do an update in transaction A ? Couldn't it foresee that the
record was previously updated by a committed transaction B, which
started after the SNAPSHOT transaction A, and so whichever the outcome
of C were there would be a collision ?

Note also that if in the example above, we wouldn't have started
transaction C, transaction A would raise an error as soon as it tried to
update the record that was updated and committed by B ( I tested it ).

Thanks for any answer.

Aldo Caruso