db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3265) "ERROR 40XC0: Dead statement" after recovering from deadlock.
Date Fri, 14 Dec 2007 16:45:43 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kathey Marsden updated DERBY-3265:
----------------------------------

    Component/s: Store

> "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: Store
>    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.


Mime
View raw message