非分片表删除插入死锁问题
相关配置
dn0中有一张表 Z_TEST
Z_TEST表在mycat中配置的非分片表,即只配置了单节点dn0
场景描述
同一个事务中执行删除语句和插入语句 两个线程并发
第一个线程,删除id为"X1"的记录,并插入id为"1"的记录
第二个线程,删除id为"X2"的记录,并插入id为"2"的记录
如果数据库中存在"X1","X2"的记录,则删除插入没有问题
如果数据库中不存在"X1","X2"的记录,则删除插入时第二个线程报死锁
测试代码
public static void testThreadDelInsert(DataSourceFactory ds){
Connection con1 = ds.getConnection();
Connection con2 = ds.getConnection();
DelInsertThread thread1 = new DelInsertThread(con1, "1");
DelInsertThread thread2 = new DelInsertThread(con2, "2");
thread1.start();
thread2.start();
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
// -- DelInsertThread.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class DelInsertThread extends Thread {
private Connection con;
private String id;
public DelInsertThread(Connection con, String id) {
super();
this.con = con;
this.id = id;
try {
con.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void run() {
try {
PreparedStatement ps1 = con.prepareStatement("delete from z_test where id=?");
ps1.setString(1, "X"+id);
ps1.executeUpdate();
ps1.close();
PreparedStatement ps2 = con.prepareStatement("insert z_test(id,code,name) values(?,?,?)");
ps2.setString(1, id);
ps2.setString(2, "code-" + id);
ps2.setString(3, "name-" + id+"="+new Date().getTime());
ps2.executeUpdate();
ps2.close();
con.commit();
con.close();
} catch (Exception e) {
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
异常信息
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
at com.am.mycatclient.DelInsertThread.run(DelInsertThread.java:35)