db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3265) "ERROR 40XC0: Dead statement" after recovering from deadlock.
Date Tue, 15 Feb 2011 00:34:58 GMT

    [ 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

        

Mime
View raw message