Re: table constraint

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

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html