db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5443) reduce number of times sequence updater does it work on user thread rather than nested user thread.
Date Fri, 09 Mar 2012 20:34:57 GMT

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

Rick Hillegas commented on DERBY-5443:
--------------------------------------

Thanks for continuing to think about this issue, Mike. CURRENTVALUE is the only column of
SYSSEQUENCES which can change value today. I believe that other columns of SYSSEQUENCES will
change value if we implement ALTER SEQUENCE, but I don't think the length of those other volatile
columns can change. There is a case when SYSSEQUENCES.CURRENTVALUE can transition between
a null value and a valid BIGINT. Thanks.
                
> 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

        

Mime
View raw message