Re: SQL deferred constraints (a bit O/T, I know)

On 31 mrt, 21:42, Roy Hann <specia..._at_processed.almost.meat> wrote:
> 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, 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. Received on Sat Apr 02 2011 - 17:21:18 CEST