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
