[
https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12852532#action_12852532
]
Fay Wang commented on OPENJPA-1608:
-----------------------------------
Changing the isolation level to repeatable read globally can reduce the
concurrency. Changing it in the middle when PESSIMISTIC LOCK is specified is
not allowed in the XA transaction. Another workaround for informix is to
execute "SET ENVIRONMENT RETAINUPDATELOCKS 'ALL'" statement before executing
the "SELECT ... FOR UPDATE" as show below:
public static void main (String a[]) throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(
Connection.TRANSACTION_READ_UNCOMMITTED);
String retainLocks = "SET ENVIRONMENT RETAINUPDATELOCKS 'ALL'";
PreparedStatement pstmt2 = conn.prepareStatement(retainLocks);
pstmt2.executeUpdate();
String sql = "select version from STOCK where S_I_ID = 8808 and S_W_ID
= 9 for update";
PreparedStatement pstmt1 = conn.prepareStatement(sql);
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next()) {
int version = rs1.getInt(1);
System.out.println("version = " + version);
}
rs1.close();
conn.commit();
}
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_2038.htm
> PESSIMISTIC_WRITE is not working in Informix
> --------------------------------------------
>
> Key: OPENJPA-1608
> URL: https://issues.apache.org/jira/browse/OPENJPA-1608
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Fix For: 2.1.0
>
>
> The following call:
> district = em.find(DistrictJPA.class, key,
> LockModeType.PESSIMISTIC_WRITE);
> generates SELECT ... FOR UPDATE .
> However, in the default isolation level (read committed). Informix does
> not lock the row, causing a lot of duplicate key errors. The work around is
> for the application to explicitly set the property below in the
> persistence.xml:
> <property name="openjpa.jdbc.TransactionIsolation"
> value="repeatable-read" />
> According to the spec 3.4.4, footnote:
> For example, a persistence provider may use an underlying database
> platform's SELECT FOR UPDATE statements to implement pessimistic locking if
> that construct provides appropriate semantics, or the provider may use an
> isolation level of repeatable read.
> It appears that the persistence provider must implements
> PESSIMISTIC_WRITE semantics transparently to the application.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
