[jira] [Resolved] (DERBY-5443) reduce number of times sequence updater does it work on user thread rather than nested user thread.

["Rick Hillegas (Resolved) (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas resolved DERBY-5443.
----------------------------------

    Resolution: Fixed

Resolving this issue because a solution was implemented on DERBY-5493 and DERBY-5494.
                
> reduce number of times sequence updater does it work on user thread rather than nested user thread.
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5443
>                 URL: https://issues.apache.org/jira/browse/DERBY-5443
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.9.0.0
>            Reporter: Mike Matrigali
>            Priority: Minor
>         Attachments: blockingDDL.sql
>
>
> Currently the Sequence updater tries to do the system catalog update as part of the user thread, but in a nested user transaction.  When this works
> all is well as the nested user transaction is immediately committed and thus the throughput of all threads depending on allocating sequences is
> optimized.  
> In order to be able to commit the nested writable transaction independently the lock manager must treat the parent and nested transactions as two
> independent transactions and locks held by the parent will thus block the child.  And in effect any lock that is blocked by the parent is a deadlock,
> but the lock manager does not understand this relationship and thus only will timeout and not recognize the implicit deadlock.
> Only 2 cases come to mind of the parent blocking the child in this manner for sequences:
> 1) ddl like create done in transaction followed by inserts into the table requiring sequence update.
> 2) users doing jdbc data dictionary lookups in a multistatment transaction resulting in holding locks on the system catalog rows and subsequently
>     doing inserts into the table requiring sequence updates.
> The sequence updater currently never waits for a lock in the nested transaction and assumes any blocked lock is this parent deadlock case.  It
> then falls back on doing the update in tranaction and then the system catalog lock remains until the user transaction commits which could then
> hold hostage all other inserts into the table.  This is ok in the above 2 cases as there is not any other choice since the user transaction is already
> holding the system hostage.  
> The problem is the case where it was not a deadlock but just another thread trying to do the sequence update.  In this case the thread should
> not be getting locks on the user thread.  
> I am not sure best way to address this project but here are some ideas:
> 1) enhance lock manager to recognize the deadlock and then change to code to somehow do an immediately deadlock check for internal 
>     nested transactions, no matter what the system default is.  Then the code should go ahead and use the system wait timeout on this lock
>     and only fall over to using user transaction for deadlock (or maybe even throw a new "self deadlock" error that would only be possible for
>     internal transactions).
> 2) somehow execute the internal system catalog update as part of a whole different transaction in the system.   Would need a separate context.
>     Sort of like the background daemon threads.  Then no self deadlock is possible and it could just go ahead and wait.  The downside is that then
>     the code to "wait" for a new sequence becomes more complicated as it has to wait for an event from another thread.  But seems like it could
>     designed with locks/synchonization blocks somehow.  
> 3) maybe add another lock synchronization that would only involve threads updating the sequences.  So first an updater would request the
>     sequence updater lock (with a key specific to the table and a new type) and it could just wait on it.  It should never be held by parent
>     transaction.  Then it would still need the catalog row lock to do the update.  I think with proper ordering this would insure that blocking on
>     the catalog row lock would only happen in the self deadlock case.  
> Overall this problem is less important as the size of the chunk of sequence is tuned properly for the application, and ultimately best if derby
> autotuned the chunk.  There is a separate jira for auto tuning: DERBY-5295

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira