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
