Re: table constraint

"joel garry" <joel-garry_at_home.com> wrote in message news:1182971897.183526.139040_at_i13g2000prf.googlegroups.com...
>>
>> 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
>

Joel,

My comment about 'first session doesn't lock all rows' was wrong - it does.

Check the code - it opens a cursor to select and lock all the rows matching a given (colour, direction). You don't have to fetch anything for this to lock the rows. This is what my session stats look like after call for one insert with 1,005 pre-existing rows in the table. Note the undo and redo particularly:

opened cursors cumulative
7
opened cursors current
3
user calls
12
recursive calls
11
recursive cpu usage
1
session logical reads
1,040
CPU used when call started
3
CPU used by this session
3
DB time
3
session uga memory
188,916
session uga memory max
370,356
session pga memory
393,216
session pga memory max
393,216
enqueue requests
4
enqueue releases
2
db block gets
1,030
db block gets from cache
1,030
consistent gets
10
consistent gets from cache
10
consistent gets - examination
1
db block changes
2,024
change write time
1
free buffer requested
11
shared hash latch upgrades - no wait
1
calls to kcmgas
2
calls to get snapshot scn: kcmgss
7
redo entries
1,006
redo size
222,672
redo ordering marks
2
undo change vector size
88,652
no work - consistent read gets
5
deferred (CURRENT) block cleanout applications 5
index fast full scans (full)
1
index scans kdiixs1
1
session cursor cache hits
2
session cursor cache count
1
parse count (total)
7
parse count (hard)
1
execute count
8
bytes sent via SQL*Net to client
766
bytes received via SQL*Net from client
1,196
SQL*Net roundtrips to/from client
8

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