# Transactions

Peewee provides several interfaces for working with transactions. The most general is the `Database.atomic()` method, which also supports nested transactions. `atomic()` blocks will be run in a transaction or savepoint, depending on the level of nesting.

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

Note

While inside a block wrapped by the `atomic()` context manager, you can explicitly rollback or commit at any point by calling `Transaction.rollback()` or `Transaction.commit()`. When you do this inside a wrapped block of code, a new transaction will be started automatically.

Consider this code:

    db.begin()  # Open a new transaction.
    try:
        save_some_objects()
    except ErrorSavingData:
        db.rollback()  # Uh-oh! Let's roll-back any partial changes.
        error_saving = True

    create_report(error_saving=error_saving)
    db.commit()  # What happens here??

If the `ErrorSavingData` exception gets raised, we call rollback, but because we are not using the `~Database.atomic` context manager, no new transaction is begun. The call to `commit()` will fail because no transaction is active!

On the other hand, consider this:

    with db.atomic() as transaction:  # Opens new transaction.
        try:
            save_some_objects()
        except ErrorSavingData:
            # Because this block of code is wrapped with "atomic", a
            # new transaction will begin automatically after the call
            # to rollback().
            db.rollback()
            error_saving = True

        create_report(error_saving=error_saving)
        # Note: no need to call commit. Since this marks the end of the
        # wrapped block of code, the `atomic` context manager will
        # automatically call commit for us.

Note

`atomic()` can be used as either a context manager or a decorator.

## Context manager

Using `atomic` as context manager:

    db = SqliteDatabase(':memory:')

    with db.atomic() as txn:
        # This is the outer-most level, so this block corresponds to
        # a transaction.
        User.create(username='charlie')

        with db.atomic() as nested_txn:
            # This block corresponds to a savepoint.
            User.create(username='huey')

            # This will roll back the above create() query.
            nested_txn.rollback()

        User.create(username='mickey')

    # When the block ends, the transaction is committed (assuming no error
    # occurs). At that point there will be two users, "charlie" and "mickey".

You can use the `atomic` method to perform get or create operations as well:

    try:
        with db.atomic():
            user = User.create(username=username)
        return 'Success'
    except peewee.IntegrityError:
        return 'Failure: %s is already in use.' % username

## Decorator

Using `atomic` as a decorator:

    @db.atomic()
    def create_user(username):
        # This statement will run in a transaction. If the caller is already
        # running in an `atomic` block, then a savepoint will be used instead.
        return User.create(username=username)

    create_user('charlie')

## Nesting Transactions

`atomic()` provides transparent nesting of transactions. When using `atomic()`, the outer-most call will be wrapped in a transaction, and any nested calls will use savepoints.

    with db.atomic() as txn:
        perform_operation()

        with db.atomic() as nested_txn:
            perform_another_operation()

Peewee supports nested transactions through the use of savepoints (for more information, see `savepoint()`).

## Explicit transaction

If you wish to explicitly run code in a transaction, you can use `transaction()`. Like `atomic()`, `transaction()` can be used as a context manager or as a decorator.

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.

    db = SqliteDatabase(':memory:')

    with db.transaction():
        # Delete the user and their associated tweets.
        user.delete_instance(recursive=True)

Transactions can be explicitly committed or rolled-back within the wrapped block. When this happens, a new transaction will be started.

    with db.transaction() as txn:
        User.create(username='mickey')
        txn.commit()  # Changes are saved and a new transaction begins.
        User.create(username='huey')

        # Roll back. "huey" will not be saved, but since "mickey" was already
        # committed, that row will remain in the database.
        txn.rollback()

    with db.transaction() as txn:
        User.create(username='whiskers')
        # Roll back changes, which removes "whiskers".
        txn.rollback()

        # Create a new row for "mr. whiskers" which will be implicitly committed
        # at the end of the `with` block.
        User.create(username='mr. whiskers')

Note

If you attempt to nest transactions with peewee using the `transaction()` context manager, only the outer-most transaction will be used. However if an exception occurs in a nested block, this can lead to unpredictable behavior, so it is strongly recommended that you use `atomic()`.

### Explicit Savepoints

Just as you can explicitly create transactions, you can also explicitly create savepoints using the `savepoint()` method. 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" will not be saved, but "mickey" will be.

Note

If you manually commit or roll back a savepoint, a new savepoint will not automatically be created. This differs from the behavior of `transaction`, which will automatically open a new transaction after manual commit/rollback.

## Autocommit Mode

By default, databases are initialized with `autocommit=True`, you can turn this on and off at runtime if you like. If you choose to disable autocommit, then you must explicitly call `Database.begin()` to begin a transaction, and commit or roll back.

The behavior below is roughly the same as the context manager and decorator:

    db.set_autocommit(False)
    db.begin()
    try:
        user.delete_instance(recursive=True)
    except:
        db.rollback()
        raise
    else:
        try:
            db.commit()
        except:
            db.rollback()
            raise
    finally:
        db.set_autocommit(True)

If you would like to manually control every transaction, simply turn autocommit off when instantiating your database:

    db = SqliteDatabase(':memory:', autocommit=False)

    db.begin()
    User.create(username='somebody')
    db.commit()