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