Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <10465269.1197257804409.JavaMail.jira@brutus>
Date: Sun, 9 Dec 2007 19:36:44 -0800 (PST)
From: "adam jvok (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-3265) "ERROR 40XC0: Dead statement" after
 recovering from deadlock.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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

                 Key: DERBY-3265
                 URL: https://issues.apache.org/jira/browse/DERBY-3265
             Project: Derby
          Issue Type: Bug
    Affects Versions: 10.3.1.4
         Environment: Linux  2.6.17-5mdv 
            Reporter: adam jvok


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)
------------------------------------------------------


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.