subject:"Any idea why a sequence is getting \"Derby could not obtain the locks needed to release the unsused preallocated values for the sequence\" error"

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

2017-09-20 Thread Rick Hillegas

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-b4d52705

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

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

2017-09-20 Thread Bergquist, Brett

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-b4d52705
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;

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

2017-09-19 Thread Rick Hillegas


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-b4d52705

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).

Any idea why a sequence is getting "Derby could not obtain the locks needed to release the unsused preallocated values for the sequence" error

2017-09-19 Thread Bergquist, Brett

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-b4d52705
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).

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

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

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

Any idea why a sequence is getting "Derby could not obtain the locks needed to release the unsused preallocated values for the sequence" error

4 matches

Site Navigation

Mail list logo

Footer information