Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint

Re: table constraint

From: Rob van Wijk <rwijk72_at_gmail.com>
Date: Wed, 27 Jun 2007 14:30:22 -0000
Message-ID: <1182954622.626963.98300@w5g2000hsg.googlegroups.com>


> This fails on two counts (on a 10g2 test):
>
> (a) if two session try to insert a row for the same
> combination of colour and direction, the second
> one attempts to lock every row of the same colour
> and direction in the table. This is likely to be a massive
> hit on unod and redo as well as concurrency if the
> OP expects to see lots of rows for each combination.
>
> b) If two users insert the "first" record for a new combination
> at the same time, they do not see each others rows, so
> the locking does not happen - which means you can get
> two rows within three minutes of each other.

Very true. It was my intention to prevent this, but this is not the right way to do it.

The locking is better implemented by doing a dbms_lock.request on the hash value of, for example, to_char(colour) || '|' || to_char(direction). This way the scenario b is covered as well, although it will still limit scalability of course. I guess that's the price to pay for making sure that this business rule is enforced well.

Jonathan, thanks for correcting me.

Regards,
Rob. Received on Wed Jun 27 2007 - 09:30:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US