db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Re: Question on why this is causing a deadlock
Date Mon, 17 Feb 2014 12:31:12 GMT
Thanks for taking the time to respond Mamta.  I have been through those and I did find the
chat very difficult to follow ;)

I am surprised that it did not dump out the lock table when this occurred as I have the following
in my derby.properties:

#Java DB Properties File
#derby.language.logStatementText=true
#derby.language.logQueryPlan=true
derby.locks.waitTimeout=60
derby.locks.deadlockTrace=true
derby.locks.monitor=true
derby.locks.escalationThreshold=20000
derby.jdbc.xaTransactionTimeout=1800
derby.infolog.append=true
derby.language.statementCacheSize=10000
derby.storage.indexStats.auto=true

I will try to get this to happen with the lock dump and figure it out.   I think it might
have something to do with the tables in question being very sparse at this time (a couple
of rows each) and maybe an index is not being used causing an exclusive table lock.  With
isolation level of READ-COMMITTED, I believe that the SELECT statement should only be locking
and unlocking a row at a time as it progresses through the result set and the UPDATE statement
should only be locking one row because there is a unique index on ID.   So I don’t see how
a deadlock could be occurring if this would be the case because one or the other statements
should succeed and release its lock.



On Feb 15, 2014, at 2:24 PM, Mamta Satoor <msatoor@gmail.com<mailto:msatoor@gmail.com>>
wrote:

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<mailto: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<http://pncs.id/> JOIN
CORE_V1.PROXYNID_PROXYNIDCLIENT PNPNC ON PNC.ID<http://pnc.id/> = PNPNC.PROXYNIDCLIENT_ID
JOIN CORE_V1.PROXY_NID PN ON PNPNC.PROXYNID_ID = PN.ID<http://pn.id/> JOIN CORE_V1.PROXY_NID_STATUS
PNS ON PN.STATUS_ID = PNS.ID<http://pns.id/> JOIN CORE_V1.AGENT_MANAGED_HARDWARE AMH
ON PN.ID<http://pn.id/> = AMH.PROXYNID_ID JOIN CORE_V1.HARDWARE HW ON AMH.ID<http://amh.id/>
= HW.ID<http://hw.id/> JOIN CORE_V1.SNMP_DEVICE SD ON AMH.ID<http://amh.id/> =
SD.AGENT_MANAGED_HARDWARE_ID JOIN CORE_V1.SNMP_DEVICE_IP SDIP ON SD.ID<http://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