db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mike matrigali <mikema...@gmail.com>
Subject Re: Derby Lock Escalation: Deadlocks
Date Fri, 09 Aug 2013 15:46:27 GMT
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
>


Mime
View raw message