Transaction management

Psycopg has a behaviour that may result surprising compared to psql: by default, any database operation will start a new transaction. As a consequence, changes made by any cursor of the connection will not be visible until Connection.commit() is called, and will be discarded by Connection.rollback(). The following operation on the same connection will start a new transaction.

If a database operation fails, the server will refuse further commands, until a rollback() is called.

Hint

If a database operation fails with an error message such as InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block, it means that a previous operation failed and the database session is in a state of error. You need to call rollback() if you want to keep on using the same connection.

Autocommit transactions

The manual commit requirement can be suspended using autocommit, either as connection attribute or as connect() parameter. This may be required to run operations that cannot be executed inside a transaction, such as CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control.

Warning

By default even a simple SELECT will start a transaction: in long-running programs, if no further action is taken, the session will remain idle in transaction, an undesirable condition for several reasons (locks are held by the session, tables bloat…). For long lived scripts, either make sure to terminate a transaction as soon as possible or use an autocommit connection.

Transaction blocks

A more transparent way to make sure that transactions are finalised at the right time is to use with Connection.transaction() to create a transaction block. When the block is entered a transaction is started; when leaving the block the transaction is committed, or it is rolled back if an exception is raised inside the block.

For instance, an hypothetical but extremely secure bank may have the following code to avoid that no accident between the following two lines leaves the accounts unbalanced:

with conn.transaction():
    move_money(conn, account1, -100)
    move_money(conn, account2, +100)

# The transaction is now committed

But because the bank is, like, extremely secure, they also verify that no account goes negative:

def move_money(conn, account, amount):
    new_balance = add_to_balance(conn, account, amount)
    if new_balance < 0:
        raise ValueError("account balance cannot go negative")

In case this function raises an exception, be it the ValueError in the example or any other exception expected or not, the transaction will be rolled back, and the exception will propagate out of the with block, further down the call stack.

Transaction blocks can also be nested (internal transaction blocks are implemented using SAVEPOINT): an exception raised inside an inner block has a chance of being handled and not completely fail outer operations. The following is an example where a series of operations interact with the database: operations are allowed to fail, plus we also want to store the number of operations successfully processed.

with conn.transaction() as tx1:
    num_ok = 0
    for operation in operations:
        try:
            with conn.transaction() as tx2:
                unreliable_operation(conn, operation)
        except Exception:
            logger.exception(f"{operation} failed")
        else:
            num_ok += 1

    save_number_of_successes(conn, num_ok)

If unreliable_operation() causes an error, including an operation causing a database error, all its changes will be reverted. The exception bubbles up outside the block: in the example it is intercepted by the try so that the loop can complete. The outermost block is unaffected (unless other errors happen there).

You can also write code to explicitly roll back any currently active transaction block, by raising the Rollback exception. The exception “jumps” to the end of a transaction block, rolling back its transaction but allowing the program execution to continue from there. By default the exception rolls back the innermost transaction block, but any current block can be specified as the target. In the following example, an hypothetical CancelCommand may stop the processing and cancel any operation previously performed, but not entirely committed yet.

from psycopg import Rollback

with conn.transaction() as outer_tx:
    for command in commands():
        with conn.transaction() as inner_tx:
            if isinstance(command, CancelCommand):
                raise Rollback(outer_tx)
            process_command(command)

# If `Rollback` is raised, it would propagate only up to this block,
# and the program would continue from here with no exception.

Transaction characteristics

You can set transaction parameters for the transactions that Psycopg handles. They affect the transactions started implicitly by non-autocommit transactions and the ones started explicitly by Connection.transaction() for both autocommit and non-autocommit transactions. Leaving these parameters to None will leave the behaviour to the server’s default (which is controlled by server settings such as default_transaction_isolation).

In order to set these parameters you can use the connection attributes isolation_level, read_only, deferrable. For async connections you must use the equivalent set_isolation_level() method and similar. The parameters can only be changed if there isn’t a transaction already active on the connection.

Warning

Applications running at REPEATABLE_READ or SERIALIZABLE isolation level are exposed to serialization failures. In certain concurrent update cases, PostgreSQL will raise an exception looking like:

psycopg2.errors.SerializationFailure: could not serialize access
due to concurrent update

In this case the application must be prepared to repeat the operation that caused the exception.