db-derby-dev mailing list archives

Re: [jira] [Commented] (DERBY-5493) Same value returned by successive calls to a sequence generator.

Date

Thu, 29 Mar 2012 19:44:52 GMT

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 updates.
> Thanks,
> -Rick
>
>
>> 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
>
>
>