JDBC Transactions ​

A database transaction is a sequence of operations composed of several SQL statements, similar to Java's synchronized synchronization. The database system ensures that all SQL statements within a transaction are either executed successfully or not at all, which means the transaction has ACID properties:

• Atomicity
• Consistency
• Isolation
• Durability

Database transactions can be executed concurrently, and for efficiency, the database system defines different isolation levels. The SQL standard specifies four isolation levels, corresponding to possible data inconsistencies:

For applications, database transactions are crucial because many mission-critical applications rely on transactions to ensure correct results.

Example ​

Suppose Xiao Ming wants to pay Xiao Hong 100. The primary keys for their records in the database are 123 and 456, respectively. The two SQL operations would be as follows:

UPDATE accounts SET balance = balance - 100 WHERE id = 123 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 456;

These two statements must be executed as a transaction to ensure the correctness of the operation. If the first SQL succeeds and the second fails, the system would lose 100. With a transaction, either the transfer succeeds, or both accounts remain unchanged if the transfer fails.

We won't discuss the details of SQL transactions here; if you're unfamiliar with them, please refer to SQL transaction documentation.

JDBC Transaction Code ​

To execute transactions in JDBC, it's essentially about how to wrap multiple SQL statements within a transaction. Here's the code for JDBC transactions:

Connection conn = openConnection();
try {
    // Disable auto-commit:
    conn.setAutoCommit(false);
    // Execute multiple SQL statements:
    insert(); update(); delete();
    // Commit the transaction:
    conn.commit();
} catch (SQLException e) {
    // Rollback the transaction:
    conn.rollback();
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

The key code to start a transaction is conn.setAutoCommit(false), which disables auto-commit. The code to commit the transaction is conn.commit(), executed after completing the specified SQL statements. It's important to note that transactions may not always succeed. If a transaction commit fails, an SQL exception will be thrown (it may also be thrown while executing the SQL statement), in which case you must catch it and call conn.rollback() to roll back the transaction. Finally, in the finally block, the Connection object's state is restored to its initial value with conn.setAutoCommit(true).

By default, when a Connection is obtained, it is in "auto-commit" mode, meaning each executed SQL statement is automatically treated as a transaction. This explains why the update operations in previous sections always succeeded—because of this "implicit transaction." As long as autoCommit is disabled on the Connection, multiple statements can be executed within a transaction, which ends with the commit() method.

Setting the Isolation Level ​

To set the transaction isolation level, use the following code:

// Set the isolation level to READ COMMITTED:
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

If the above method is not called, the database's default isolation level will be used. In MySQL, the default isolation level is REPEATABLE_READ.

Summary ​

Database transactions have ACID properties:

• Atomicity: Ensures that all operations in a transaction are completed; otherwise, none are.
• Consistency: Guarantees that the database remains in a consistent state before and after the transaction.
• Isolation: Transactions are independent of one another.
• Durability: Once a transaction is committed, changes are permanent.

JDBC supports transactions, allowing you to start, commit, or roll back transactions using the Connection object.