Subject Re: [Firebird-general] Re: Snapshot Isolation in A Critique of ANSI SQL Isolation Levels
Author Ann W. Harrison
On 10/19/2010 4:20 PM, unordained wrote:

> For deferred constraints, sure. But they're not all deferred.

Sorry, I was stuck in deferred constraints. For immediate constraints,

> after any change:
> if (deferred) then mark change as suspicious;
> else if (not okay locally) then reject change;
> else if (not okay globally) then mark change as suspicious;
> else accept change;

There's no point in marking a "not OK globally" as suspicious
for immediate constraints - there's no place where you can recheck.
If it's not OK globally or locally, reject the changes. Which is
what Firebird does for referential and unique constraints.

> I don't think you can write works-anywhere rules for which changes "help" vs.
> "hurt" when deciding if the global state would work or not.

The global view is the only one that can decide whether the database
will be correct after adding your change plus the original state, plus
other uncommitted changes. I do agree that sometimes it's faster to
delay all checking of deferred constraints, but think that there are
more times when it is faster to check during the operations and recheck
before commit only those that changes that failed before. And I think
an intelligent compiler could decide which checks to do which way.

> The rollback wouldn't fail -- when committed, I'm saying you only check locally
> and with other committed transactions, but not uncommitted ones. You can't
> commit on the assumption that an uncommitted transaction that made
> "cooperative" changes will commit later. Because yes, that would cause problems
> when that transaction has to rollback.
> Transaction A inserts 5 rows (okay everywhere)
> Transaction B inserts 5 rows (deferred constraint gets marked as suspicious,
> because of A)
> Transaction B commits (okay, because A hasn't committed yet, and B's changes
> are okay stand-alone and within the global committed context)

No. In that case the global check says that B needs to wait for A to
commit before it can commit.

> Transaction A commits (not okay -- it didn't get a suspicious mark, but it
> should recheck anyway and discover B's incompatible changes that are now part
> of the global committed context)

B had the mark, so even though it tried to commit first, it must wait
for A and fail if A commits.
> I guess you're aiming instead for the on-commit checking to see uncommitted
> stuff too? B would fail because of A's uncommitted changes, and my "globally"
> vs. "remotely" difference goes away?