Mycat_非分片表删除插入死锁问题

最新推荐文章于 2022-11-14 11:34:02 发布

weixin_33811539

最新推荐文章于 2022-11-14 11:34:02 发布

阅读量236

点赞数

文章标签： java python 数据库

原文链接：https://my.oschina.net/sandant/blog/703157

版权

2019独角兽企业重金招聘Python工程师标准>>>

非分片表删除插入死锁问题

场景描述

同一个事务中执行删除语句和插入语句两个线程并发
第一个线程，删除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)

转载于:https://my.oschina.net/sandant/blog/703157