最近公司有个项目，需要多并发完成任务，也就是一个任务控制中心控制多个WORKER的问题，这里的核心点在于如果WORKER_A正在执行1号任务，任务中心不能让WORKER_B重复执行1号任务，即WORKER_A和WORKER_B同时来任务中心需要互斥。
咱们的解决方案是使用MYSQL的INNODB行锁机制完成这项工作，即使用MYSQL来充当任务中心的角色。相关参考：SELECT FOR UPDATE原理。
一、创建数据表: testCREATE TABLE `test` ( `unit_id` int(11) NOT NULL AUTO_INCREMENT, `style` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`unit_id`) ) ENGINE=InnoDB AUTO_INCREMENT=64011569 DEFAULT CHARSET=utf8
......
三、程序：import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
public class Jdbc {
public static void main(String[] args) throws Exception {
Connection conn = null;
String sql;
String url = "jdbc:mysql://10.235.160.137:3306/lz_main?" + "user=lzstat&password=711TJS&useUnicode=true&characterEnco
ding=UTF8";
try {
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
int i = 0, result = -1;
String id = "";
while (i<1000000) {
i = i+1;
//查找等待执行的数据
sql = "SELECT unit_id as id,style FROM test WHERE style = 0 LIMIT 1";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()){
id = rs.getString(1);
//锁定执行
stmt.executeUpdate("SET AUTOCOMMIT=0;");
stmt.executeUpdate("BEGIN WORK;");
sql = "SELECT unit_id as id,style FROM test WHERE unit_id = " + id + " AND style = 0 FOR UPDATE";
ResultSet rs1 = stmt.executeQuery(sql);
if (rs1.next()){
id = rs1.getString(1);
sql = "UPDATE test SET style = 1 WHERE unit_id = " + id;
stmt.executeUpdate(sql);
sql = "COMMIT;";
result = stmt.executeUpdate(sql);
if(result!=-1){
System.out.println("do things:" + id);
System.out.println(System.currentTimeMillis());
sql = "update test set style = style + 1 where unit_id = " + id;
result = stmt.executeUpdate(sql);
}
}
else{
stmt.executeUpdate("COMMIT;");
}
}else{
break;
}
}
} catch (SQLException e) {
System.out.println("MySQL操作错误");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
conn.close();
}
}
}
执行：java -cp :/home/wb-liqiu/java/libs/mysql-connector-java-5.1.13.jar Jdbc。相关原理：
四、执行在多个窗口执行这个程序，输出类似：
do things:38114 1390532207770 do things:38115 1390532207785 do things:38116 1390532207804 do things:38117 1390532207818 do things:38118 1390532207833 do things:38119 1390532207848 do things:38120 1390532207862 do things:38121 1390532207877 do things:38122 1390532207891 do things:38123 1390532207906 do things:38124 1390532207920 do things:38125 1390532207933 do things:38126 1390532207947 do things:38127 1390532207962
这样就可以统计同一秒，执行了多少任务了。
1390532370秒并发数量的命令：cat log3 | grep 1390532370 | wc -l
我测试的综合结果是：每秒QPS在60左右。如果增加WORKER，那么QPS依然在50左右徘徊，说明核心瓶颈是数据表的锁定时间
PS：问题
- QPS不能满足需要怎么办？考虑分表解决
- 如何再不增加QPS，如何完成更多的任务？一次锁定id+100行，然后update，再同时执行这些任务
