[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Could not serialize access due to concurrent update
From: Mladen Gogala <gogala.mladen () gmail ! com>
Date: 2022-01-26 16:31:14
Message-ID: aaf58c64-2b51-1f45-481d-c46e0e01552a () gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>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:</p>
<p><a class="moz-txt-link-freetext" \
href="https://pganalyze.com/docs/log-insights/app-errors/U138">https://pganalyze.com/docs/log-insights/app-errors/U138</a></p>
<p>OK, the app is not setting transaction isolation level to
repeatable read, so what's going on? The documentation for
Websphere reveals the truth:</p>
<p><a class="moz-txt-link-freetext" \
href="https://www.ibm.com/support/pages/transaction-isolation-levels-and-websphere-app \
lication-server">https://www.ibm.com/support/pages/transaction-isolation-levels-and-websphere-application-server</a></p>
<p><font color="blue"><b>If you do not specify the isolation level</b><br>
</font>
<font color="blue"><br>
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.<br>
</font>
<font color="blue"><br>
</font>
<font color="blue"><b>For most drivers, WebSphere Application
Server uses an isolation level default of
TRANSACTION_REPEATABLE_READ. </b>(Bold font is my addition)</font></p>
<p>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:<br>
</p>
<p><br>
</p>
<table class=" domino-table" border="1">
<tbody>
<tr valign="top">
<td width="108" valign="bottom" bgcolor="#DADADA"><b>Possible
values</b></td>
<td width="307" valign="bottom" bgcolor="#DADADA"><b>JDBC isolation \
level</b></td>
<td width="189" valign="bottom" bgcolor="#DADADA"><b>DB2 isolation \
level</b></td> </tr>
<tr valign="top">
<td width="108" bgcolor="#FFFFFF">8</td>
<td width="307" bgcolor="#FFFFFF">TRANSACTION_SERIALIZABLE</td>
<td width="189" bgcolor="#FFFFFF">Repeatable Read (RR)</td>
</tr>
<tr valign="top">
<td width="108" bgcolor="#FFFFFF">4 (default)</td>
<td width="307" bgcolor="#FFFFFF">TRANSACTION_REPEATABLE_READ</td>
<td width="189" bgcolor="#FFFFFF">Read Stability (RS)</td>
</tr>
<tr valign="top">
<td width="108" bgcolor="#FFFFFF">2</td>
<td width="307" bgcolor="#FFFFFF">TRANSACTION_READ_COMMITTED</td>
<td width="189" bgcolor="#FFFFFF">Cursor Stability (CS)</td>
</tr>
<tr valign="top">
<td width="108" bgcolor="#FFFFFF">1</td>
<td width="307" bgcolor="#FFFFFF">TRANSACTION_READ_UNCOMMITTED</td>
<td width="189" bgcolor="#FFFFFF">Uncommitted Read (UR)</td>
</tr>
<tr valign="top">
<td width="108" bgcolor="#FFFFFF"> 0</td>
<td width="307" bgcolor="#FFFFFF"> TRANSACTION_NONE</td>
<td width="189" bgcolor="#FFFFFF"> No Commit (NC)</td>
</tr>
</tbody>
</table>
<br>
<br>
<b>Note</b><b>:</b> If TRANSACTION_NONE is used, the DB file does
not have to be journaled.<br>
<br>
To define this custom property for a data source, you should do the
following:
<table class=" domino-table" width="100%" cellspacing="0"
cellpadding="0" border="0">
<tbody>
<tr valign="top">
<td width="4%">1.</td>
<td width="96%">Click <b>Resources</b> > <b>JDBC provider</b> > \
<b><i>JDBC_provider</i></b>.</td> </tr>
<tr valign="top">
<td width="4%">2.</td>
<td width="96%">Click <b>Data sources</b> in the Additional
Properties section.</td>
</tr>
<tr valign="top">
<td width="4%">3.</td>
<td width="96%">Click the name of the data source.</td>
</tr>
<tr valign="top">
<td width="4%">4.</td>
<td width="96%">Click <b>Custom properties</b>.</td>
</tr>
<tr valign="top">
<td width="4%">5.</td>
<td width="96%">Create the
webSphereDefaultIsolationLevel custom property<br>
a. Click <b>New</b>.<br>
b. Enter <b>webSphereDefaultIsolationLevel</b> for the name
field.<br>
c. Enter one of the <b>"possible values</b>" in the value
field from the table above.<b> </b>i.e. 0, 1, 2, 4, or 8</td>
</tr>
</tbody>
</table>
<br>
<p>The value that needs to be entered is 2.<br>
<b></b></p>
<p><b><br>
</b></p>
<pre class="moz-signature" cols="72">--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" \
href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a> </pre>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic