On 8/9/2013 8:37 AM, Katherine Marsden wrote:
On 8/7/2013 2:13 PM, Jasmeet Bhatia (jasmbhat) wrote:
I am troubleshooting a derby deadlock and need help. (setup - -Derby
10.7.1 + in-memory DB + Hibernate 3.6.9 + spring 3.0.3)
Inside a transaction, I execute delete on a specific instance of an
entity that has a 1-* relationship with another entity. This results
in an update SQL that tries to break that relationship. So basically,
something like this…
Update A.col1=null, A.col2=null from A where A.col3 = ?
Even, though col3 has a non-unique index on it, this results in an
escalated table lock (verified it by querying
the SYSCS_DIAG.LOCK_TABLE). Why a table lock? I verified this behavior
can you post the lock list, sometimes this output is misinterpreted with
respect to table locks. Derby will always get "intent" table locks that
will show up as IX or IS table locks, these are necessary to support
real table lock escalation which will show up as X or S table locks.
What isolation level are you expecting/requesting?
on an oracle system and it just creates a row lock. I tried tuning
different parameters such as the "derby.locks.escalationThreshold",
but it did not help. Further down the logic, I have to start a new
nested transaction (for complicated reasons….I will leave out the
details) that tries to read a different entity from the same table and
it gets blocked.
You can set derby.language.logQueryPlan=true and look at the query plan
to make sure you are using the index. If the query plan is bad, you may
want to update statistics to see if that corrects the problem. What
isolation level are you running with?
I second this request. Often locking problems are bad query plan
problems that may be related to bad statistics or not having indexes
that derby requires.
We have also tried upgrading to the latest 10.8 version as well as
switched from in-memory to file based DB but it didn't help.
Thank you for doing that. You may want to try with 10.10 as well.
Any insight is appreciated…
Jasmeet Bhatia
