Re: Declaring Unenforced Constraints

Kenneth Downs wrote:
> 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.

Maybe Oracle is, ahem, unique in this regard but it CAN do much better than you can do yourself. For DIY uniqueness checking you have to serialize inserts: if user U1 inserts a row into table T1, then no one else may insert a row into T1 until U1 commits his transaction. That is a BIG bottleneck for a busy system. In contrast, the Oracle DBMS does not need to serialize inserts, because it can see what is going on, including the uncomitted insert. Received on Fri Nov 05 2004 - 18:16:23 CET