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

        adam jvok created issue -
        Kathey Marsden made changes -
        Field Original Value New Value
        Component/s Store [ 11412 ]
        Mike Matrigali made changes -
        Labels derby_triage10_8
        Urgency Normal
        Component/s SQL [ 11408 ]
        Component/s Store [ 11412 ]
        Gavin made changes -
        Workflow jira [ 12418927 ] Default workflow, editable Closed status [ 12798649 ]

          People

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

            Dates

            • Created:
              Updated:

              Development