openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tobias Meyer (JIRA)" <>
Subject [jira] [Commented] (OPENJPA-2450) Option to disable execution of ALTER SEQUENCE...INCREMENT BY statement for sequences.
Date Tue, 12 Nov 2013 08:43:18 GMT


Tobias Meyer commented on OPENJPA-2450:

The execution of ALTER SEQUECE in the same transaction in which the application code persists
a new (the first) object can also lead to a (temporary) deadlock, when multiple interacting
applications use the same database sequence.

Suppose we have two applications (EARs) A and B running on two different servers. They both
use the same database and therefore the same sequence to persist objects and generate IDs.
Both applications get restartet (perhabs because a new release is being rolled out). Now,
when the first executed use case in application A involves persisting an object and then call
a web service of application B which also persists an object in the same database, this is
what happens:
1. application A persists its object
1.1. since it's the first usage of the sequence, OpenJPA executes the ALTER SEQUENCE statement
1.2. By executing the statement on the database application A automatically locks the internal
sequence table of the database (SYSIBM.SYSSEQUENCES in the case of DB2)
2. application A calls a web service of application B within the same transaction
3. application B persists an object
3.1. since it's the first usage of the sequence in application B, OpenJPA executes the ALTER
SEQUENCE statement
3.2. the execution of the statement on the database blocks, because application A still holds
the lock on it

Theoretically, this deadlock persists until application A gets some kind of timeout exception
from the web service call and rolls back its transaction, which typically happens after one
or two minutes. 

I'm not sure what exactly happend, but I've seen threads blocked for more than an hour caused
by locks held on DB2's internal sequence table. (see stacktrace below)

CONCLUSION: Executing ALTER SEQUENCE within the same transaction as the application uses to
persist its object might lead to tricky situations, even if the applications have the permissions
to alter the sequence.

{code}[10/9/13 13:41:02:036 CEST] 0000007c ThreadMonitor W   WSVR0605W: Thread "SIBJMSRAThreadPool
: 3" (00000092) has been active for 654385 milliseconds and may be hung.  There is/are 1 thread(s)
in total in the server that may be hung.
	at Method)
	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(
        at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(
	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(
	at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.getSequence(
	at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(
	at org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(
	at org.apache.openjpa.util.ImplHelper.generateValue(
	at org.apache.openjpa.util.ImplHelper.generateFieldValue(
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(
	at org.apache.openjpa.util.ApplicationIds.assign(
	at org.apache.openjpa.util.ApplicationIds.assign(
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(
	at org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(
	at org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(
	at org.apache.openjpa.kernel.StateManagerImpl.preFlush(
	at org.apache.openjpa.kernel.PNewState.beforeFlush(
	at org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(
	at org.apache.openjpa.kernel.BrokerImpl.flush(
	at org.apache.openjpa.kernel.BrokerImpl.flushSafe(
	at org.apache.openjpa.kernel.BrokerImpl.flush(
	at org.apache.openjpa.kernel.DelegatingBroker.flush(
	at org.apache.openjpa.persistence.EntityManagerImpl.flush(

> Option to disable execution of ALTER SEQUENCE...INCREMENT BY statement for sequences.
> -------------------------------------------------------------------------------------
>                 Key: OPENJPA-2450
>                 URL:
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: sql
>            Reporter: Heath Thomann
>            Assignee: Heath Thomann
>            Priority: Minor
>             Fix For: 2.1.2,, 2.2.3, 2.3.0, 2.4.0
>         Attachments: OPENJPA-2450-2.2.1.x.patch
> OpenJPA attempts to execute an ALTER SEQUENCE....INCREMENT BY SQL statement for a user
defined sequence.  This is done to ensure that the 'allocationSize' value defined by the entity's
sequence, or default value, matches the sequence defined in the database.  For example, with
an allocationSize of 1000 for a sequence named 'SEQ_JPASAMPLE', the following SQL will be
generated (the SQL might vary slightly depending on the databases): 
> If the user executing this command doesn't have permissions to execute the command, it
will fail and in turn OpenJPA will disable sequence caching.  User's have asked for a way
to disable this SQL statement and have full control over the sequence they define in their
domain model and its corresponding definition in the database.
> We can easily add a property to disable this SQL statement.  However, the onus is then
on the user to ensure that they keep in sync the 'allocationSize' in their entity's sequence
definition with the 'INCREMENT BY' for the corresponding sequence in the database.
> To disable this sequence I propose a new property on DBDictionary named 'disableAlterSeqenceIncrementBy',
with a default of false.  I also propose adding a warning message, logged once, when this
property is enabled and we avoid executing the SQL.
> Thanks,
> Heath Thomann

This message was sent by Atlassian JIRA

View raw message