db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.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 Tue, 19 Sep 2017 23:54:40 GMT
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
>
> Loaded from 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).



Mime
View raw message