0

We have been facing StaleStateException in our code mostly with following error Hibernate - Batch update returned unexpected row count from update: 0 actual row count: 0 expected: 1.

This error wasn't seen when we were using Oracle 12c database but it started frequenting only after migration to PostgreSQL 12.1.

Going through the following article, I have found that:

  1. Oracle doesn't support repeatable reads - I suppose it means that the new value that would be read by Hibernate will be the latest committed value in the Oracle DB at that instant of time, rather than the committed value that is read during the beginning of the transaction. Am I right?

  2. Postgres supports repeatable reads - I suppose that it means if we read a value at the beginning of the transaction, we get a committed value at that instant of time and whenever we perform the read throughout the same transaction, then we get the same value until the transaction is committed, even if a new value is committed to the database after the initial read. Am I right again?

  3. I suppose that verification of expectations come after the commit. Isn't it?

So, my question is.. Does expectations of Hibernate differ in these cases (postgres, oracle)? i.e. the expected row counts has any thing to do with presence/absence of Repeatable Reads?

Are there any other possible causes for StaleStateException not occurring in Oracle but only in PostgreSQL?

JavaTechnical
  • 8,846
  • 8
  • 61
  • 97
  • I am confused: your question is all about the "repeatable read" isolation level, but then in the P.S. you claim you are using "read committed" which is something different in Postgres. Which level are you **really** using. The behaviour of repeatable read should be the same in both DBMS. –  May 04 '20 at 10:29
  • @a_horse_with_no_name In postgres, from what I saw it is read_committed – JavaTechnical May 04 '20 at 10:33
  • Your question sounds as if you are using (or trying to use) "repeatable read". If that is the case this would explain the behaviour: if you request "repeatable read" in Oracle you get "read committed", if you do that in Postgres you get "repeatable read" (which indeed behaves differently). So what **is** the isolation level you are requesting or initializing? –  May 04 '20 at 10:35
  • @a_horse_with_no_name We would want the same behavior as in Oracle read_committed. – JavaTechnical May 04 '20 at 10:37
  • Then set Postgres to read committed as well. –  May 04 '20 at 10:38
  • @a_horse_with_no_name Then what could be the reason(s) we are seeing StaleStateExceptions in Postgres which we haven't seen for years working with Oracle? – JavaTechnical May 04 '20 at 10:39
  • To me it sounds as if you do request repeatable read somewhere but never noticed in Oracle –  May 04 '20 at 10:41
  • @a_horse_with_no_name During the verification of outcomes, it seems like there is a failure with postgres because the expectations are not met. I suppose that if at all Hibernate is aware of the 'new' state of database before the verification of outcome, then the expectations would be different and the outcome would be satisfied. Isn't it? – JavaTechnical May 04 '20 at 10:49

0 Answers0