1. Why transactions are needed at all
When you write a program, you usually want an action to either happen completely or not happen at all. Not “money was debited but not credited,” not “an expense was added but a log entry didn’t appear,” not “a user was created but a profile wasn’t.” In program memory we often solve this with a normal sequence of operations and checks. But a database lives its own life: it’s a separate world where commands can fail in the middle, and some changes may already be saved.
A transaction is a way to tell the database: “Look, a set of commands is coming. Treat them as one whole action. If everything succeeds—persist it. If something breaks—roll it back as if we did nothing.”
Imagine you’re carrying two cups of coffee: one for yourself, one for a colleague. If you trip, you’d prefer either to deliver both or (sadly) spill both, but definitely not the situation “you made it with yours, and now your colleague is your enemy for life.” A transaction is like lids on the cups and the rule “carry them together.”
2. autoCommit: why the default mode gets in the way
Before writing a “proper” transaction, you need to understand the setting that is enabled by default in almost all JDBC connections: autoCommit.
In JDBC, a Connection has the following mode:
- autoCommit = true — each SQL statement is committed immediately, like its own mini-transaction.
- autoCommit = false — you manually control when to persist changes (commit()) and when to undo them (rollback()).
It sounds convenient: “let it commit by itself.” But it’s only good while you have one statement that, by itself, is the whole action. As soon as an action consists of two or more statements, autoCommit = true turns your “atomic action” into a “chain of independent shots.”
Let’s summarize it in a table—it’s easier to scan:
| Mode | What happens | When it fits | Main risk |
|---|---|---|---|
|
Each statement is saved immediately | One statement = one action | Partially completed scenarios |
|
You decide when to save | Multiple statements as one action | If you forget commit/rollback, you can get stuck in a weird state |
And yes, “forgetting commit()” is a real classic. It’s like writing your thesis but not pressing “Save.” You did the work, but there’s no result.
3. Transaction boundaries
There’s a subtle point with transactions: it’s important not only to “use transactions,” but also to choose their boundaries correctly.
If a transaction is too small, you’ll again get partial execution. If a transaction is too large, you’ll hold the connection and locks longer than necessary (and the database will start looking at you suspiciously).
A practical rule for beginners sounds like this:
A transaction should cover only those SQL operations (and the minimal surrounding logic) that must be consistent with each other.
In our training console application (let it be a simple expense tracker ExpenseTracker), a typical consistency example is: we want to add an expense and simultaneously write an event to an audit table. If the expense is added but the audit isn’t—unpleasant: we get a “silent” expense that doesn’t show up in the action history.
Schematically, a transaction looks like this:
flowchart TD
A[Started action] --> B[autoCommit = false]
B --> C[SQL #1]
C --> D[SQL #2]
D --> E{Were there any errors?}
E -- no --> F["commit()"]
E -- yes --> G["rollback()"]
F --> H[autoCommit back / close]
G --> H[autoCommit back / close]
The main idea: commit only once, at the end. And rollback()—also once, if something goes wrong.
4. Basic transaction template in JDBC
Now let’s assemble the core template. It may seem verbose… but this is the case where verbosity is your insurance against “ghosts in the database.”
Key elements:
- conn.autoCommit = false — we take control.
- try { ...; conn.commit() } — run all SQL operations and persist the result.
- catch { conn.rollback(); throw } — on any error, roll back and rethrow the exception (let the caller decide what to do).
- finally { conn.autoCommit = ... } — return the connection to a “normal state” if you plan to reuse it.
By the way, finally as “guaranteed cleanup” is a familiar idea from exception handling: code in finally runs regardless of whether an exception occurred.
Mini example: a transaction as a wrapper function
Let’s start with a small but useful utility. It’s not “magic,” just a way not to copy the same try/catch/finally all over the project.
import java.sql.Connection
fun <T> inTx(conn: Connection, block: (Connection) -> T): T {
val oldAutoCommit = conn.autoCommit
conn.autoCommit = false
try {
val result = block(conn)
conn.commit()
return result
} catch (e: Exception) {
conn.rollback()
throw e
} finally {
conn.autoCommit = oldAutoCommit
}
}
Notice a bit of “paranoia”: we save the old autoCommit value and restore exactly that, rather than simply setting true. This is useful if the connection reached you already in a non-default mode (in large applications this happens more often than you’d like).
5. Practice: add an expense and write an audit record in one transaction
Now let’s tie this to our ExpenseTracker.
Assume (at the idea level, without diving into DB administration) that we have two tables:
- expenses(id, amount, category, note)
- audit_log(id, message)
We want the “add expense” action to be done so that:
1) the expense is inserted into expenses,
2) a record is inserted into audit_log,
3) if it fails at step 2, step 1 is rolled back as well.
Insert the expense
Let’s start with inserting the expense itself. Here it’s important to remember that executeUpdate() returns the number of affected rows—this is a great quick check that the statement actually did something.
import java.sql.Connection
fun insertExpense(conn: Connection, amount: Long, category: String, note: String): Int {
val sql = "INSERT INTO expenses(amount, category, note) VALUES (?, ?, ?)"
conn.prepareStatement(sql).use { st ->
st.setLong(1, amount)
st.setString(2, category)
st.setString(3, note)
return st.executeUpdate()
}
}
Insert the audit record
Audit is just a record of “what happened.” In real life audit can be more complex, but for us right now the key is the “second operation” principle.
import java.sql.Connection
fun insertAudit(conn: Connection, message: String): Int {
val sql = "INSERT INTO audit_log(message) VALUES (?)"
conn.prepareStatement(sql).use { st ->
st.setString(1, message)
return st.executeUpdate()
}
}
Combine them into one transaction
Now we tie the two operations into a single transaction using inTx.
import java.sql.Connection
fun addExpenseWithAudit(conn: Connection, amount: Long, category: String, note: String) {
inTx(conn) { c ->
val inserted1 = insertExpense(c, amount, category, note)
check(inserted1 == 1) { "Expense insert failed: inserted=$inserted1" }
val inserted2 = insertAudit(c, "Added expense $amount in category=$category")
check(inserted2 == 1) { "Audit insert failed: inserted=$inserted2" }
}
println("Expense saved") // Expense saved
}
Here check(...) is our “emergency brake”: if we expected 1 row but got 0, something went wrong (for example, the table doesn’t exist, constraints failed, or the query is incorrect). We treat that as an error, throw an exception, and inTx will automatically call rollback().
6. Why you can’t “catch the error and continue” inside a transaction
Sometimes a beginner sees an exception and thinks: “I’ll catch it, print println("oy"), and everything will be fine.” The problem is that a transaction is not the place where you can pretend nothing happened.
If an error happens inside a transaction, you almost always have only two honest options:
- roll back and report that the operation failed;
- roll back and retry (but that’s a separate strategy and usually requires careful design).
The point of catch in a transaction template is not to “swallow” the error, but to reliably perform rollback() and then let the error propagate upward.
And here we recall the familiar construct again: try/catch/finally is not only about error handling, but also a mechanism for guaranteed “cleanup” via finally.
7. Example: transferring money as two linked operations
Expenses are fine, but sometimes you want an example that intuitively “hurts” if executed only halfway. Money transfers are a classic.
Let’s say we have a table accounts(id, balance). A transfer is:
1) decrease the balance of from,
2) increase the balance of to.
If you fail between them, you either “lost money” or “created money.” Both are interesting… but usually only for tests, not production.
import java.sql.Connection
fun transfer(conn: Connection, fromId: Long, toId: Long, amount: Long) {
require(amount > 0) { "Amount must be > 0" }
inTx(conn) { c ->
val dec = c.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ?"
).use { st ->
st.setLong(1, amount)
st.setLong(2, fromId)
st.executeUpdate()
}
check(dec == 1) { "Sender not found: id=$fromId" }
val inc = c.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?"
).use { st ->
st.setLong(1, amount)
st.setLong(2, toId)
st.executeUpdate()
}
check(inc == 1) { "Receiver not found: id=$toId" }
}
}
Yes, there’s a nuance of “what if the sender doesn’t have enough money,” but that’s application logic (and/or DB-level constraints). In this lecture it’s more important to see the transaction skeleton: two UPDATE statements as one action.
8. Relationship with use {}: closing resources even on rollback
It’s very easy to make a mistake: “If we have a transaction, then use {} is no longer needed.” It is needed—more than ever.
The logic is simple: a transaction is responsible for data consistency, while use {} is responsible for closing resources (PreparedStatement, ResultSet, and sometimes the Connection itself). These are different guarantees, and both matter.
In Kotlin, use {} is essentially like “try/finally, where the resource is closed in finally.” That means even if an error occurs inside, the resource will be closed. It’s the same guaranteed-cleanup philosophy as finally.
So a good style is: inside a transaction, close all statements and result sets via use {}, and outside the transaction (or in the inTx wrapper) manage commit()/rollback().
9. Typical mistakes when working with transactions in JDBC
Mistake #1: leaving autoCommit = false and forgetting to restore it.
This is one of the nastiest problems because it doesn’t show up immediately. You ran the first statement “manually,” and then some other piece of code suddenly also started working in manual mode and began requiring commit(). If the connection is reused, always restore autoCommit in finally. The calmest option is to remember the old value and restore it, as we did in inTx.
Mistake #2: calling commit() too early.
Sometimes you see code like this: do the first UPDATE, immediately commit(), then the second UPDATE, and again commit(). This turns a transaction into two independent actions and brings us back to the “halfway” problem. In a normal design, commit() should happen once—at the end, when all related operations have succeeded.
Mistake #3: forgetting to call rollback() in catch.
Catching an exception and not rolling back means leaving the connection in a suspended state where changes are partially “hanging,” but not committed. Then you get a whole series of episodes called “Why is the database behaving weirdly when I didn’t do anything.” The rule is simple: if you manually disabled autoCommit, then any error before commit() should almost always lead to rollback().
Mistake #4: catching an exception and “muting” it, pretending everything is fine.
Sometimes code does catch (e: Exception) { rollback(); println(...) } and keeps running as if the operation succeeded. This is dangerous: the caller thinks everything is fine, but in reality nothing was saved. If you’re not designing an explicit operation result (for example, a Boolean or a sealed class), it’s better to rethrow after rollback(). The try/catch/finally mechanics are exactly what lets you do this neatly.
Mistake #5: an overly broad transaction that includes “everything.”
If you put not only SQL but also user input, menu printing, network requests, and text parsing inside a transaction, you hold the transaction longer than necessary. Even if “everything works in a training SQLite,” it’s a bad habit. A transaction should be short: SQL operations + minimal result checks.
Mistake #6: not checking the executeUpdate() result and committing “into the void.”
The statement UPDATE ... WHERE id = ? can update 0 rows if that id doesn’t exist. If you then call commit(), formally everything “succeeded,” but the business meaning failed. Check the number of changed rows via check(...) or returned values, and decide whether to treat it as an error.
GO TO FULL VERSION