mysql innodb 并发插入问题，包大量死锁错误

Question

开了10个并发写线程，没1000条记录批量提交一次，结果mysql包大量死锁错误！
"Deadlock found when trying to get lock; try restarting transaction"

引擎用的是Innodb 主键字段是auto_increament.

mysql 有这么脆弱吗？

create table ASIA_ODDS( 
    id int NOT NULL AUTO_INCREMENT, 
    match_id INT DEFAULT 0, 
    match_bet007_id INT NOT NULL, 
    company_id SMALLINT NOT NULL ,	 
    first_pk DECIMAL(9,4) DEFAULT 0, 
    first_host_odds DECIMAL(9,4) DEFAULT 0, 
    first_guest_odds DECIMAL(9,4) DEFAULT 0, 
    pk DECIMAL(9,4) , 
    host_odds DECIMAL(9,4) DEFAULT 0, 
    guest_odds DECIMAL(9,4) DEFAULT 0, 
    is_end TINYINT DEFAULT 0, 
    is_zd TINYINT DEFAULT 0 , 
    changeState char(3) DEFAULT '000', 
    last_update_time timestamp , 
    PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我的测试java代码如下：

public class TestInsertAsiaOdds { 
    public static void main(String args[]) { 
        final long s = System.currentTimeMillis(); 
        CyclicBarrier latch = new CyclicBarrier(10, new Runnable() { 
            public void run() { 
                System.out.print("耗时：" + (System.currentTimeMillis() - s)); 
            } 
        }); 

        //10个线程并发插入500w条数据 
        List<Thread> lt = new ArrayList<Thread>(); 

        for (int i = 0; i < 10; i++) { 
            Thread t = new Thread(new Task(5000000, latch)); 
            lt.add(t); 
        } 

        //启动线程 
        for (Thread t : lt) { 
            t.start(); 
        } 
    } 

    private static class Task implements Runnable { 
        private int count; 
        private CyclicBarrier latch; 
        private String insSql = "insert into asia_odds2(match_id, match_bet007_id, company_id, first_pk, first_host_odds, first_guest_odds, pk, host_odds, guest_odds, is_end, is_zd, changeState) values(?,?,?,?,?,?,?,?,?,?,?,?)"; 
        
        public Task(int count, CyclicBarrier latch) { 
            this.count = count; this.latch = latch; 
        } 

        public void run() { 
            Connection conn = null; 
            PreparedStatement ps = null; 

            try { 
                conn = getConn(); //设为自动提交 
                conn.setAutoCommit(true); 
                ps = conn.prepareStatement(insSql); 

                for (int i = 0; i < count; i++) { 
                    ps.setInt(1, 1); 
                    ps.setInt(2, 2); 
                    ps.setInt(3, 3); 
                    ps.setDouble(4, 1.1); 
                    ps.setDouble(5, 1.1); 
                    ps.setDouble(6, 1.1); 
                    ps.setDouble(7, 1.1); 
                    ps.setDouble(8, 1.1); 
                    ps.setDouble(9, 1.1); 
                    ps.setInt(10, 0); 
                    ps.setInt(11, 0); 
                    ps.setString(12, "000");
                    ps.addBatch(); 

                    //1000一批提交一次 
                    if (i % 1000 == 0) { 
                        ps.executeBatch(); 
                        ps.clearBatch(); 
                    } 
                } 
                ps.executeBatch(); 
            } catch (Exception ex) { 
                ex.printStackTrace(); 
            } finally { 
                if (conn != null) { 
                    try { 
                        conn.close(); 
                    } catch (SQLException e) { 
                        e.printStackTrace(); 
                    } 
                } 
                try { 
                    latch.await(); 
                } catch (BrokenBarrierException e) { 
                    e.printStackTrace(); 
                } catch (Exception e) { 
                    e.printStackTrace(); 
                } 
            } 
        } 
}

运行一段时间后就会报：

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting >transaction at >com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1669) at >com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1085)

Answer 1

用 ps.executeBatch()的前提是cnn.setAutoCommit(false);等插入完毕只有再手工commit，

另外，第1000个数据的时候ps.executeBatch(); ps.clearBatch()也不是必须的

Answer 2

insert 是不会锁表的，唯一的可能就是 AUTO_INCREMENT..
遇到同样的问题，不使用 自增键就好了，删掉AUTO_INCREMENT