db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Question on why this is causing a deadlock
Date Sat, 15 Feb 2014 17:57:52 GMT
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