开了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)
天蓬老师2017-04-17 10:59:51
用 ps.executeBatch()的前提是cnn.setAutoCommit(false);等插入完毕只有再手工commit,
另外,第1000个数据的时候ps.executeBatch(); ps.clearBatch()也不是必须的
PHP中文网2017-04-17 10:59:51
insert 是不会锁表的,唯一的可能就是 AUTO_INCREMENT..
遇到同样的问题,不使用 自增键就好了,删掉AUTO_INCREMENT