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] Commented: (DERBY-3265) "ERROR 40XC0: Dead statement" after recovering from deadlock.
Date Mon, 06 Jul 2009 15:10:14 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12727572#action_12727572
] 

Kathey Marsden commented on DERBY-3265:
---------------------------------------

I think this is perhaps not a bug. The documentation at:
http://db.apache.org/derby/docs/dev/devguide/cdevspecial41728.html
says:
A method that catches a deadlock exception and then continues is probably making a mistake.
Errors of transaction severity should be caught not by nested code, but only by the outermost
application code. That is the only way to ensure that transactions begin and end where you
expect them to.

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