You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2007/12/14 17:45:43 UTC
[jira] Updated: (DERBY-3265) "ERROR 40XC0: Dead statement" after
recovering from deadlock.
[ 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.