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

开了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 lt = new ArrayList();

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)