[
https://issues.apache.org/jira/browse/DERBY-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13789303#comment-13789303
]
Knut Anders Hatlen commented on DERBY-6372:
-------------------------------------------
Yes, indexes may indeed help reducing the likelihood of deadlocks. I've filed
DERBY-6375 so that we can add that tip to the developer's guide. I think we
also fixed a bug not that long ago where the optimizer preferred a table scan
over an index scan in a particular query even though there was an index that
could be used, and that bug led to unexpected deadlocks.
It is true that a small table with 4 rows doesn't need an index in order to
speed up the scans. But the index helps reducing the number of locks needed by
the scan. The query above might have to go through the entire table in order to
find the row that matches executioni0_.ID_=?. If some other transaction holds
an exclusive lock on one of the rows, the query will have to wait, even if that
row is not the row it was looking for. (With MVCC, the query could just have
read the old version of the row and gone on without waiting for the other
transaction to release its locks.)
If there is an index, the query can jump directly to the row that matches
executioni0_.ID_=?, without looking at any other rows, and it doesn't run into
lock conflicts with transactions that have locked other rows in the table.
> 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)
