db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Question on why this is causing a deadlock
Date Sat, 15 Feb 2014 19:24:28 GMT
Brett,

May be you are aware of following information already but here is what has
been suggested for investigating deadlock issues in the past.

***********************************
Dag Wanvik suggested
In general, this resource may help you understand how Derby uses locks:
http://db.apache.org/derby/docs/10.8/devguide/cdevconcepts30291.html
 and specifically how to debug deadlocks:
http://db.apache.org/derby/docs/10.8/devguide/cdevconcepts50894.html
http://wiki.apache.org/db-derby/LockDebugging

 If you feel you understand how Derby takes locks but are seeing Derby
 take locks that you feel are not appropriate for your usage and
 isolation level, please let us know. If you can provide a lock table
 dump, that would be helpful. Please state version of Derby are you
 using, too.
***********************************
Kathey Marsden suggested
If you are using the new 10.8.1.2 release, setting
derby.stream.error.extendedDiagSeverityLevel=30000
 will print the stack traces of all active threads on deadlock to
derby.log, which can help debug both application and possible Derby  issues
with Deadlocks.
http://db.apache.org/derby/docs/10.8/ref/rrefproperextdiagsevlevel.html
***********************************

thanks,
Mamta


On Sat, Feb 15, 2014 at 9:57 AM, Bergquist, Brett <BBergquist@canoga.com>wrote:

> Here is the output from the deadlock:
>
> Fri Feb 14 16:33:55 EST 2014 Thread[DRDAConnThread_26,5,main] (XID =
> 879610), (SESSIONID = 28952), (DATABASE = csemdb), (DRDAID =
> NF000001.F677-578992634681601532{719}), Failed Statement is: UPDATE
> CORE_V1.PROXY_NID_CLIENT_STATUS SET CONNECTION_STATE_DATE = ?, OPLOCK = ?
> WHERE ((ID = ?) AND (OPLOCK = ?)) with 4 parameters begin parameter #1:
> 2014-02-14 16:33:35.667 :end parameter begin parameter #2: 10607 :end
> parameter begin parameter #3: 2 :end parameter begin parameter #4: 10606
> :end parameter
> ERROR 40001: A lock could not be obtained due to a deadlock, cycle of
> locks and waiters is:
> Lock : ROW, PROXY_NID_CLIENT_STATUS, (1,7)
>   Waiting XID : {879610, X} , CSEM, UPDATE CORE_V1.PROXY_NID_CLIENT_STATUS
> SET CONNECTION_STATE_DATE = ?, OPLOCK = ? WHERE ((ID = ?) AND (OPLOCK = ?))
>   Granted XID : {879611, S}
> Lock : ROW, PROXY_NID_STATUS, (1,8)
>   Waiting XID : {879611, S} , CSEM, SELECT COUNT(*) FROM
> CORE_V1.PROXY_NID_CLIENT PNC JOIN CORE_V1.PROXY_NID_CLIENT_STATUS PNCS ON
> PNC.STATUS_ID = PNCS.ID JOIN CORE_V1.PROXYNID_PROXYNIDCLIENT PNPNC ON
> PNC.ID = PNPNC.PROXYNIDCLIENT_ID JOIN CORE_V1.PROXY_NID PN ON
> PNPNC.PROXYNID_ID = PN.ID JOIN CORE_V1.PROXY_NID_STATUS PNS ON
> PN.STATUS_ID = PNS.ID JOIN CORE_V1.AGENT_MANAGED_HARDWARE AMH ON PN.ID =
> AMH.PROXYNID_ID JOIN CORE_V1.HARDWARE HW ON AMH.ID = HW.ID JOIN
> CORE_V1.SNMP_DEVICE SD ON AMH.ID = SD.AGENT_MANAGED_HARDWARE_ID JOIN
> CORE_V1.SNMP_DEVICE_IP SDIP ON SD.ID = SDIP.SNMPDEVICE_ID
>   Granted XID : {879610, X}
> . The selected victim is XID : 879610.
>
> There are two separate processes running.  One is periodically querying
> which is the "SELECT COUNT(*)..." above.  The second is updating the state of
> one of the rows which is the "UPDATE" above.
>
> I am not sure how to read the above and what I can do about it.
>
> The query is done using a native JPA query and the second is using JPA
> directly.  Both are being done within a Java EE stateless session being
> with a transaction.   The isolation level is read committed.
>
> Any help will be appreciated.
>
> Brett
>

Mime
View raw message