CODEWITHSUNDEEP
javaoracle-databasejdbc
Vy Do
Vy Do

Reputation: 52516

Why set connection.setAutoCommit(false) , insert, then rollback, still insert data to database?

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, "[email protected]");
                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();
        }
    }

}

enter image description here

enter image description here

Why set connection.setAutoCommit(false) , insert, then rollback, still insert data to database?

Upvotes: 0

Views: 212

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108992

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).

Upvotes: 2

Related Questions