[DERBY-3265] "ERROR 40XC0: Dead statement" after recovering from deadlock.

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.3.1.4
• Component/s: SQL
• Labels: derby_triage10_8
• Environment: Linux 2.6.17-5mdv
• Urgency: Normal
• Assignee: Unassigned
• Reporter: adam jvok
• Created: 10/Dec/07 03:36
• Updated: 15/Feb/11 00:33

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