Transaction Management

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Statements

DuckDB supports ACID database transactions. Transactions provide isolation, i.e., changes made by a transaction are not visible from concurrent transactions until it is committed. A transaction can also be aborted, which discards any changes it made so far.

Statements

DuckDB provides the following statements for transaction management.

Starting a Transaction

To start a transaction, run:

BEGIN TRANSACTION;

Committing a Transaction

You can commit a transaction to make it visible to other transactions and to write it to persistent storage (if using DuckDB in persistent mode). To commit a transaction, run:

COMMIT;

If you are not in an active transaction, the COMMIT statement will fail.

Rolling Back a Transaction

You can abort a transaction. This operation, also known as rolling back, will discard any changes the transaction made to the database. To abort a transaction, run:

ROLLBACK;

You can also use the abort command, which has an identical behavior:

ABORT;

If you are not in an active transaction, the ROLLBACK and ABORT statements will fail.

Multi-Statement Transactions

When multiple SQL statements are submitted together (e.g., separated by semicolons), they are executed within a single implicit transaction. If any statement fails, all preceding statements in the batch are rolled back. This also applies to PRAGMA commands that decompose into multiple internal operations, such as COPY FROM DATABASE.

Isolation Level

DuckDB's concurrency model guarantees snapshot isolation. Transactions that violate this isolation level are aborted.

Using PostgreSQL's transaction isolation levels, DuckDB guarantees repeatable reads.

Example

We illustrate the use of transactions through a simple example.

CREATE TABLE person (name VARCHAR, age BIGINT);

BEGIN TRANSACTION;
INSERT INTO person VALUES ('Ada', 52);
COMMIT;

BEGIN TRANSACTION;
DELETE FROM person WHERE name = 'Ada';
INSERT INTO person VALUES ('Bruce', 39);
ROLLBACK;

SELECT * FROM person;

The first transaction (inserting “Ada”) was committed but the second (deleting “Ada” and inserting “Bruce”) was aborted. Therefore, the resulting table will only contain <'Ada', 52>.

About this page

Code of Conduct Trademark Use Blog