Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 6793F10CEB for ; Tue, 8 Oct 2013 14:38:46 +0000 (UTC) Received: (qmail 76678 invoked by uid 500); 8 Oct 2013 14:38:45 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 76637 invoked by uid 500); 8 Oct 2013 14:38:44 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 76456 invoked by uid 99); 8 Oct 2013 14:38:43 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 08 Oct 2013 14:38:43 +0000 Date: Tue, 8 Oct 2013 14:38:43 +0000 (UTC) From: "Hendra Jaya (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DERBY-6372) ERROR 40001: A lock could not be obtained due to a deadlock MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13789248#comment-13789248 ] Hendra Jaya commented on DERBY-6372: ------------------------------------ Hi Knut, thanks for the response. I'm able to avoid this deadlock by adding more index on JBPM4_EXECUTION.ID_ column. I'm happy for it but it makes me wonder. At a time, there will be at most 4 records in database. That's because I only send 4 requests. Why would an index be helpful? Should Derby choose table scan instead of using index? Selecting 1 out of 4 doesn't need an index, does it? Honestly, I'm new to MVCC concept. Glad to know it. Our SQL Server isolation level is READ_COMMITTED (default), same as our Derby. I don't think this is the case. Oh by the way, please close this bug after answering my questions above. Curiosity kills you know :) > ERROR 40001: A lock could not be obtained due to a deadlock > ----------------------------------------------------------- > > Key: DERBY-6372 > URL: https://issues.apache.org/jira/browse/DERBY-6372 > Project: Derby > Issue Type: Bug > Affects Versions: 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1 > Environment: Reproducible in: > Laptop, Win 7 Pro 64-bit, Java 6u38, Derby 10.8.2.2, Intel i5, 4GB RAM, JBPM4.4 > Desktop, Win 7 Pro 64-bit, Java 6, Derby 10.8.2.2, Intel i7, 8GB RAM > Distributed VMs, Win Server 2008R2, Java 6u38, Derby 10.8.2.2, Intel i7, 8GB RAM > Reporter: Hendra Jaya > Attachments: 3rd replicate.zip, 4th.zip, 5th.zip, 8th.zip > > > Our software uses 10.8.2.2 and the relevant queries are: > Insert to parent table > Insert to child table 3 times > Update parent table > Update child table 3 times > Select from parent table > Delete from child table 3 times > Delete from parent table > In an aggressive test, I hit this deadlock > ERROR 40001: A lock could not be obtained due to a deadlock, cycle of locks and waiters is: > Lock : ROW, JBPM4_EXECUTION, (9,6) > Waiting XID : {93235, S} , APP, select executioni0_.DBID_ as DBID1_9_, executioni0_.DBVERSION_ as DBVERSION3_9_, executioni0_.ACTIVITYNAME_ as ACTIVITY4_9_, executioni0_.PROCDEFID_ as PROCDEFID5_9_, executioni0_.HASVARS_ as HASVARS6_9_, executioni0_.NAME_ as NAME7_9_, executioni0_.KEY_ as KEY8_9_, executioni0_.ID_ as ID9_9_, executioni0_.STATE_ as STATE10_9_, executioni0_.SUSPHISTSTATE_ as SUSPHIS11_9_, executioni0_.PRIORITY_ as PRIORITY12_9_, executioni0_.HISACTINST_ as HISACTINST13_9_, executioni0_.PARENT_ as PARENT14_9_, executioni0_.INSTANCE_ as INSTANCE15_9_, executioni0_.SUPEREXEC_ as SUPEREXEC16_9_, executioni0_.SUBPROCINST_ as SUBPROC17_9_ from JBPM4_EXECUTION executioni0_ where executioni0_.ID_=? and (executioni0_.PARENT_ is null) fetch first 1 rows only > Granted XID : {93206, X} > Lock : ROW, JBPM4_EXECUTION, (5,11) > Waiting XID : {93206, S} , APP, select executioni0_.DBID_ as DBID1_9_, executioni0_.DBVERSION_ as DBVERSION3_9_, executioni0_.ACTIVITYNAME_ as ACTIVITY4_9_, executioni0_.PROCDEFID_ as PROCDEFID5_9_, executioni0_.HASVARS_ as HASVARS6_9_, executioni0_.NAME_ as NAME7_9_, executioni0_.KEY_ as KEY8_9_, executioni0_.ID_ as ID9_9_, executioni0_.STATE_ as STATE10_9_, executioni0_.SUSPHISTSTATE_ as SUSPHIS11_9_, executioni0_.PRIORITY_ as PRIORITY12_9_, executioni0_.HISACTINST_ as HISACTINST13_9_, executioni0_.PARENT_ as PARENT14_9_, executioni0_.INSTANCE_ as INSTANCE15_9_, executioni0_.SUPEREXEC_ as SUPEREXEC16_9_, executioni0_.SUBPROCINST_ as SUBPROC17_9_ from JBPM4_EXECUTION executioni0_ where executioni0_.ID_=? and (executioni0_.PARENT_ is null) fetch first 1 rows only > Granted XID : {93235, X} > . The selected victim is XID : 93235. > 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) > at org.apache.derby.impl.services.locks.ConcurrentPool.zeroDurationlockObject(Unknown Source) > at org.apache.derby.impl.store.raw.xact.RowLocking2nohold.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.fetchNextGroup(Unknown Source) > at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown Source) > at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown Source) > at org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(Unknown Source) > at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source) > at org.apache.derby.impl.sql.execute.RowCountResultSet.getNextRowCore(Unknown Source) > at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source) > at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source) > at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source) > at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859) > at org.hibernate.loader.Loader.doQuery(Loader.java:825) > at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) > at org.hibernate.loader.Loader.doList(Loader.java:2542) > at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) > at org.hibernate.loader.Loader.list(Loader.java:2271) > at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459) > at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365) > at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) > at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) > at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) > at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890) > at org.jbpm.pvm.internal.hibernate.DbSessionImpl.findProcessInstanceByIdIgnoreSuspended(DbSessionImpl.java:148) > at org.jbpm.pvm.internal.hibernate.DbSessionImpl.deleteProcessInstance(DbSessionImpl.java:185) > at org.jbpm.pvm.internal.model.ExecutionImpl.end(ExecutionImpl.java:396) > at org.jbpm.jpdl.internal.activity.EndActivity.execute(EndActivity.java:81) > at org.jbpm.jpdl.internal.activity.EndActivity.execute(EndActivity.java:43) > at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60) > at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672) > at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperation(ExecutionImpl.java:632) > at org.jbpm.pvm.internal.model.ExecutionImpl.start(ExecutionImpl.java:217) > at org.jbpm.pvm.internal.cmd.StartProcessInstanceCmd.execute(StartProcessInstanceCmd.java:60) > at org.jbpm.pvm.internal.cmd.StartProcessInstanceCmd.execute(StartProcessInstanceCmd.java:37) > at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42) > at org.jbpm.pvm.internal.tx.SpringCommandCallback.doInTransaction(SpringCommandCallback.java:45) > at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) > at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:49) > at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.executeInNewEnvironment(EnvironmentInterceptor.java:53) > at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:40) > at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:56) > at org.jbpm.pvm.internal.svc.ExecutionServiceImpl.startProcessInstanceById(ExecutionServiceImpl.java:59) > at com.corporate.domain.bundle.executor.CreationBundleExecutor.execute(CreationBundleExecutor.java:196) > at com.corporate.executor.filter.ExecutionWorkflowExecutorFilter$ExecutionTask.callBundleExecutor(ExecutionWorkflowExecutorFilter.java:194) > at com.corporate.executor.filter.ExecutionWorkflowExecutorFilter$ExecutionTask.call(ExecutionWorkflowExecutorFilter.java:151) > at com.corporate.executor.filter.ExecutionWorkflowExecutorFilter$ExecutionTask.call(ExecutionWorkflowExecutorFilter.java:101) > at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) > at java.util.concurrent.FutureTask.run(FutureTask.java:138) > at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) > at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) > at java.lang.Thread.run(Thread.java:662) > I've tried another 3 Derby versions and still same deadlock occurs. When I switched to MS SQL Server 2008 R2, it passes this test (no deadlock). So most likely Derby bug. I browsed previous Derby bugs and found few similar bugs. > Urgency: Critical (my side), we start thinking to drop Derby from our supported database. -- This message was sent by Atlassian JIRA (v6.1#6144)