[
https://issues.apache.org/jira/browse/OPENJPA-2450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13819926#comment-13819926
]
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 java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:161)
at java.net.SocketInputStream.read(SocketInputStream.java:132)
at com.ibm.db2.jcc.t4.z.b(z.java:199)
at com.ibm.db2.jcc.t4.z.c(z.java:289)
at com.ibm.db2.jcc.t4.z.c(z.java:402)
at com.ibm.db2.jcc.t4.z.v(z.java:1170)
at com.ibm.db2.jcc.t4.cb.b(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.sb.i(sb.java:135)
at com.ibm.db2.jcc.am.ho.gb(ho.java:2066)
at com.ibm.db2.jcc.am.io.pc(io.java:3450)
at com.ibm.db2.jcc.am.io.b(io.java:4240)
at com.ibm.db2.jcc.am.io.cc(io.java:722)
at com.ibm.db2.jcc.am.io.executeQuery(io.java:696)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1110)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:712)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:270)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:1116)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1806)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:258)
at
org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.getSequence(NativeJDBCSeq.java:311)
at
org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:235)
at
org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:202)
at
org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60)
at org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160)
at
org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:780)
at
org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493)
at
org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:764)
at
org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135)
at
org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600)
at
org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3023)
at org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44)
at
org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042)
at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2116)
at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2076)
at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:1847)
at
org.apache.openjpa.kernel.DelegatingBroker.flush(DelegatingBroker.java:1045)
at
org.apache.openjpa.persistence.EntityManagerImpl.flush(EntityManagerImpl.java:663)
at
com.ibm.ws.jpa.management.JPATxEmInvocation.flush(JPATxEmInvocation.java:255)
at
com.ibm.ws.jpa.management.JPAEntityManager.flush(JPAEntityManager.java:221)
{code}
> Option to disable execution of ALTER SEQUENCE...INCREMENT BY statement for
> sequences.
> -------------------------------------------------------------------------------------
>
> Key: OPENJPA-2450
> URL: https://issues.apache.org/jira/browse/OPENJPA-2450
> Project: OpenJPA
> Issue Type: Improvement
> Components: sql
> Reporter: Heath Thomann
> Assignee: Heath Thomann
> Priority: Minor
> Fix For: 2.1.2, 2.2.1.1, 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):
> ALTER SEQUENCE SEQ_JPASAMPLE INCREMENT BY 1000
> 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
(v6.1#6144)
