Bug #27897 dead lock , affter delete data ,execute insert data
Submitted: 17 Apr 2007 18:56 Modified: 31 Mar 2014 12:38
Reporter: abayi tay Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.0.37-community OS:Microsoft Windows (xp)
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: affter delete data, dead lock, execute insert data

[17 Apr 2007 18:56] abayi tay
Description:
testing in transaction isolation =2 ,4 ,8 also cant working
============================================================================
Case A , not working in XA , when multiple Connection
============================================================================

Connection conn1,conn2,conn3;
 jotm.begin();
 conn1 = ds.getConnection();
 conn2 = ds.getConnection();
 conn3 = ds.getConnection();
 conn3.createStatement().execute("delete from testCoordination");
 conn1.createStatement().executeUpdate("INSERT INTO testCoordination VALUES ('a1')");
 conn2.createStatement().executeUpdate("INSERT INTO testCoordination VALUES ('a2')");
 jotm.commit();

so when i execute delete sql ,and insert data again .mysql will throw exception.
============================================================================

============================================================================
Case B , not working in XA transaction , when multiple Connection
============================================================================

   Connection conn1 = null;
      Connection conn2 = null;
      XAConnection xaConn1 = null;
      XAConnection xaConn2 = null;
      XADataSource xaDs=getMysqlXADataSource();
      try
      {
         xaConn1 = xaDs.getXAConnection();
         XAResource xaRes1 = xaConn1.getXAResource();
         conn1 = xaConn1.getConnection();

         xaConn2 = xaDs.getXAConnection();
         XAResource xaRes2 = xaConn2.getXAResource();
         conn2 = xaConn2.getConnection();

         Xid xid1 = createXid();
         Xid xid2 = createXid(xid1);

         xaRes1.start(xid1 , XAResource.TMNOFLAGS);
         xaRes2.start(xid2 , XAResource.TMNOFLAGS);
         conn1.createStatement().executeUpdate("delete from testCoordination ");
         conn2.createStatement().executeUpdate("INSERT INTO testCoordination VALUES (2)");
         xaRes1.end(xid1 , XAResource.TMSUCCESS);
         xaRes2.end(xid2 , XAResource.TMSUCCESS);

         xaRes1.prepare(xid1);
         xaRes2.prepare(xid1);

         xaRes1.commit(xid1 , false);
         xaRes2.commit(xid1 , false);

      }
      finally
      {
         if (conn1 != null)
         {
            conn1.close();
         }

         if (conn2 != null)
         {
            conn2.close();
         }

         if (xaConn1 != null)
         {
            xaConn1.close();
         }

         if (xaConn2 != null)
         {
            xaConn2.close();
         }
      }

============================================================================
Case C: will working in XA , when not multiple Connection
============================================================================

     Connection conn1 = null;
 
      XAConnection xaConn1 = null;
 
      XADataSource xaDs=getMysqlXADataSource();
      try
      {
         xaConn1 = xaDs.getXAConnection();
         XAResource xaRes1 = xaConn1.getXAResource();
         conn1 = xaConn1.getConnection();

         Xid xid1 = createXid();

         xaRes1.start(xid1 , XAResource.TMNOFLAGS);

         conn1.createStatement().executeUpdate("delete from testCoordination ");
         conn1.createStatement().executeUpdate("INSERT INTO testCoordination VALUES (2)");
         xaRes1.end(xid1 , XAResource.TMSUCCESS);

         xaRes1.prepare(xid1);

         xaRes1.commit(xid1 , false);

      }
      finally
      {
         if (conn1 != null)
         {
            conn1.close();
         }

         if (xaConn1 != null)
         {
            xaConn1.close();
         }
 
      }

============================================================================
Case D: will working
============================================================================
  Class.forName("com.mysql.jdbc.Driver");
      Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.0.11/db1" , "root" , "qweasdzxc");
      conn.setAutoCommit(false);
      conn.createStatement().execute("delete from testCoordination");
      conn.createStatement().executeUpdate("INSERT INTO testCoordination VALUES ('a1')");
      conn.createStatement().executeUpdate("INSERT INTO testCoordination VALUES ('a2')");
      conn.commit();
      conn.close();
============================================================================

below is server response 
================================
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1343)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1260)
	at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:801)
	at my.jta.test.XATest.testJTAService(XATest.java:762)
	at my.jta.test.XATest.main(XATest.java:837)

How to repeat:
repeat in transaction 
when delete sql execute in one connection , and other one connection execute insert data into same table ,will occur this exception.

normally, in same transaction boundary will not got the dead lock , even different connection (in mysql , a connection is a XAResource), because that are in same transaction.  

got any sugest to fix my problem ,thanks.
[28 Apr 2007 20:51] Tonci Grgin
Hi and thanks for your report.

Can you tell me if you are using transactional tables? Ie. is table from example InnoDB or MyISAM?
[28 May 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Nov 2008 9:34] Meik Suchlich
I have the same Problem with INNODB-Tables.
Transaction with delete and insert on one table with autoinc-field (part of primary key)
This transaction is called from different users parallel.
There are deadlocks. Why? And how can I solve it?
[31 Mar 2014 12:38] Alexander Soklakov
I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.