Managing Commit and Rollback Operations in JDBC

Course: Advanced Java Training

Topic: Managing Commit and Rollback Operations in JDBC

Course Link: Advanced Java Training Course

Demo Link: Register for a Free Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction to Commit and Rollback Operations

In Java Database Connectivity (JDBC), commit and rollback operations are used to manage database transactions. A transaction is a group of database operations treated as one logical unit of work. These operations help maintain data consistency when inserting, updating, or deleting records.

A commit permanently applies the changes made during a transaction, while a rollback cancels uncommitted changes. These operations are especially important when a process involves multiple related SQL statements.

For example, transferring money between two bank accounts requires two operations: subtracting money from one account and adding it to another. If the debit succeeds but the credit fails, the transaction should be rolled back so that the database does not remain in an inconsistent state.

2. Learning Objectives

• Understand database transactions and their purpose.
• Learn how JDBC handles transactions by default.
• Disable auto-commit mode using setAutoCommit(false).
• Save changes using commit().
• Cancel uncommitted changes using rollback().
• Handle SQL exceptions during transaction processing.
• Use savepoints to roll back part of a transaction.
• Apply transaction management to practical database applications.

3. What Is a Database Transaction?

A database transaction is a sequence of SQL operations that should succeed or fail as a logical unit. Transactions are commonly used in banking, e-commerce, inventory management, booking systems, and employee management applications.

Example of a Transaction

Suppose an application transfers ₹1,000 from Account A to Account B. The transaction contains these steps:

1. Subtract ₹1,000 from Account A.
2. Add ₹1,000 to Account B.
3. Commit the transaction if both operations succeed.
4. Roll back the transaction if either operation fails before the commit.

Without transaction management, one operation could succeed while the other fails, producing incorrect account balances.

4. Understanding Auto-Commit Mode

JDBC connections use auto-commit mode by default. When auto-commit is enabled, each SQL statement is generally committed automatically after it completes successfully according to the JDBC driver's transaction behavior.

To manage several SQL statements as a single transaction, disable auto-commit before executing those statements.

Checking Auto-Commit Mode

boolean autoCommit = connection.getAutoCommit();
System.out.println("Auto-commit enabled: " + autoCommit);

Disabling Auto-Commit

connection.setAutoCommit(false);

After disabling auto-commit, the application controls when the transaction is committed or rolled back. Changes remain uncommitted until the application calls commit(), calls rollback(), or the transaction is otherwise ended by the connection or database. Do not rely on closing a connection to commit changes.

5. The commit() Method

The commit() method of the JDBC Connection interface ends the current transaction and makes its pending changes permanent in the database.

Syntax

connection.commit();

Example

Connection connection = null;
PreparedStatement statement = null;

try {
    connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/company",
        System.getenv("DB_USER"),
        System.getenv("DB_PASSWORD")
    );

    connection.setAutoCommit(false);

    String sql =
        "UPDATE employees SET salary = ? WHERE employee_id = ?";

    statement = connection.prepareStatement(sql);
    statement.setBigDecimal(1, new java.math.BigDecimal("65000.00"));
    statement.setInt(2, 101);

    int rowsUpdated = statement.executeUpdate();

    if (rowsUpdated == 1) {
        connection.commit();
        System.out.println("Transaction committed successfully.");
    } else {
        connection.rollback();
        System.out.println("No matching employee found. Transaction rolled back.");
    }
} catch (SQLException e) {
    if (connection != null) {
        try {
            connection.rollback();
        } catch (SQLException rollbackError) {
            e.addSuppressed(rollbackError);
        }
    }
    e.printStackTrace();
} finally {
    try {
        if (statement != null) statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    try {
        if (connection != null) connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Explanation: The connection is placed in manual transaction mode. The salary update is executed using a parameterized query. If exactly one employee row is updated, the transaction is committed. If no matching row is found, the transaction is rolled back. SQL errors also trigger a rollback attempt.

6. The rollback() Method

The rollback() method cancels changes made in the current uncommitted transaction. It restores the database to the transaction's starting state, or to a savepoint if the savepoint-specific rollback method is used.

Syntax

connection.rollback();

Example

Connection connection = null;

try {
    connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/company",
        System.getenv("DB_USER"),
        System.getenv("DB_PASSWORD")
    );

    connection.setAutoCommit(false);

    try (PreparedStatement statement = connection.prepareStatement(
            "UPDATE products SET stock = stock - ? WHERE product_id = ?")) {
        statement.setInt(1, 5);
        statement.setInt(2, 201);

        int rowsUpdated = statement.executeUpdate();

        if (rowsUpdated != 1) {
            throw new SQLException("Product not found or stock update failed.");
        }
    }

    // Perform other related transaction operations here.
    // If all operations succeed:
    connection.commit();

} catch (SQLException e) {
    if (connection != null) {
        try {
            connection.rollback();
            System.out.println("Transaction rolled back.");
        } catch (SQLException rollbackError) {
            e.addSuppressed(rollbackError);
            System.err.println("Rollback failed; verify the database state.");
        }
    }
    e.printStackTrace();
} finally {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This example demonstrates the transaction structure. In a production inventory application, validate that sufficient stock is available and check the affected row count before committing.

7. Commit vs. Rollback

8. Complete Example: Transferring Money Between Accounts

This example demonstrates a transfer that updates two account records within one transaction. It assumes an accounts table with account_id and balance columns, and that the database enforces suitable constraints such as a non-negative balance.

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BankTransfer {
    public static void transfer(
            int fromAccount,
            int toAccount,
            BigDecimal amount) throws SQLException {

        if (amount == null || amount.signum() <= 0) {
            throw new IllegalArgumentException(
                "Transfer amount must be greater than zero."
            );
        }

        String url = "jdbc:mysql://localhost:3306/banking";
        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        try (Connection connection =
                 DriverManager.getConnection(url, user, password)) {

            connection.setAutoCommit(false);

            try {
                int debited;

                try (PreparedStatement debit = connection.prepareStatement(
                        "UPDATE accounts SET balance = balance - ? " +
                        "WHERE account_id = ? AND balance >= ?")) {
                    debit.setBigDecimal(1, amount);
                    debit.setInt(2, fromAccount);
                    debit.setBigDecimal(3, amount);
                    debited = debit.executeUpdate();
                }

                if (debited != 1) {
                    throw new SQLException(
                        "Source account missing or insufficient funds."
                    );
                }

                int credited;

                try (PreparedStatement credit = connection.prepareStatement(
                        "UPDATE accounts SET balance = balance + ? " +
                        "WHERE account_id = ?")) {
                    credit.setBigDecimal(1, amount);
                    credit.setInt(2, toAccount);
                    credited = credit.executeUpdate();
                }

                if (credited != 1) {
                    throw new SQLException("Destination account not found.");
                }

                connection.commit();
                System.out.println("Transfer completed successfully.");

            } catch (SQLException | RuntimeException e) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackError) {
                    e.addSuppressed(rollbackError);
                }
                throw e;
            }
        }
    }

    public static void main(String[] args) {
        try {
            transfer(1001, 1002, new BigDecimal("1000.00"));
        } catch (SQLException | RuntimeException e) {
            System.err.println("Transfer failed: " + e.getMessage());
        }
    }
}

How the Example Works

1. The application validates the transfer amount.
2. The connection disables auto-commit.
3. The debit statement subtracts the amount only if the source account has enough balance.
4. The credit statement adds the same amount to the destination account.
5. If both updates affect exactly one row, the transaction is committed.
6. If a SQL or runtime error occurs before commit, the code attempts to roll back the transaction.

Important: This is an educational example. Production banking systems also require authorization, transaction logging, concurrency controls, idempotency where appropriate, and careful handling of uncertain outcomes if the connection fails during commit. A commit exception does not always prove that the database made no changes.

9. Using Savepoints for Partial Rollback

A savepoint marks a position inside a transaction. It allows an application to roll back some operations without necessarily cancelling all earlier operations in the transaction.

Creating and Using a Savepoint

import java.sql.Connection;
import java.sql.Savepoint;
import java.sql.SQLException;

connection.setAutoCommit(false);

try {
    // First group of database operations.

    Savepoint savepoint = connection.setSavepoint("BeforeOptionalUpdate");

    try {
        // Perform an optional database operation.
        // If it fails and the application can safely continue:
    } catch (SQLException e) {
        connection.rollback(savepoint);
        System.out.println("Rolled back to the savepoint.");
    }

    // Continue with required operations.
    connection.commit();

} catch (SQLException e) {
    try {
        connection.rollback();
    } catch (SQLException rollbackError) {
        e.addSuppressed(rollbackError);
    }
    throw e;
}

Note: This is a structural example; replace the comments with actual SQL operations and ensure exceptions are handled according to the application's business rules. Savepoint support depends on the database and JDBC driver. A savepoint does not independently commit earlier changes.

10. Transaction Properties: ACID

11. Handling Exceptions During Transactions

Transaction code must account for failures during SQL execution, rollback, and commit. A rollback can also fail, for example if the connection has already been lost. In that situation, the application should preserve both errors and verify the final database state where necessary.

• Use try-catch blocks to handle SQLException.
• Attempt rollback when a transaction fails before successful completion.
• Keep rollback errors instead of silently ignoring them.
• Use try-with-resources for connections and statements when appropriate.
• Do not assume that a commit exception means the transaction definitely failed; the outcome can be uncertain after a network interruption.
• Log useful diagnostic information without exposing passwords, tokens, or sensitive database data.

12. Common Mistakes and Troubleshooting

13. Best Practices for Transaction Management

• Disable auto-commit before starting a group of related database operations.
• Commit only after all required operations and validations succeed.
• Roll back the transaction when an operation fails before a successful commit.
• Use PreparedStatement for parameterized SQL statements.
• Keep transactions short to reduce lock contention and improve performance.
• Use database constraints to protect important business rules.
• Check the number of affected rows after updates and deletes.
• Use try-with-resources to release JDBC resources.
• Do not share a single JDBC connection across unrelated transactions without appropriate coordination.
• Remember that transaction behavior depends on the database, table engine, driver, and transaction configuration.
• For production applications, consider connection pools and transaction management frameworks such as Spring.

14. Practice Exercises

1. Create a JDBC program that inserts a new employee and commits the transaction.
2. Update two employee records in one transaction and roll back both updates if either operation fails.
3. Create a banking transaction that transfers money between two accounts.
4. Use a savepoint before an optional update and roll back to it if the optional operation fails.
5. Write exception-handling code that attempts rollback and preserves rollback exceptions.
6. Test the behavior of auto-commit mode by inserting a record with auto-commit enabled and disabled.
7. Investigate what happens when a database connection is interrupted during a commit.

15. Interview Questions and Answers

Q1. What is a transaction in JDBC?

A transaction is a group of database operations managed as a logical unit of work. It helps maintain data integrity when several related statements must succeed together.

Q2. What is the purpose of commit()?

The commit() method makes changes in the current transaction permanent. It is normally used when auto-commit mode is disabled.

Q3. What is the purpose of rollback()?

The rollback() method cancels changes made in the current uncommitted transaction.

Q4. What is auto-commit mode?

Auto-commit mode automatically commits SQL statements individually when they complete. It is enabled by default for a newly created JDBC connection.

Q5. How do you disable auto-commit?

Use connection.setAutoCommit(false) before executing the statements that belong to the transaction.

Q6. What happens if an exception occurs during a transaction?

The application should attempt to roll back the current transaction if it has not been committed. Rollback can itself fail, so that failure should also be handled.

Q7. What is a savepoint?

A savepoint marks a position within a transaction. The application can use rollback(savepoint) to undo operations performed after that point while retaining earlier uncommitted work.

Q8. Can rollback undo a committed transaction?

No. A normal rollback cannot undo changes that have already been committed. Reversing committed business operations generally requires a separate compensating transaction or another recovery process.

Q9. Why should transactions be kept short?

Short transactions reduce the time database locks are held and can help prevent contention, deadlocks, and performance problems.

Q10. What is the difference between rollback() and rollback(savepoint)?

rollback() undoes the current transaction's uncommitted changes. rollback(savepoint) undoes changes made after a particular savepoint.

16. Quick Revision Checklist

• Understand the purpose of database transactions.
• Know that JDBC connections use auto-commit mode by default.
• Use setAutoCommit(false) for manual transaction management.
• Use commit() to persist successful transaction changes.
• Use rollback() to cancel uncommitted changes after failure.
• Use savepoints when partial rollback is appropriate.
• Handle SQL and rollback exceptions carefully.
• Verify transaction support and configuration in the target database.
• Apply transaction management to banking, orders, inventory, and employee applications.

17. Official Documentation and Further Learning

• Java JDBC Connection Interface Documentation
• Oracle JDBC Transactions Tutorial
• MySQL Connector/J Documentation

18. Conclusion

Managing commit and rollback operations is an essential JDBC skill for building reliable Java database applications. By disabling auto-commit, grouping related SQL statements into transactions, committing successful operations, and rolling back failed operations, developers can help preserve data consistency. Savepoints provide additional control when only part of a transaction needs to be reversed.

To learn more about Java database programming and enterprise application development, explore the Advanced Java Training Course at JustAcademy, visit the JustAcademy homepage, or register for a course demo.