db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6497) Deadlock with OnDelete="CASCADE"
Date Thu, 06 Mar 2014 12:26:43 GMT

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

Mime
View raw message