1.PG安装的两套,分别在两个主机上;
2.mycat将employee表,分成10片,每5片对应一个数据库(由于没有环境安装10个pg数据库)。配置文件如下:
table name="employee" primaryKey="eid" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10" rule="auto-sharding-long" /dataNode name="dn1" dataHost="indexdb1" database="idx"
dataNode name="dn2" dataHost="indexdb2" database="idx"
......
dataNode name="dn9" dataHost="indexdb1" database="idx"
dataNode name="dn10" dataHost="indexdb2" database="idx"
3、通过java应用插入数据。pg数据库也进行了xa相关设置
for(int i=1;i<=500;i++){
try {
con = DriverManager.getConnection(
");
con.setAutoCommit(false);
con.prepareStatement("set xa=on").execute();
sql = "insert into employee(eid,name,level) values("+i+",'txb2','2')";
rt = con.prepareStatement(sql).execute();
System.out.println(i);
sql = "insert into employee(eid,name,level) values("+ (i + 10000) + ",'tommy','2')";
//rt = stmt.execute(sql); tommy
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 20000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 30000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 40000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 50000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 60000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 70000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 80000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute(); tommy
sql = "insert into employee(eid,name,level) values("+ (i + 90000) + ",'tommy','2')";
rt = con.prepareStatement(sql).execute();
con.commit();
con.close();
System.out.println(rt);
} catch (Exception e) {
System.out.println(e);
System.out.println(rt);
try {
con.rollback();
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
4.插入数据过程中,将其中1台pg数据库,通过命令关闭,或者kill -9 杀掉。反复操作。无论哪种情况,期望结果应该是,两台数据库数据条数相同,且为5的倍数。实际测试结果,条数不等,且也不为5的倍数。即同一个事务中,部分执行完成,当数据库连接断开后,并没有完全回滚。同样的数据库分成2片的时候,未测出该问题。 烦请大神帮助解决,多谢!