[
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.
