OpenJPA
  1. OpenJPA
  2. OPENJPA-1608

PESSIMISTIC_WRITE is not working in Informix

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0
    • Fix Version/s: 2.0.0
    • Component/s: jdbc
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      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.

        Activity

        Darren Woods committed 932660 (2 files)
        Reviews: none

        OPENJPA-1608: the RetainUpdateLock is not supported for IDS 11.10. Other parts of 1608 were already merged in. Merging in from trunk r932643 by Fay.

        Donald Woods made changes -
        Fix Version/s 2.0.0 [ 12314019 ]
        Fix Version/s 2.1.0 [ 12314542 ]
        Affects Version/s 2.1.0 [ 12314542 ]
        Fay Wang committed 930916 (1 file)
        Fay Wang made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Fay Wang made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.0.0 [ 12314019 ]
        Resolution Fixed [ 1 ]
        Hide
        Fay Wang added a comment -

        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.

        Show
        Fay Wang added a comment - 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.
        Fay Wang committed 930889 (1 file)
        Donald Woods made changes -
        Assignee Fay Wang [ faywang ]
        Fix Version/s 2.0.0 [ 12314019 ]
        Affects Version/s 2.0.0 [ 12314019 ]
        Patch Info [Patch Available]
        Hide
        Michael Dick added a comment -

        Patch looks good Fay, go for it.

        Show
        Michael Dick added a comment - Patch looks good Fay, go for it.
        Fay Wang made changes -
        Field Original Value New Value
        Attachment OPENJPA-1608.patch [ 12440550 ]
        Hide
        Fay Wang added a comment -

        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

        Show
        Fay Wang added a comment - 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
        Fay Wang created issue -

          People

          • Assignee:
            Fay Wang
            Reporter:
            Fay Wang
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development