db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "adam jvok" <ajv...@gmail.com>
Subject ERROR 40XC0: Dead statement (by zak allen)
Date Thu, 06 Dec 2007 11:12:10 GMT
This is also a problem for me.

I would like a stored proc to be able to retry a transaction if it has
become a deadlock victim.
All attempts so far fail with an "ERROR 40XC0: Dead statement" message.

I'd be grateful for any advice. Thanks.

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) ;
    boolean ac=conn.getAutoCommit();
    conn.setAutoCommit(false) ;
    Statement st = conn.createStatement();
    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() ;
        System.out.println("Data read:") ;
        while (data1[0].next()) {
          System.out.println("[" + firstTable + "-" + secondTable +
"]" + data1[0].getInt(1));
        }
        return ;
      } catch (SQLException se) {
        conn.rollback();
        if ((!se.getSQLState().equals("40001"))) throw(se) ;
        System.out.println("[" + firstTable + "-" + secondTable + "]"
+ "I am a dealock victim. Will try again.") ;
        // If deadlock then try again (via the while loop).
      } catch (Exception e) {
        System.out.println("[" + firstTable + "-" + secondTable + "]"
+ "Exception:" + e.getMessage()) ;
        conn.rollback();
        throw(e) ;
      } finally {
        conn.setAutoCommit(ac) ;
      }
    }
  }
}

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 System.out.println()'s):

>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Data read:
[tab2-tab1]I am a dealock victim. Will try again.
Data read:
[tab2-tab1]1
>>>>>>>>>>>>>>>>>>>>>>>>>>>>

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

Is this a bug? Any ideas for a work around?


===========================================
Sorry, couldn't see how to reply directly to this thread.

I can post new messages by mailing derby-user@db.apache.org, but how
to reply to existing posts - is it necessary to register with one of
the archives?

Original message was....
===========================================

After catching that I've become a deadlock victim in a transaction
(within a stored procedure) using:

} catch (SQLException se) {
    if ((!se.getSQLState().equals("40001"))...

.. I retry my transaction (without returning from the stored procedure).

The retry completes without problems but 'ij' shows the messsage:

"ERROR 40XC0: Dead statement. This may be caused by catching a
transaction severity error inside this statement."

and does not display the resultset from the procedure.

Is there anyway to avoid this?

(If the retry succeeds I'd like the deadlock to be invisible to the caller.)

Mime
View raw message