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 that bothered me though and that was the whole section on ACID, deferred constraint checking and the need for a multiple assignment operator.
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?’
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 by Chris and Hugh Darwen and read through that, but I still wasn’t convinced. So when I bought Database in Depth (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.
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”
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).”
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.”
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?
I think most database professionals would understand the great value of a database in which the data could never be inconsistent (particularly if you’ve worked with databases when the data is usually inconsistent). Perhaps it’s an area that doesn’t call for pragmatic workarounds, but rigid rules?
All interesting stuff and I can’t recommend this book highly enough.