> On Feb 28, 2017, at 3:07 PM, Aldo Caruso wrote:
>
> I'm trying to understand interactions between transactions with
> different isolation levels.

The problem is not mixed isolation levels. You would get the same behavior if all transactions were Snapshot

The scenario (described below in detail) is

Start transaction A

Start transaction B

B updates some record

B commits

Start transaction C

C updates the same record

A attempts to update that record and waits for C to end

Whatever C dies A will fail because of a conflict with C if C commits or a conflict with B if C rolls back. Why doesn't A just fail immediately?

The answer is that Firebird checks only the most recent record version for conflicts. Checking the next back version would avoid having A wait in this case but would require more reading in general.

Good luck,

Ann

>
> 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
>
>
>
>
> ------------------------------------
> Posted by: Aldo Caruso <aldo.caruso@...>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>