[ https://issues.apache.org/jira/browse/OPENJPA-2476?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16786450#comment-16786450 ]
David Campbell commented on OPENJPA-2476: ----------------------------------------- Is this problem also able to occur if you have a Timestamp in the DB and a java.util.Date to represent it instead of java.sql.Timestamp? java.sql.Timestamp is a thin layer over java.util.Date. > OptimisticLockEx due to rounding of a Timestamp (either by OJ, or the DB) > ------------------------------------------------------------------------- > > Key: OPENJPA-2476 > URL: https://issues.apache.org/jira/browse/OPENJPA-2476 > Project: OpenJPA > Issue Type: Bug > Components: jdbc > Affects Versions: 2.1.0, 2.2.0, 2.2.1.1, 2.3.0, 2.4.0 > Reporter: Heath Thomann > Assignee: Heath Thomann > Priority: Minor > Fix For: 2.1.2, 2.2.1.1, 2.2.3, 2.4.0 > > > HI! I'm hitting a rather interesting issue and unfortunately to describe it > is going to be a bit lengthy......so settle in. :) Here is the exception: > <openjpa-2.1.2-SNAPSHOT-r422266:1548248 fatal store error> > org.apache.openjpa.persistence.RollbackException: Optimistic locking errors > were detected when flushing to the data store. The following objects may > have been concurrently modified in another transaction: > [hat.entities.VersionTSEntity-1390400526251] > at > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:593) > at hat.tests.PMR57956Test.testupdate(PMR57956Test.java:94) > ........ > Caused by: <openjpa-2.1.2-SNAPSHOT-r422266:1548248 nonfatal store error> > org.apache.openjpa.persistence.OptimisticLockException: Optimistic locking > errors were detected when flushing to the data store. The following objects > may have been concurrently modified in another transaction: > [hat.entities.VersionTSEntity-1390400526251] > at > org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2310) > at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2158) > at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2056) > at > org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1974) > at > org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81) > at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1498) > at > org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933) > at > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:569) > ... 21 more > Caused by: <openjpa-2.1.2-SNAPSHOT-r422266:1548248 nonfatal store error> > org.apache.openjpa.persistence.OptimisticLockException: An optimistic lock > violation was detected when flushing object instance > "hat.entities.VersionTSEntity-1390400526251" to the data store. This > indicates that the object was concurrently modified in another transaction. > FailedObject: hat.entities.VersionTSEntity-1390400526251 > at > org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.populateRowManager(AbstractUpdateManager.java:183) > at > org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:97) > at > org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:78) > at > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:742) > at > org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131) > ... 28 more > To see how this occurs, lets look at the test. Here is the entity: > @Entity > public class VersionTSEntity implements Serializable { > @Id > private Long id; > > @Version > private Timestamp updateTimestamp; > private Integer someInt; > And here is my test, with pertinent in-line comments: > //This loop is necessary since we need a timestamp which has been //rounded > up by either OJ, or the database....usually 1 or 2 loops will cause //the > exception. > for (int i = 0; i < 1000; i++) { > // Find an existing VersionTSEntity: > VersionTSEntity t = em.find(VersionTSEntity.class, id); > tx.begin(); > // Update/dirty VersionTSEntity: > t.setSomeInt(t.getSomeInt() + 1); > t = em.merge(t); > tx.commit(); > //If this clear is removed the test works fine. > em.clear(); > // Lets say at this point the 'in-memory' timestamp is: > // 2014-01-22 07:22:11.548778567. What we actually sent to the DB (via > // the previous merge) is by default rounded (see > // DBDictionary.setTimestamp) to the nearest millisecond on Oracle (see > // DBDictionary.datePrecision) and nearest microsecond on DB2 (see > // DB2Dictionary.datePrecision) when sending the value to the db. > // Therefore, what we actually send to the db is: > // 2014-01-22 07:22:11.548779 (for DB2) or 2014-01-22 07:22:11.549 (for > // Oracle). > // Notice in either case we rounded up...keep this in mind as it will be > // important for the remainder of the test...... > // now, do a merge with the unchanged entity > tx = em.getTransaction(); > tx.begin(); > t = em.merge(t); > > //This 'fixes' the issue: > //em.refresh(t); > > // Here is where things get interesting.....an error will happen here when > // the timestamp has been rounded up, as I'll explain: > // As part of this merge/commit, we select the timestamp from the db to get > // its value (see method checkVersion below), i.e: > // 'SELECT t0.updateTimestamp FROM VersionTSEntity t0 WHERE > // t0.id = ?'. > // We then compare the 'in-memory' timestamp to that which we got back > // from the DB, i.e. on DB2 we compare: > // in-mem: 2014-01-22 07:22:11.548778567 > // from db: 2014-01-22 07:22:11.548779 > // Because these do not 'compare' properly (the db version is greater), we > // throw the OptimisticLockEx!! > // For completeness, lets look at an example where the timestamp is as > // follows after the above update: 2014-01-22 07:22:11.548771234. We > // would send to DB2 the following value: 2014-01-22 07:22:11.548771. > // Then, as part of the very last merge/commit, we'd compare: > // in-mem: 2014-01-22 07:22:11.548771234 > // from db: 2014-01-22 07:22:11.548771 > // These two would 'compare' properly (the db version is lesser), as such > // we would not throw an OptLockEx and the test works fine. > > tx.commit(); > To see where/why the OptLockEx is thrown, we need to look here in > VersionColumnStrategy (see //HAT:): > public boolean checkVersion(OpenJPAStateManager sm, JDBCStore store, > boolean updateVersion) throws SQLException { > Column[] cols = vers.getColumns(); > Select sel = store.getSQLFactory().newSelect(); > sel.select(cols); > sel.wherePrimaryKey(sm.getObjectId(), vers.getClassMapping(), > store); > // No need to lock version field (i.e. optimistic), except when > version update is required (e.g. refresh) > JDBCFetchConfiguration fetch = store.getFetchConfiguration(); > if (!updateVersion && fetch.getReadLockLevel() >= > MixedLockLevels.LOCK_PESSIMISTIC_READ) { > fetch = (JDBCFetchConfiguration) fetch.clone(); > fetch.setReadLockLevel(MixedLockLevels.LOCK_NONE); > } > Result res = sel.execute(store, fetch); > try { > if (!res.next()) > return false; > //HAT: here is where we get the timestamp from the db, and > 'compare' them: > Object memVersion = sm.getVersion(); > Object dbVersion = populateFromResult(res, null); > > boolean refresh = compare(memVersion, dbVersion) < 0; > if (updateVersion) > sm.setVersion(dbVersion); > return !refresh; > } finally { > res.close(); > } > } > Keep in mind here that it seems like the scenario is a bit unique....in other > words, if I remove the 'em.clear', we do not deem it necessary to call the > 'checkVersion' method (thus we don't go back to the DB to get the timestamp). > So it seems that there are two unique things causing us to hit the issue: 1) > merge/clear/merge which causes a 'checkVersion', 2) rounding of timestamps. > I added the 'clear' in my test because it is at this point the customer > returns the entity to the caller of an EJB....the caller then sends the > entity back to the EJB to merge it..... > Now, there are two existing fixes to this issue on DB2, and one fix for this > on Oracle. As you can see in my test above, if we 'em.refresh' the entity > before commit, the in-memory value will match that of the db value. This fix > works regardless of whether or not DB2 or Oracle is used (the customer > doesn't really like this idea though). The next fix, which only works on > DB2, is to set DBDictionary.datePrecision=1 (nanosecond precision). This > still doesn't resolve the issue on Oracle. To see why this is the case, we > have to look at the way DB2 and Oracle appear to handle timestamp precision. > Both seem to use microsecond precision by default (for both I think you have > the option to go up to nanosecond). Where they differ is that for precision > greater than microsecond, DB2 truncates (no rounding) to a microsec. > However, Oracle rounds to the microsec. So, lets take the value 2014-01-22 > 07:22:11.548778567. If datePrecision=1 we'd send to the DB 2014-01-22 > 07:22:11.548778567. On DB2, it would truncate the value to 2014-01-22 > 07:22:11.548778, but on Oracle, it would round to: 2014-01-22 > 07:22:11.548779. So, for DB2, the compare would be: > in-mem: 2014-01-22 07:22:11.548778567 > from db: 2014-01-22 07:22:11.548778 > This compare passes, so all is well. For Oracle, we'd compare: > in-mem: 2014-01-22 07:22:11.548778567 > from db: 2014-01-22 07:22:11.548779 > This compare fails, thus the OptLockEx. I think setting datePrecision=1 is a > fair recommendation/fix for DB2 (others?)......but for Oracle (others?) a fix > is needed. My plan is to provide a fix which rounds the in-memory version of > the timestamp to the value set in DBDictionary.datePrecision. So the > exception can still occur if a user has not set datePrecision to match the > precision of the Timestamp defined in the database. > Thanks, > Heath Thomann -- This message was sent by Atlassian JIRA (v7.6.3#76005)