Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 38 ยท Concepts and CLI

Transactions, Files and Attached Databases

Learn DuckDB ACID transactions, the single-writer rule, ATTACH for several databases including SQLite and Postgres, and EXPORT and IMPORT DATABASE for portable backups.

A database that you cannot trust with your data is a toy. DuckDB is a real transactional database: its changes are atomic and durable even though it is an embedded analytics engine. This lesson explains how transactions behave, what "one writer" means in practice, how to work with several databases at once using ATTACH, and how to back up and move a database.

Transactions and ACID

A transaction groups statements so they succeed or fail together. DuckDB gives you the full ACID guarantees:

  • Atomic: all statements in the transaction happen, or none do.
  • Consistent: constraints such as PRIMARY KEY and CHECK hold after every commit.
  • Isolated: concurrent work does not see half-finished changes.
  • Durable: once committed, data survives a crash (the write-ahead log makes this possible).

By default every single statement is its own transaction ("autocommit"). To group several, use BEGIN, then COMMIT or ROLLBACK. This example transfers money between two accounts; the stand-in uses SQLite, and the SQL is the same in DuckDB:

SQL Stand-in: commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

The first transfer is committed, so Ana has 70 and Ben 80. The second is rolled back on purpose, so nothing changes. Had the second update broken the CHECK (balance >= 0) rule, the statement would fail with an error. In DuckDB an error inside a transaction aborts it, and you must ROLLBACK before continuing.

Isolation: MVCC and optimistic concurrency

DuckDB uses MVCC (multi-version concurrency control): readers see a consistent snapshot of the data as of when their transaction began, and writers create new versions of rows, so readers never block writers. When two transactions in the same process try to change the very same rows, the second one to commit fails with a conflict error and you retry. This is called optimistic concurrency, since DuckDB assumes conflicts are rare and checks at the end.

SQL Stand-in: changes inside a transaction

Press Run (or Ctrl + Enter) to see the result here.

The single-writer rule

This is the rule that surprises newcomers. A DuckDB database file can be opened either by:

  • one process with read and write access, which may use many threads (and many connections inside it), or
  • many processes that all open it read-only.

You cannot have two separate programs both writing to the same .duckdb file at the same time. The second to open it gets an error saying the file is locked. Within one Python program, you can create several connections to the same database and use them from different threads; DuckDB coordinates them.

Who may open the file?

One writer process Read and write, many threads
Many readers Separate processes, read-only
Two writers Not allowed, lock error

Need many writers? Use a server database.

1 / 2

Common workarounds: have one process own the file and serve results to others; write each job's output to its own Parquet file and query them all together; or use ATTACH with a server database such as PostgreSQL for the shared, concurrent part.

ATTACH: working with several databases

ATTACH connects another database to your session under an alias. Then you can use alias.table names and even move data between them (read-only examples):

SQL
-- DuckDB (read-only example)
ATTACH 'archive.duckdb' AS archive;
SELECT count(*) FROM archive.orders;

-- copy a table across
CREATE TABLE archive.orders_2024 AS SELECT * FROM main.orders WHERE year = 2024;

DETACH archive;
USE archive;          -- make it the default database for unqualified names

The names you can use are database.schema.table, and the defaults are the current database and the main schema. ATTACH ... (READ_ONLY) protects a file from accidental edits.

DuckDB's scanner extensions also attach other database systems so you can query them with DuckDB's SQL (read-only examples; they need the extensions installed, covered in Extensions):

SQL
-- DuckDB (read-only example)
ATTACH 'app.sqlite' AS app (TYPE sqlite);
SELECT * FROM app.users LIMIT 5;

ATTACH 'dbname=shop host=localhost user=me' AS pg (TYPE postgres);
SELECT count(*) FROM pg.public.orders;

This makes DuckDB a convenient analytics layer over operational databases: pull a month of data from PostgreSQL, join it with a Parquet file, and write the result back out.

The stand-in below shows the idea of qualified names with SQLite's own main schema:

SQL Stand-in: qualified table names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Backups, export and import

Because a database is one file, a simple backup is a file copy while nothing is writing (and after a CHECKPOINT). For a portable, version-independent backup, DuckDB can export the entire database as a folder of files:

SQL
-- DuckDB (read-only example)
EXPORT DATABASE 'backup_2026_01' (FORMAT parquet, COMPRESSION zstd);

-- later, or on another machine:
IMPORT DATABASE 'backup_2026_01';

The folder holds a schema.sql that recreates tables, views and sequences, a load.sql script that loads the data, and one data file per table. Because it is plain SQL plus Parquet or CSV, you can read it with any tool. It also lets you upgrade between versions even if the binary storage layout changes.

You can also copy a whole database into another with COPY FROM DATABASE source TO target, or write individual tables with COPY table TO 'file.parquet'.

Checkpoint and the WAL

Changes are first appended to the write-ahead log (mydb.duckdb.wal). A checkpoint merges them into the main file. DuckDB checkpoints automatically when the log gets large and when you close the database cleanly, and you can force it with CHECKPOINT;. If a program is killed, the next open replays the log, so committed work is not lost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens if you ROLLBACK a transaction?

    ROLLBACK discards every change made since BEGIN, leaving the data as it was.
  2. 2. Which statement about opening one database file is true?

    DuckDB allows a single read-write process, or many read-only processes, but not several writers.
  3. 3. What does ATTACH 'archive.duckdb' AS archive do?

    ATTACH makes another database available in the session, and you reach its tables with a qualified name.
  4. 4. What does EXPORT DATABASE produce?

    The export folder contains plain SQL scripts plus data files in CSV or Parquet, which makes it portable.

Summary

  • DuckDB gives ACID transactions; use BEGIN, COMMIT, ROLLBACK to group statements.
  • MVCC gives snapshot reads; conflicting writes fail at commit and can be retried.
  • One process may write, or many may read; two writers are not allowed.
  • ATTACH connects other DuckDB, SQLite or PostgreSQL databases under an alias.
  • EXPORT DATABASE and IMPORT DATABASE make portable backups; the .wal file is merged on checkpoint.

Next: Reading query plans with EXPLAIN