Transactions & db_session

@db_session(allowed_exceptions=[], immediate=False, optimistic=True, retry=0, retry_exceptions=[TransactionError], serializable=False, strict=False, sql_debug=None, show_values=None)

Used for establishing a database session.

• Parameters

  • allowed_exceptions (list) – a list of exceptions which when occurred do not cause the transaction rollback. Can be useful with some web frameworks which trigger HTTP redirect with the help of an exception.

  • immediate (bool) – tells Pony when start a transaction with the database. Some databases (e.g. SQLite, Postgres) start a transaction only when a modifying query is sent to the database(UPDATE, INSERT, DELETE) and don’t start it for SELECTs. If you need to start a transaction on SELECT, then you should set immediate=True. Usually there is no need to change this parameter.

  • optimistic (bool) – True by default. When optimistic=False, no optimistic checks will be added to queries within this db_session (new in version 0.7.3)

  • retry (int) – specifies the number of attempts for committing the current transaction. This parameter can be used with the @db_session decorator only. The decorated function should not call commit() or rollback() functions explicitly. When this parameter is specified, Pony catches the TransactionError exception (and all its descendants) and restarts the current transaction. By default Pony catches the TransactionError exception only, but this list can be modified using the retry_exceptions parameter.

  • retry_exceptions (list|callable) – a list of exceptions which will cause the transaction restart. By default this parameter is equal to [TransactionError]. Another option is using a callable which returns a boolean value. This callable receives the only parameter - an exception object. If this callable returns True then the transaction will be restarted.

  • serializable (bool) – allows setting the SERIALIZABLE isolation level for a transaction.

  • strict (bool) – when True the cache will be cleared on exiting the db_session. If you’ll try to access an object after the session is over, you’ll get the pony.orm.core.DatabaseSessionIsOver exception. Normally Pony strongly advises that you work with entity objects only within the db_session. But some Pony users want to access extracted objects in read-only mode even after the db_session is over. In order to provide this feature, by default, Pony doesn’t purge cache on exiting from the db_session. This might be handy, but in the same time, this can require more memory for keeping all objects extracted from the database in cache.

  • sql_debug (bool) – when sql_debug=True - log SQL statements to the console or to a log file. When sql_debug=False - suppress logging, if it was set globally by set_sql_debug(). The default value None means it doesn’t change the global debug mode. (new in version 0.7.3)

  • show_values (bool) – when True, query parameters will be logged in addition to the SQL text. (new in version 0.7.3)

Can be used as a decorator or a context manager. When the session ends it performs the following actions:

• Commits transaction if data was changed and no exceptions occurred otherwise it rolls back transaction.

• Returns the database connection to the connection pool.

• Clears the Identity Map cache.

If you forget to specify the db_session where necessary, Pony will raise the TransactionError: db_session is required when working with the database exception.

When you work with Python’s interactive shell you don’t need to worry about the database session, because it is maintained by Pony automatically.

If you’ll try to access instance’s attributes which were not loaded from the database outside of the db_session scope, you’ll get the DatabaseSessionIsOver exception. This happens because by this moment the connection to the database is already returned to the connection pool, transaction is closed and we cannot send any queries to the database.

When Pony reads objects from the database it puts those objects to the Identity Map. Later, when you update an object’s attributes, create or delete an object, the changes will be accumulated in the Identity Map first. The changes will be saved in the database on transaction commit or before calling the following functions: get(), exists(), commit(), select().

Example of usage as a decorator:

@db_session
def check_user(username):
    return User.exists(username=username)

As a context manager:

def process_request():
    ...
    with db_session:
        u = User.get(username=username)
        ...

Transaction isolation levels and database peculiarities

Isolation is a property that defines when the changes made by one transaction become visible to other concurrent transactions Isolation levels). The ANSI SQL standard defines four isolation levels:

• READ UNCOMMITTED - the most unsafe level

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE - the most safe level

When using the SERIALIZABLE level, each transaction sees the database as a snapshot made at the beginning of a transaction. This level provides the highest isolation, but it requires more resources than other levels.

This is the reason why most databases use a lower isolation level by default which allow greater concurrency. By default Oracle and PostgreSQL use READ COMMITTED, MySQL - REPEATABLE READ. SQLite supports the SERIALIZABLE level only, but Pony emulates the READ COMMITTED level for allowing greater concurrency.

If you want Pony to work with transactions using the SERIALIZABLE isolation level, you can do that by specifying the serializable=True parameter to the db_session() decorator or db_session() context manager:

@db_session(serializable=True)
def your_function():
    ...

READ COMMITTED vs. SERIALIZABLE mode

In SERIALIZABLE mode, you always have a chance to get a “Can’t serialize access due to concurrent update” error, and would have to retry the transaction until it succeeded. You always need to code a retry loop in your application when you are using SERIALIZABLE mode for a writing transaction.

In READ COMMITTED mode, if you want to avoid changing the same data by a concurrent transaction, you should use SELECT FOR UPDATE. But this way there is a chance to have a database deadlock - the situation where one transaction is waiting for a resource which is locked by another transaction. If your transaction got a deadlock, your application needs to restart the transaction. So you end up needing a retry loop either way. Pony can restart a transaction automatically if you specify the retry parameter to the db_session() decorator (but not the db_session() context manager):

@db_session(retry=3)
def your_function():
    ...

SQLite

When using SQLite, Pony’s behavior is similar as with PostgreSQL: when a transaction is started, selects will be executed in the autocommit mode. The isolation level of this mode is equivalent of READ COMMITTED. This way the concurrent transactions can be executed simultaneously with no risk of having a deadlock (the sqlite3.OperationalError: database is locked is not arising with Pony ORM). When your code issues non-select statement, Pony begins a transaction and all following SQL statements will be executed within this transaction. The transaction will have the SERIALIZABLE isolation level.

PostgreSQL

PostgreSQL uses the READ COMMITTED isolation level by default. PostgreSQL also supports the autocommit mode. In this mode each SQL statement is executed in a separate transaction. When your application just selects data from the database, the autocommit mode can be more effective because there is no need to send commands for beginning and ending a transaction, the database does it automatically for you. From the isolation point of view, the autocommit mode is nothing different from the READ COMMITTED isolation level. In both cases your application sees the data which have been committed by this moment.

Pony automatically switches from the autocommit mode and begins an explicit transaction when your application needs to modify data by several INSERT, UPDATE or DELETE SQL statements in order to provide atomicity of data update.

MySQL

MySQL uses the REPEATABLE READ isolation level by default. Pony doesn’t use the autocommit mode with MySQL because there is no benefit of using it here. The transaction begins with the first SQL statement sent to the database even if this is a SELECT statement.

Oracle

Oracle uses the READ COMMITTED isolation level by default. Oracle doesn’t have the autocommit mode. The transaction begins with the first SQL statement sent to the database even if this is a SELECT statement.

CockroachDB

CocrkoachDB uses optimistic transactions implemented at the database level. An application should handle error with code 40001 and an error message that begins with the string “retry transaction” by retrying the code of transaction, see more info here.

PonyORM can handle that logic automatically. If you specify retry=N option to db_session decorator, then PonyORM will automatically do N attempts to retry the code decorated with the db_session. Note that db_session should be specified as a decorator and not as a context manager, as context manager in Python cannot retry the code block.