OpenJPA
  1. OpenJPA
  2. OPENJPA-991

Implement LockTimeout using db specific server-side lock timeout feature

    Details

    • Type: Improvement Improvement
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.0.4, 1.1.1, 1.2.2, 1.3.0, 2.0.0, 2.2.0, 2.3.0
    • Fix Version/s: None
    • Component/s: kernel
    • Labels:
      None

      Description

      This issue is originally reported by Donald Woods in OPENJPA-957.

      "Looks like the existing implementation (not defined in JPA 1.0 Spec and OpenJPA specific) is using setQueryTimeout() in PessimisticLockManager, which is a client side JDBC timeout function, while lock timeouts are implemented in the DB server. See -
      DB2 - http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0011874.htm
      MS SQL - http://msdn.microsoft.com/en-us/library/aa213032(SQL.80).aspx
      Derby - config property - http://db.apache.org/derby/docs/dev/devguide/cdevconcepts16400.html
      Oracle - LOCKWAIT on the connection or on the DB
      Also, the following discussion gives a good overview of the two and why apps should use both to handle unreliable network conditions -
      http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/95755534-bbef-4c2c-afa4-b80ca2a2c333/"

      This issue caused TestException.testThrowsOptimisticException to hang when run against DB2 because the default lock timeout is -1 on DB2, which means to wait forever. This test case will be disabled until the issue is resolved and re-enabled.

      Albert Lee.

        Issue Links

          Activity

          Albert Lee created issue -
          Albert Lee committed 756180 (1 file)
          Reviews: none

          OPENJPA-991 - disable TestException.testThrowsOptimisticException() until this issue is addressed. This will avoid the test hang condition and allow other tests to proceed, when run in DB2.

          Hide
          Albert Lee added a comment -

          Oracle 11g lock timeout support in the

          LOCK TABLE ..... IN lockmode MODE WAIT xsec clause pg 18-72
          SELECT... .FOR UPDATE .... WAIT xsec clause pg 19-13

          http://download.oracle.com/docs/cd/B28359_01/server.111/b28286.pdf

          -----------------------------------
          DB2 uses "SET CURRENT LOCK TIMEOUT = <secs>" for all statements on the current connection.

          -----------------------------------
          Derby reference.

          http://db.apache.org/derby/docs/10.1/ref/refderby.pdf

          SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
          Use the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure to set or delete the value of a property of the database on the current connection. If "VALUE" is not null, then the property with key value "KEY" is set to "VALUE". If "VALUE" is null, then the property with key value "KEY" is deleted from the database property set.

          Syntax
          SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(IN KEY VARCHAR(128),IN VALUE VARCHAR(32672))
          This procedure does not return any results.
          JDBC example
          Set the derby.locks.deadlockTimeout property to a value of 10:

          CallableStatement cs = conn.prepareCall
          ("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)");
          cs.setString(1, "derby.locks.deadlockTimeout");
          cs.setString(2, "10");
          cs.execute();
          cs.close();

          SQL example
          Set the derby.locks.deadlockTimeout property to a value of 10:
          CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
          ('derby.locks.deadlockTimeout', '10');

          Derby 10.1 does not implement
          • setQueryTimeout

          Derby 10.2 has full support for Statement.setQueryTimeout()

          Show
          Albert Lee added a comment - Oracle 11g lock timeout support in the LOCK TABLE ..... IN lockmode MODE WAIT xsec clause pg 18-72 SELECT... .FOR UPDATE .... WAIT xsec clause pg 19-13 http://download.oracle.com/docs/cd/B28359_01/server.111/b28286.pdf ----------------------------------- DB2 uses "SET CURRENT LOCK TIMEOUT = <secs>" for all statements on the current connection. ----------------------------------- Derby reference. http://db.apache.org/derby/docs/10.1/ref/refderby.pdf SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY Use the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure to set or delete the value of a property of the database on the current connection. If "VALUE" is not null, then the property with key value "KEY" is set to "VALUE". If "VALUE" is null, then the property with key value "KEY" is deleted from the database property set. Syntax SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(IN KEY VARCHAR(128),IN VALUE VARCHAR(32672)) This procedure does not return any results. JDBC example Set the derby.locks.deadlockTimeout property to a value of 10: CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); cs.setString(1, "derby.locks.deadlockTimeout"); cs.setString(2, "10"); cs.execute(); cs.close(); SQL example Set the derby.locks.deadlockTimeout property to a value of 10: CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY ('derby.locks.deadlockTimeout', '10'); Derby 10.1 does not implement • setQueryTimeout Derby 10.2 has full support for Statement.setQueryTimeout()
          Donald Woods made changes -
          Field Original Value New Value
          Link This issue is related to OPENJPA-957 [ OPENJPA-957 ]
          Donald Woods made changes -
          Affects Version/s 2.0.0 [ 12314019 ]
          Affects Version/s 2.0.0-M2 [ 12313483 ]
          Michael Dick made changes -
          Fix Version/s 1.2.2 [ 12313681 ]
          Fix Version/s 2.0.0 [ 12314019 ]
          Michael Dick made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Donald Woods made changes -
          Fix Version/s 2.0.0-beta [ 12314149 ]
          Fix Version/s 2.0.0 [ 12314019 ]
          Donald Woods made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Pinaki Poddar made changes -
          Link This issue relates to OPENJPA-1565 [ OPENJPA-1565 ]
          Hide
          Milosz Tylenda added a comment -

          Has this been actually fixed? I can't see any commits in subversion panel or in e.g. OracleDictionary?

          Show
          Milosz Tylenda added a comment - Has this been actually fixed? I can't see any commits in subversion panel or in e.g. OracleDictionary?
          Hide
          Donald Woods added a comment -

          This has not been fixed, as pointed out by Milosz.

          Show
          Donald Woods added a comment - This has not been fixed, as pointed out by Milosz.
          Donald Woods made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Darren Woods committed 923222 (2 files)
          Reviews: none

          OPENJPA-991 Remove from release notes as being fixed.

          Donald Woods made changes -
          Fix Version/s 2.1.0 [ 12314542 ]
          Fix Version/s 1.2.2 [ 12313681 ]
          Fix Version/s 2.0.0-beta [ 12314149 ]
          Donald Woods made changes -
          Link This issue relates to OPENJPA-1594 [ OPENJPA-1594 ]
          Michael Dick made changes -
          Fix Version/s 2.2.0 [ 12315910 ]
          Fix Version/s 2.1.0 [ 12314542 ]
          Albert Lee made changes -
          Fix Version/s 2.3.0 [ 12319463 ]
          Fix Version/s 2.2.0 [ 12315910 ]
          Affects Version/s 2.2.0 [ 12315910 ]
          Albert Lee made changes -
          Fix Version/s 2.3.0 [ 12319463 ]
          Affects Version/s 2.3.0 [ 12319463 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Albert Lee
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development