db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6372) ERROR 40001: A lock could not be obtained due to a deadlock
Date Tue, 08 Oct 2013 15:37:43 GMT

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

Mime
View raw message