db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Need help understanding a database deadlock that was detected
Date Wed, 02 Sep 2015 21:19:03 GMT
Derby 10.9.1.0

Here is the output from the derby.log:

Wed Sep 02 16:42:17 EDT 2015 Thread[DRDAConnThread_14,5,main] (XID = 50633), (SESSIONID =
2110), (DATABASE = csemdb), (DRDAID = NF000001.D5E2-4183279662650783751{166}), Cleanup action
starting
Wed Sep 02 16:42:17 EDT 2015 Thread[DRDAConnThread_14,5,main] (XID = 50633), (SESSIONID =
2110), (DATABASE = csemdb), (DRDAID = NF000001.D5E2-4183279662650783751{166}), Failed Statement
is: SELECT CHASSIS_9145E10G_ID, IP_STR, DS.TEMPLATE, DS.SYSTEM_TEMPLATE, DS.ADMIN_ENABLED,
DS.ALIAS_NAME, DS.MODEL, DS.HARDWARE_VERSION, DS.ACTIVE_FIRMWARE, DS.INACTIVE_FIRMWARE, DS.BOOTCODE,
CONTACT, CIRCUIT, CIRCUIT_2, LOCATION, HAS_MGMT_PORT, HAS_CONSOLE_PORT, POWERSUPPLY_A_MODEL,
POWERSUPPLY_B_MODEL, HAS_FANS, HAS_TEMP_SENSOR, HAS_USER_XFP_CONNECTOR, USER_XFP_MODEL, HAS_NET_XFP_CONNECTOR,
NET_XFP_MODEL, IP, SYNCHRONIZING, LAST_SYNCHRONIZED_TIMESTAMP, CREATED_TIMESTAMP, ADMIN_STATE_TIMESTAMP,
MACADDRESS_STR, PEER_MACADDRESS_STR, ZTP_STAGED, ZTP_STAGED_TIMESTAMP, ZTP_PROVISIONING_STATE,
ZTP_PROVISIONING_STATE_TIMESTAMP, R2_MODEL FROM --DERBY-PROPERTIES joinOrder=FIXED
PKG_9145E10G.DEVICE_SUMMARY DS JOIN CORE_V1.MANAGED_HARDWARE_SUMMARY MHS ON DS.CHASSIS_9145E10G_ID
= MHS.MANAGED_HARDWARE_ID WHERE DS.CHASSIS_9145E10G_ID = ? with 1 parameters begin parameter
#1: 1052 :end parameter
ERROR 40001: A lock could not be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, MANAGED_HARDWARE_SUMMARY, (47,8)
  Waiting XID : {50633, S} , CSEM, SELECT CHASSIS_9145E10G_ID, IP_STR, DS.TEMPLATE, DS.SYSTEM_TEMPLATE,
DS.ADMIN_ENABLED, DS.ALIAS_NAME, DS.MODEL, DS.HARDWARE_VERSION, DS.ACTIVE_FIRMWARE, DS.INACTIVE_FIRMWARE,
DS.BOOTCODE, CONTACT, CIRCUIT, CIRCUIT_2, LOCATION, HAS_MGMT_PORT, HAS_CONSOLE_PORT, POWERSUPPLY_A_MODEL,
POWERSUPPLY_B_MODEL, HAS_FANS, HAS_TEMP_SENSOR, HAS_USER_XFP_CONNECTOR, USER_XFP_MODEL, HAS_NET_XFP_CONNECTOR,
NET_XFP_MODEL, IP, SYNCHRONIZING, LAST_SYNCHRONIZED_TIMESTAMP, CREATED_TIMESTAMP, ADMIN_STATE_TIMESTAMP,
MACADDRESS_STR, PEER_MACADDRESS_STR, ZTP_STAGED, ZTP_STAGED_TIMESTAMP, ZTP_PROVISIONING_STATE,
ZTP_PROVISIONING_STATE_TIMESTAMP, R2_MODEL FROM --DERBY-PROPERTIES joinOrder=FIXED
PKG_9145E10G.DEVICE_SUMMARY DS JOIN CORE_V1.MANAGED_HARDWARE_SUMMARY MHS ON DS.CHASSIS_9145E10G_ID
= MHS.MANAGED_HARDWARE_ID WHERE DS.CHASSIS_9145E10G_ID = ?
  Granted XID : {50631, X}
Lock : ROW, DEVICE_SUMMARY, (4,8)
  Waiting XID : {50631, X} , CSEM, UPDATE PKG_9145E10G.DEVICE_SUMMARY SET ZTP_STAGED_TIMESTAMP
= ?, ZTP_STAGED = ? WHERE (ID = ?)
  Granted XID : {50633, S}
. The selected victim is XID : 50633.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.services.locks.Deadlock.buildException(Unknown Source)
        at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown Source)
        at org.apache.derby.impl.services.locks.ConcurrentLockSet.zeroDurationLockObject(Unknown
Source)
        at org.apache.derby.impl.services.locks.AbstractPool.zeroDurationlockObject(Unknown
Source)


I have the following properties set:

derby.locks.waitTimeout=60
derby.locks.deadlockTrace=true
derby.locks.monitor=true
derby.locks.escalationThreshold=20000
#derby.storge.pageCacheSize=16000
derby.jdbc.xaTransactionTimeout=1800
derby.language.statementCacheSize=10000
derby.infolog.append=true
derby.storage.indexStats.auto=true
derby.stream.error.style=rollingFile
derby.stream.error.rollingFile.limit=10000000
derby.stream.error.rollingFile.count=10
derby.stream.error.rollingFile.pattern=%d/derby-%g.log


I don’t understand the deadlock.  The first statement does query the DEVICE_SUMMARY table
that the second statement is trying to update, but I don’t see in the output the second
statement having any requirement for the locks the first statement has MANAGED_HARDWARE_SUMMARy
row lock.

I tried to force the join order as

DEVICE_SUMMARY
MANAGED_HARDWARE_SUMMARY

in the select statement to force the query to acquire locks first on DEVICE_SUMMARY and then
MANAGED_HARDWARE_SUMMARY so that the locking pattern would be the same an the second statement
of

DEVICE_SUMMARY

but this does not seem to had the desired effect.

Any ideas will be greatly appreciated.

Brett


________________________________
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300

This e-mail and any attached document(s) is confidential and is intended only for the review
of the party to whom it is addressed. If you have received this transmission in error, please
notify the sender immediately and discard the original message and any attachment(s).
Mime
View raw message