MySQL Bugs: #27897: dead lock , affter delete data ,execute insert data

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:	Windows (xp)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	affter delete data, dead lock, execute insert data

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.

Hi and thanks for your report.

Can you tell me if you are using transactional tables? Ie. is table from example InnoDB or MyISAM?

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

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?

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.