You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Billow Gao (JIRA)" <ji...@apache.org> on 2014/03/05 21:20:42 UTC
[jira] [Created] (DERBY-6497) Deadlock with OnDelete="CASCADE"
Billow Gao created DERBY-6497:
---------------------------------
Summary: 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)