Thanks for that extra information, Brett. How many sessions run that
code? It will be interesting to figure out why the application doesn't
catch any errors when it tries to get a sequence number. I wonder if JPA
or something else in your stack is issuing DDL behind the scenes.
Thanks,
-Rick
On 9/20/17 7:23 AM, Bergquist, Brett wrote:
Doing none of that. The posted code is the only running application
and method accessing that database.
I don’t think that the database is being shutdown, even abnormally by
something internal as there is nothing in the derby.log to indicate
such and no indication of the database then being booted. Note that
these messages are coming out while the application is running, not at
the end. The application continues to run even after these message
come out.
Another strange observation is that the application does not report
any errors itself yet it does not create the required number of
entries either. The application is trying to initialize 500,000 rows
in the database, yet after it finishes and these errors reported, it
has somewhere around 499,7xx entries. The only way that I can see
that happen with the application not reporting any error yet not
creating the number of required entries is for the sequence to return
the same sequence numbers when the error is happening. If this were
happening, then the application would already see a row and not try to
create one again (the logic below in the application checks to see if
there is an entry already present).
Note that the application is batching up inserts and committing these
10 at a time. It is using the JPA transaction manager to perform the
transactions but that should not matter because in this standalone
case, that should in turn be using the native database transactions.
I will turn on statement logging to make sure and see if I can get
this into a smaller reproducible case.
*From:*Rick Hillegas [mailto:rick.hille...@gmail.com]
*Sent:* Tuesday, September 19, 2017 7:55 PM
*To:* derby-dev@db.apache.org; Bergquist, Brett
*Subject:* Re: Any idea why a sequence is getting "Derby could not
obtain the locks needed to release the unsused preallocated values for
the sequence" error
Hi Brett,
SequenceUpdater.clean() is the only method which raises that error.
The header comment on that method says that the condition can arise if
someone else is doing DDL while you are allocating sequence numbers,
or if someone else is scanning SYS.SYSSEQUENCES, or if the database
shuts down while sequence numbers are still being grabbed. Is your
application doing any of this at steady-state?
Thanks,
-Rick
On 9/19/17 3:04 PM, Bergquist, Brett wrote:
I have a simple application that is running with no other access
to the database. A sequence is defined
CREATE SEQUENCE LOG_ENTRY_SEQUENCE AS BIGINT START WITH 1 MINVALUE
1 CYCLE
The application is retrieving the next sequence number with a
“VALUES (NEXT VALUE FOR LOG_ENTRY_SEQUENCE)” statement and then
that sequence number is being used as an ID for the rows to be
inserted int another database table.
500,000 rows are going to be inserted. When this is run,
periodically there is an error being logged by derby.
Here is the derby.log
Tue Sep 19 15:55:06 EDT 2017:
Booting Derby version The Apache Software Foundation - Apache
Derby - 10.13.1.1
- (1765088): instance a816c00e-015e-9bb4-c83b-ffffb4d52705
on database directory
/opt/csemlogger/glassfish4/databases/csemloggerdb with class
loader sun.misc.Launcher$AppClassLoader@5c647e05
<mailto:sun.misc.Launcher$AppClassLoader@5c647e05>
Loaded from file:/opt/csemlogger/glassfish4/javadb/lib/derby.jar
<file:///%5C%5Copt%5Ccsemlogger%5Cglassfish4%5Cjavadb%5Clib%5Cderby.jar>
java.vendor=Oracle Corporation
java.runtime.version=1.8.0_40-b26
user.dir=/
os.name=SunOS
os.arch=amd64
os.version=5.10
derby.system.home=/opt/csemlogger/glassfish4/databases
Database Class Loader started - derby.database.classpath=''
Database Class Loader started -
derby.database.classpath='CSEM.csemderby'
Derby could not obtain the locks needed to release the unused,
preallocated values for the sequence 'CSEM'.'LOG_ENTRY_SEQUENCE'. As
a result, unexpected gaps may appear in this sequence.
Derby could not obtain the locks needed to release the unused,
preallocated values for the sequence 'CSEM'.'LOG_ENTRY_SEQUENCE'. As
a result, unexpected gaps may appear in this sequence.
Derby could not obtain the locks needed to release the unused,
preallocated values for the sequence 'CSEM'.'LOG_ENTRY_SEQUENCE'. As
a result, unexpected gaps may appear in this sequence.
Derby could not obtain the locks needed to release the unused,
preallocated values for the sequence 'CSEM'.'LOG_ENTRY_SEQUENCE'. As
a result, unexpected gaps may appear in this sequence.
Here is the code used. Note that it is using JPA, however the
LogEnty entity being inserted is not using the sequence. All
LogEntry ID assignment is done manually using the result of the
sequence number retrieved (the slot assignment).
private static void initializeLoggerEntryTable(SetupContext ctx,
long entryCount) {
EntityManager em = ctx.getEntityManager();
Query nextSeqQuery = em.createNativeQuery("VALUES (NEXT
VALUE FOR LOG_ENTRY_SEQUENCE)");
int j = 0;
// Create a transaction
ctx.getEntityManager().getTransaction().begin();
for (int i = 0; i < entryCount; i++) {
long nextSeq = ((Long)
nextSeqQuery.getSingleResult()).longValue();
long slot = nextSeq % entryCount;
// See if there is a LogEntry at this slot
LogEntry logEntry = em.find(LogEntry.class, slot);
if (null == logEntry) {
// There is not, so create one
logEntry = new LogEntry();
logEntry.setSlot(slot);
// Persist the chassis template
em.persist(logEntry);
}
// Commit the transaction
if (++j >= 10) {
em.getTransaction().commit();
em.clear();
em.getTransaction().begin();
j = 0;
}
}
ctx.getEntityManager().getTransaction().commit();
}
The sequence is not used outside of this code and there is no
other access to the database. I don’t understand why some
internal lock could not be obtained.
Any insight will be greatly appreciated.
------------------------------------------------------------------------
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is
intended only for the review of the party to whom it is addressed.
If you have received this transmission in error, please notify the
sender immediately and discard the original message and any
attachment(s).
------------------------------------------------------------------------
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is
intended only for the review of the party to whom it is addressed. If
you have received this transmission in error, please notify the sender
immediately and discard the original message and any attachment(s).
