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 <msat...@gmail.com<mailto:msat...@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 <bbergqu...@canoga.com<mailto:bbergqu...@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
