Picture a bank transfer: money gets withdrawn from one account and deposited into another. That's two separate database updates. If the withdrawal succeeds but the app crashes right before the deposit runs, the money has simply vanished — withdrawn from one place, never arriving anywhere else. A transaction is what prevents that: it groups multiple operations into a single all-or-nothing unit. Either every operation in it succeeds, or none of them take effect at all.
Learning Objectives
After completing this lesson, you'll be able to:
- Wrap multiple queries in a cftransaction block so they succeed or fail together.
- Choose an isolation level, and explain what it actually controls.
- Use a savepoint for a partial rollback, and know the one real gotcha with nested transactions.
Basic Syntax
<cftransaction>
<cfquery datasource="bank">
UPDATE accounts SET balance = balance - 100 WHERE id = 1
</cfquery>
<cfquery datasource="bank">
UPDATE accounts SET balance = balance + 100 WHERE id = 2
</cfquery>
</cftransaction>transaction {
queryExecute("UPDATE accounts SET balance = balance - 100 WHERE id = 1", {}, {datasource: "bank"});
queryExecute("UPDATE accounts SET balance = balance + 100 WHERE id = 2", {}, {datasource: "bank"});
}By default, both queries commit together automatically if they both succeed. If either one throws a database error, everything in the block rolls back automatically — the first UPDATE never actually sticks, even though it ran without an error of its own.
Taking Manual Control: commit and rollback
transaction {
try {
queryExecute("UPDATE accounts SET balance = balance - 100 WHERE id = 1", {}, {datasource: "bank"});
queryExecute("UPDATE accounts SET balance = balance + 100 WHERE id = 2", {}, {datasource: "bank"});
transaction action="commit";
} catch (any e) {
transaction action="rollback";
}
}<cftransaction>
<cftry>
<cfquery datasource="bank">UPDATE accounts SET balance = balance - 100 WHERE id = 1</cfquery>
<cfquery datasource="bank">UPDATE accounts SET balance = balance + 100 WHERE id = 2</cfquery>
<cftransaction action="commit" />
<cfcatch type="any">
<cftransaction action="rollback" />
</cfcatch>
</cftry>
</cftransaction>This is functionally the same as the automatic behavior above — it's shown explicitly here because real code often needs its own logic (logging the error, for example) in the catch block before deciding to roll back.
Isolation Levels
| Level | What it prevents |
|---|---|
| read_uncommitted | Nothing — allows dirty reads, non-repeatable reads, and phantom reads |
| read_committed | Dirty reads only (seeing another transaction's uncommitted changes) |
| repeatable_read | Dirty reads and non-repeatable reads (a row changing between two reads in the same transaction) |
| serializable | All of the above, plus phantom reads (new rows appearing that match an earlier query in the same transaction) |
Higher isolation means fewer surprises from other transactions running at the same time, but also more locking and lower concurrency. read_committed is a reasonable default for most applications; serializable is for cases where correctness genuinely can't tolerate any of those anomalies.
Savepoints: Partial Rollbacks
A savepoint marks a point inside a transaction that a rollback can return to, without undoing everything back to the very start of the transaction.
<cftransaction>
<cfquery datasource="bank">UPDATE accounts SET balance = balance - 50 WHERE id = 1</cfquery>
<cftransaction action="setsavepoint" savepoint="afterFirstWithdrawal" />
<cfquery datasource="bank">UPDATE accounts SET balance = balance - 9999999 WHERE id = 2</cfquery>
<!--- if this one fails a business-rule check, roll back only to the savepoint --->
<cftransaction action="rollback" savepoint="afterFirstWithdrawal" />
</cftransaction>Rolling back to a savepoint undoes everything after it, but keeps everything before it — the first withdrawal in this example stays in place even though the second one gets undone.
A Real Gotcha: Nested Transactions
It's common to call a function that has its own cftransaction block from inside code that's already running in a transaction. Since ColdFusion 9, nested cftransaction tags are allowed, but only the outermost one actually takes effect — an inner commit or rollback doesn't do anything on its own; the outer transaction still decides the final outcome.
This is actually useful: it means a function can wrap its own database work in a transaction to be safe when called on its own, without that transaction interfering when the same function gets called from inside a larger transaction elsewhere.
Programmatic Control: transactionCommit() and transactionRollback()
transaction {
queryExecute("UPDATE accounts SET balance = balance - 100 WHERE id = 1", {}, {datasource: "bank"});
if (someBusinessRuleFails) {
transactionRollback();
} else {
transactionCommit();
}
}These functions (Lucee) do the same job as <cftransaction action="commit"> and <cftransaction action="rollback"> — a plain function call instead of the tag's action attribute, useful when the decision to commit or roll back depends on conditional logic rather than just whether an error was thrown.
A Real Constraint: One Data Source Per Transaction
A single cftransaction block is meant to manage changes against one data source. Reading from multiple data sources inside it is fine, but modifying more than one within the same transaction can trigger an exception and an automatic rollback rather than the coordinated multi-database commit a beginner might expect.
Common Beginner Mistakes
Assuming a transaction automatically retries a failed operation
It doesn't — a transaction only decides whether changes get kept or undone. Retrying is separate logic the application has to implement itself.
Expecting an inner cftransaction's commit to take effect independently
Only the outermost cftransaction actually commits or rolls back — a nested one's action is effectively ignored on its own.
Modifying two different data sources inside one cftransaction
A transaction is scoped to one data source for its actual changes — mixing writes to two different databases in the same block risks an exception and rollback rather than a coordinated commit across both.
Best Practices
- Keep a transaction as short as possible — it holds database locks for its entire duration, and a long-running one hurts concurrency for everyone else.
- Default to read_committed unless a specific correctness requirement calls for stricter isolation.
- Wrap a function's own database writes in a transaction if it might reasonably be called on its own, relying on the nested-transaction rule to keep it safe when called from inside a larger one too.
Interview Questions
What problem does a transaction actually solve?
It groups multiple database operations into one all-or-nothing unit, so a failure partway through doesn't leave the database in an inconsistent state where some changes took effect and others didn't.
What's the difference between read_committed and serializable isolation?
read_committed only prevents dirty reads (seeing another transaction's uncommitted changes). serializable additionally prevents non-repeatable reads and phantom reads, at the cost of more locking and lower concurrency.
What does a savepoint let you do that a plain rollback doesn't?
A plain rollback undoes the entire transaction back to the start. Rolling back to a savepoint undoes only what happened after that point, keeping earlier changes in the same transaction intact.
What happens with a nested cftransaction?
Nesting is allowed, but only the outermost cftransaction actually takes effect — an inner block's commit or rollback doesn't independently finalize or undo anything on its own.
Summary
In this lesson, you wrapped multiple queries in a cftransaction so they succeed or fail as one unit, took manual control with commit and rollback, chose an isolation level, used a savepoint for a partial rollback, and covered the real gotcha with nested transactions.
What's Next?
The next lesson introduces ORM (Object-Relational Mapping) — working with database rows as CFC objects instead of raw SQL, which the following CRUD Operations lesson builds on directly.