You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "palo.liska" <pa...@gmail.com> on 2018/03/29 09:42:17 UTC
deadlock on select count(*) from tableName
Hi
did someone saw deadlock during concurrent select count(*) from tableName?
09:15:41,959 [ERROR] o.h.e.j.s.SqlExceptionHelper:146 - A lock could not be
obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SIPSAFQUEUE, (1,10)
Waiting XID : {20748, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20506, X}
Lock : ROW, SIPSAFQUEUE, (1,14)
Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20748, X}
. The selected victim is XID : 20748.
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
Re: deadlock on select count(*) from tableName
Posted by "palo.liska" <pa...@gmail.com>.
Rick Hillegas-3 wrote
> I'm afraid I cannot make sense of the attached stack trace. There is no
> reason that a SequenceUpdater would be called while executing a "select
> count(*) ..." query. It is hard to believe that that is the stack trace
> corresponding to a deadlock on "select count(*) ...". Can you provide
> any more evidence which ties that stack trace to the "select count(*)
> ..."?
that stacktrace was just illustration to DEBRY-6934. "select count(*) "
stacktrace was posted earlier:
...
Caused by: org.apache.derby.iapi.error.StandardException: A lock could not
be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, THETABLE, (1,10)
Waiting XID : {21567, S} , EPS, /* SELECT COUNT(*) FROM THETABLE r */
select count(*) as col_0_0_ from THETABLE thetable0_
Granted XID : {20506, X}
Lock : ROW, THETABLE, (2,15)
Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM THETABLE r */
select count(*) as col_0_0_ from THETABLE thetable0_
Granted XID : {21567, X}
. The selected victim is XID : 21567.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
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.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getRowFromResultSet(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown
Source)
... 64 common frames omitted
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
Re: deadlock on select count(*) from tableName
Posted by Rick Hillegas <ri...@gmail.com>.
I'm afraid I cannot make sense of the attached stack trace. There is no
reason that a SequenceUpdater would be called while executing a "select
count(*) ..." query. It is hard to believe that that is the stack trace
corresponding to a deadlock on "select count(*) ...". Can you provide
any more evidence which ties that stack trace to the "select count(*) ..."?
Thanks,
-Rick
On 4/5/18 12:37 AM, palo.liska wrote:
> Rick Hillegas-3 wrote
>> It is likely that you have run into
>> https://issues.apache.org/jira/browse/DERBY-6934
> actually I run into this issue, but I dont think this time.
> My understanding is that for this deadlock, I am not requesting new
> autogenerated Id. But during counting rows derby is locking (why?) rows one
> by another. So when there are more threads counting, they could lock out
> each other.
> However, I am not able to isolate this issue. It happens once (so I think it
> could happen again), but when I tried to reporoduce it in demonstration
> code, I was not successful.
>
> For DERBY-6934 stack trace look like this (see tooMuchContentionException
> call):
>
> Caused by: org.apache.derby.iapi.error.StandardException: A lock could not
> be obtained within the time requested
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.catalog.SequenceUpdater.tooMuchContentionException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.catalog.SequenceUpdater.getCurrentValueAndAdvance(Unknown
> Source)
> at
> org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getCurrentValueAndAdvance(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.BaseActivation.getCurrentValueAndAdvance(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(Unknown
> Source)
>
>
>
> --
> Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
>
Re: deadlock on select count(*) from tableName
Posted by "palo.liska" <pa...@gmail.com>.
Rick Hillegas-3 wrote
> It is likely that you have run into
> https://issues.apache.org/jira/browse/DERBY-6934
actually I run into this issue, but I dont think this time.
My understanding is that for this deadlock, I am not requesting new
autogenerated Id. But during counting rows derby is locking (why?) rows one
by another. So when there are more threads counting, they could lock out
each other.
However, I am not able to isolate this issue. It happens once (so I think it
could happen again), but when I tried to reporoduce it in demonstration
code, I was not successful.
For DERBY-6934 stack trace look like this (see tooMuchContentionException
call):
Caused by: org.apache.derby.iapi.error.StandardException: A lock could not
be obtained within the time requested
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.catalog.SequenceUpdater.tooMuchContentionException(Unknown
Source)
at
org.apache.derby.impl.sql.catalog.SequenceUpdater.getCurrentValueAndAdvance(Unknown
Source)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getCurrentValueAndAdvance(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BaseActivation.getCurrentValueAndAdvance(Unknown
Source)
at
org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(Unknown
Source)
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
Re: deadlock on select count(*) from tableName
Posted by Rick Hillegas <ri...@gmail.com>.
It is likely that you have run into
https://issues.apache.org/jira/browse/DERBY-6934
On 4/3/18 7:07 AM, palo.liska wrote:
> i dont set read-only explicitly, and I guess that transaction is reused
> (default Propagation.REQUIRED).
>
> code:
>
> @Transactional
> public int getQueueSize() {
> return ((Long)
> sessionProvider.getCurrentSession().createQuery("SELECT COUNT(*) FROM
> tableName r").uniqueResult()).intValue();
> }
>
>
> This is stacktrace if it helps:
>
> Caused by: org.hibernate.exception.LockAcquisitionException: could not
> execute query
> at
> org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:92)
> at
> org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
> at
> org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
> at org.hibernate.loader.Loader.doList(Loader.java:2557)
> at org.hibernate.loader.Loader.doList(Loader.java:2540)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
> at org.hibernate.loader.Loader.list(Loader.java:2365)
> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
> at
> org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
> at
> org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
> at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
> at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
> at
> org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
> ... 3 lines ommited
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
> at
> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
> at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
> at com.sun.proxy.$Proxy103.store(Unknown Source)
> ... 2 lines ommited
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
> at
> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
> at
> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
> at
> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
> at
> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
> at com.sun.proxy.$Proxy102.sendFinaliseTransactionRequest(Unknown Source)
> ... 19 common frames omitted
> Caused by: java.sql.SQLTransactionRollbackException: A lock could not be
> obtained due to a deadlock, cycle of locks and waiters is:
> Lock : ROW, SIPSAFQUEUE, (1,10)
> Waiting XID : {20477, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20506, X}
> Lock : ROW, SIPSAFQUEUE, (1,9)
> Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20477, X}
> . The selected victim is XID : 20477.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.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.EmbedResultSet.closeOnTransactionError(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
> at
> org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:191)
> at
> org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:191)
> at org.hibernate.loader.Loader.processResultSet(Loader.java:950)
> at org.hibernate.loader.Loader.doQuery(Loader.java:921)
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
> at org.hibernate.loader.Loader.doList(Loader.java:2554)
> ... 58 common frames omitted
> Caused by: org.apache.derby.iapi.error.StandardException: A lock could not
> be obtained due to a deadlock, cycle of locks and waiters is:
> Lock : ROW, SIPSAFQUEUE, (1,10)
> Waiting XID : {20477, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20506, X}
> Lock : ROW, SIPSAFQUEUE, (1,9)
> Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20477, X}
> . The selected victim is XID : 20477.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> 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.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getRowFromResultSet(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown
> Source)
> ... 66 common frames omitted
>
>
>
> --
> Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
>
Re: deadlock on select count(*) from tableName
Posted by "palo.liska" <pa...@gmail.com>.
i dont set read-only explicitly, and I guess that transaction is reused
(default Propagation.REQUIRED).
code:
@Transactional
public int getQueueSize() {
return ((Long)
sessionProvider.getCurrentSession().createQuery("SELECT COUNT(*) FROM
tableName r").uniqueResult()).intValue();
}
This is stacktrace if it helps:
Caused by: org.hibernate.exception.LockAcquisitionException: could not
execute query
at
org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:92)
at
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.loader.Loader.doList(Loader.java:2557)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
at
org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
at
org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
at
org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966)
... 3 lines ommited
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy103.store(Unknown Source)
... 2 lines ommited
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy102.sendFinaliseTransactionRequest(Unknown Source)
... 19 common frames omitted
Caused by: java.sql.SQLTransactionRollbackException: A lock could not be
obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SIPSAFQUEUE, (1,10)
Waiting XID : {20477, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20506, X}
Lock : ROW, SIPSAFQUEUE, (1,9)
Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20477, X}
. The selected victim is XID : 20477.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.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.EmbedResultSet.closeOnTransactionError(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
at
org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:191)
at
org.apache.commons.dbcp2.DelegatingResultSet.next(DelegatingResultSet.java:191)
at org.hibernate.loader.Loader.processResultSet(Loader.java:950)
at org.hibernate.loader.Loader.doQuery(Loader.java:921)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
... 58 common frames omitted
Caused by: org.apache.derby.iapi.error.StandardException: A lock could not
be obtained due to a deadlock, cycle of locks and waiters is:
Lock : ROW, SIPSAFQUEUE, (1,10)
Waiting XID : {20477, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20506, X}
Lock : ROW, SIPSAFQUEUE, (1,9)
Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
Granted XID : {20477, X}
. The selected victim is XID : 20477.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
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.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getRowFromResultSet(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ScalarAggregateResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown
Source)
... 66 common frames omitted
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
Re: deadlock on select count(*) from tableName
Posted by Rick Hillegas <ri...@gmail.com>.
Seems odd that a read-only query would grab exclusive, blocking locks.
Is there other outstanding work in those transactions?
On 3/29/18 2:42 AM, palo.liska wrote:
> Hi
> did someone saw deadlock during concurrent select count(*) from tableName?
>
> 09:15:41,959 [ERROR] o.h.e.j.s.SqlExceptionHelper:146 - A lock could not be
> obtained due to a deadlock, cycle of locks and waiters is:
> Lock : ROW, SIPSAFQUEUE, (1,10)
> Waiting XID : {20748, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20506, X}
> Lock : ROW, SIPSAFQUEUE, (1,14)
> Waiting XID : {20506, S} , EPS, /* SELECT COUNT(*) FROM SIPSAFQueue r */
> select count(*) as col_0_0_ from SIPSAFQueue sipsafqueu0_
> Granted XID : {20748, X}
> . The selected victim is XID : 20748.
>
>
>
> --
> Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
>