openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fay Wang (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-1608) PESSIMISTIC_WRITE is not working in Informix
Date Thu, 01 Apr 2010 20:20:27 GMT

    [ 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.


Mime
View raw message