-
Notifications
You must be signed in to change notification settings - Fork 38k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Consistent CannotAcquireLockException
translation for PostgreSQL serialization failure behind JPA
#31274
Comments
We specifically handle '40001' as a That said, the problem in your scenario above is the JPA commit which does not translate to a Could you paste the stacktrace of that transaction exception with its |
CannotAcquireLockException
translation for PostgreSQL serialization failure behind JPA
Indeed, if Regarding JPA, when a statement is rejected, what I wrote in my first message about JPA is not correct. Correction: if a statement is rejected due to serialization anomaly: Error message: org.hibernate.exception.LockAcquisitionException: could not execute statement [ERROR: could not serialize access due to read/write dependencies among transactions (the real sql query is shown, not "..."). Stacktrace: jakarta.persistence.OptimisticLockException: org.hibernate.exception.LockAcquisitionException: could not execute statement [ERROR: could not serialize access due to read/write dependencies among transactions If the commit (txManager.commit) is rejected due to serialization anomaly org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection
|
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 aSQLException
with SQLSTATE 40001 .SQLException
thrown 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 specificSerializationFailureException
would help. I don't know if it is realistic, is state 40001 really reliable across databases ? How about NoSQL ACID database ?The text was updated successfully, but these errors were encountered: