db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Pearson <j...@emnico.com>
Subject Deadlock while updating statistics
Date Fri, 26 Mar 2010 13:49:25 GMT

I am seeing a deadlock while running a statistics update .

It appears that each operation has a ROW lock which the other wants.

Any advice on how to avoid this?

I've enclosed the deadlock trace below:

2010-03-25 05:06:44,647 ERROR:AlertProcessor: AlertProcessor
org.springframework.dao.DeadlockLoserDataAccessException: 
PreparedStatementCallback; SQL [DELETE FROM USER_DOMAIN_ALERT WHERE 
ALERT_HANDLE=? ]; A lock could not be obtained due to a deadlock, cycle 
of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (8,2408)
   Waiting XID : {271568763, S} , EMNOC, DELETE FROM USER_DOMAIN_ALERT 
WHERE ALERT_HANDLE=?
   Granted XID : {271568697, X}
Lock : ROW, SYSSTATISTICS, (11,2006)
   Waiting XID : {271568697, X} , EMNOC, alter table 
"EMNOC20"."USER_DOMAIN_ALERT" all update statistics
   Granted XID : {271568763, S}
. The selected victim is XID : 271568763.; nested exception is 
java.sql.SQLTransactionRollbackException: A lock could not be obtained 
due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (8,2408)
   Waiting XID : {271568763, S} , EMNOC, DELETE FROM USER_DOMAIN_ALERT 
WHERE ALERT_HANDLE=?
   Granted XID : {271568697, X}
Lock : ROW, SYSSTATISTICS, (11,2006)
   Waiting XID : {271568697, X} , EMNOC, alter table 
"EMNOC20"."USER_DOMAIN_ALERT" all update statistics
   Granted XID : {271568763, S}
. The selected victim is XID : 271568763.
Caused by:
java.sql.SQLTransactionRollbackException: A lock could not be obtained 
due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (8,2408)
   Waiting XID : {271568763, S} , EMNOC, DELETE FROM USER_DOMAIN_ALERT 
WHERE ALERT_HANDLE=?
   Granted XID : {271568697, X}
Lock : ROW, SYSSTATISTICS, (11,2006)
   Waiting XID : {271568697, X} , EMNOC, alter table 
"EMNOC20"."USER_DOMAIN_ALERT" all update statistics
   Granted XID : {271568763, S}
. The selected victim is XID : 271568763.
     at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
Source)
     at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
     at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
Source)
     at 
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
Source)
     at 
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
     at 
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
     at 
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
     at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown 
Source)
     at 
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown 
Source)
     at 
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
     at 
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
     at 
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:745)
     at 
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:538)
     at 
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:739)
     at 
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:797)
     at 
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:805)
     at com.emnico.emnoc.generated.a.C.b(DBUserDomainAlert.java:72)
     at com.emnico.emnoc.c.a.b(AlertProcessor.java:1118)
     at com.emnico.emnoc.c.a.c(AlertProcessor.java:1078)
     at com.emnico.emnoc.c.a.run(AlertProcessor.java:1492)
     at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: A lock could not be obtained due to a 
deadlock, cycle of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (8,2408)
   Waiting XID : {271568763, S} , EMNOC, DELETE FROM USER_DOMAIN_ALERT 
WHERE ALERT_HANDLE=?
   Granted XID : {271568697, X}
Lock : ROW, SYSSTATISTICS, (11,2006)
   Waiting XID : {271568697, X} , EMNOC, alter table 
"EMNOC20"."USER_DOMAIN_ALERT" all update statistics
   Granted XID : {271568763, S}
. The selected victim is XID : 271568763.
     at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
Source)
     at 
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown 
Source)
     ... 21 more
Caused by: ERROR 40001: A lock could not be obtained due to a deadlock, 
cycle of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (8,2408)
   Waiting XID : {271568763, S} , EMNOC, DELETE FROM USER_DOMAIN_ALERT 
WHERE ALERT_HANDLE=?
   Granted XID : {271568697, X}
Lock : ROW, SYSSTATISTICS, (11,2006)
   Waiting XID : {271568697, X} , EMNOC, alter table 
"EMNOC20"."USER_DOMAIN_ALERT" all update statistics
   Granted XID : {271568763, S}
. The selected victim is XID : 271568763.
     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.AbstractPool.lockObject(Unknown Source)
     at 
org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForRead(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown 
Source)
     at 
org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown Source)
     at 
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(Unknown 
Source)
     at 
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(Unknown 
Source)
     at 
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getStatisticsDescriptors(Unknown 
Source)
     at 
org.apache.derby.iapi.sql.dictionary.TableDescriptor.getStatistics(Unknown 
Source)
     at 
org.apache.derby.iapi.sql.dictionary.TableDescriptor.statisticsExist(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.FromBaseTable.estimateCost(Unknown Source)
     at 
org.apache.derby.impl.sql.compile.OptimizerImpl.estimateTotalCost(Unknown Source)
     at 
org.apache.derby.impl.sql.compile.OptimizerImpl.costBasedCostOptimizable(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.OptimizerImpl.costOptimizable(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.FromBaseTable.optimizeIt(Unknown Source)
     at 
org.apache.derby.impl.sql.compile.ProjectRestrictNode.optimizeIt(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.OptimizerImpl.costPermutation(Unknown 
Source)
     at org.apache.derby.impl.sql.compile.SelectNode.optimize(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.DMLModStatementNode.optimizeStatement(Unknown 
Source)
     at 
org.apache.derby.impl.sql.compile.DeleteNode.optimizeStatement(Unknown 
Source)
     at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown 
Source)
     at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
     at 
org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(Unknown Source)
     ... 15 more



-- 
Emnico Services Limited, Dauntsey House, Stonehill Green, Westlea, Swindon SN5 7HB
Company No. 06390113

This message is confidential and may contain privileged information.  If you are not the addressee
indicated in this message (or responsible for delivery of the message to them), you may not
copy or deliver this message to anyone or take any action in reliance on it.  If you have
received this e-mail in error, please delete it and notify the sender as soon as possible.
 The Emnico group of companies do not accept any liability for any harm that may be caused
to the recipient's system or data by this message.  Please carry out virus and other such
checks as you consider appropriate.


Mime
View raw message