Database Transaction Management in ColdFusion: cftransaction, Isolation Levels, and Deadlocks

Database Transaction Management in ColdFusion: cftransaction, Isolation Levels, and Deadlocks

ColdFusion manages database transactions with the cftransaction tag, which groups multiple queries into one atomic unit so they all commit together or all roll back together — enforcing the ACID guarantee that partial writes never persist. You control it with action="begin|commit|rollback|setsavepoint", tune concurrency with isolation="read_uncommitted|read_committed|repeatable_read|serializable", and (since ColdFusion 8) undo part of a transaction with savepoints. Two things trip teams up. First, a cftry/cfcatch inside a cftransaction swallows the exception that would trigger automatic rollback — so a caught error can leave a partial write committed unless you explicitly call <cftransaction action="rollback"/> in the catch. Second, deadlocks ("Transaction was deadlocked on lock resources... chosen as the deadlock victim. Rerun the transaction") happen when concurrent transactions lock the same rows in different orders — fixed by keeping transactions short, accessing tables in a consistent order, indexing, choosing an appropriate isolation level (prefer READ COMMITTED over SERIALIZABLE), and retrying with exponential backoff. This guide covers cftransaction, isolation levels, and deadlocks with working code.

Why Transactions: ACID and Atomicity

A transaction groups multiple database operations into one unit that follows the ACID properties: Atomicity (all operations succeed or none do), Consistency (database rules hold throughout), Isolation (concurrent transactions don’t interfere), and Durability (committed changes persist).

The classic case: transferring money debits one account and credits another. If the debit succeeds but the credit fails, you must undo the debit — otherwise money vanishes. A transaction guarantees both happen or neither does.

In ColdFusion, cftransaction wraps the queries. All queries inside share one transaction context; if any fails, you roll back everything; on success you commit.

<cftransaction>
    <cfquery datasource="bankDSN">
        UPDATE accounts SET balance = balance - <cfqueryparam value="#amount#" cfsqltype="cf_sql_decimal">
        WHERE  id = <cfqueryparam value="#fromID#" cfsqltype="cf_sql_integer">
    </cfquery>
    <cfquery datasource="bankDSN">
        UPDATE accounts SET balance = balance + <cfqueryparam value="#amount#" cfsqltype="cf_sql_decimal">
        WHERE  id = <cfqueryparam value="#toID#" cfsqltype="cf_sql_integer">
    </cfquery>
</cftransaction>

Default behavior (no action specified): if the cfquery operations inside the block complete without error, the transaction commits automatically; if an unhandled error occurs, it rolls back. That implicit behavior is convenient — but as the next section shows, it breaks the moment you add cftry/cfcatch.

The cftransaction Tag: action, Syntax, Constraints

Full syntax:

<cftransaction
    action    = "begin|commit|rollback|setsavepoint"
    isolation = "read_uncommitted|read_committed|repeatable_read|serializable"
    savepoint = "savepoint name">
</cftransaction>

The action values:

• begin — start of the transaction block (the default).
• commit — commit a pending transaction. <cftransaction action="commit"/> (the slash is shorthand for an end tag).
• rollback — roll back a pending transaction.
• setsavepoint — set a named savepoint (ColdFusion 8+).

For explicit control, use action="begin" and commit/roll back based on success or failure:

<cftransaction action="begin">
    <cftry>
        <cfquery datasource="appDSN"> <!--- op 1 ---> </cfquery>
        <cfquery datasource="appDSN"> <!--- op 2 ---> </cfquery>
        <cftransaction action="commit"/>
        <cfcatch type="database">
            <cftransaction action="rollback"/>   <!--- REQUIRED — see gotcha below --->
            <cfrethrow>
        </cfcatch>
    </cftry>
</cftransaction>

Key constraints (verified from Adobe):

• One datasource per transaction at a time. You can write to more than one database in a transaction block, but you must commit or roll back to one database before writing a query to another — ColdFusion cannot span multiple datasources in a single atomic transaction.
• Nested transactions are supported since ColdFusion 9.
• ORM interaction: cftransaction begin flushes and closes any existing ORM session and starts a new one; ormFlush() inside runs SQL but commits only when the transaction commits. If you use ORM, manage sessions with transactions deliberately.

The #1 Gotcha: cftry/cfcatch Swallows Auto-Rollback

This is the most damaging and most common cftransaction mistake, documented in an Adobe Community incident (Feb 2017) and confirmed as a repeated production issue.

Automatic rollback only fires on an unhandled error. If you wrap your queries in cftry/cfcatch inside the transaction, the cfcatch handles the exception — so ColdFusion sees no unhandled error and commits whatever completed, leaving a partial write. The catch swallowed the very signal that would have triggered rollback.

Reproduction: force a database error on query 3 of four. With a cfcatch present, queries 1 and 2 are rolled back, but query 3 completes (the error is caught) and query 4 also completes — a corrupted partial result.

The fix: if you catch inside a transaction, you MUST explicitly roll back in the catch:

<cftransaction action="begin">
    <cftry>
        <cfquery datasource="appDSN"> <!--- op 1 ---> </cfquery>
        <cfquery datasource="appDSN"> <!--- op 2 ---> </cfquery>
        <cfquery datasource="appDSN"> <!--- op 3 ---> </cfquery>
        <cftransaction action="commit"/>
<cfcatch type="any">
            <cftransaction action="rollback"/>   <!--- explicit - do NOT rely on auto-rollback here --->
            <cfrethrow>                          <!--- surface the error after rolling back --->
        </cfcatch>
    </cftry>
</cftransaction>

Rule: either let errors propagate unhandled (auto-rollback fires) or catch them and explicitly action="rollback". Never catch-and-do-nothing inside a transaction.

Isolation Levels: Controlling Concurrency

The isolation attribute controls how the database engine performs locking during the transaction — the trade-off between consistency and concurrency. If you don't set it, ColdFusion uses the default isolation level for the associated database.

The four ANSI/ISO levels, defined by which read anomalies they prevent:

• read_uncommitted (lowest) — transactions can read uncommitted changes from other transactions. Allows dirty reads (reading data that might be rolled back), non-repeatable reads, and phantom reads. Fastest, least safe.
• read_committed — only committed data is read. Prevents dirty reads; still allows non-repeatable reads (a re-read row changed) and phantom reads (new matching rows appear). The common default for SQL Server and Oracle.
• repeatable_read — a row read once returns the same value throughout the transaction. Prevents dirty and non-repeatable reads; still allows phantom reads. (Not supported by Oracle.)
• serializable (highest) — full isolation; transactions behave as if run one at a time. Prevents all three (dirty, non-repeatable, phantom). Safest, but most locking and lowest concurrency — the most deadlock-prone.

The three anomalies:

Get Coding Algorithms’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Anomaly Meaning Dirty read Reading uncommitted data that may later roll back Non-repeatable read Re-reading a row and finding it changed Phantom read Re-running a query and finding new/missing rows Isolation level Dirty read Non-repeatable Phantom read_uncommitted Possible Possible Possible read_committed Prevented Possible Possible repeatable_read Prevented Prevented Possible serializable Prevented Prevented Prevented

<!--- Higher isolation for a balance calculation that must be stable during the read --->
<cftransaction action="begin" isolation="repeatable_read">
    <cfquery name="qBalance" datasource="appDSN">
        SELECT SUM(balance) AS total FROM accounts
        WHERE  userId = <cfqueryparam value="#userID#" cfsqltype="cf_sql_integer">
    </cfquery>
</cftransaction>

Practical guidance: don’t default to serializable — it maximizes locking and deadlocks. Prefer read_committed (or SQL Server's READ COMMITTED SNAPSHOT / RCSI where available) for most work, raise to repeatable_read only when a calculation needs a stable read, and reserve serializable for cases that genuinely require it. Higher isolation = more consistency, less concurrency, more contention.

Savepoints: Partial Rollback (ColdFusion 8+)

Savepoints let you undo part of a transaction without discarding all of it. Set a named savepoint with action="setsavepoint", then roll back to it — earlier work stays intact.

<cftransaction action="begin">
    <cfquery datasource="appDSN"> <!--- Step 1 ---> </cfquery>
    <cftransaction action="setsavepoint" savepoint="step1"/>
<cfquery datasource="appDSN"> <!--- Step 2 ---> </cfquery>
    <cfif someCondition>
        <cftransaction action="rollback" savepoint="step1"/>
        <!--- Step 2 undone; Step 1 remains --->
    </cfif>
    <cftransaction action="commit"/>
</cftransaction>

Useful for multi-step operations where a later optional step may fail but you want to keep the earlier committed-in-block work.

Deadlocks: What They Are and How to Fix Them

A deadlock happens when two transactions each hold a lock the other needs — transaction A locked row 1 and wants row 2, transaction B locked row 2 and wants row 1. Neither can proceed. The database detects it, kills one as the deadlock victim, and returns an error. The classic SQL Server message:

Transaction was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Deadlocks are a concurrency problem, not a code bug per se — they appear under load when multiple transactions touch the same tables. Note also: many “timeout” errors are actually contention/deadlocks in disguise — investigate locking before assuming a performance problem.

Fix 1: Access tables/rows in a consistent order

The single most effective fix. If every transaction updates table A before table B (and rows in a consistent order), two transactions can’t hold locks in the opposing order that creates a deadlock. Decide a global lock/update order and enforce it everywhere in the codebase.

Fix 2: Keep transactions short

Wrap only the necessary statements in cftransaction. Never include unrelated work — HTTP calls, heavy computation, file I/O — inside a transaction; they hold locks far longer than needed and multiply contention. Compute outside, lock briefly to write.

Fix 3: Index the queried columns

Add indexes on columns in WHERE and JOIN conditions. Concurrency turns small tables into hotspots quickly — an unindexed query does a table scan that locks more rows than needed. Analyze execution plans, avoid table scans in transactional workloads, and keep queries parameterized with cfqueryparam to preserve plan stability.

Fix 4: Choose an appropriate isolation level

Prefer read_committed (or RCSI) over serializable. Higher isolation holds more locks and is more deadlock-prone; use the lowest level that meets your correctness needs.

Fix 5: Set timeouts

Bound the transaction and request so a stuck transaction can’t hold locks indefinitely:

<cfsetting requesttimeout="60">
<cftransaction action="begin" timeout="30">
    <!--- transaction operations --->
</cftransaction>

Fix 6: Retry with exponential backoff

Deadlocks are transient — the DB kills one transaction so the other can proceed, and the killed one can simply rerun. For idempotent operations, catch the deadlock and retry with increasing delay:

<cfloop index="retryCount" from="1" to="3">
    <cftry>
        <cftransaction action="begin">
            <!--- transaction operations --->
            <cftransaction action="commit"/>
        </cftransaction>
        <cfbreak/>   <!--- success — exit the retry loop --->
<cfcatch type="database">
            <cfif retryCount LT 3 AND findNoCase("deadlock", cfcatch.message)>
                <cfset sleep(1000 * retryCount)>   <!--- backoff: 1s, 2s, 3s --->
                <!--- loop continues to retry --->
            <cfelse>
                <cfrethrow>   <!--- out of retries, or not a deadlock --->
            </cfif>
        </cfcatch>
    </cftry>
</cfloop>

Only retry idempotent operations — ensure the retried transaction is safe to run again (guard against duplicate inserts), or you’ll double-apply on retry.

Common Mistakes to Avoid

• Assuming cftransaction always improves performance. It doesn't — it adds locking overhead. Use it only when atomicity is required, and keep transactions short.
• Catching errors inside a transaction without an explicit rollback. The auto-rollback won’t fire; you get partial writes. Always action="rollback" in the catch.
• Ignoring indexing “because tables are small.” Concurrency turns small tables into contention hotspots — index the queried columns.
• No retry logic on deadlock-prone operations. Add targeted retries with backoff for idempotent actions.
• Mixing lock order across the codebase. Document and enforce one global table/row access order.
• Defaulting to serializable. Prefer read_committed or snapshot options; only escalate when genuinely required.
• Treating timeouts as pure performance issues. Many are contention/deadlocks — investigate locking.
• Putting remote calls or heavy work inside a transaction. Keep them out; hold locks for the minimum time.

Conclusion

ColdFusion’s cftransaction gives you ACID guarantees with a simple tag — group queries, and they commit together or roll back together — but using it well means knowing three things beyond the basics. First, the auto-rollback only fires on an unhandled error, so a cftry/cfcatch inside a transaction must call <cftransaction action="rollback"/> explicitly or you'll silently commit partial writes. Second, the isolation attribute trades consistency for concurrency across read_uncommitted, read_committed, repeatable_read, and serializable — default to read_committed, escalate only when a read must be stable, and avoid serializable unless required, because higher isolation means more locking and more deadlocks. Third, deadlocks are a concurrency reality, not a rare bug: prevent them by keeping transactions short, accessing tables in a consistent global order, indexing the queried columns, and choosing a sensible isolation level — then survive the ones that still happen with idempotent retries and exponential backoff. Add savepoints (ColdFusion 8+) when you need partial rollback, remember one datasource per atomic transaction, and keep expensive work out of the transaction entirely.

Get these right and your ColdFusion data writes are atomic, your concurrency is tuned, and deadlocks become a handled edge case instead of a production incident.

If your organization is dealing with data-integrity bugs, partial writes, deadlock errors, or transaction-related slowdowns in a ColdFusion application — auditing cftransaction usage for the catch-swallows-rollback trap, tuning isolation levels for the right consistency-concurrency balance, eliminating deadlocks through consistent lock ordering and indexing, and adding robust retry logic — Lucid Outsourcing Solutions can help. As a dedicated ColdFusion development partner, Lucid brings the CFML and database expertise to make your transactions correct, your concurrency safe, and your deadlocks rare and handled. The right first step is a review of your transaction management and concurrency patterns — reach out to Lucid Outsourcing Solutions to get started.

Sources and Research Audit Trail

cftransaction syntax, action, isolation, savepoint, constraints — Adobe official (Tier 1):

• Adobe ColdFusion — cftransaction reference (action = "begin|commit|rollback|setsavepoint" isolation = "read_uncommitted|read_committed|repeatable_read" savepoint = "savepoint name"; "If you do not specify a value for the action attribute, automatic transaction processing proceeds as follows: If the cfquery operations within the transaction block complete without an error, the transaction is committed"; "If you do not specify a value for the isolation attribute, ColdFusion uses the default isolation level for the associated database"; "Commit a database transaction by nesting the <cftransaction action='commit'/> tag... Roll back a transaction by nesting the <cftransaction action='rollback'/> tag (the slash is an alternate syntax equivalent to an end tag)"; "In a transaction block, you can write queries to more than one database, but you must commit or roll back a transaction to one database before writing a query to another"; "To control how the database engine performs locking during the transaction, use the isolation attribute"; "ColdFusion 8: Added the setsavepoint value to the action attribute. Added the savepoint attribute"; related cfquery/cfqueryparam/cfstoredproc): helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-t/cftransaction.html
• O’Reilly — Sams Teach Yourself ColdFusion in 21 Days (CFTRANSACTION) (“Use CFTRANSACTION to group multiple queries into a single unit. CFTRANSACTION also provides commit and rollback processing”; ACTION BEGIN (default)/COMMIT/ROLLBACK; ISOLATION valid entries Read_Uncommitted, Read_Committed, Repeatable_Read, Serializable): oreilly.com/library/view/sams-teach-yourself/0672317966/

cftry/cfcatch swallows auto-rollback; nested transactions; ORM interaction; savepoint/retry code (Tier 1/2 — Adobe Community + Deepak Purohit):

• Adobe Community — CFTRY/CFCATCH and CFTRANSACTION (Feb 2017) (“I can recreate the issue every time with the posted code if I force a database error on query 3: queries 1 and 2 are rolled back, query 3 is completed (with the catch version) and query 4 is completed”; “If you want finer tuning, you should use the actions ‘commit’ and ‘rollback’ to instruct ColdFusion”; deadlock or timeout on DB server also causes the error; forcing a DB error e.g. inserting a varchar into an int field): community.adobe.com/t5/coldfusion-discussions/cftry-cfcatch-and-cftransaction/td-p/8863684
• Deepak Purohit (DevOps.dev) — ColdFusion Transaction Rollback Not Working Properly (“cftry/cfcatch inside cftransaction prevents auto-rollback — Adobe Community Feb 2017 documented incident; Isolation levels: read_uncommitted, read_committed, repeatable_read, serializable — Adobe docs + O’Reilly; ColdFusion 8 added setsavepoint action and savepoint attribute; ColdFusion 9 added nested transaction support; Cannot span multiple datasources in single transaction; cftransaction begin flushes and closes existing ORM session; New ORM session created when cftransaction begins; ormFlush() inside cftransaction runs SQL but commits only on transaction commit; Multiple dirty sessions cause transaction to roll back”): blog.devops.dev/coldfusion-transaction-rollback-not-working-properly-fc9739d62d08
• Deepak Purohit (Towards Dev) — ColdFusion Transaction Rollback Not Working (savepoint example: <cftransaction action="begin"> <cfquery> Step 1 <cftransaction action="setsavepoint" savepoint="step1"/> <cfquery> Step 2 <cfif someCondition> <cftransaction action="rollback" savepoint="step1"/> </cfif> <cftransaction action="commit"/> </cftransaction>; timeout: <cfsetting requesttimeout="60"> <cftransaction action="begin" timeout="30">; deadlock retry loop: <cfloop index="retryCount" from="1" to="3"> <cftry> <cftransaction action="begin"> ... <cftransaction action="commit"/> </cftransaction> <cfbreak/> <cfcatch type="database"> <cfif retryCount LT 3 AND findNoCase("deadlock", cfcatch.message)> <cfset sleep(1000 * retryCount)> <cfcontinue>...; ACID definitions; "The cftransaction tag begins a database transaction. All queries inside share the same transaction context. If any query fails, you can rollback everything"): towardsdev.com/coldfusion-transaction-rollback-not-working-how-to-prevent-partial-data-saves-fcd3276aa320

Deadlock diagnosis and fixes (Tier 2 — ColdFusion Central + Adobe Community):

• ColdFusion Central — How to Fix Deadlocks in ColdFusion Applications (“Wrap only necessary statements in CFTRANSACTION. Avoid including unrelated work (e.g., HTTP calls, heavy computations). Use a single, consistent order when accessing multiple tables or rows. Decide the order and enforce it everywhere”; “Add indexes to columns in WHERE and JOIN conditions. Analyze execution plans; avoid table scans for transactional workloads. Ensure queries are parameterized with cfqueryparam to preserve plan stability”; “Prefer READ COMMITTED or READ COMMITTED SNAPSHOT (RCSI) where acceptable. Avoid SERIALIZABLE unless necessary”; “If a deadlock still occurs, retry the transaction with exponential backoff. Ensure the operation is idempotent”; Mistakes: “Assuming CFTRANSACTION always improves performance. Use it only when atomicity is required; keep transactions short”; “Ignoring indexing because ‘tables are small.’ Concurrency turns small tables into hotspots quickly”; “Setting SERIALIZABLE isolation by default”; “Treating timeouts as pure ‘performance’ issues. Many timeouts are contention/deadlocks in disguise”): coldfusioncentral.com/fix-deadlocks-coldfusion/
• Adobe Community — Isolation level in cftransaction (real deadlock error: “Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction”; SQL Server 2005; “multiple transactions trying to access the same tables at the same time. Implicitly, one transaction has locked the tables while it is inserting data, and other transactions are not able to access them”; confusion over isolationLevel and dirty read/phantom data/nonrepeatable reads): forums.adobe.com/thread/837241

Isolation-level definitions and anomaly matrix (Tier 1 — PostgreSQL + Microsoft + O’Reilly):

• PostgreSQL Documentation — Transaction Isolation (“dirty read: A transaction reads data written by a concurrent uncommitted transaction. nonrepeatable read: A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read). phantom read: A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed”; ANSI/ISO table: Read uncommitted (dirty/nonrepeatable/phantom all Possible), Read committed (dirty Not possible, others Possible)): postgresql.org/docs/current/transaction-iso.html
• O’Reilly (Java Programming with Oracle SQLJ) — Transaction Isolation Levels (“SERIALIZABLE — Dirty reads, non-repeatable reads, and phantom reads are not permitted. REPEATABLE READ — Dirty reads and non-repeatable reads are not permitted, but phantom reads are permitted. This isolation level is not supported by Oracle. READ COMMITTED — default used by Oracle. Dirty reads not permitted, but non-repeatable reads and phantom reads permitted. READ UNCOMMITTED — Dirty reads, non-repeatable reads, and phantom reads are permitted. Not supported by Oracle”): oreilly.com/library/view/java-programming-with/0596000871/
• Microsoft Learn — SET TRANSACTION ISOLATION LEVEL (READ COMMITTED “This option is the SQL Server default”; READ_COMMITTED_SNAPSHOT (RCSI) and SNAPSHOT isolation minimize locking contention while preventing dirty reads; “Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data”): learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql