英文:
Why set connection.setAutoCommit(false) , insert, then rollback, still insert data to database?
问题
我环境：Windows 11 x64，Oracle数据库 21c Express Edition，Java /JDK 19，IntelliJ IDEA 2023.x。我有以下代码：
select banner from v$version;
-- Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.ZoneId;
public class VyInsert {
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "12345678")) {
if (connection != null) {
connection.setAutoCommit(false);
System.out.println("Connected to the database.");
String query = "insert into SYSTEM.CUSTOMER (ID, NAME, EMAIL, CREATED_DATE) values (?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, 17);
preparedStatement.setString(2, "Nguyen Thu Hang23");
preparedStatement.setString(3, "anhtrangmuathu32@gmail.com");
preparedStatement.setObject(4, LocalDate.now(ZoneId.of("America/Montreal")));
// preparedStatement.setObject(4, LocalDate.now(ZoneId.of("Asia/Ho_Chi_Minh")));
preparedStatement.executeUpdate();
// connection.commit();
//connection.setAutoCommit(true);
} else {
System.out.println("Failed to make connection.");
}
} catch (SQLException sqlException) {
System.err.format("SQL State: %s\n%s", sqlException.getSQLState(), sqlException.getMessage());
} catch (Exception exception) {
exception.printStackTrace();
}
}
}
为什么设置connection.setAutoCommit(false)，然后插入数据，然后回滚，数据仍然插入到数据库？
英文:
My environment: Windows 11 x64, Oracle database 21c Express edtion, Java /JDK 19,IntelliJ IDEA 2023.x . I have
select banner from v$version;
-- Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.ZoneId;
public class VyInsert {
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "12345678")) {
if (connection != null) {
connection.setAutoCommit(false);
System.out.println("Connected to the database.");
String query = "insert into SYSTEM.CUSTOMER (ID, NAME, EMAIL, CREATED_DATE) values (?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, 17);
preparedStatement.setString(2, "Nguyen Thu Hang23");
preparedStatement.setString(3, "anhtrangmuathu32@gmail.com");
preparedStatement.setObject(4, LocalDate.now(ZoneId.of("America/Montreal")));
// preparedStatement.setObject(4, LocalDate.now(ZoneId.of("Asia/Ho_Chi_Minh")));
preparedStatement.executeUpdate();
// connection.commit();
//connection.setAutoCommit(true);
} else {
System.out.println("Failed to make connection.");
}
} catch (SQLException sqlException) {
System.err.format("SQL State: %s\n%s", sqlException.getSQLState(), sqlException.getMessage());
} catch (Exception exception) {
exception.printStackTrace();
}
}
}
Why set connection.setAutoCommit(false) , insert, then rollback, still insert data to database?
答案1
得分: 2
您的代码从未调用connection.rollback()，而Oracle JDBC驱动程序（或Oracle本身）是少数在关闭连接时提交打开的事务的驱动程序之一。因此，一旦您的应用程序退出try-with-resource块，应用程序所做的更改已经被提交。
要知道，大多数JDBC驱动程序（或DBMS）在关闭连接时会回滚活动事务，而Oracle在这方面是个例外。
此外，您的截图显示您在一个不相关的应用程序中调用了回滚。rollback语句只能回滚该应用程序及其连接的该事务，而不能回滚完全不同应用程序及其连接的事务（尤其是当连接已关闭且事务已提交时）。
英文:
Your code never calls connection.rollback(), and the Oracle JDBC driver (or Oracle itself) is one of the few out there which commit an open transaction when you close the connection. So, once your application exits the try-with-resource block, the changes made by your application have already been committed.
Know that most JDBC drivers (or DBMSes) will rollback active transactions on connection close, and Oracle is an exception in this regard.
In addition, your screenshot shows you calling rollback in an unrelated application. That rollback statement can only roll back that transaction of that application and its connection, not the transaction of an entirely different application and its connection (especially not when that connection is already closed and its transaction committed).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论