Catalin Teodorescu wrote:
Check this link.
http://asktom.oracle.com/pls/ask/f?p=4950:8:1513219::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,%7Bprimary%7D%20and%20%7Bkey%7D%20and%20%7Bupdate%7D%20and%20%7Btable%7D%20and%20%7Block%7D
Dan
-----Original Message-----
From: Michael Bartmann [mailto:michael.bartmann@;lisytec.de]
Sent: Monday, October 28, 2002 12:21 PM
To: [EMAIL PROTECTED]
Subject: [JBoss-dev] Oracle, table locks and CMP2 incompatibility
Hallo db gurus,
we have for some time experienced nasty timeouts on oracle
without jboss deadlock detection (or oracle deadlock detection)
kicking in. We might have found the solution today, allthough
this is not verified, perhaps only time will show.
(...or some db guru will tell me that my following explanation is
severly misguided.)
The EJB locking is based on entity locks. If you have an underlying
database which does locking too, it has to use row level locking,
(as opposed to table level locking) or else jboss detect deadlocks
on the database, as the check which detects deadlocks is based on a
graph representing locks on entity (row) level.
But there are circumstances, under which oracle does not use row
level locking even if with row level locking configured to "always".
This happens when a table TableA has a dependent table TableB in a
1:n relationship (i.e. TableB has a foreign key pointing to TableA.)
If a row of TableA gets updated, oracle tries to lock all entries in
TableB with matching foreign key.
Here comes the vital point: If there is no index on the foreign key
columns of TableB, oracle does a shared lock on _table_ level. This
is probably because otherwise oracle would need to do a full table
scan on the fly to find the matching rows in TableB.
So if you configure jboss to generate the foreign key constraints
you are walking on thin ice; you have to generate indices on the
foreign key columns by hand, or else this effect might hount you.
Solution:
1) [workaround] disable foreign key (or their generation), and let
CMP2 itself take care of the constraints
or
2) implement generation of foreign key indices in jboss. (I'm not fully
convinced that this would suffice; jboss deadlock detection would
have to lock the same entities and avoid race conditions).
Even ignoring the table-lock escalation issue, this is what should
happen. In fact, I'm a little surprised that Oracle doesn't just create
the indices: I believe PostgreSQL does.
Does this sound reasonable to you?
Regards,
Michael Bartmann
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
