Could not serialize access due to concurrent update

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