[
https://issues.apache.org/jira/browse/DERBY-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12994590#comment-12994590
]
Dag H. Wanvik commented on DERBY-3265:
--------------------------------------
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.
> "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
> Components: SQL
> Affects Versions: 10.3.1.4
> Environment: Linux 2.6.17-5mdv
> Reporter: adam jvok
> Labels: derby_triage10_8
>
> 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.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
