Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 27 Mar 2012 15:20:28 +0000 (UTC)
From: "Rick Hillegas (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1872816479.23689.1332861628050.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1424585030.4824.1317678574246.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-5443) reduce number of times sequence
 updater does it work on user thread rather than nested user thread.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

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

Both approaches discussed on this issue have some messy problems. I have investigated the approach of introducing an invisible conglomerate with a dedicated transaction controller. With that approach it is easier for me to reason about the completeness of the solution. Based on that approach, I have posted a candidate fix for the correctness problems involving sequences. That fix is attached to DERBY-5493.

There are still corresponding correctness problems affecting identity columns. In addition, identity pre-allocation does not seem to work as well as sequence pre-allocation, perhaps because of the more intricate code paths involving SYSCOLUMNS. These pre-allocation problems have surfaced in NsTest and need to be addressed for 10.9.

Here are some possible ways to tackle these issues:

1) Back out identity pre-allocation, restoring the 10.8 behavior. Identity columns would go back to low concurrency and their correctness problems would not be addressed. I am not sure how important the correctness problems are--no real user has complained about this issue yet. We would tell users that identity columns are appropriate for low concurrency applications and that high concurrency applications should use sequences instead. Backing out pre-allocation would not have to be a permanent solution. We could spend a year studying the behavior of sequences and then decide whether we want to revisit pre-allocation for identity columns in 10.10.

2) Apply the DERBY-5493 solution to identity columns. This will fix the correctness problems and make identity generation highly concurrent. This solution comes in two variants:

a) Easy: Require that autocommit be on in order to reset the value of an identity generator. This would be a backward compatibility issue for legacy applications which upgrade to 10.9. However, I think this backward compatibility is not likely to cause much disruption.

b) Complex: We could build special logic to allow resetting identity generators when autocommit is not on. I'm not keen on adding that complexity for what seems to me to be an edge-case usage.

3) Continue brainstorming solutions which involve pushing/popping isolation levels around queries involving SYSCOLUMNS.

4) Something else...

I am willing to do (1) or (2a) myself. I might consider (2b) if we can think up a way to make it less complex. I don't understand options (3) and (4) and would expect that someone else would specify and implement them.

Thanks,
-Rick

                
> 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