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).
