[
https://issues.apache.org/jira/browse/DERBY-5493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13241580#comment-13241580
]
Mike Matrigali commented on DERBY-5493:
---------------------------------------
Rick Hillegas (Commented) (JIRA) wrote:
> [
> https://issues.apache.org/jira/browse/DERBY-5493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13239613#comment-13239613
> ]
> Rick Hillegas commented on DERBY-5493:
> --------------------------------------
>
> Thanks for the quick analysis, Mike. Some responses follow:
>> o yet another system catalog, now we would have 2 for sequences
>
> More files are bad, I understand. But this would be similar to adding another
> index on syssequences, which is well within the bounds of what we do during a
> feature release.
>
We do continue to keep adding to problem here. This is yet another catalog
that is added to db, and everyone has to pay for it even if they
don't use the sequence feature. I know "1" more does not sound like a problem,
but I don't think it is the right direction for derby.
I know that there are applications out there that use 1000's of databases. We
have definitely gotten complaints from users in the past just for having 1
background thread per database. Every resource we add
on a per database level can get multiplied in some applications, and it
is sad to add these overheads when the application is not even using the
feature. I have the same problem with the new authorization work. I
understand that the system has current problems in this area (roles), but we
should not keep digging bigger holes.
>> o A hidden system catalog is going to present even more problems for users
>> if there is a problem with it. How do run maintenance on it if necessary.
>> How do you tell if there is a problem with it?
>
> Another valid question. I see it as being similar to the property
> conglomerate. How do we do maintenance on the property conglomerate? With
> SYSGHOST there is a diagnostic VTI for inspecting it and looking for
> problems, so I think we are in a better situation than we are with the
> property conglomerate.
>
And property conglomerate is a current problem, lets not add to it. At least
with property conglomerate the expectation is very low utilization and there
are just not that many properties for the database. I think the utilization of
this new catalog is unbounded, with size relative to
number of sequences, and maybe number of auto generated keys.
>> o I can't tell from the patch description, but previous description of this
>> approach seemed to be funneling all sequence work to a single
>> thread/transaction. This is the wrong architectural direction for derby, we
>> should always be looking to spread this work across many threads, as does
>> current implementation where work is done in the user thread.
>
> It's true that everything happens in a single transaction controller.
> However, a thread switch does not happen. Instead, a context manager is
> pushed and popped around the use of the shared transaction controller. It's
> also true that calls to the GhostController are synchronized, which will
> single-thread access to SYSGHOST. We could introduce a separate transaction
> controller per sequence and synchronize on those sequence-specific
> transaction controllers. The additional single-threading introduced by the
> current patch funnels all pre-allocation requests for all sequences through
> the same chokepoint. This might be an issue for an application which has
> small pre-allocation ranges and many sequences. I don't know how typical that
> usage will be or whether it is worth the extra complexity of more transaction
> controllers.
ok, sounds like the thread issue is not a problem. If we go with this
solution, i would like to understand why a shared single transaction is needed.
Seems like we should just create a new context as we need it.
>
>> o brand new boot time work. I would rather not add sql layer garbage
>> collection to the architecture. I know there are existing applications that
>> boot quite often, and any additional work at boot time will cause the
>> performance issues.
>
> I doubt that this extra boot cost will be measurable. However, we could add
> an initial tuple to SYSGHOST which would flag whether there was any boot-time
> work to do. This would reduce the extra boot cost to one page read.
>
I would rather see no boot time work, sounds like more complication for not
enough benefit.
I think sequences are new enough that we could implement the following as a fix:
o do all sequence work, including creating them in a nested transaction as we
did before or in this new context that you have created.
wait on all locks in this nested transaction and throw an error if we
timeout - don't escalate. The error should not be a generic lock timeout
error, it should include warnings that sequence generation can fail if users do
direct system catalog selects.
I think this would fix the correctness issue, and solve the "normal" lock
contention issue on sequence generation across threads. It would
mean sequence creation is autocommitted, which if I understand it is more in
keeping with the standard, and would avoid the problem of user
thread holding locks on system catalogs as part of creating sequences.
I think the new interface you have as part of your patch would make it even
less likely that users would interfere with sequences by doing
direct system catalog selects.
> Same value returned by successive calls to a sequence generator.
> ----------------------------------------------------------------
>
> Key: DERBY-5493
> URL: https://issues.apache.org/jira/browse/DERBY-5493
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2,
> 10.9.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_9
> Attachments: derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff
>
>
> The following script shows the same value being returned from a sequence
> generator by two successive NEXT VALUE FOR calls. Thanks to Knut for finding
> this:
> connect 'jdbc:derby:memory:db;create=true';
> create table t (x int);
> create sequence s;
> autocommit off;
> select count(*) from sys.syssequences with rs;
> values next value for s;
> drop table t;
> rollback;
> -- same value as previous call
> values next value for s;
--
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
