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 Thu, 08 Mar 2012 18:49:59 GMT

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

Rick Hillegas commented on DERBY-5443:

Here are some more thoughts about item (G) in my previous proposal. That is the item which
causes a backward-incompatibility.

The problem which (G) tries to address is this:

* User A alters a table to change the current value of its identity counter from X to Y.
* Without committing, user A then continues to insert into the table.
* Meanwhile, user B continues to insert into the table.
* Then user A rolls back the transaction, rolling back the change to the identity counter
as well as the subsequent inserts. 

Today, B is blocked from inserting into the table until A commits or rolls back. I am attaching
blockingDDL.sql, a script which shows this behavior.

Here is a refinement to my previous proposal, which preserves the existing behavior and removes
the backward-incompatibility:

This refinement assumes that we can expose the transaction id of the transaction currently
in-flight in a TransactionController. The javadoc for TransactionId says that the TransactionId
should never escape the Raw Store. The reason given is that these ids are not stable across
database reboots and should never be persisted by logic outside the Raw Store. What we need,
however, is just an in-memory TransactionId which we won't persist and which will disappear
if the database reboots. Hopefully, it will be ok to expose the transaction id for this purpose.
Perhaps we could wrap TransactionId in some class which has a useful equals() method but whose
writeExternal() method writes nothing; that might clarify that the handle can't be persisted
but can be used in-memory.

The refinement makes the following other changes:

I) SYSCOLUMNS.CURRENTVALUE was made redundant by the previous proposal. Now I have found a
use for this column. Instead of holding the next identity value, it will hold a schema version

II) We expand the contents of the Formatable column of INVISIBLE_CONGLOMERATE. The object
will have these fields:

  TempCurrentValue (normally this is null, it only has meaning if the ALTER TABLE statement
has not been committed/rolled-back)

III) The execution logic of "ALTER TABLE ... ALTER COLUMN ... RESTART WITH" does the following:

  a) Bumps the schema version number in SYSCOLUMNS.CURRENTVALUE
  b) Throws away the corresponding SequenceGenerator
  c) Writes the reset identity value into TempCurrentValue using IC_TRAN.
  d) Remembers the TransactionId of the execution transaction

IV) When a connection needs to get the next identity value, it does the following:

  a) If the currently executing TransactionId is the remembered one, then don't try to get
a SequenceGenerator. Instead, just bump TempCurrentValue.
   b) Otherwise, try to get the SequenceGenerator. This will block all other transactions
until the remembered TransactionId commits or rolls back.

V) When the new SequenceGenerator is created (this happens after remembered TransactionId
is committed or rolled back), we forget the remembered TransactionId (if any) and check to
see if SchemaVersionNumber matches SYSCOLUMNS.CURRENTVALUE.

  a) If so, there is nothing special to do.
  b) Otherwise, we copy SYSCOLUMNS.CURRENTVALUE into SchemaVersionNumber, recompute EndOfPreallocationRange
based on TempCurrentValue, and null out TempCurrentValue. Note that the creation of a SequenceGenerator
(as opposed to the steady-state use of an existing SequenceGenerator) does work in two transactions:
the current execution transaction and IC_TRAN.

> 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:
>            Reporter: Mike Matrigali
>            Priority: Minor
> 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
> 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


View raw message