When isolation is Serializable Snapshot Isolation (SSI) the application must be prepared to retry rejected transactions. Quote from postgresql documentation: "It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.".
Spring retry could be the "generalized way" but based on what type of exception ?
With JDBC the SQLSTATE 40001 is translated in a CannotAcquireLockException.
With Jpa and postgresql it is depends:
- if the commit is rejected (
transactionManager.commit(tx)) a JpaSystemException (Unable to commit against JDBC Connection) is thrown. But the root cause of this exception is a SQLException with SQLSTATE 40001 .
- if a statement is rejected the
SQLException thrown by postgresql has SQLSTATE 40001 and is translated into a CannotAcquireLockException.
An option is to test the root exception:
boolean serializationFailure = NestedExceptionUtils.getRootCause(exception) instanceof SQLException se && "40001".equals(se.getSQLState());
At a high level the common denominator is DataAccessException, but then any data access problem would triggers a retry. Bad sql queries should result in test failure during dev but still, a specific SerializationFailureException would help. I don't know if it is realistic, is state 40001 really reliable across databases ? How about NoSQL ACID database ?
When isolation is Serializable Snapshot Isolation (SSI) the application must be prepared to retry rejected transactions. Quote from postgresql documentation: "It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.".
Spring retry could be the "generalized way" but based on what type of exception ?
With JDBC the SQLSTATE 40001 is translated in a
CannotAcquireLockException.With Jpa and postgresql it is depends:
transactionManager.commit(tx)) aJpaSystemException(Unable to commit against JDBC Connection) is thrown. But the root cause of this exception is aSQLExceptionwith SQLSTATE 40001 .SQLExceptionthrown by postgresql has SQLSTATE 40001 and is translated into aCannotAcquireLockException.An option is to test the root exception:
At a high level the common denominator is
DataAccessException, but then any data access problem would triggers a retry. Bad sql queries should result in test failure during dev but still, a specificSerializationFailureExceptionwould help. I don't know if it is realistic, is state 40001 really reliable across databases ? How about NoSQL ACID database ?