For different database technologies, MS-SQL and H2 recently, and even with different applications, I get exceptions from statements that could not acquire a table lock.
This happens in particular when accessing the same table from many parallel threads. Assuming these conflicts cannot be avoided in principle with complex enough statements and enough parallel threads, what is a good way to handle these failed statements?
- The MS-SQL exception explicitly suggests to retry the statement.
- H2 allows to set a LOCK_TIMEOUT.
Since every statement may fail this way a retry would be needed for every statement in the software. Seems like a lot of boiler-plate code that needs to be added.
In contrast, the timeout is a simple configuration, but it needs to be excessively high to guarantee that a few more threads do not again trigger the exception. Which brings me back to the retry logic.
The legacy code contains several ten statements, each implemented in its own method, opening/closing the triplet of connection/statement/result set. What would be a good way to refactor it apart from wrapping each of them into a retry-loop? Lower performance is acceptable, but no failed statements.
