Question on why this is causing a deadlock
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 =
NF01.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
Re: Question on why this is causing a deadlock
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=3
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 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 =
> NF01.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
>
Re: Question on why this is causing a deadlock
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=2
derby.jdbc.xaTransactionTimeout=1800
derby.infolog.append=true
derby.language.statementCacheSize=1
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
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=3
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
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 =
NF01.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
