> 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.car...@argencasas.com>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
