Transactions

A transaction groups one or more SQL statements into a single unit of work. Either all of the statements succeed and are committed to the database, or none of them are - the database rolls back to the state it was in before the transaction began.

Peewee operates in autocommit mode: every statement that runs outside an explicit transaction runs in its own implicit transaction. To group statements, use the tools described in this document.

db.atomic

Database.atomic() is the recommended transaction API. atomic() can be used as a context manager or a decorator, and it handles nesting automatically.

If an unhandled exception occurs in a wrapped block, the current block will be rolled back. Otherwise the statements will be committed at the end of the block.

As a context manager:

with db.atomic() as txn:
    user = User.create(username='charlie')
    tweet = Tweet.create(user=user, content='Hello')

# Both rows are committed when block exits normally.

As a decorator:

@db.atomic()
def create_user_with_tweet(username, content):
    user = User.create(username=username)
    Tweet.create(user=user, content=content)
    return user

If an unhandled exception propagates out of the block, the transaction (or savepoint - see below) is rolled back and the exception continues to propagate:

with db.atomic() as txn:
    User.create(username='huey')
    # User has been INSERTed into the database but the transaction is not
    # yet committed because we haven't left the scope of the "with" block.

    raise ValueError('something went wrong')
    # This exception is unhandled - the transaction will be rolled-back and
    # the ValueError will be raised.

# User('huey') was NOT committed, the transaction rolled-back.
# The ValueError is raised here.

Manual Commit / Rollback

You can commit or roll-back explicitly inside an atomic() block. After calling commit() or rollback() a new transaction (or savepoint) begins automatically:

with db.atomic() as txn:
    try:
        save_objects()
    except SaveError:
        txn.rollback()  # Roll back, new transaction starts automatically.
        log_error()

    finalize()  # Runs in a new transaction.

# finalize()'s changes are committed here.

Nesting Transactions

The outermost atomic() block creates a transaction. Any nested atomic() blocks create savepoints instead. A savepoint is a named point within a transaction to which you can roll back without affecting the rest of the transaction.

with db.atomic():                        # Transaction begins.
    User.create(username='charlie')

    with db.atomic() as sp:              # Savepoint begins.
        User.create(username='huey')
        sp.rollback()                    # Rolls back huey only.
        User.create(username='alice')    # New savepoint begins here.

    User.create(username='mickey')
# Committed: charlie, alice, mickey. huey was rolled back.

Savepoints can be nested arbitrarily deep:

with db.atomic():
    with db.atomic():
        with db.atomic() as inner:
            do_something_risky()
            inner.rollback()   # Only the innermost work is lost.
        do_something_safe()

atomic() tracks the nesting depth internally. You do not need to manage savepoint names or transaction state manually.

Explicit Transaction

Database.transaction() opens an explicit transaction that does not nest. Any transaction() call inside an outer transaction() block is ignored - only the outermost transaction is active.

Use this only when you explicitly need a flat, non-nesting transaction. For most cases, atomic() is the better choice.

If an exception occurs in a wrapped block, the transaction will be rolled back. Otherwise the statements will be committed at the end of the wrapped block.

with db.transaction() as txn:
    User.create(username='mickey')
    txn.commit()         # Commit now; a new transaction begins.
    User.create(username='huey')
    txn.rollback()       # Roll back huey; a new transaction begins.
    User.create(username='zaizee')
# zaizee is committed when the block exits.

If you attempt to nest transactions with peewee using the transaction() context manager, only the outer-most transaction will be used.

As this may lead to unpredictable behavior, it is recommended that you use atomic().

Explicit Savepoints

Database.savepoint() creates a savepoint within an active transaction. Savepoints must occur within a transaction, but can be nested arbitrarily deep.

with db.transaction() as txn:
    with db.savepoint() as sp:
        User.create(username='mickey')

    with db.savepoint() as sp2:
        User.create(username='zaizee')
        sp2.rollback()  # "zaizee" is not saved.
        User.create(username='huey')

# mickey and huey were created.

If you manually commit or roll back a savepoint, a new savepoint will automatically begin.

Autocommit Mode

Peewee requires the underlying driver to run in autocommit mode and manages transaction boundaries itself. This differs from the DB-API 2.0 default, which starts a transaction implicitly and requires you to commit manually. As a result, Peewee puts all DB-API drivers into autocommit mode.

In rare cases where you need to take direct control of BEGIN/COMMIT/ ROLLBACK - bypassing Peewee’s transaction management entirely - use manual_commit():

with db.manual_commit():
    db.begin()  # Begin transaction explicitly.
    try:
        user.delete_instance(recursive=True)
    except:
        db.rollback()  # Rollback! An error occurred.
        raise
    else:
        try:
            db.commit()  # Commit changes.
        except:
            db.rollback()
            raise

manual_commit suspends Peewee’s transaction management for the duration of the block. atomic() and transaction() have no effect inside it. This should rarely be needed in application code.

SQLite Transaction Locking Modes

SQLite supports three locking modes for transactions. Use these when precise control over read-write locking is required:

with db.atomic('EXCLUSIVE'):
    # No other connection can read or write until this commits.
    do_something()

@db.atomic('IMMEDIATE')
def load_data():
    # No other writer is allowed, but readers can proceed.
    insert_records()

The three modes:

  • DEFERRED (default) - acquires the minimum necessary lock as reads and writes occur. Another writer can intervene between BEGIN and your first write.

  • IMMEDIATE - acquires a write reservation lock at BEGIN. Other writers are blocked; readers can proceed.

  • EXCLUSIVE - acquires an exclusive lock at BEGIN. No other connection can read or write until the transaction completes.

See also

SQLite locking documentation.

Postgresql Isolation Notes

Postgresql supports configurable isolation levels per-transaction, from least to most strict:

  • READ UNCOMMITTED

  • READ COMMITTED (default in most deployments)

  • REPEATABLE READ

  • SERIALIZABLE

See the Postgresql transaction isolation docs for discussion.

The default isolation level is specified when initializing PostgresqlDatabase:

db = PostgresqlDatabase(
    'my_app',
    user='postgres',
    host='10.8.0.1',
    port=5432,
    isolation_level='SERIALIZABLE')

# Or use the constants provided by the driver.

from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
db = PostgresqlDatabase(
    ...
    isolation_level=ISOLATION_LEVEL_SERIALIZABLE)


from psycopg import IsolationLevel
db = PostgresqlDatabase(
    ...
    isolation_level=IsolationLevel.SERIALIZABLE)

To control the isolation-level for a transaction, you can pass the desired setting to the outer-most atomic() block:

with db.atomic('SERIALIZABLE') as txn:
    ...

Isolation level cannot be specified for nested atomic() blocks.