Hi,

We are using hibernate to interact oracle database.
We are observing a deadlock in one scenario, need help to understand how hibernate generates query and manages isolation etc.

There are two tables involved Parent And Child
There can be multiple entries in child table for each row in Parent table...
cascade=all is requested in case of deletion of parent
and foreign key constraints in child table are indexed
Multiple clients from different servers can be doing insert into child table, also there can be requests to delete rows from parent
which can result in cascade deletion of corresponding rows from child

We are observing deadlock in above scenario...Here is deadlock trace from oracle
Am not clear on few things...queries generated for delete ? what is sequence hibernate following to delete
Also exclusive locks acquired even after foreign key indexing...
and any solution to address this problem :)

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00060010-0000484e 32 801 X 24 726 S
TX-000a002f-00005b90 24 726 X 32 801 S
session 801: DID 0001-0020-00005CA2 session 726: DID 0001-0018-00012884
session 726: DID 0001-0018-00012884 session 801: DID 0001-0020-00005CA2
Rows waited on:
Session 726: no row
Session 801: no row
Information on the OTHER waiting sessions:
Session 726:
sid: 726 ser: 61552 audsid: 1770951 user: 26/REPOMANAGER
flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 24 O/S info: user: orascn, term: UNKNOWN, ospid: 1040400
image: oracle@test0101
O/S info: user: root, term: unknown, ospid: 1234, machine: test0101
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
update Message set forCycle=null, forCycle_order=null where forCycle=:1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update LogMessage set forCycle=null, forCycle_order=null where forCycle=:1


Thanks,
Vrishali