Used for establishing a database session.

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

    • 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: , exists(), , select().

    Example of usage as a decorator:

    As a context manager:

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

    • READ UNCOMMITTED - the most unsafe level

    • READ COMMITTED

    • REPEATABLE READ

    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 context manager:

    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 - 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 context manager):

    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, .

    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 . 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.