Re: table constraint

On Jun 27, 6:48 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <rwij..._at_gmail.com> wrote in message
>
> news:1182862654.451560.217130_at_n60g2000hse.googlegroups.com...
>
>
>
>
>
> > 3 procedure lck
> > 4 ( p_colour in mytable.colour%type
> > 5 , p_direction in mytable.direction%type
> > 6 )
> > 7 is
> > 8 cursor c_lock
> > 9 is
> > 10 select 'dummy'
> > 11 from mytable
> > 12 where colour = p_colour
> > 13 and direction = p_direction
> > 14 for update of mydate
> > 15 ;
> > 16 begin
> > 17 open c_lock;
> > 18 close c_lock;
> > 19 end lck
> > 20 ;
>
> 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.
>
> I am a little puzzled that the first session to insert a row
> DOESN'T (in 10g) lock all the other rows when you
> open that cursor - but that's the way it seems to work.

Could that be because the insert is in a no-data-found exception done after the lock procedure is called? What would it lock?

jg

--
@home.com is bogus.
http://www.wisdomquotes.com/002491.html