[
https://issues.apache.org/jira/browse/DERBY-3961?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali resolved DERBY-3961.
-----------------------------------
Resolution: Duplicate
Fix Version/s: 10.5.1.2
> Deadlock detection fails for InternalTransaction
> ------------------------------------------------
>
> Key: DERBY-3961
> URL: https://issues.apache.org/jira/browse/DERBY-3961
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.4.2.0
> Environment: Windows Vista
> Reporter: Jeff Stuckman
> Fix For: 10.5.1.2
>
>
> It is easy to cause a deadlock which is not detected by the deadlock
> detection algorithm. The transactions fail due to a lock timeout , possibly
> because a transaction of type InternalTransaction is part of the cycle.
> Resolving issue DERBY-2991 will make it more difficult to cause such
> deadlocks, but it will still be possible.
> My test case creates two threads and executes the following statements until
> they deadlock against each other:
> UPDATE urls SET jobflag=? WHERE urlid=?
> SELECT urlid,url,expectation FROM urls WHERE site=?
> The test eventually deadlocks with the following transaction and lock table
> contents:
> XID TYPE MODE TABLENAME LOCKNAME STATE TABLETYPE LOCKCOUNT INDEXNAME
> 2217109 ROW S URLS (13,1) GRANT T 1 FINDURLBYSITEANDJOB
> 2217114 ROW X URLS (13,1) WAIT T 0 FINDURLBYSITEANDJOB
> 2217113 ROW S URLS (15,1) GRANT T 1 FINDURLBYSITEANDJOB
> 2217113 ROW X URLS (3,132) GRANT T 3 null
> 2217109 ROW S URLS (3,132) WAIT T 0 null
> 2217109 TABLE IS URLS Tablelock GRANT T 2 null
> 2217113 TABLE IX URLS Tablelock GRANT T 4 null
> 2217114 TABLE IX URLS Tablelock GRANT T 1 null
> 2217113 ROW S URLS (6,1) GRANT T 1 SQL081111021116970
> XID GLOBAL_XID USERNAME TYPE STATUS FIRST_INSTANT
> SQL_TEXT
> 2217115 null APP UserTransaction IDLE null select * from
> SYSCS_DIAG.TRANSACTION_TABLE
> 2217114 null APP InternalTransaction ACTIVE null UPDATE urls
> SET jobflag=? WHERE urlid=?
> 2217113 null APP UserTransaction ACTIVE (526,52925) UPDATE
> urls SET jobflag=? WHERE urlid=?
> 2069160 null null SystemTransaction IDLE null null
> 2217109 null APP UserTransaction ACTIVE null SELECT
> urlid,url,expectation FROM urls WHERE site=?
> Here is what I think is happening:
> 1. The SELECT statement begins to execute and the cursor is stepping through
> the result set. The results are derived from index FINDURLBYSITEANDJOB as
> expected.
> 2. The UPDATE statement begins to execute. The row to be updated is the row
> immediately after the SELECT statement's cursor. The row is locked and
> updated.
> 3. The UPDATE statement must perform index maintenance (tree rebalancing or
> similar?). This apparently causes an InternalTransaction to be created. It
> then must lock the row that the SELECT statement's cursor is currently
> occupying. It cannot do this, so the transaction waits.
> 4. The SELECT statement is ready to advance the cursor. However, it cannot
> advance the cursor because the UPDATE statement has locked the next row. The
> transaction waits.
> The result: Transaction 2217113 waits for the "nested transaction" 2217114 to
> complete. 2217114 waits for 2217109 to release its lock. 2217109 waits for
> 2217113 to release its lock. We have a cycle and a deadlock. The transactions
> time out with "A lock could not be obtained within the time requested",
> apparently because the dependency between transactions 2217113 and 2217114 is
> not detected.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
