Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!193.201.147.68.MISMATCH!feeder.news-service.com!94.75.214.39.MISMATCH!aioe.org!.POSTED!not-for-mail From: Roy Hann Newsgroups: comp.databases.theory Subject: Re: SQL deferred constraints (a bit O/T, I know) Date: Mon, 4 Apr 2011 09:17:46 +0000 (UTC) Organization: Aioe.org NNTP Server Lines: 35 Message-ID: References: NNTP-Posting-Host: fUEh9+sMLTMq2OZR54fPQw.user.speranza.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: XPN/1.0.0 (Monkey Business ; Windows) X-Notice: Filtered by postfilter v. 0.8.2 Xref: news.cambrium.nl Erwin wrote: > On 31 mrt, 21:42, Roy Hann 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 ? Yes, but when I posed the question I was unaware that the SQL standard requires constraint checking to done using serializable isolation *regardless* of the isolation level of the updating transaction. Obviously if you insist on serializable isolation a lot of easy-to-contrive violations are impossible. -- Roy