db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
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
Date Wed, 20 Sep 2017 14:23:26 GMT
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.hillegas@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:///\\opt\csemlogger\glassfish4\javadb\lib\derby.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).

Mime
View raw message