JDBC · Lesson 7

Transactions in JDBC

What a Transaction Does

A transaction groups related database operations into one unit of work. Atomicity means either all required changes succeed or none do, protecting the database from half-completed workflows. JDBC connections commit automatically by default, so multi-step work usually needs explicit transaction control.

Commit and Rollback

Disable auto-commit, perform the related operations, and commit only after every step succeeds. Roll back in the failure path and restore the connection state before returning it to a pool.

import java.sql.Connection;
import java.sql.PreparedStatement;

public class EnrollmentService {
    public void enroll(Connection connection, long learnerId, long courseId) throws Exception {
        boolean originalAutoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        try {
            try (PreparedStatement enrollment = connection.prepareStatement(
                    "INSERT INTO enrollments (learner_id, course_id) VALUES (?, ?)")) {
                enrollment.setLong(1, learnerId);
                enrollment.setLong(2, courseId);
                enrollment.executeUpdate();
            }
            try (PreparedStatement audit = connection.prepareStatement(
                    "INSERT INTO enrollment_events (learner_id, course_id) VALUES (?, ?)")) {
                audit.setLong(1, learnerId);
                audit.setLong(2, courseId);
                audit.executeUpdate();
            }
            connection.commit();
        } catch (Exception failure) {
            connection.rollback();
            throw failure;
        } finally {
            connection.setAutoCommit(originalAutoCommit);
        }
    }
}

Transaction Choices

Common Mistake and Practices

Common Mistake: Catching a database exception, logging it, and continuing without a rollback. The connection may remain in a failed transaction state and corrupt later work when reused.

• Keep transaction boundaries close to the business operation.
• Roll back every failed transaction.
• Restore auto-commit before returning pooled connections.
• Keep transactions short to reduce lock contention.
• Choose an isolation level deliberately.

Real-World Use Case

Course enrollment inserts both an enrollment row and an audit event. A transaction ensures the learner never appears enrolled without a corresponding audit record.