Re: Declaring Unenforced Constraints

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Fri, 05 Nov 2004 16:45:28 GMT
Message-ID: <fnagmc.ncp.ln@192.168.10.210>

Tony Andrews wrote:

> Laconic2 wrote:

>> So what's the point?
>>
>> Well,  there are a lot of data architects who don't want the DBMS to

> enforce

>> a constraint "for performance reasons".  The merits of that argument

> vary

>> widely from case to case, but there's some merit to it.  It's

> particularly

>> true if the data can be guaranteed to be "cleansed" by the

> transactions that

>> write the data.

>
> For a "read-only" database, i.e. a Data Warehouse, this may make sense.
> For an OLTP environment it really does not. The performance arguments
> are largely bogus anyway, and performing DIY uniqueness and referential
> integrity checking in a multi-user database is REALLY HARD, verging on
> downright impossible, to do right - and is sure to hit performance
> harder than letting the DBMS do it anyway!
>
> It is especially hard in Oracle, which does not support READ
> UNCOMMITTED, to prevent 2 users from performing simultaneous
> transactions that are correct in isolation but corrupt the database
> when taken together.

Enforcing uniqueness in some non-declarative way, such as with a trigger, is not that hard and does not require READ UNCOMMITTED. All you need to have is some kind of explicit lock on the resource. The lock that is effective while having the smallest impact on other users is to lock out other inserts (but not necessarily updates). When you use a declarative unique constraint, the server cannot do much better than this, so all you need is for the server to give you a command to do what it itself is doing.

To my knowledge, none of three products I have used with any depth provide this level of locking, if you roll your own you have to settle for swinging a heavier ax and you might hit bystanders, that is, you may block transactions that are not in fact interfering at all with the insert.

This leaves the argument one of judgement. "Should I?" rather than "Can I?"

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Fri Nov 05 2004 - 10:45:28 CST