{"id":969,"date":"2005-06-30T12:00:00","date_gmt":"2005-06-30T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=969"},"modified":"2005-06-30T12:00:00","modified_gmt":"2005-06-30T12:00:00","slug":"the-multiple-assignment-operator-and-deferred-constraints","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/06\/30\/the-multiple-assignment-operator-and-deferred-constraints\/","title":{"rendered":"The Multiple Assignment Operator and Deferred Constraints"},"content":{"rendered":"

I attended a day long seminar in Edinburgh last year with Chris Date lecturing and it was a rare treat. Thanks again to Peter Robson for organising this.<\/p>\n

Although I’m not the most academically-inclined techie, which I’m always honest about, I found what Chris had to say enlightening and mostly entertaining. There was one part that bothered me though and that was the whole section on ACID, deferred constraint checking and the need for a multiple assignment operator.<\/p>\n

I remember being quite bothered by Chris’ insistence that database integrity must be maintained at statement boundaries, not transaction boundaries. As an Oracle DBA, my immediate reaction was to say (not out loud!) ‘What about read-consistency? Isn’t the main thing that at each commit point, the database is consistent? Who cares if it’s temporarily inconsistent if I am the only transaction that can see those inconsistencies and that I knowingly introduced them?’<\/p>\n

Earlier this year, I heard a terrific presentation by Hugh Darwen at the Scottish SIG talking about NULLs. As an aside, he also mentioned the multiple assignment operator and consistency being maintained at statement boundaries, which kept me thinking …I downloaded Multiple Assignment<\/a> by Chris and Hugh Darwen and read through that, but I still wasn’t convinced. So when I bought Database in Depth<\/a> (which I talked about in my last posting) I cheated a bit and went straight to the section called “Why Database Constraint Checking Must Be Immediate” (not my capitalisation!). Having read that, I can see the points being made more clearly. It’s a reasonably long section and I won’t quote all of it (hopefully increasing the sales of the book in the process), but here are a few lines on the first three of the five reasons Chris gives for immediate constraint checking being mandatory. <\/p>\n

1) “While it might be true, thanks to the isolation property, that no more than one transaction ever sees any particular inconsistency, the fact remains that that particular transaction does see the inconsistency and can therefore produce wrong answers”<\/p>\n

2) “For if transaction T1 produces some result, in the database or elsewhere, that’s subsequently read by transaction T2, then T1 and T2 aren’t truly isolated from one each other (and this remark applies regardless of whether T1 and T2 run concurrently or otherwise).”<\/p>\n

3) “We surely don’t want every program (or other “code unit”) to have to cater for the possibility that the database might be inconsistent when it’s invoked.”<\/p>\n

So I can’t really argue with any of what Chris is saying there, other than to highlight that problems one and two are problems if you choose to do something stupid, like writing out values half-way through a transaction that another source could use, or introducing an inconsistency in your own transaction and not being aware of it. The third reason, though, seems to be a more likely problem and a variant of the first two. If I write a function that could be called inside another transaction, what can I say about the consistency of the database when my function is called?<\/p>\n

I think most database professionals would understand the great value of a database in which the data could never<\/strong> be inconsistent (particularly if you’ve worked with databases when the data is usually<\/strong> inconsistent). Perhaps it’s an area that doesn’t call for pragmatic workarounds, but rigid rules?<\/p>\n

All interesting stuff and I can’t recommend this book highly enough.<\/p>\n","protected":false},"excerpt":{"rendered":"

I attended a day long seminar in Edinburgh last year with Chris Date lecturing and it was a rare treat. Thanks again to Peter Robson for organising this. Although I’m not the most academically-inclined techie, which I’m always honest about, I found what Chris had to say enlightening and mostly entertaining. There was one part… Continue reading The Multiple Assignment Operator and Deferred Constraints<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-969","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":881,"url":"http:\/\/orcldoug.com\/blog\/2006\/01\/15\/peter-robson-on-the-temporal-database-seminar\/","url_meta":{"origin":969,"position":0},"title":"Peter Robson on the Temporal Database seminar","date":"January 15, 2006","format":false,"excerpt":"It's a bit of a blog frenzy today!On the 3rd November last year, immediately after the UKOUG conference, there was a seminar in Edinburgh to discuss temporal databases. (Mogens blogged about it here and I'd mentioned some of Rob Squire's work here.) I had planned to go initially but was\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":970,"url":"http:\/\/orcldoug.com\/blog\/2005\/06\/24\/chris-dates-new-book\/","url_meta":{"origin":969,"position":1},"title":"Chris Date’s new book","date":"June 24, 2005","format":false,"excerpt":"I've just started reading Chris Date's new book and I can recommend it very strongly if, like me, you're a bit lacking in relational theory. It was originally recommended to me by my friend Peter Robson, who was one of the reviewers.Although I've read a number of Chris' articles on\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1249,"url":"http:\/\/orcldoug.com\/blog\/2007\/04\/06\/a-shameful-attempt-to-accept-a-bribe-of-a-free-book\/","url_meta":{"origin":969,"position":2},"title":"A Shameful Attempt to Accept a Bribe of a Free Book","date":"April 6, 2007","format":false,"excerpt":"I received this email from Toon Koppelaars yesterday about a book I'd been hearing about for a while.Lex de Haan and myself started writing a book entitled \"Applied Mathematics for Database Professionals\" (AM4DP) at the end of 2005. Finishing this book took a bit longer than anticipated, but I'm relieved\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1207,"url":"http:\/\/orcldoug.com\/blog\/2007\/02\/17\/dst\/","url_meta":{"origin":969,"position":3},"title":"DST","date":"February 17, 2007","format":false,"excerpt":"Three letters that I am positively sick of hearing. I remember reading about this first on Peter K's blog and - shame on me - it registered but wasn't top of my to do list. Well, it is now. Several other bloggers including Chris Foot have talked about the changes\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":895,"url":"http:\/\/orcldoug.com\/blog\/2005\/12\/07\/oracle-enterprise-manager-10gr2\/","url_meta":{"origin":969,"position":4},"title":"Oracle Enterprise Manager 10gR2","date":"December 7, 2005","format":false,"excerpt":"First of all, let me be clear that I'm not a fan of GUI tools. That's not based on a misguided macho opinion that somehow you need to be able to struggle with a command line interface to prove that you're a good DBA but because :- I'm a contractor\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":968,"url":"http:\/\/orcldoug.com\/blog\/2005\/07\/22\/deferred-constraint-checking\/","url_meta":{"origin":969,"position":5},"title":"Deferred Constraint Checking","date":"July 22, 2005","format":false,"excerpt":"In my previous blog entry I highlighted a few of the reasons that integrity constraint checking should not be deferred, stated by Date in his recent book (and by him and others in other papers). At the time and while looking at an entry over on Jeff Hunter's Blog I\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=969"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/969\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}