Implementing Database Transactions in JDBC

Course: Advanced Java Training

Topic: Implementing Database Transactions

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Database Transactions

A database transaction is a group of one or more database operations treated as a single logical unit of work. These operations may include inserting records, updating values, deleting data, or transferring information between tables.

In Java applications, JDBC (Java Database Connectivity) provides methods to manage database transactions. Transactions help maintain data consistency when several related operations must succeed together. If an operation fails, an application can roll back the transaction to avoid leaving the database in an unintended intermediate state.

For example, transferring money between two bank accounts involves deducting money from one account and adding it to another. Both operations should succeed together. If the debit succeeds but the credit fails, the application should not leave the accounts in an inconsistent state.

2. Learning Objectives

• Understand the purpose and properties of database transactions.
• Learn how JDBC handles auto-commit mode.
• Use setAutoCommit(false), commit(), and rollback().
• Implement transaction handling using Connection.
• Handle SQL exceptions and transaction failures.
• Understand savepoints and transaction isolation levels.
• Apply transaction management to real-world Java applications.

3. Why Are Database Transactions Important?

Without proper transaction management, an application may save only part of a related operation. This can produce inconsistent records, incorrect account balances, incomplete orders, or inaccurate inventory information.

Example: Bank Transfer

Suppose Account A has ₹10,000 and Account B has ₹5,000. An application transfers ₹2,000 from Account A to Account B.

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

The expected final balances are ₹8,000 for Account A and ₹7,000 for Account B. Transaction management helps ensure that the transfer is handled as one logical operation.

4. Understanding ACID Properties

Reliable relational database transactions are commonly described using the ACID properties.

5. JDBC Transaction Management Methods

6. Understanding Auto-Commit Mode

JDBC connections commonly start with auto-commit enabled. In this mode, each completed SQL statement is committed automatically according to the JDBC and database behavior. This is convenient for independent operations but may not be suitable for a group of related operations.

To manage several operations as one transaction, disable auto-commit before executing them.

Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/company",
    "root",
    "your_password"
);

con.setAutoCommit(false);

// Execute related SQL operations here.

con.commit();

In production applications, place the connection and statements inside appropriate resource-management structures, and make sure errors trigger the required rollback behavior.

7. Implementing a Basic JDBC Transaction

The following example updates two employee records within one transaction. If an SQL operation fails before commit, the application attempts to roll back the transaction.

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

public class BasicTransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company";
        String user = "root";
        String password = "your_password";

        String sql =
            "UPDATE employees SET salary = salary + ? WHERE id = ?";

        try (Connection con =
                 DriverManager.getConnection(url, user, password);
             PreparedStatement ps = con.prepareStatement(sql)) {

            con.setAutoCommit(false);

            try {
                ps.setDouble(1, 2000.00);
                ps.setInt(2, 101);
                ps.executeUpdate();

                ps.setDouble(1, 3000.00);
                ps.setInt(2, 102);
                ps.executeUpdate();

                con.commit();
                System.out.println("Transaction committed successfully.");

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

        } catch (SQLException e) {
            System.out.println("Transaction failed: " + e.getMessage());
        }
    }
}

Explanation

1. The program establishes a connection to the database.
2. A prepared SQL statement is created for updating employee salaries.
3. Auto-commit is disabled to begin application-managed transaction handling.
4. The first employee's salary is updated.
5. The second employee's salary is updated.
6. If both operations succeed, commit() finalizes the transaction.
7. If an SQL exception occurs before commit, the program attempts rollback().

Important: This example demonstrates the transaction pattern. A real application should also check the affected-row count returned by executeUpdate() when it must verify that each employee record exists and was updated.

8. Implementing a Bank Transfer Using JDBC Transactions

This example demonstrates a practical transaction involving two bank accounts. It deducts money from one account and credits another account. Both updates must succeed before the transaction is committed.

Sample Database Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(12, 2) NOT NULL
);

INSERT INTO accounts VALUES
(1, 'Amit', 10000.00),
(2, 'Priya', 5000.00);

Java Program

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

public class BankTransferTransaction {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company";
        String user = "root";
        String password = "your_password";

        int fromAccount = 1;
        int toAccount = 2;
        BigDecimal amount = new BigDecimal("2000.00");

        if (amount.signum() <= 0) {
            throw new IllegalArgumentException(
                "Transfer amount must be positive."
            );
        }

        String debitSql =
            "UPDATE accounts SET balance = balance - ? " +
            "WHERE account_id = ? AND balance >= ?";

        String creditSql =
            "UPDATE accounts SET balance = balance + ? " +
            "WHERE account_id = ?";

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

            con.setAutoCommit(false);

            try {
                try (PreparedStatement debit =
                         con.prepareStatement(debitSql);
                     PreparedStatement credit =
                         con.prepareStatement(creditSql)) {

                    debit.setBigDecimal(1, amount);
                    debit.setInt(2, fromAccount);
                    debit.setBigDecimal(3, amount);

                    int debitRows = debit.executeUpdate();

                    if (debitRows != 1) {
                        throw new SQLException(
                            "Source account not found or insufficient balance."
                        );
                    }

                    credit.setBigDecimal(1, amount);
                    credit.setInt(2, toAccount);

                    int creditRows = credit.executeUpdate();

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

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

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

        } catch (SQLException e) {
            System.out.println("Transfer failed: " + e.getMessage());
        }
    }
}

Program Explanation

1. Validate the amount: The transfer amount must be greater than zero.
2. Disable auto-commit: Both account updates become part of a manually controlled transaction.
3. Debit the source: The SQL condition checks that the source account has enough money.
4. Verify the debit: If exactly one account row was not updated, an exception is raised.
5. Credit the destination: The destination account receives the transfer amount.
6. Verify the credit: The destination account must exist and be updated.
7. Commit or roll back: Both changes are committed on success. If a SQL error occurs before commit, the program attempts to roll back.

Production consideration: This example illustrates the basic transaction pattern. Real financial systems also need controls for concurrent transfers, account authorization, duplicate requests, transaction timeouts, audit records, and uncertain outcomes if the connection fails while the commit result is being returned.

9. Using Savepoints in JDBC

A savepoint is a checkpoint inside a transaction. It allows an application to roll back part of a transaction without necessarily discarding all the work performed before that checkpoint.

Savepoints are useful when an application processes several independent steps but wants to preserve earlier successful steps if a later optional step fails. Savepoint support and behavior depend on the database and JDBC driver.

Example of Savepoint Usage

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

public class SavepointExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company";
        String user = "root";
        String password = "your_password";

        String sql =
            "INSERT INTO departments (id, name) VALUES (?, ?)";

        try (Connection con =
                 DriverManager.getConnection(url, user, password);
             PreparedStatement ps = con.prepareStatement(sql)) {

            con.setAutoCommit(false);

            try {
                ps.setInt(1, 501);
                ps.setString(2, "Human Resources");
                ps.executeUpdate();

                Savepoint savepoint = con.setSavepoint("AFTER_HR");

                try {
                    ps.setInt(1, 502);
                    ps.setString(2, "Information Technology");
                    ps.executeUpdate();

                    // Keep both inserts if no error occurs.
                    con.commit();

                } catch (SQLException optionalStepError) {
                    con.rollback(savepoint);

                    // Preserve the earlier insert and commit it.
                    con.commit();
                }

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

        } catch (SQLException e) {
            System.out.println("Transaction error: " + e.getMessage());
        }
    }
}

In this example, if the second insert fails and rollback to the savepoint succeeds, the first insert remains part of the active transaction. The program then commits that earlier work. In a production implementation, a failure during savepoint rollback or commit must be handled carefully; do not assume the transaction is safely completed if those calls fail.

Savepoint Methods

• setSavepoint() creates an unnamed savepoint.
• setSavepoint("NAME") creates a named savepoint.
• rollback(savepoint) undoes work performed after the savepoint.
• releaseSavepoint(savepoint) releases a savepoint when supported.

10. Transaction Isolation Levels

Transaction isolation controls how concurrent transactions can see each other's changes. JDBC provides constants for standard isolation levels, but the database determines which levels and behaviors it supports.

Common Concurrency Problems

• Dirty read: A transaction reads changes that another transaction has not committed.
• Non-repeatable read: A transaction reads the same row twice and sees different committed values.
• Phantom read: Repeating a query returns a different set of rows because another transaction inserted or deleted matching records.
• Lost update: One transaction overwrites another transaction's update, depending on the application logic and concurrency controls.

Setting the Isolation Level

Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/company",
    "root",
    "your_password"
);

con.setTransactionIsolation(
    Connection.TRANSACTION_READ_COMMITTED
);

con.setAutoCommit(false);

// Perform related database operations.

con.commit();

Set transaction configuration before executing the work that depends on it. Verify support through database metadata and test the behavior with the actual database and driver. Stronger isolation is not automatically the best choice for every application.

11. Handling SQL Exceptions During Transactions

A transaction may fail because of constraint violations, connection problems, deadlocks, invalid SQL, or database errors. An application should respond deliberately rather than assuming that catching an exception automatically reverses every operation.

Recommended Error-Handling Strategy

1. Catch the relevant SQLException.
2. Attempt to roll back the active transaction if the connection state permits it.
3. Preserve rollback errors as suppressed exceptions or report them separately.
4. Log useful diagnostic information without exposing credentials or sensitive data.
5. Do not report success unless the commit completes successfully.
6. If the commit outcome is uncertain because of a connection failure, reconcile the database state before retrying a business operation.

Useful SQLException Information

catch (SQLException e) {
    System.out.println("Message: " + e.getMessage());
    System.out.println("SQL State: " + e.getSQLState());
    System.out.println("Error Code: " + e.getErrorCode());
}

SQL state and vendor error codes can help identify the type of failure. Retry logic should be based on known transient errors and designed to avoid duplicate business operations.

12. Common Mistakes in Transaction Management

13. Best Practices for JDBC Transactions

• Use PreparedStatement for parameterized SQL operations.
• Disable auto-commit only when explicit transaction management is required.
• Group logically related operations in the same transaction.
• Commit after all required steps succeed.
• Attempt rollback when a transaction fails before commit.
• Keep transactions short to reduce lock contention.
• Check affected-row counts when business rules depend on a record being updated.
• Choose isolation levels based on consistency requirements and concurrency needs.
• Use try-with-resources for statements and connections.
• Handle rollback and commit exceptions rather than ignoring them.
• Test transaction behavior against the actual database engine.
• Use connection pools appropriately in server applications.
• For transactions spanning multiple services or databases, consider suitable distributed transaction or application-level consistency patterns instead of assuming a single JDBC connection can coordinate everything.

14. Practical Project: Order Processing System

Project objective: Implement a Java application that creates an order, records order items, and updates inventory using a database transaction.

Suggested Workflow

1. Validate the customer, products, quantities, and prices.
2. Disable auto-commit on the JDBC connection.
3. Insert the order header into an orders table.
4. Insert each order item into an order-details table.
5. Update stock quantities using conditional SQL updates that prevent stock from dropping below the allowed minimum.
6. Verify the affected-row counts for required updates.
7. Commit only if all required operations succeed.
8. Roll back if any required operation fails.
9. Return an order confirmation after successful completion.

Expected Outcome

The project demonstrates how transaction management protects related database operations from being saved only partially. It also introduces validation, SQL constraints, exception handling, and reliable order-processing logic.

15. Practice Exercises

1. Write a JDBC program that inserts two related records and commits them as one transaction.
2. Modify the program to roll back when the second insert fails.
3. Implement a bank transfer using debit and credit operations.
4. Create a savepoint and roll back only the operations performed after it.
5. Set the transaction isolation level to TRANSACTION_READ_COMMITTED and verify database support.
6. Simulate a duplicate-key exception and observe the rollback behavior.
7. Build an order-processing workflow that updates both order details and inventory.
8. Measure the effect of transaction duration on concurrent database operations.

16. Interview Questions and Answers

Q1. What is a database transaction?

Answer: A database transaction is a logical unit of work containing one or more operations that are managed together to preserve data integrity.

Q2. What are the ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability.

Q3. What is auto-commit mode in JDBC?

Answer: Auto-commit mode automatically commits each completed SQL statement according to JDBC and database behavior.

Q4. How do you disable auto-commit?

Answer: Call connection.setAutoCommit(false).

Q5. What is the purpose of commit()?

Answer: It commits the changes made during the current transaction.

Q6. What is the purpose of rollback()?

Answer: It attempts to undo uncommitted changes in the current transaction.

Q7. What is a savepoint?

Answer: A savepoint is a checkpoint within a transaction that permits partial rollback where supported.

Q8. What is transaction isolation?

Answer: Transaction isolation determines how concurrent transactions interact and which changes they can observe.

Q9. What happens if commit() fails?

Answer: The application must treat the outcome carefully. A connection failure can leave the final outcome uncertain, so the application may need to reconcile the database state before retrying.

Q10. Why should transactions be short?

Answer: Short transactions reduce the time locks and database resources are held, helping limit contention and improve concurrency.

17. Quick Revision Checklist

• A transaction groups related database operations.
• ACID describes important transaction properties.
• setAutoCommit(false) enables explicit transaction control.
• commit() finalizes successful transaction changes.
• rollback() attempts to undo uncommitted changes.
• Savepoints enable partial rollback when supported.
• Isolation levels influence concurrent transaction behavior.
• SQL exceptions require deliberate error handling.
• Transactions should be short and focused.
• Commit, rollback, and connection failures must be handled explicitly.

18. Conclusion

Implementing database transactions is a core skill in Advanced Java development. JDBC provides transaction-control methods through the Connection interface, allowing developers to group related SQL operations, commit successful changes, and roll back incomplete work. Understanding ACID properties, isolation levels, savepoints, and exception handling helps developers build more reliable applications for banking, order management, inventory systems, and other data-intensive solutions.

Continue learning through the Advanced Java Training Course. You can also register for a course demo or visit the JustAcademy website.