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
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.
