[
https://issues.apache.org/jira/browse/DERBY-5443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13225855#comment-13225855
]
Mike Matrigali commented on DERBY-5443:
---------------------------------------
I have been thinking about this some more, and am unsure if you are trying to
solve more problems than are described in the jira description.
If an acceptable goal is to solve the problem of multiple internal tranaction
conflicts leading to work being done on user transaction then
I think that I would recommend option 3. Mostly because I understand how to
do it. Option 1 I think is the most elegant but I was not comfortable with the
lock manager years ago before it was heavily updatated, so now am even more
unsure of enhancements to it.
For option 3 I can give even more detail if someone can just point me at the
line of code in language layer that is calling store in the internal
transaction. It is probably a conglomerate upate call. I think all that needs
to happen is for there to be an enhancement to that interface. For
this description lets say it is called internal_update(). I don't think it
even needs new arguments. Then store can do everything else.
Store will just make one additional lock call before doing the normal row lock.
I think the lock call change is simply coming up with something
to lock that is not ROW, lets call it INTERNAL_XACT_ROW. This lock would
always be requested before the regular row lock, and be held until
after internal xact commits or aborts. It would only conflict with other
INTERNAL_XACT_ROW locks, and for this usage it would always be requested in
exclusive mode. I think I would make the interface fail and ASSERT if it were
ever called not from an internal transaction.
I have not looked at code so estimates could be off, but I think doing the
store side as described would be days rather than weeks. A problem
could be waiting on this new lock vs. not waiting on the others but I think
that is easy once you are at the store level making the lock call. A question
for the interface is what amount of time should it wait, especially in cases
where user has altered default lock timeouts. Should we
do something different in the internal transaction or just use defaults (not
using the defaults is probably more work and might need lock manager
changes).
If anyone is interested in working on this I would be willing to implement the
store side of this, I would need help in testing and identifying and
implementing the SQL layer changes.
> 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
