Derby
  1. Derby
  2. DERBY-3265

"ERROR 40XC0: Dead statement" after recovering from deadlock.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Linux 2.6.17-5mdv
    • Urgency:
      Normal

      Description

      I would like a stored proc to be able to retry a transaction if it has become a deadlock victim.

      This does not appear to be possible as, even after detecting a deadlock and sucessfully retrying the transacation, the server reports: "ERROR 40XC0: Dead statement" and fails to return the valid query results.

      The problem can be reproduced like this:

      1. Create 2 tables:
      ij> create table tab1 (a int);
      0 rows inserted/updated/deleted
      ij> create table tab2 (a int) ;
      0 rows inserted/updated/deleted

      2. Write a stored proc:

      package testPackage;

      import java.sql.*;

      public class testClass {
      public static void deadLock(String firstTable, String secondTable, ResultSet[] data1) throws SQLException, Exception {
      Connection conn = DriverManager.getConnection("jdbc:default:connection");
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ;
      Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
      while (true) {
      conn.setSavepoint() ;
      try {
      st.execute("insert into " + firstTable + " values(1)" );
      Thread.currentThread().sleep(10000) ;
      data1[0] = st.executeQuery("select * from " + secondTable) ;
      conn.commit() ;
      while (data1[0].next())

      { log(firstTable,secondTable,"Data Read:" + data1[0].getInt(1)); }

      data1[0].beforeFirst();
      return ;
      } catch (SQLException se)

      { conn.rollback(); if ((!se.getSQLState().equals("40001"))) throw(se) ; log(firstTable,secondTable,"I am a dealock victim. Will try again.") ; // If deadlock then try again (via the while loop). }

      catch (Exception e)

      { log(firstTable,secondTable, "Exception:" + e.getMessage()) ; conn.rollback(); throw(e) ; }

      }
      }
      public static void log(String firstTable, String secondTable, String msg)

      { System.out.println("[" + firstTable + "-" + secondTable + "]" + msg) ; }

      3. Install the stored proc:
      ij> create procedure deadLock(firstTable varchar(10), secondTable
      varchar(10)) parameter style java language java modifies sql data
      dynamic result sets 1 external name 'testPackage.testClass.deadLock';

      4. Startup 2 instances of 'ij'.
      In one,
      ij> call deadLock('tab1','tab2');
      and (as soon as you can) in the other:
      ij> call deadLock('tab2','tab1');

      5. With the last 2 commands I have deliberately created a dead lock. I get:
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
      ij> call deadLock('tab1','tab2');
      A
      -----------

      0 rows selected
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      AND

      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
      ij> call deadLock('tab2','tab1');
      ERROR 40XC0: Dead statement. This may be caused by catching a transaction severity error inside this statement.
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      And the server gives (from my 'log' method):
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
      [tab2-tab1]I am a dealock victim. Will try again.
      [tab2-tab1]Data Read:1
      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      6. This is NOT the desired result.
      It is clear from the server output that the stored proc did detect the deadlock and retry successfully.
      The "ERROR 40XC0: Dead statement." is not helpful.
      Instead, the results of the select should be available.

      SYSINFO
      =======
      ------------------ Java Information ------------------
      Java Version: 1.6.0_02-ea
      Java Vendor: Sun Microsystems Inc.
      Java home: /usr/java/jdk1.6.0_02/jre
      Java classpath: /home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derby.jar:/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbynet.jar:/home/ajvok/derby/db-derby-
      10.3.1.4-bin/lib/derbytools.jar:/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbyclient.jar:/home/ajvok/derby/local/sp1.jar
      OS name: Linux
      OS architecture: i386
      OS version: 2.6.17-5mdv
      Java user name: ajvok
      Java user home: /home/ajvok
      Java user dir: /home/ajvok/derby/local
      java.specification.name: Java Platform API Specification
      java.specification.version: 1.6
      --------- Derby Information --------
      JRE - JDBC: Java SE 6 - JDBC 4.0
      [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derby.jar] 10.3.1.4 - (561794)
      [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbytools.jar] 10.3.1.4 - (561794)
      [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbynet.jar] 10.3.1.4 - (561794)
      [/home/ajvok/derby/db-derby-10.3.1.4-bin/lib/derbyclient.jar] 10.3.1.4 - (561794)
      ------------------------------------------------------
      ----------------- Locale Information -----------------
      Current Locale : [English/United Kingdom [en_GB]]
      Found support for locale: [cs]
      version: 10.3.1.4 - (561794)
      Found support for locale: [de_DE]
      version: 10.3.1.4 - (561794)
      Found support for locale: [es]
      version: 10.3.1.4 - (561794)
      Found support for locale: [fr]
      version: 10.3.1.4 - (561794)
      Found support for locale: [hu]
      version: 10.3.1.4 - (561794)
      Found support for locale: [it]
      version: 10.3.1.4 - (561794)
      Found support for locale: [ja_JP]
      version: 10.3.1.4 - (561794)
      Found support for locale: [ko_KR]
      version: 10.3.1.4 - (561794)
      Found support for locale: [pl]
      version: 10.3.1.4 - (561794)
      Found support for locale: [pt_BR]
      version: 10.3.1.4 - (561794)
      Found support for locale: [ru]
      version: 10.3.1.4 - (561794)
      Found support for locale: [zh_CN]
      version: 10.3.1.4 - (561794)
      Found support for locale: [zh_TW]
      version: 10.3.1.4 - (561794)
      ------------------------------------------------------

        Activity

        Hide
        Kathey Marsden added a comment -

        I think this is perhaps not a bug. The documentation at:
        http://db.apache.org/derby/docs/dev/devguide/cdevspecial41728.html
        says:
        A method that catches a deadlock exception and then continues is probably making a mistake. Errors of transaction severity should be caught not by nested code, but only by the outermost application code. That is the only way to ensure that transactions begin and end where you expect them to.

        Show
        Kathey Marsden added a comment - I think this is perhaps not a bug. The documentation at: http://db.apache.org/derby/docs/dev/devguide/cdevspecial41728.html says: A method that catches a deadlock exception and then continues is probably making a mistake. Errors of transaction severity should be caught not by nested code, but only by the outermost application code. That is the only way to ensure that transactions begin and end where you expect them to.
        Hide
        Mike Matrigali added a comment -

        triaged for 10.8.

        I also am not sure if this is a bug or not. If so it would be in the sql layer that handles the context for procedures. Can anyone comment on
        the standard of manupulating transactions withing a nested procedure? I think we don't support that and your only option is to commit/abort and do no other work.

        Show
        Mike Matrigali added a comment - triaged for 10.8. I also am not sure if this is a bug or not. If so it would be in the sql layer that handles the context for procedures. Can anyone comment on the standard of manupulating transactions withing a nested procedure? I think we don't support that and your only option is to commit/abort and do no other work.
        Hide
        Dag H. Wanvik added a comment -

        A work-around might be to open ordinary connections in the stored
        procedure (i.e. not use the the default connection). I think the
        current behavior is correct.

        As for the standard, section 12.1.1 in SQL 2008 volume 13 Package
        java.sql has this information about nested connections:

        <<
        In an SQL system that implements this part of ISO/IEC 9075, the
        package java.sql supports the default connection. The default
        connection for a Java method invoked as an SQL routine has the
        following characteristics:

        • The default connection is pre-allocated to provide efficient access
          to the database.
        • The default connection is included in the current session and
          transaction.
        • The authorization ID of the default connection is the current
          authorization ID. The JDBC AUTOCOMMIT setting of the default
          connection is false.
          >>

        A transaction severity error will cause transaction of the "current
        session and transaction" above to be aborted, so there is no "current
        transaction" available for the nested connection in which to continue
        work. Hence the "dead statement" error.

        Show
        Dag H. Wanvik added a comment - A work-around might be to open ordinary connections in the stored procedure (i.e. not use the the default connection). I think the current behavior is correct. As for the standard, section 12.1.1 in SQL 2008 volume 13 Package java.sql has this information about nested connections: << In an SQL system that implements this part of ISO/IEC 9075, the package java.sql supports the default connection. The default connection for a Java method invoked as an SQL routine has the following characteristics: The default connection is pre-allocated to provide efficient access to the database. The default connection is included in the current session and transaction. The authorization ID of the default connection is the current authorization ID. The JDBC AUTOCOMMIT setting of the default connection is false. >> A transaction severity error will cause transaction of the "current session and transaction" above to be aborted, so there is no "current transaction" available for the nested connection in which to continue work. Hence the "dead statement" error.

          People

          • Assignee:
            Unassigned
            Reporter:
            adam jvok
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development