What is a Transaction?

A transaction is a logical unit of work that transforms data from one consistent state to another. It ensures that a series of operations either all succeed or all fail together.

Transaction Processing

Transactions guarantee atomic execution: if any part fails, the entire units rolled back to its initial state. Upon successful completion, changes are permanently committed. This mechanism preserves data integrity even in the event of system failures.

To maintain consistency, database operations must be grouped into discrete units. Only when every step completes successfully should the changes take effect; otherwise, the system reverts to its prior state.

The ACID Properties

1. Atomicity: The transaction is indivisible—all actions within it execute completely or not at all.
2. Consistency: A transaction transitions the database from one valid state to another, preserving all defined rules and constraints.
3. Isolation: Concurrent transactions do not interfere with each other. Each operates as if it were running alone.
4. Durability: Once committed, a transaction’s effects persist permanently, surviving subsequent crashes or errors.

Concurrency Issues Without Proper Isolation

When multiple transactions access shared data simultaneously without adequate isolation, the following anomalies may occur:

• Dirty Read: Transaction T1 reads data modified by T2 before T2 commits. If T2 later rolls back, T1 has read invalid (temporary) data.
• Non-Repeatable Read: T1 reads a value, then T2 updates it. When T1 reads again, the value differs.
• Phantom Read: T1 performs a query, then T2 inserts new rows matching T1’s criteria. On re-execution, T1 sees additional rows it didn’t see before.

Isolation levels define how strictly transactions are separated. Higher isolation improves consistency but reduces concurrency performance.

SQL Standard Isolation Levels

Implementation Example Using JDBC

Configuration File: jdbc.properties

user=root
password=00000000
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

Utility Class: JDBCUtils.java

public class JDBCUtils {
    public static Connection getConnection() throws Exception {
        Properties props = new Properties();
        try (InputStream is = JDBCUtils.class.getClassLoader()
                .getResourceAsStream("jdbc.properties")) {
            props.load(is);
        }
        String url = props.getProperty("url");
        String user = props.getProperty("user");
        String password = props.getProperty("password");
        String driver = props.getProperty("driverClass");
        
        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }

    public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) try { rs.close(); } catch (SQLException ignored) {}
        if (stmt != null) try { stmt.close(); } catch (SQLException ignored) {}
        if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
    }
}

Transactional Update Method

public int executeUpdate(Connection conn, String sql, Object... params) {
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
        return ps.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

Example: Atomic Money Transfer

@Test
public void testMoneyTransfer() {
    try (Connection conn = JDBCUtils.getConnection()) {
        conn.setAutoCommit(false);

        executeUpdate(conn, "UPDATE accounts SET balance = balance - 100 WHERE name = ?", "Alice");
        // Simulate failure
        if (true) throw new RuntimeException("Network error");

        executeUpdate(conn, "UPDATE accounts SET balance = balance + 100 WHERE name = ?", "Bob");

        conn.commit();
        System.out.println("Transfer completed.");
    } catch (Exception e) {
        e.printStackTrace();
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Generic Query Method with Reflection

public <T> List<T> queryList(Connection conn, Class<T> type, String sql, Object... params) {
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
        try (ResultSet rs = ps.executeQuery()) {
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            List<T> results = new ArrayList<>();

            while (rs.next()) {
                T obj = type.getDeclaredConstructor().newInstance();
                for (int i = 1; i <= colCount; i++) {
                    String columnName = meta.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    Field field = type.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(obj, value);
                }
                results.add(obj);
            }
            return results;
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

Entity Class: User.java

public class User {
    private String name;
    private String password;
    private int balance;

    // Constructors, getters, setters, and toString omitted for brevity
}

Testing Isolation Behavior

@Test
public void observeIsolationLevel() throws Exception {
    try (Connection conn = JDBCUtils.getConnection()) {
        System.out.println("Default isolation: " + conn.getTransactionIsolation()); // Typically 2 (READ_COMMITTED)
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        conn.setAutoCommit(false);

        List<User> users = queryList(conn, User.class,
            "SELECT name, password, balance FROM accounts WHERE name = ?", "Charlie");
        users.forEach(System.out::println);
    }
}

@Test
public void concurrentUpdateTest() throws Exception {
    try (Connection conn = JDBCUtils.getConnection()) {
        conn.setAutoCommit(false);
        executeUpdate(conn, "UPDATE accounts SET balance = ? WHERE name = ?", 5000, "Charlie");
        Thread.sleep(15000); // Hold transaction open to observe isolation effects
        System.out.println("Update finalized.");
        conn.commit();
    }
}