0

We have an Informix DB table used by multiple tech stacks like ESQL/C, Java (JPA), db batch updates. The below is the production issue that keeps bugging us (not often, though).

Caused by: org.hibernate.exception.GenericJDBCException: could not update: 
Caused by: java.sql.SQLException: Could not do a physical-order read to fetch next row.
Caused by: java.sql.SQLException: ISAM error: record is locked.

I have removed some stacktrace for brevity.

After initial analysis, we found out ESQL/C and Java were trying to update the record at the same time. ESQL/C had locked the record JPA was trying to flush. Our JPA entity have @Version and is designed for optimistic locking.

Now, my question is, how does JPA behave when its entity is designed for optimistic locking, but the record it's trying to update is locked by an entirely different application at DB level.

  • DB specifications: Page locked, NO WAIT - both default.
  • JPA specification: READ_UNCOMMITTED, wait timeout - 60 sec.

My Thoughts:

  1. Will setting table's lock mode to WAIT solve it?
  2. How will this concur with the Optimistic locking set at JPA level, which are designed to never involve lock at all.
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
krisp
  • 73
  • 1
  • 7
  • It should have given you StaleObjecException ..it seems that the other application is not updating the version column in DB while locking on the record ... – Zulfi Jun 29 '16 at 16:33
  • What do you mean by 'solve' it? This has nothing to do with Optimistic locking, but what do you want to happen when JPA or any other application tries to read a row that is locked by some other application. – Chris Jun 29 '16 at 18:18
  • How big are the rows? Would row locking provide any help? (Thinking: you might be running into ESQL/C updating row 1 on page N while JPA is updating row 2 on page N. With page locking, that would be a conflict; with row locking, it wouldn't be a conflict.) If the rows are big enough that you have only one row per page (e.g. row size of 1 KiB on a system with 2 KiB pages), this is immaterial. I think the ESQL/C locking mode to WAIT or NOT WAIT or WAIT [time] is set per session (SET LOCK MODE TO WAIT 60), not a database attribute per se. – Jonathan Leffler Jun 29 '16 at 18:45

0 Answers0