---------- Original Message -----------
From: "Ann W. Harrison" <aharrison@...>

> > So in that case, every check against uncommitted changes has to check for

all

> > combinations of "self + committed" + none/each/some/all "uncommitted", with

short-

> > circuit eval?
>
> Don't think about the checks as "per transaction" - transactions write
> their changes directly into data pages, so the number of checks is not
> relative to the number of concurrent transactions, but the amount of
> data. For your example of "no more than 25 items per invoice", the
> check consists of counting the items in the parent invoice - committed
> and uncommitted and the cost will never go over 26 row reads because
> you don't care whether the constraint is violate by a little or a lot.
>
> But yes, you'd need to read those 26 rows each time you

The "combinations" thing comes from the three-transaction example, where two of
them "cooperate" to make it seem like there's no problem from the third's
perspective:

A: +5 (okay), B -5 (okay), C +5 (okay?)

If C goes to add 5 rows and only considers its change plus A and B (together)
it won't see the problem. If it considers itself plus B only, it's fine. If it
ignores everyone else, it's also fine (checking again on-commit would solve
that.) But if it checks on itself plus A only, it'll see the problem, and fail
early, which is what you're wanting to preserve. (This is assuming 18 at
beginning, limit of 25, and none of these transactions have even tried to
commit yet.)

But I'm again assuming you can't know ahead of time which changes help/hurt, so
I don't assume you can ignore B's deletes and only count A's inserts. This
example maybe makes it too easy to categorize changes as helping/hurting; let's
assume the query were written as "no more than 25 on-sale widgets" and all the
transactions were performing updates on the "on-sale" column (30 total rows at
beginning, 18 marked as on-sale), without inserting/deleting or even ever
updating the same rows (avoiding direct conflicts), but still changing the
total count of on-sale widgets. I'm not seeing a generalized solution that
would know how to ignore some changes but include others. I'm instead saying
"any of these other uncommitted transactions may or may not commit, in any
order, and I can't be in conflict with any of them when they do or don't, and
I'm the only one who knows there could be a problem". Thus I propose checking
all combinations.

> People who call on agreed-on-ahead-of-time ordering of transactions
> tend to be academic ... how many real-world problems have you seen
> that can be reduced to pre-ordered transactions?

None. And that thing they do where they re-order transactions on the fly to
"make them work"? Not so much that either.

(BTW, between this and your emails yesterday comparing/contrasting the MGA
approaches in various databases ... thank you!)

-Philip