> 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 > > >
