Sybase中的JDBC事务控制 [英] JDBC Transaction control in Sybase

问题描述

在JAVA中需要JDBC事务控制机制的帮助.

Need help in JDBC transaction control mechanism in JAVA.

问题:

我们的Sybase DB中有某些存储过程需要在取消链接模式下运行.由于我们正在两个不同的数据库(不幸的是，两个Sybase)上更新数据，因此，如果发生任何故障，我们需要能够回滚所有以前的事务.

There are certain stored procedures in our Sybase DB that needs to be run on Unchained mode. Since we are updating our data on two different databases (unfortunately, both Sybase) we need to be able to rollback all our previous transactions, if there is any failure.

但是以Unchained模式(自动提交-开启)运行并不能帮助我们进行回滚，因为某些SP已经提交了事务.

But running with Unchained Mode (Auto commit - on) is not helping us with the rollbacks as some of the SPs have already committed the transactions.

Connection connection = getConnection();
PreparedStatement ps = null;
try{
   String sql = getQuery(); // SQL Chained Mode 
   ps = connection.prepareStatement(sql); 
   ps.executeUpdate();        //Step 1
   .
   .
   sql = getTransctionQuery(); // SQL Unchained Mode
   connection.setAutoCommit(true);        //Step 2
   ps = connection.prepareStatement(sql); 
   ps.executeUpdate();
   connection.setAutoCommit(false);
   .
   .
   sql = getQuery(); // SQL Chained Mode 
   ps = connection.prepareStatement(sql);  
   ps.executeUpdate();     //Step 3 This step fails.
   connection.commit();
}catch(){
   connection.rollback(); //Doesn’t rollback step 1 and understandably step 2.
}
finally{
   connection.close();  //cleanup code
}

理想情况下，如果3失败，我们希望有效地回滚步骤1和步骤2.

We would ideally like to rollback both step 1 and step 2 effectively if 3 fails.

当前解决方案:

我们的想法是重新发明轮子并编写我们自己的回滚版本(通过从Java删除插入的记录并还原更新的值).

Our idea is to reinvent the wheel and write our own version of rollback (by deleting inserted records and reverting the updated values, from Java).

需要有效的解决方案

由于此解决方案需要大量的工作量，而且并非万无一失，所以我们想知道是否还有其他更好的解决方案.

Since this solution is effort intensive and not fool proof we would like to know if there are any other better solutions.

谢谢

推荐答案

您需要执行显式的BEGIN TRANSACTION语句.否则，每个DML本身就是您无法控制的事务.显然，自动提交也必须关闭.

You need to perform an explicit BEGIN TRANSACTION statement. Otherwise, every DML is a transaction by itself which you cannot control. Obviously autocommit must be off as well.

这篇关于Sybase中的JDBC事务控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！