On 10/19/2010 2:45 PM, unordained wrote:

>> The correct implementation ... is to validate
>> all constraints from the omniscient view of the server, not just
>> uniqueness and referential integrity.

> ... which would allow constraints such as "an invoice can have at most 25 items"
> to be enforced even when two transactions simultaneously insert 5 items to an 18-
> item invoice, without resorting to locking the invoice record. From neither
> perspective is it wrong, yet it is from the global point of view.

Right - there's no way to enforce implicit constraints in a global
context, but the system could recognize that declared constraints need
special handling.

>
> If you validate constraints at commit (prepare) time, taking into consideration
> everything that's happened in the current transaction, plus all changes from
> other transactions that have now committed (or asked to commit), you could
> potentially find unexpected violations -- at the time you inserted those new line
> items, the total would still have been under 25, but by the time you commit,
> another transaction has also inserted and committed before you, and you're no
> longer allowed to commit your new items.

If constraints are evaluated from the global system context, the first
transaction to exceed quota would be blocked, whether it was you or the
sneak who came in after you, stored some more line items, and committed
before you, ruining your day.

> But if you don't check again because
> previous checks (at each item insertion) all passed, you'd miss something
> important.

Don't think about constraints as belonging to you, me, or the sneaky
commit transaction. They're system-wide and evaluated in the global
context. In order to have more than 25 line items, some transaction
had to create enough line items that the sum of the committed line
items and the uncommitted line items was more than 25. That transaction
gets a black mark in its history and will be validated again on commit.
The second validation is also done in a global context, and if the
total number of committed and uncommitted line items is greater than
25, the transaction fails.

> I think some database constraints would need to be marked as "always
> check on commit" because there's no clear trigger to invalidate them.

You may, but I haven't found a case that convinces me yet.

> (Or add a
> SP-callable function that allows real triggers to mark constraints as invalidated
> in the current transaction and all other running transactions, and trust the
> programmer implementing the constraint to know both how to check it, and what
> could cause it to invalidate.)

No, programmers don't handle constraints. They're created by the
database designer and work without program, procedure, or trigger
intervention.


Cheers,

Ann