Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 38 ยท Python and Embedded Use

Persistence, Connections and Concurrency

Learn how DuckDB stores data in a single file, what in-memory means, how cursors and threads share a database, and the one-writer process rule.

Because DuckDB is embedded, questions that a server database answers for you become your job: where does the data live, who may open it, and what happens when two things write at once? This lesson gives the rules, which are short and strict.

The real DuckDB cannot run on this site, so the DuckDB calls are read-only. The runnable parts use SQLite through Python and portable SQL to illustrate transactions and constraints.

In-memory and file databases

Python
import duckdb

mem = duckdb.connect()                       # :memory: private database
mem2 = duckdb.connect(":memory:")            # same, spelled out
disk = duckdb.connect("analytics.duckdb")    # file, created if missing
ro = duckdb.connect("analytics.duckdb", read_only=True)

An in-memory database lives as long as its connection object; two connect() calls give two separate databases. A file database holds everything in one file (plus a temporary .wal write-ahead log next to it while changes are not yet checkpointed). Copy the file and you have copied the database. The common extension is .duckdb or .db.

Use :memory: for scratch work, tests and pipelines that read files and write files. Use a database file when you want tables, views, sequences and macros to survive between runs.

SQL
ATTACH 'warehouse.duckdb' AS wh;     -- open a second database file
CREATE TABLE wh.sales AS SELECT * FROM staging;
DETACH wh;

ATTACH lets one session work with several files and even with other engines through extensions (SQLite, PostgreSQL, MySQL) as covered in extensions.

The concurrency rule

DuckDB supports two modes, and you must pick one per process:

  1. One process with read and write access. Inside it, many threads can use the database at once.
  2. Many processes with read-only access. None may write.

You cannot have a writer process and a second process open the same file at the same time. A second process that tries gets an error such as "Could not set lock on file". This is the biggest surprise for people coming from PostgreSQL, so plan for it: if several programs need to write, funnel writes through one service, write separate Parquet files, or use a client-server database for the shared state.

flowchart LR
    subgraph P1["Process A (read/write)"]
      T1["thread 1"] --> DB[("analytics.duckdb")]
      T2["thread 2"] --> DB
    end
    P2["Process B"] -. "blocked while A has the file open for writing" .-> DB

Threads share a database inside one process. Other processes are locked out while a writer holds it.

Connections, cursors and threads

A Python DuckDBPyConnection is not safe to share across threads. The idiom is to create a cursor per thread:

Python
import duckdb, threading

con = duckdb.connect("analytics.duckdb")

def worker(n):
    cur = con.cursor()               # own connection to the SAME database
    cur.execute("INSERT INTO events VALUES (?, now())", [n])

threads = [threading.Thread(target=worker, args=(i,)) for i in range(4)]
[t.start() for t in threads]; [t.join() for t in threads]

con.cursor() shares the underlying database instance, so all cursors see each other's committed work. Separate duckdb.connect("same-file") calls in one process also resolve to the same database instance. DuckDB uses optimistic concurrency control: if two transactions change the same rows, one fails with a conflict and you retry.

Transactions

DuckDB is ACID. Every statement runs in a transaction; by default each statement auto-commits. Group statements explicitly:

SQL
BEGIN TRANSACTION;
INSERT INTO accounts VALUES (3, 'cho', 100);
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 3;
COMMIT;      -- or ROLLBACK;

Snapshot isolation applies: a transaction sees the database as it was when it began, plus its own changes. DDL (CREATE TABLE, ALTER) is transactional too.

SQL Transactions: commit and rollback (portable SQL)

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

Change ROLLBACK to COMMIT and the new balances stick. The same pattern works in DuckDB.

Durability, checkpoints and backups

Committed changes first go to the write-ahead log and are merged into the main file at a checkpoint. This happens automatically when the log grows (the default threshold is a few hundred megabytes) and when the database closes cleanly. CHECKPOINT; forces it. Because the database is one file after a clean close, a backup is a file copy; EXPORT DATABASE 'backup_dir' (FORMAT parquet) writes a portable folder of Parquet files plus schema SQL, and IMPORT DATABASE 'backup_dir' restores it.

Deleted data does not always shrink the file immediately; space is reused later, and VACUUM or a fresh export-import compacts when needed. Always close connections (or use with) in scripts so the checkpoint happens.

Stand-in: constraint-protected writes

Constraints behave the same everywhere, and are your friend in embedded apps. This runnable stand-in shows a failed batch leaving the table unchanged when you roll back in SQLite:

Python Stand-in: a failed batch rolls back (sqlite3)

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only row 1 survives: the valid insert for row 2 was undone with the failing one. In DuckDB use con.begin(), con.commit() and con.rollback() or the with con: style similarly.

Practical guidance

  • One writer process; scale reads with read-only connections or file copies.
  • Use a cursor per thread; share the database, not the connection object.
  • Retry on transaction conflict errors.
  • Keep the file on a local disk, not a network share that does not honour file locks.
  • For multi-user, always-on workloads with many writers, pair DuckDB with a server database for state, and use DuckDB for the analytics.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which access pattern is allowed on one database file?

    Either a single process has read/write access, or multiple processes open the file read-only.
  2. 2. How should several Python threads use one DuckDB database?

    con.cursor() makes a per-thread connection sharing the same database instance.
  3. 3. What does a bare duckdb.connect() give you?

    Without a path the database lives in memory and disappears when closed.
  4. 4. What does a checkpoint do?

    Checkpoints fold WAL contents into the main file, automatically or via CHECKPOINT.

Summary

  • A DuckDB database is one file (plus a WAL until checkpointed); :memory: is private and temporary.
  • One read/write process or many read-only ones; never a writer plus other processes.
  • Threads share the database through cursors; transactions use snapshot isolation and optimistic concurrency.
  • Back up with a file copy or EXPORT DATABASE; close connections cleanly.

Next: Window Functions: The Basics