You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Fay Wang (JIRA)" <ji...@apache.org> on 2010/04/01 19:26:27 UTC
[jira] Created: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
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.
[jira] Commented: (OPENJPA-1608) PESSIMISTIC_WRITE is not working
in Informix
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OPENJPA-1608) PESSIMISTIC_WRITE is not working
in Informix
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12853416#action_12853416 ]
Fay Wang commented on OPENJPA-1608:
-----------------------------------
The pessimistic write lock can still be a potential problem for other databases where select for update is only effective in certain isolation levels. The current patch is to fix informix only.
> 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.0.0, 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.0.0, 2.1.0
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Updated: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1608:
------------------------------
Attachment: OPENJPA-1608.patch
> 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
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Updated: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1608:
----------------------------------
Patch Info: [Patch Available]
Affects Version/s: 2.0.0
Fix Version/s: 2.0.0
Assignee: Fay Wang
> 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.0.0, 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.0.0, 2.1.0
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Resolved: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang resolved OPENJPA-1608.
-------------------------------
Resolution: Fixed
Fix Version/s: (was: 2.0.0)
> 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.0.0, 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Commented: (OPENJPA-1608) PESSIMISTIC_WRITE is not working
in Informix
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12852866#action_12852866 ]
Michael Dick commented on OPENJPA-1608:
---------------------------------------
Patch looks good Fay, go for it.
> 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
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Closed: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang closed OPENJPA-1608.
-----------------------------
> 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.0.0, 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.
[jira] Updated: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in
Informix
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1608?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1608:
----------------------------------
Affects Version/s: (was: 2.1.0)
Fix Version/s: (was: 2.1.0)
2.0.0
> 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.0.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.0.0
>
> Attachments: OPENJPA-1608.patch
>
>
> 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.