db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3961) Deadlock detection fails for InternalTransaction
Date Wed, 10 Jun 2009 06:37:08 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3961?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12717943#action_12717943
] 

Kristian Waagan commented on DERBY-3961:
----------------------------------------

It is not immediately clear to me that this report is a duplicate. Has this been investigated?
Is it correct that these transactions time out instead of being detected as a deadlock?
Too bad we don't have a reproduction...

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


Mime
View raw message