接前文
http://blog.itpub.net/29254281/viewspace-2119080/

两个数据库假设在不同的服务器,不同的实例

drop database db1;
drop database db2;
create database db1;
use db1;
create table t(translog int primary key,ts timestamp default current_timestamp);
create database db2;
use db2;
create table t(translog int primary key,ts timestamp default current_timestamp);

如果像前文那样同步写两个库,
那么第一个库的连接和锁持有时间,都会翻倍.
可以尝试多线程处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class Test {
private static ExecutorService threadpool = Executors.newFixedThreadPool(10);
public void shutdown() {
threadpool.shutdown();
}
private synchronized static Connection getConnection(String dbName) {
String URL = "jdbc:mysql://127.0.0.1:3306/" + dbName;
String USERNAME = "xx";
String PWD = "xx";
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(URL, USERNAME, PWD);
con.setAutoCommit(false);
} catch (SQLException e) {
} catch (ClassNotFoundException e) {
}
return con;
}
public void insertTranslogid(int translogid) {
Connection db1 = getConnection("db1");
Connection db2 = getConnection("db2");
CountDownLatch latch = new CountDownLatch(2);
Worker worker1 = new Worker(db1, translogid, latch);
Worker worker2 = new Worker(db2, translogid, latch);
threadpool.submit(worker1);
threadpool.submit(worker2);
try {
latch.await();
} catch (InterruptedException e1) {
e1.printStackTrace();
}
try {
if (worker1.isSuccessed() && worker2.isSuccessed()) {
db1.commit();
db2.commit();
} else {
db1.rollback();
db2.rollback();
}
db1.close();
db2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
long start=System.currentTimeMillis();
Test t = new Test();
for (int i = 0; i <= 500; i++) {
t.insertTranslogid(i);
}
t.shutdown();
long end=System.currentTimeMillis();
System.out.println((end-start)/1000);
}
}
class Worker implements Runnable {
public Worker(Connection con, int translogid, CountDownLatch latch) {
this.con = con;
this.translogid = translogid;
this.latch = latch;
}
private CountDownLatch latch;
private Connection con;
private int translogid;
private boolean successed=false;
public boolean isSuccessed() {
return successed;
}
@Override
public void run() {
PreparedStatement pt;
try {
pt = con.prepareStatement("insert into t(translog) values(?)");
pt.setInt(1, translogid);
pt.execute();
successed = true;
} catch (SQLException e) {
e.printStackTrace();
successed = false;
} finally {
latch.countDown();
}
}
}

501个Translog,用时29s
前文的处理时间为32s...似乎提高并不多...

这程序其实放生产,自己也没有什么把握.
有时候感觉压力很大.新单位已经2年了.
数据库还是一知半解.JAVA程序也忘的差不多了.其实原来JAVA也是一知半解.
希望2016年自己还能有一些突破吧.

ITPub博客

同步写两个数据库--多线程

最新文章