Transaction Management with JDBC
Every real-world database operation involves more than one SQL statement. If a network error strikes halfway through a multi-step update, you can end up with orphaned records, mismatched balances, or corrupted state. Transactions are the mechanism that keeps multi-step operations atomic: either every statement succeeds together, or they all fail together. This chapter explains how JDBC handles transactions, how to control isolation levels, and how to speed up bulk work with batching.
Prerequisites
- MySQL running with the
jdbc_demodatabase - The
userstable from previous chapters - A Maven project with
mysql-connector-jinpom.xml
Understanding ACID
A transaction is a logical unit of work. Database theory defines four properties that every transaction should guarantee, abbreviated as ACID.
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations in the transaction complete, or none do. | A bank transfer deducts from account A and adds to account B. If the second step fails, the first is undone. |
| Consistency | The database moves from one valid state to another. | A transfer does not create or destroy money; the total balance across both accounts stays the same. |
| Isolation | Concurrent transactions do not interfere with each other. | Two users transferring money at the same time see correct balances, not partial writes. |
| Durability | Once committed, changes survive power loss or crashes. | After a transfer commits, the data is written to disk (or the transaction log) and is safe. |
JDBC gives you direct control over these properties through the Connection object.
Auto-Commit vs. Manual Commit
When a Connection is created, it starts in auto-commit mode. That means every executeUpdate or executeQuery is wrapped in its own invisible transaction and committed immediately. For multi-step operations, you must turn this off.
A Classic Transfer Example
Imagine a simple accounts table:
CREATE TABLE IF NOT EXISTS accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 500.00);Run that in your MySQL client or IntelliJ SQL console so the data exists for the examples below.
Manual Transaction Code Template
The standard pattern is to disable auto-commit, execute your work inside a nested try block, and call commit only at the end. If anything throws, you rollback.
Tip
Always Roll Back on Failure
If you do not call rollback() in the catch block, the pending changes remain in an ambiguous state. When the connection eventually closes, the driver may or may not roll them back automatically. Explicit rollback removes that uncertainty.
Why Not Roll Back in finally?
You might be tempted to put rollback() inside a finally block. The danger is that finally runs even after a successful commit. Calling rollback after commit is either a no-op or an error, depending on the driver. Keep rollback inside the catch block, or guard it with a boolean flag that tracks whether the commit succeeded.
Savepoints
A savepoint is a marker inside a transaction. You can roll back to that marker without undoing the entire transaction. This is useful when a transaction contains independent steps and you want to retry or skip one step without losing the others.
Warning
Savepoint Names Are Optional
You can call setSavepoint() without a name to get an anonymous savepoint. Named savepoints make debugging easier because the name appears in some driver logs.
Transaction Isolation Levels
Isolation determines how much one transaction can see of another transaction's in-progress work. The SQL standard defines four levels, and JDBC exposes them as constants on the Connection interface.
The Three Classic Anomalies
| Anomaly | What Happens | Example |
|---|---|---|
| Dirty read | Transaction A reads a row that transaction B has modified but not yet committed. | B deducts 100 from Alice. A reads Alice's balance before B commits. B then rolls back. A acted on data that never existed. |
| Non-repeatable read | Transaction A reads a row. Transaction B updates and commits it. Transaction A reads the same row again and sees different data. | A reads Alice's balance twice during a report. Between reads, B transfers money out. The two reads disagree. |
| Phantom read | Transaction A runs a query that returns a set of rows. Transaction B inserts or deletes rows that match A's query criteria and commits. Transaction A runs the same query again and sees a different number of rows. | A sums all accounts with balance > 1000. B opens a new qualifying account and commits. A's second sum includes the new account. |
Isolation Level Matrix
| JDBC Constant | Dirty Read | Non-Repeatable Read | Phantom Read | Typical Use |
|---|---|---|---|---|
TRANSACTION_READ_UNCOMMITTED | Allowed | Allowed | Allowed | Rare; mostly for read-only reporting where stale data is acceptable |
TRANSACTION_READ_COMMITTED | Prevented | Allowed | Allowed | Default in PostgreSQL, Oracle, SQL Server. Good general-purpose balance. |
TRANSACTION_REPEATABLE_READ | Prevented | Prevented | Allowed | Default in MySQL (InnoDB). Good for read-heavy workloads. |
TRANSACTION_SERIALIZABLE | Prevented | Prevented | Prevented | Highest safety, lowest concurrency. Use for critical financial calculations. |
Setting the Isolation Level
You can inspect and change the isolation level before starting work:
MySQL InnoDB's default is REPEATABLE_READ. Not all databases support all four levels, and some may silently upgrade a requested level to a stronger one.
Tip
Do Not Change Isolation Levels Casually
Higher isolation levels reduce concurrency because the database must lock more data or track more versions. Start with your database's default and only tighten isolation when you can demonstrate a real anomaly in your application.
Batch Operations
Sending one SQL statement at a time over the network is slow. Batching lets you queue many commands and ship them to the database in a single round-trip.
Statement Batching
For a plain Statement, you can queue unrelated SQL strings:
Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO users (name, email) VALUES ('A', 'a@example.com')");
stmt.addBatch("INSERT INTO users (name, email) VALUES ('B', 'b@example.com')");
stmt.addBatch("UPDATE accounts SET balance = balance - 10 WHERE id = 1");
int[] results = stmt.executeBatch();This is flexible but still vulnerable to SQL injection if you concatenate values.
PreparedStatement Batching
The safer and more common pattern is to batch a single parameterized statement with many sets of values:
Interpreting the Return Value
executeBatch() returns an int[] where each element is the number of rows affected by the corresponding command:
- A positive number: the rows affected by that command.
Statement.SUCCESS_NO_INFO(-2): the command succeeded but the row count is unavailable.Statement.EXECUTE_FAILED(-3): the command failed. The driver may stop at the first failure or continue with the rest, depending on the database and driver settings.
Tip
Always Pair Batches with Transactions
Without an explicit transaction, each executeBatch() call commits immediately. If the last chunk fails, earlier chunks are already committed and you are left with a half-populated table. Disable auto-commit, run the entire batch, then commit once at the end.
FAQ
What happens if I forget to call commit()?
When auto-commit is disabled and you close the connection without committing, the driver typically rolls back the pending changes. However, this behavior is not guaranteed by the specification. Always call commit() explicitly when the work is done.
What is MySQL's default isolation level?
MySQL's InnoDB storage engine defaults to REPEATABLE_READ. Many other databases default to READ_COMMITTED.
Can I change the isolation level in the middle of a transaction?
Some drivers allow it, but it is implementation-defined and can produce confusing behavior. Set the isolation level immediately after obtaining the connection and before executing any SQL.
What is the difference between rollback() and rollback(Savepoint)?
rollback() undoes every change since the transaction began. rollback(Savepoint) undoes only the changes made after that savepoint was set, leaving earlier work intact.
Should I use batching for updates as well as inserts?
Yes. Any repeated INSERT, UPDATE, or DELETE with the same SQL structure benefits from batching. The performance gain comes from reducing network round-trips and allowing the database to process a stream of similar operations efficiently.
How large should a batch be?
There is no universal number. A common starting point is 500 to 1000 rows per batch. Very large batches can exhaust client memory or trigger database timeouts. Profile with your actual data size and network latency.