56,675
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `test` (
`key` bigint(20) NOT NULL AUTO_INCREMENT,
`id` bigint(20) DEFAULT NULL,
`name` varchar(60) DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`key`),
KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000 DEFAULT CHARSET=utf8;
UpdateNameTask t1 = new TestUtil().new UpdateNameTask(8, 0);
UpdateNameTask t2 = new TestUtil().new UpdateNameTask(8, 1);
UpdateNameTask t3 = new TestUtil().new UpdateNameTask(8, 2);
UpdateNameTask t4 = new TestUtil().new UpdateNameTask(8, 3);
UpdateNameTask t5 = new TestUtil().new UpdateNameTask(8, 4);
UpdateNameTask t6 = new TestUtil().new UpdateNameTask(8, 5);
UpdateNameTask t7 = new TestUtil().new UpdateNameTask(8, 6);
UpdateNameTask t8 = new TestUtil().new UpdateNameTask(8, 7);
new Thread(t1).start();
new Thread(t2).start();
new Thread(t3).start();
new Thread(t4).start();
new Thread(t5).start();
new Thread(t6).start();
new Thread(t7).start();
new Thread(t8).start();
com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356)
at junit.test.TestUtil.updateTestData(TestUtil.java:122)
at junit.test.TestUtil$UpdateNameTask.run(TestUtil.java:152)
at java.lang.Thread.run(Thread.java:595
if(_keys.indexOf(",") > -1) {
_keys = ids.substring(0, _keys.lastIndexOf(","));
}
得改成:
if(_keys.indexOf(",") > -1) {
_keys = _keys.substring(0, _keys.lastIndexOf(","));
}
抱歉,浪费大家时间了。关闭。
_stmt = conn.createStatement();
stmt.addBatch(String.format(updateSql, "["+mod+":"+remainder+"]", _ids));
stmt.addBatch(String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys));
stmt.executeBatch();
结果是:所有的线程全部死锁!
[/quote]
我的意思是,之前只是个别死锁(个别没执行成功),用这样的方式后每个线程的每个sql都没执行成功的。
_stmt = conn.createStatement();
stmt.addBatch(String.format(updateSql, "["+mod+":"+remainder+"]", _ids));
stmt.addBatch(String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys));
stmt.executeBatch();
结果是:所有的线程全部死锁!
class UpdateNameTask implements Runnable {
private int mod;
private int remainder;
public UpdateNameTask(int mod, int remainder) {
this.mod = mod;
this.remainder = remainder;
}
public void run() {
System.out.println("Thread start..." + Thread.currentThread().getName() + ", " + mod + ":" +remainder);
try {
TestUtil.updateTestData(mod, remainder);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void updateTestData(int mod, int remainder) throws Exception {
String selectSql = "select count(*) from test where MOD(id, %s)=%s";
String selectIdSql = "select id, `key` from test where MOD(id, %s)=%s limit %s, %s";
String updateSql = "update test set name=CONCAT(name, '%s'), createTime=NOW() where id in (%s)";
String updateSql2 = "update test set name=CONCAT(name, '%s'), createTime=NOW() where `key` in (%s)";
Connection conn = TestUtil.getDirectTestConntion();
PreparedStatement stmt = null;
ResultSet rs = null;
int count = 0;
try {
selectSql = String.format(selectSql, mod, remainder);
System.out.println(String.format("[%s]count sql: %s", mod+":"+remainder, selectSql));
stmt = conn.prepareStatement(selectSql);
rs = stmt.executeQuery();
if(rs.next()) {
count = rs.getInt(1);
}
if(count == 0) {
System.out.println(String.format("[%s]not record found!", mod+":"+remainder));
return;
}
int updateBatchSize = 3000;
int rmd = count % updateBatchSize;
int updateTime = (rmd == 0 ? count/updateBatchSize : count/updateBatchSize + 1);
int startIndex = 0;
for(int i=0;i<updateTime;i++) {
System.out.println(String.format("[%s]Process begin: startIndex-%s", mod+":"+remainder, startIndex));
String _selectIdSql = String.format(selectIdSql, mod, remainder, startIndex, updateBatchSize);
System.out.println(String.format("[%s]select id sql: %s", mod+":"+remainder, _selectIdSql));
stmt = conn.prepareStatement(_selectIdSql);
rs = stmt.executeQuery();
StringBuilder ids = new StringBuilder();
StringBuilder keys = new StringBuilder();
while(rs.next()) {
ids.append(rs.getLong(1)).append(",");
keys.append(rs.getLong(2)).append(",");
}
String _ids = ids.toString();
if(_ids.indexOf(",") > -1) {
_ids = ids.substring(0, _ids.lastIndexOf(","));
}
String _keys = keys.toString();
if(_keys.indexOf(",") > -1) {
_keys = ids.substring(0, _keys.lastIndexOf(","));
}
// 先根据id更新(id是具有非唯一性索引)
System.out.println(String.format("[%s]start to update by ids: %s", mod+":"+remainder, _ids));
String _updateSql = String.format(updateSql, "["+mod+":"+remainder+"]", _ids);
System.out.println(String.format("[%s]update sql: %s", mod+":"+remainder, _updateSql));
stmt = conn.prepareStatement(_updateSql);
stmt.execute();
System.out.println(String.format("[%s]update by ids finished!", mod+":"+remainder));
// 在根据key更新(key是主键)
System.out.println(String.format("[%s]start to update by keys: %s", mod+":"+remainder, _keys));
String _updateSql2 = String.format(updateSql2, "{"+mod+":"+remainder+"}", _keys);
System.out.println(String.format("[%s]update sql2: %s", mod+":"+remainder, _updateSql2));
stmt = conn.prepareStatement(_updateSql2);
stmt.execute();
System.out.println(String.format("[%s]update by keys finished!", mod+":"+remainder));
startIndex = startIndex + updateBatchSize;
}
} catch(Exception e) {
e.printStackTrace();
} finally {
if(conn != null)
conn.close();
}
}
代码逻辑简单,好多是用来打日志的。。。