0

Environment: JPA 2.1, EclipseLink 2.6.3, SQL Server 2016

I want to use a field of type Timestamp for versioning and optimistic. I do not have option to use numeric column for versioning. My understanding is I just need to annotate the field with @Version and that all.

Database Table: token_t

token_id int PK
token_name varchar(100)
last_updt_dtm datetime

Database Record

Entity Class

@Entity
@Table(name = "token_t")
public class TokenAE {

  @Id
  @Column(name = "token_id")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int tokenId;

  @Column(name = "token_name")
  private String tokenName;

  @Version
  @Column(name = "last_updt_dtm")
  private Timestamp lastUpdtDtm;

  // getter/setter omitted to avoid cluttering
}

Test Method

@Test
public void optimisticLockingTest1() throws Exception {
    PersistenceHelper.getEntityManager().getTransaction().begin();

    TokenAE tokenAE = tokenDAO.getToken(616);
    assertNotNull("tokenAE is null", tokenAE);

    tokenAE.setTokenName("new token name");

    PersistenceHelper.getEntityManager().merge(tokenAE);

    PersistenceHelper.getEntityManager().getTransaction().commit();
}

Note - PersistenceHelper is just helper class instantiating entity manager

As you can see, I am loading TokenAE updating name and doing merge. I made sure that underlying database record is not changed. So I am expecting the merge/update should be successful but it always throws OptimisticLockException.

See the stacktrace below. I enabled JPA query/param logging and I can see the UPDATE query and bind parameters. The value of last_updt_dtm in WHERE clause [2018-07-17 22:59:48.847] matches exactly to the value in database record and this UPDATE query should return rowCount 1 and it should be successful.

I have no idea what going on here. Any help is greatly appreciated.

Exception Stacktrace

[EL Fine]: sql: 2018-07-18 23:54:13.137--ClientSession(1451516720)--Connection(1323996324)--Thread(Thread[main,5,main])--
UPDATE token_t SET token_name = ?, last_updt_dtm = ? WHERE ((token_id = ?) AND (last_updt_dtm = ?))
    bind => [new token name, 2018-07-18 23:54:13.35, 616, 2018-07-17 22:59:48.847]
[EL Warning]: 2018-07-18 23:54:13.286--UnitOfWork(998015174)--Thread(Thread[main,5,main])--Local Exception Stack: 
Exception [EclipseLink-5006] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.OptimisticLockException
Exception Description: The object [TokenAE [tokenId=616, tokenName=new token name, lastUpdtDtm=2018-07-18 23:54:13.35]] cannot be updated because it has changed or been deleted since it was last read. 
Class> com.test.TokenAE Primary Key> 616
    at org.eclipse.persistence.exceptions.OptimisticLockException.objectChangedSinceLastReadWhenUpdating(OptimisticLockException.java:144)
    at org.eclipse.persistence.descriptors.VersionLockingPolicy.validateUpdate(VersionLockingPolicy.java:790)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:1086)
    at org.eclipse.persistence.queries.UpdateObjectQuery.executeCommitWithChangeSet(UpdateObjectQuery.java:84)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:803)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1790)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitChangedObjectsForClassWithChangeSet(CommitManager.java:273)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:131)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4264)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:278)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commit(UnitOfWorkImpl.java:1113)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:137)
    at sunlife.us.dc.bds.token.domain.TokenDAOTest.optimisticLockingTest1(TokenDAOTest.java:39)
Rakesh Prajapati
  • 1,078
  • 8
  • 17
  • Are you able to query based on the date or does it have the same issue? – Chris Jul 19 '18 at 14:22
  • I suspect queries using dateTime won't work in general due to DB rounding - see https://dba.stackexchange.com/questions/108287/why-does-my-query-search-datetime-not-match . Try using the dateTime2 type instead and see if it helps. – Chris Jul 19 '18 at 14:31
  • Thanks @Chris for pointing out toward precision/round-up of datetime datatype in SQL server. If I take UPDATE query from stacktrace above, replace params with exact value as bind variables from stacktrace, it actually updates one record successfully. So if precision was causing the issue, manually UPDATE query should also not update any record, which is not the case. I still feel like you have the point but just need facts/logic to prove it. – Rakesh Prajapati Jul 20 '18 at 02:11
  • Other point is round up of timestamp happens during save. The value of timestamp ( 2018-07-17 22:59:48.847 ) in WHERE condition is retrieved from database it self which is already rounded up. So even if any round up is happening in UPDATE query in WHERE clause it should not impact. Which means timestamp round up of '2018-07-17 22:59:48.847' will be exactly same value. See output of this query. `SELECT '2018-07-17 22:59:48.847', convert(datetime, '2018-07-17 22:59:48.847');` **Result** `------------------------------- 2018-07-17 22:59:48.847 2018-07-17 22:59:48.847 ` – Rakesh Prajapati Jul 20 '18 at 02:14
  • I missed how you were testing. Did you try executing find by id, then execute a JPQL query for that same entity using the lastUpdtDtm from the entity? You might also try with an entity that you persist and commit during the test, and so exists in the shared cache rather then a value read back from the database. This would prove something is happening on the date read from the DB. – Chris Jul 20 '18 at 15:07
  • Thanks @Chris for your thoughts on this. When I first query by id and then do HQL query using lastUpdtDtm from the entity object (queried first) surprisingly it does not find the record and I get NoResultException. If I take SQL SELECT query (generated from HQL) from JPA log, it does return record. Very confused at this point, what is going wrong inside JPA. – Rakesh Prajapati Jul 23 '18 at 02:24
  • What happens when inserting a new record? Can you immediately query using the lastUpdtDtm after calling persist then flush? This would help narrow down if it only occurs with timestamps read in from the database. As mentioned, you should also try the dateTime2 type in the database to see if it correct the issue. – Chris Jul 23 '18 at 16:12
  • @Chris If I add a new entity, persist then flush and right after that (within same method and transaction) if I do `CriteriaQuery` using tokenName (string property) it finds it but if I do CriteriaQuery by lastUpdtDtm it does not find it. Same behavior with `HQL`. Also I noticed in JPA log that it is executing SELECT against database for both CriteriaQuery and HQL, but I was expecting that since the entity is already in Session cache and it should not hit Database. I do not have option to change datatype to `datetime2` – Rakesh Prajapati Jul 24 '18 at 21:41
  • By HQL you mean JPQL I hope. But if it can't look it up using the date right after persist, then it is purely a DB rounding issue where it is taking in a date and storing it differently so that the same date can't be used for a search. – Chris Jul 29 '18 at 14:26
  • I met the same issue today, do you have a solution for this afterwards? Thanks in advance. – cyper Mar 10 '23 at 07:46

0 Answers0