Transactions

with db.transaction():
    db.insert('accounts', {'balance': 100})
    db.update('accounts', {'balance': 50}, {'id': 2})
# commits on the way out; rolls back if an exception propagates

Manual form:

db.begin()
try:
    ...
    db.commit()
except Exception:
    db.rollback()
    raise

Nesting

Supported through SAVEPOINT. The inner block rolls back only its own part; the outer one stays alive:

with db.transaction():
    db.insert('t', {'name': 'outer'})
    try:
        with db.transaction():          # SAVEPOINT
            db.insert('t', {'name': 'inner'})
            raise RuntimeError()
    except RuntimeError:
        pass                            # 'inner' rolled back, 'outer' still live
    db.insert('t', {'name': 'another'}) # still inside the outer transaction

Ownership

The transaction belongs to the thread that opened it until the outermost commit or rollback. Operations from other threads raise RuntimeError — see Concurrency.

The with block ends the exact level it opened. Do not close or replace that level from inside the block with commit(), rollback() or close(); the block checks its level on exit and raises RuntimeError if it changed.

After a failed transaction command

If begin(), a commit, a rollback or a savepoint command fails, further database work raises RuntimeError until the owning thread calls rollback() or close(). That recovery rollback targets the whole transaction, not a savepoint.

A failed commit is not repeated, including on a second call to commit().

If a rollback also fails while an exception is leaving a block, the original exception stays the primary one and the rollback error is chained as its cause.

Pending work outside a transaction

execute() and query() can leave an open unit of work behind. While that work is pending, these raise RuntimeError: insert, update, delete, upsert, the batch operations, begin(), truncate(), connect(), reconnect() and resetSession().

db.execute("INSERT INTO audit (message) VALUES (%s)", ('x',))
db.insert('users', {'email': '[email protected]'})   # RuntimeError

db.commit()      # or rollback(); then it works again

execute() and query() stay available, so the unit can be finished. To keep both operations together, open the block first:

with db.transaction():
    db.execute("INSERT INTO audit (message) VALUES (%s)", ('x',))
    db.insert('users', {'email': '[email protected]'})

Batches

executemany(), execute_multiple(), insert_many() and upsert_many() always run inside a transaction. Nested in an outer one, each batch gets its own savepoint:

with db.transaction():
    db.insert('orders', {'customer_id': 7})
    try:
        db.execute_multiple([...])    # rolled back to its own savepoint
    except Exception:
        pass
    db.insert('orders', {'customer_id': 8})
# both inserts commit; nothing from the failed batch does

The guarantee covers transactional DML on a transactional engine. It does not extend to DDL, raw transaction-control statements, non-transactional engines, or errors that abort the whole server-side transaction.

An empty batch sends no SQL and commits nothing.

Interaction with the rest of the API

• close() with a transaction open calls rollback() and emits a UserWarning.
• A successful commit keeps count() and getLastId() from the data operation. Rollback, close, reset and reconnect clear them. The library's internal savepoints never replace them.
• On PostgreSQL, the outermost transaction restores the driver's previous autocommit setting when it ends.

is_in_transaction()

db.is_in_transaction()   # -> bool

It reports the library's own nesting: whether a block opened with transaction() or begin() is active.