Re: Why does my process hang?

Jerome Peter (jerrym_at_cix.compulink.co.uk) wrote:

: I believe that you may be suffering at the hands of an Oracle
: feature (!!) which causes statement 3 to lock the child table
: in order to confirm whether or not the delete from the parent
: table violates the foreign key constraint.

: Once you issue a commit or rollback in session 2, session 1 will
: be able to acquire the table lock it needs and delete the row
: from the parent table.

: Because of this feature, Oracle recommend that you index the foreign
: key column(s) in the child table - so that the index can be used
: for any foreign key constraint checking and therefore negate the need
: to acquire the table lock on the child table.

: I hope this helps ....... Jerry Mckeon

Your information verifies what we eventually decided what the problem was. My compatriot (the DBA) discovered the lock monitor screen, and we could see that a process had requested a table share lock while another process had already acquired a table row exclusive lock on the same table. A bit more sleuthing and we were able to convert the "resorce id #1" value from the lock screen to an actual table name. Then it dawned on me that maybe the other respondent meant to put an index on the foreign key column in the child table (duh). I did that and presto, problem solved. We probably would have put indices on the columns eventually, but the application is still in developement and the tables aren't populated yet. At least going through this exercise has increased my Oracle knowledge an iota. :-)

  Francis

--
| . . . . 5 . . . . 10. . . . 15. . . . 20. . . . 25. . . . 30. . . . 35. . .

Padres                                         Francis Small - M/S M1RA     
Giants                                           Test Engineering
Bums                                               Microwave Instruments Div.
Rockies                                              telnet 794-3305
Diamondbacks                                           fthes_at_sr.hp.com
******************** Only 52 days left until Opening Day ********************