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
