[
https://issues.apache.org/jira/browse/DERBY-6497?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13922416#comment-13922416
]
Dag H. Wanvik commented on DERBY-6497:
--------------------------------------
Would you be able to attach the repro code to this issue? That will make it
quicker for one of the devs to check this one out. Thanks.
> Deadlock with OnDelete="CASCADE"
> --------------------------------
>
> Key: DERBY-6497
> URL: https://issues.apache.org/jira/browse/DERBY-6497
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.1.1
> Environment: Tested in Linux, Window, Unix with Java 7 1.7.0_40
> Reporter: Billow Gao
> Priority: Critical
>
> We saw error like:
> java.sql.SQLTransactionRollbackException: A lock could not be obtained due to
> a deadlock, cycle of locks and waiters is:..
> We had two tables, one is parent, the other one is a child with foreign key
> on parent's id column. We set OnDelete='CASCADE' to delete related records
> from the child table.
> The problem: if we have one thread inserting to parent/child table, another
> thread deleting from the parent table, then we will hit the deadlock. To
> confirm, I wrote an unit to verify and it could reproduce the issue. If we
> remove the OnDelete='CASCADE', and delete record from the child table, then
> the issue is gone.
> When I turn on the query plan log, we found out:
> when it's deleting records from child table, it's using table lock.
> Index Scan ResultSet for childTable using index On Foreign Key at
> serializable isolation level using exclusive table locking chosen by the
> optimizer"
> We had index on the id field so it should be using index.
> But it picked the scan with table lock which caused the dead lock.
>
> 1. working case Without OnDelete='CASCADE'
> delete from childTable where id IN (SELECT id from parentTable where
> msgId='xxxx')
> delete from parentTable where msgId='xxx'
> It's using row locking, and worked fine
> 2. deadlock case WITH OnDelete='CASCADE'
> delete from parentTable where msgId='xxx'
> It's using table locking on the childTable which introduced the deadlock.
> Our unit test setup:
> Thread 1:
> 1.1 insert into parentTable ...
> 1.2 insert into childTable....
> Thread 2:
> 2.1 When OnDelete='CASCADE' is set
> Delete from parentTable where msgId='xxx'
> => DEAD lock
> 2.2 When OnDelete='CASCADE' is NOT set
> delete from childTable where id IN (SELECT id from parentTable
> where msgId='xxxx')
> delete from parentTable where msgId='xxx'
> => working
> {code}
> java.sql.SQLTransactionRollbackException: A lock could not be obtained due to
> a deadlock, cycle of locks and waiters is:
> Lock : ROW, parentTable, (2,6)
> Waiting XID : {668, U} , APP, delete from parentTable where msgId = ?
> Granted XID : {669, X}
> Lock : TABLE, childTable, Tablelock
> Waiting XID : {669, IS} , APP, insert into childTable (id, recipient)
> values (?, ?)
> Granted XID : {668, X}
> . The selected victim is XID : 668.
> 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.execute(Unknown
> Source)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
> ........
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
> 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, parentTable, (2,6)
> Waiting XID : {668, U} , APP, delete from parentTable where msgId = ?
> Granted XID : {669, X}
> Lock : TABLE, childTable, Tablelock
> Waiting XID : {669, IS} , APP, insert into childTable (id, recipient)
> values (?, ?)
> Granted XID : {668, X}
> . The selected victim is XID : 668.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 23 more
> Caused by: ERROR 40001: A lock could not be obtained due to a deadlock, cycle
> of locks and waiters is:
> Lock : ROW, parentTable, (2,6)
> Waiting XID : {668, U} , APP, delete from parentTable where msgId = ?
> Granted XID : {669, X}
> Lock : TABLE, childTable, Tablelock
> Waiting XID : {669, IS} , APP, insert into childTable (id, recipient)
> values (?, ?)
> Granted XID : {668, X}
> . The selected victim is XID : 668.
> 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.services.locks.ConcurrentPool.lockObject(Unknown Source)
> at
> org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
> Source)
> at org.apache.derby.impl.store.access.heap.HeapScan.fetchNext(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.collectAffectedRows(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.open(Unknown Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> ... 17 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
