What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit of work. Transactions are used to ensure data integrity and consistency, and they adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability).

Transaction Management in JDBC

Enabling Auto-Commit Mode

By default, JDBC operates in auto-commit mode, where each SQL statement is treated as a transaction and is automatically committed after execution. To manage transactions manually, you need to disable auto-commit mode.

  • Disable Auto-Commit Mode:

      connection.setAutoCommit(false);

Committing Transactions

  • Commit a Transaction:

    • Use the commit method of the Connection object to save all changes made during the transaction.

    • Example:

        connection.commit();

Rolling Back Transactions

  • Rollback a Transaction:

    • Use the rollback method to revert all changes made during the transaction if an error occurs or if you need to undo changes.

    • Example:

        connection.rollback();

Example Code

Here’s a complete example demonstrating transaction management:

  import java.sql.*;

public class TransactionManagementExample {
    public static void main(String[] args) {
        String url = "jdbc:your_database_url";
        String username = "your_username";
        String password = "your_password";

        Connection connection = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            // Establish a connection
            connection = DriverManager.getConnection(url, username, password);

            // Disable auto-commit mode
            connection.setAutoCommit(false);

            // Create and execute the first SQL statement
            String sql1 = "INSERT INTO accounts (account_id, balance) VALUES (?, ?)";
            pstmt1 = connection.prepareStatement(sql1);
            pstmt1.setInt(1, 101);
            pstmt1.setDouble(2, 5000.00);
            pstmt1.executeUpdate();

            // Create and execute the second SQL statement
            String sql2 = "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
            pstmt2 = connection.prepareStatement(sql2);
            pstmt2.setDouble(1, 2000.00);
            pstmt2.setInt(2, 102);
            pstmt2.executeUpdate();

            // Commit the transaction
            connection.commit();
            System.out.println("Transaction committed successfully.");
        } catch (SQLException e) {
            // Rollback the transaction in case of error
            if (connection != null) {
                try {
                    System.out.println("Rolling back transaction.");
                    connection.rollback();
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (pstmt1 != null) pstmt1.close();
                if (pstmt2 != null) pstmt2.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Key Points

  • Atomicity: Ensures that all operations within the transaction are completed successfully; otherwise, none are applied.
  • Consistency: Guarantees that the database remains in a consistent state before and after the transaction.
  • Isolation: Ensures that transactions are isolated from one another to prevent data anomalies.
  • Durability: Guarantees that once a transaction is committed, the changes are permanent, even in the case of a system failure.

Best Practices

  • Use Transactions for Multiple Related Operations: Transactions are essential when multiple related operations need to be executed together.
  • Handle Exceptions Gracefully: Ensure that transactions are rolled back in case of errors to maintain data integrity.
  • Close Resources: Always close database resources to prevent memory leaks and other issues.