Wednesday, June 29, 2005

Deferrable Constraints

One of my developers asked me how to drop a constraint in PL/SQL today. "Don't do that" was my immediate answer.

Well, seems they have a legitimate case where they need to change a PK value in a controlled environment. I suggested deferrable constraints would give them some flexibility yet still maintain data integrity at the transaction level. I quickly got in over my head when peppered with questions about how it works and when errors are raised. So I cobbled up an example:

Sure enough. I still need to investigate any locking issues, but it looks like this might be what we are looking for in this particular case. If anybody has any hints on what to look out for, I'd appreciate it. Also, assuming we have a legitimate business case, do you think this is an appropriate use of deferrable constraints?

OK, theoretically you could choose to call an autonomous transaction part way through a deferred operation, but I hardly think that's likely.

All that "T1 references T2" stuff is fine, but in reality T1 and T2 see a read consistent view of the database so they don't even know the deferred operation is happening until it's complete, at which point everything is Kool and the gang!

If I'm missing the point I'd like to know more.

(Note to self. Stop mentioning autonomous transactions. You never know when TK might be listening.)

I don't think you're missing anything Tim and that was why I struggled with Date's insistence on this stuff. I think the best bit is to look at http://dbdebunk.com or Chris' book and decide for yourself - there's lots more to read on this subject. That's why I wanted to put it out there, for people to think about and have a view on.

The way I reconcile it to myself is that if we see the database as always being a consistent version of the truth which moves forward at each assigmnent, then even *my own* transaction could never see a child without a parent for example. Now, of course, you would think that if I inserted a child without a parent, knowingly, using deferred constraints, then what's the problem? But it's true that I am currently looking at an inconsistent database. If we had a multiple assignment operator, that could never be true.

But then how big might those multiple assignment statements be!

Personally I think the logic is undeniable, but that you might say - 'So what? That's never going to be a problem really ...'

I take the point that without deferrable constraints the transaction can never be internally inconsistent, but if someone makes the choice to use deferrable constraints and make a mistake like that then they are a bit of a donkey. :)

"... 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."

In fairness, there are actually five reasons he gives for immediate checking and he fleshes out the detail - this was just a taster ...

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"I can kind of agree with this in theory. You change a pk value in the master and query a child table using a join, you could possibly get bad results. It would definitely be something the programmer would have to be aware of.