# Transactions


> How BEGIN, COMMIT, and ROLLBACK behave on MotherDuck databases, including Dual Execution, the Postgres endpoint, concurrent writers, and dropped connections.

MotherDuck supports explicit multi-statement transactions on MotherDuck databases. Use `BEGIN`, `COMMIT`, and `ROLLBACK` from a DuckDB client (CLI, Python, Node.js, JDBC, and others) or through the [Postgres endpoint](/sql-reference/postgres-endpoint). Transactions follow the DuckDB transaction model, with the MotherDuck-specific limits on this page. For the DuckDB statement syntax, see [Transaction management](https://duckdb.org/docs/stable/sql/statements/transactions) in the DuckDB documentation.

## Syntax

```sql
BEGIN TRANSACTION;
-- one or more statements
COMMIT;      -- apply all changes
-- or
ROLLBACK;    -- discard all changes
```

`BEGIN` is shorthand for `BEGIN TRANSACTION`, and `ABORT` is an alias for `ROLLBACK`. Without `BEGIN`, each statement runs in its own transaction and commits when it finishes.

## Example

This transaction replaces one day of orders. Either both statements apply or neither does:

```sql
BEGIN TRANSACTION;

DELETE FROM my_db.main.orders
WHERE order_date = DATE '2026-03-10';

INSERT INTO my_db.main.orders
SELECT * FROM read_parquet('s3://<bucket>/daily/2026-03-10/*.parquet');

COMMIT;
```

For more load patterns that use transactions, see [Idempotent loads with transactions](/key-tasks/loading-data-into-motherduck/loading-patterns#idempotent-loads-with-transactions).

## What a transaction can contain

| Operation inside one transaction | Supported | Notes |
|---|---|---|
| `INSERT`, `UPDATE`, `DELETE`, `MERGE INTO` on a MotherDuck database | Yes | `ROLLBACK` undoes all of them. |
| `CREATE TABLE`, `CREATE OR REPLACE TABLE`, `ALTER TABLE`, `DROP TABLE` | Yes | DDL on tables is transactional. `ROLLBACK` restores the previous tables and columns. |
| Writes to several tables in the same database | Yes | The writes commit together. |
| Writes to two different databases | No | Fails with `a single transaction can only write to a single attached database`. Use one transaction per database. |
| Writes to a local database and a MotherDuck database | No | Fails with the same error. A local DuckDB file or in-memory database counts as a separate database. |
| Read local data and write a MotherDuck database ([Dual Execution](/concepts/architecture-and-capabilities#dual-execution)) | Yes | Sources can be a pandas DataFrame, an Arrow table, a local file, or a local database. |
| Create a local temporary table and write a MotherDuck database | Yes | Temporary tables don't count as a written database. |
| Read other MotherDuck databases or attached shares while writing one database | Yes | Only writes are limited to one database. |
| `CREATE DATABASE`, `DROP DATABASE`, `UNDROP DATABASE`, `CREATE SHARE` | Runs, but not transactional | These statements take effect immediately. `ROLLBACK` doesn't undo them. A database dropped inside a rolled-back transaction stays dropped; restore it with [`UNDROP DATABASE`](/sql-reference/motherduck-sql-reference/undrop-database). |
| `CREATE RESULT` | No | Fails with `Results cannot be modified/created in multi-statement transactions`. |
| Nested `BEGIN`, `SAVEPOINT`, `ROLLBACK TO` | No | A second `BEGIN` fails with `cannot start a transaction within a transaction`. `SAVEPOINT` is a syntax error. |
| `SELECT ... FOR UPDATE`, `BEGIN ISOLATION LEVEL SERIALIZABLE` | No | MotherDuck has no row locks or advisory locks. See [Concurrent transactions](#concurrent-transactions). |

Keep transactions short. For loads, see the guidance on [batch and transaction size](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data).

## Errors inside a transaction

When a statement fails inside a transaction, for example on a `NOT NULL` or primary key violation, the transaction is aborted. Every later statement fails with:

```text
TransactionContext Error: Current transaction is aborted (please ROLLBACK)
```

None of the transaction's changes are kept, including statements that succeeded before the error. A `COMMIT` sent after the error doesn't raise an error, but it applies nothing. Check the result of each statement, or use your driver's transaction helper, instead of relying on `COMMIT` to report the failure.

## Concurrent transactions

This section covers several connections from the same user writing to the same database, for example several processes that use one service account token.

- Each transaction reads from a snapshot taken at its first statement. Uncommitted changes from other connections aren't visible. Changes that other connections commit later stay invisible until the transaction ends.
- Concurrent `INSERT` statements into the same table from different transactions all commit.
- Concurrent updates or deletes of different rows all commit.
- When two open transactions update or delete the same row, the second one fails right away with `TransactionContext Error: Conflict on update!`. MotherDuck doesn't wait for the first transaction to finish. The error aborts the second transaction, so roll it back and retry it.
- When one transaction alters a table (for example `ALTER TABLE ... ADD COLUMN`) while another writes to it, the writer fails with `Transaction conflict: attempting to insert into table "<table>" but it has been altered by a different transaction`.
- `CREATE OR REPLACE TABLE` replaces a table atomically. A reader with an open transaction keeps seeing the old table until its transaction ends.

## Connection drops

If the client disconnects before `COMMIT`, MotherDuck rolls back the open transaction. This applies when the process exits or is killed, when the connection closes without `COMMIT`, and when a Postgres endpoint connection closes. None of the transaction's changes become visible, and other connections can write the same rows afterwards.

To recover, run the whole transaction again. Write loads so that running them twice gives the same result. See [Idempotent loads with transactions](/key-tasks/loading-data-into-motherduck/loading-patterns#idempotent-loads-with-transactions).

## Postgres endpoint

Transactions over the [Postgres endpoint](/sql-reference/postgres-endpoint) behave as described on this page. Send `BEGIN`, the statements, and `COMMIT` or `ROLLBACK` on the same connection. Parameterized statements and `RETURNING` clauses work inside a transaction.

Driver helpers that wrap a block in one transaction, such as psycopg's `connection.transaction()`, work on a connection opened with `autocommit=True`. Without autocommit, the driver opens a transaction implicitly on the first statement, and a later `transaction()` block becomes a `SAVEPOINT`, which fails. Nested `transaction()` blocks fail for the same reason.

```python
import psycopg

with psycopg.connect(dsn, autocommit=True) as conn:
    with conn.transaction():
        conn.execute(
            "UPDATE accounts SET balance = balance - %s::INTEGER WHERE id = %s",
            (100, 1),
        )
        conn.execute(
            "UPDATE accounts SET balance = balance + %s::INTEGER WHERE id = %s",
            (100, 2),
        )
    # Both updates commit here. If the block raises an exception, neither applies.
```

The `::INTEGER` casts give the parameters a type. Without them, `balance - %s` fails with `Prepared statement with ambiguous result column types is not supported`.

Postgres-specific locking, such as `SELECT ... FOR UPDATE`, `SKIP LOCKED`, and `pg_advisory_lock()`, isn't available.


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fduckdb-sql-reference%2Ftransactions%2F&page_title=Transactions&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.
