In this post, I am not asking a question, I am sharing an experience. The application is running on Linux, PostgreSQL 13.5. using Websphere 9 application server. When using "SKIP LOCKED" option, I suddenly started seeing errors like "Could not serialize access due to concurrent update". After some reading, the problem was pinpointed to the transaction isolation level:
https://pganalyze.com/docs/log-insights/app-errors/U138
OK, the app is not setting transaction isolation level to repeatable read, so what's going on? The documentation for Websphere reveals the truth:
https://www.ibm.com/support/pages/transaction-isolation-levels-and-websphere-application-server
If you do not specify the isolation level
The product does not require you to set the isolation level on a data source resource reference for a non-CMP application module. If you do not specify isolation level on the resource reference, or if you specify TRANSACTION_NONE, the WebSphere Application Server run time uses a default isolation level for the data source. Application Server uses a default setting based on the JDBC driver.
For most drivers, WebSphere Application Server uses an isolation level default of TRANSACTION_REPEATABLE_READ. (Bold font is my addition)
Fortunately, the same document explains how to set the transaction isolation level to READ COMMITTED for the Websphere data source. No wonder that IBM stands for "It's Better Manually". Be vewy, vewy cawefull when using Websphere and PostgreSQL. Here is how to deal with the problem:
| Possible values | JDBC isolation level | DB2 isolation level |
| 8 | TRANSACTION_SERIALIZABLE | Repeatable Read (RR) |
| 4 (default) | TRANSACTION_REPEATABLE_READ | Read Stability (RS) |
| 2 | TRANSACTION_READ_COMMITTED | Cursor Stability (CS) |
| 1 | TRANSACTION_READ_UNCOMMITTED | Uncommitted Read (UR) |
| 0 | TRANSACTION_NONE | No Commit (NC) |
Note: If TRANSACTION_NONE is used, the DB file does not have to be journaled.
To define this custom property for a data source, you should do the following:
| 1. | Click Resources > JDBC provider > JDBC_provider. |
| 2. | Click Data sources in the Additional Properties section. |
| 3. | Click the name of the data source. |
| 4. | Click Custom properties. |
| 5. | Create the webSphereDefaultIsolationLevel custom property
a. Click New.
b. Enter webSphereDefaultIsolationLevel for the name field.
c. Enter one of the "possible values" in the value field from the table above. i.e. 0, 1, 2, 4, or 8 |
The value that needs to be entered is 2.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
