Transactions
A database transaction is a sequence of one or more operations that are executed as a single logical unit of work, either fully applied or fully rolled back.
Overview
A transaction groups multiple reads and writes into one atomic unit. If every statement in the transaction succeeds, the changes are committed and become permanent; if any statement fails, the whole transaction can be rolled back, leaving the database as if it never happened. This all-or-nothing behavior is what lets applications safely perform multi-step operations, like transferring money between two accounts, without ever leaving the data in a half-updated state.
Transaction Control in SQL
Standard SQL exposes transactions through BEGIN (or START TRANSACTION), COMMIT, and ROLLBACK:
Copy code
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If either UPDATE fails, issuing ROLLBACK instead of COMMIT undoes both changes. Most databases run in auto-commit mode by default, wrapping each individual statement in its own implicit transaction unless a transaction is explicitly opened.
DuckDB Transactions
DuckDB supports the same BEGIN TRANSACTION / COMMIT / ROLLBACK syntax and gives every transaction a consistent snapshot of the database via MVCC (multi-version concurrency control). DuckDB combines MVCC with optimistic concurrency control: multiple writer threads can start transactions concurrently, and appends never conflict with each other, but two transactions that modify the same rows at the same time will cause one of them to fail with a transaction conflict at commit time rather than blocking and waiting for a lock. This design favors the read-heavy, bulk-write patterns typical of analytical workloads over the fine-grained row locking used by OLTP databases.
Related terms
Concurrency control is the set of techniques a database uses to let multiple transactions execute at the same time without violating data consistency or isolation guarantees.
Isolation level →An isolation level defines how much one transaction's in-progress changes are visible to other concurrently running transactions, trading off consistency against concurrency.
ACID →ACID is an acronym describing four properties — Atomicity, Consistency, Isolation, and Durability — that guarantee database transactions are processed reliably.
OLTP →OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
OLAP vs OLTP →OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two workload categories: OLTP handles many small, concurrent transactional writes on normalized schemas, while OLAP handles large read-heavy analytical queries on denormalized, columnar data.
FAQS
None of its changes are applied. The database discards the transaction's uncommitted work, so other queries never see partial results.
Yes. DuckDB supports BEGIN TRANSACTION, COMMIT, and ROLLBACK, and runs in auto-commit mode for individual statements otherwise.
