This is probably not the proper place to pose a question about how SQL
"should" work, but I don't know of a better one. Any suggestions?

I am curious to know the moment to which a deferred constraint should be
understood to be deferred. I assume it should be after the last
update in a transaction (signalled by a COMMIT) but before the
transaction surrenders read consistency. But if that's the case,
one can construct a pair of concurrent transactions that
severally satisfy all constraints yet jointly leave the database
inconsistent. So what's the defined behaviour? (At this moment I'm
not interested in what we'd like it to be; I want to know what the
standards define it to be.)

--
Roy

Roy Hann wrote:

You assume the standard defines the behavior. You would have to read the
standards document to see if it does.

Bob Badour wrote:

That's plan B, if no one who has it at their fingertips is kind
enough to give up five seconds of their time to save me some (dreary)
hours.

--
Roy

Roy Hann wrote:

Five seconds?!? Bwa-ha-ha-ha-ha

Maybe Joe will step in and help.

Bob Badour wrote:

Strangly, "Joe" turns out to be a key; and I doubt he'd manage to get
his answer out in just 5 seconds.

PS: looks like I'g going to be spending the morning reading.

--
Roy

Bob Badour wrote:

Looking at section 4.10.1 of the standard left me none the wiser, but in
section 4.35.4 we are told that integrity constraints are supposed to be
processed using serializable isolation regardless of the isolation level
of the transaction that triggered the check. (That is not the obvious
section in which to secrete that vital detail.)

So now, after more that 12 hours, I have my answer. My gift to the
Internet is to share it here.

--
Roy

Thank you for saving me the work I would not have rattled it off
in 5 minutes.

It is worth mentioning that a constraint can be initially deffered or
active, then explictiy set the other way. But the goal is that at the
end of session, all constraints must be true. Years ago, Jim Melton
had a paper on some ambigous situations which I cannot remember.

On 31 mrt, 21:42, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Roy, I am curious to know what kind of concurrent transactions you are
thinking of fabricating.

Are you thinking of scenario's in which the concurrency control
mechanism is MVCC, and the constraint checking too happens under
MVCC ?

E.g.

Constraint : "Table1 UNION Table2 have at most one row"
Table1 is empty and Table2 is empty.
Trans1 starts (meaning all its reads from both Table1/2 will return
zero rows) .
Trans2 starts (meaning all its reads from both Table1/2 will return
zero rows).
Trans1 inserts a row into Table1.
Trans2 inserts a row into Table2.
Trans1 commits, checking that no rows exist in both of Table1 and
Table2, which is "true" because the check is run under MVCC, with
Trans1 as a 'mere reader' on Table2.
Trans2 commits, checking that no rows exist in both of Table1 and
Table2, which is "true" because the check is run under MVCC, with
Trans2 as a 'mere reader' on Table1.
Both transactions succeed, leaving the database inconsistent.

This has been documented before. MVCC and database constraint
checking do not fit together.

D&D have stated several times before that "Serializable" really is the
only option if you want data(base) integrity to be _guaranteed_.

I suppose they might even be delighted to see this positions of theirs
being confirmed by the SQL standard admitting as much !!!

I suppose you already know that MVCC does not guarantee the isolation
level "Serializable" in all and every circumstance.

On 1 apr, 16:48, -CELKO- <jcelko... (AT) earthlink (DOT) net> wrote:
The count of sloppy use of language in these few words is TWO.

ONE : it is not "a constraint that can be initially deferred", it is
instead "_THE (EXECUTION OF THE) CHECKING OF_ a constraint that can be
initially deferred".

TWO : the goal is NOT that "at the end of the session, all constraints
must be _TRUE_", the goal is that at the end of the session, all
constraints must be _SATISFIED_.

Nonetheless, thanks for reminding me why I like Chris Date so much.
Unlike you, he takes the time it takes to be _ACCURATE_ in what he
writes down. Also unlike you, he doesn't bother to make people read
sentences about what the subjects are on which "he cannot remember".

On Apr 2, 11:28 pm, Erwin <e.sm... (AT) myonline (DOT) be> wrote:

Are you saying that because you don't regard a constraint as a boolean
valued expression or function, or because you would never use
terminology that ignores the distinction between an expression and
what it evaluates to?