oslo.db.sqlalchemy.session

Session Handling for SQLAlchemy backend.

Recommended ways to use sessions within this framework:

  • Don’t use them explicitly; this is like running with AUTOCOMMIT=1. model_query() will implicitly use a session when called without one supplied. This is the ideal situation because it will allow queries to be automatically retried if the database connection is interrupted.

    Note

    Automatic retry will be enabled in a future patch.

    It is generally fine to issue several queries in a row like this. Even though they may be run in separate transactions and/or separate sessions, each one will see the data from the prior calls. If needed, undo- or rollback-like functionality should be handled at a logical level. For an example, look at the code around quotas and reservation_rollback().

    Examples:

    def get_foo(context, foo):
        return (model_query(context, models.Foo).
                filter_by(foo=foo).
                first())
    
    def update_foo(context, id, newfoo):
        (model_query(context, models.Foo).
                filter_by(id=id).
                update({'foo': newfoo}))
    
    def create_foo(context, values):
        foo_ref = models.Foo()
        foo_ref.update(values)
        foo_ref.save()
        return foo_ref
    
  • Within the scope of a single method, keep all the reads and writes within the context managed by a single session. In this way, the session’s __exit__ handler will take care of calling flush() and commit() for you. If using this approach, you should not explicitly call flush() or commit(). Any error within the context of the session will cause the session to emit a ROLLBACK. Database errors like IntegrityError will be raised in session‘s __exit__ handler, and any try/except within the context managed by session will not be triggered. And catching other non-database errors in the session will not trigger the ROLLBACK, so exception handlers should always be outside the session, unless the developer wants to do a partial commit on purpose. If the connection is dropped before this is possible, the database will implicitly roll back the transaction.

    Note

    Statements in the session scope will not be automatically retried.

    If you create models within the session, they need to be added, but you do not need to call model.save():

    def create_many_foo(context, foos):
        session = sessionmaker()
        with session.begin():
            for foo in foos:
                foo_ref = models.Foo()
                foo_ref.update(foo)
                session.add(foo_ref)
    
    def update_bar(context, foo_id, newbar):
        session = sessionmaker()
        with session.begin():
            foo_ref = (model_query(context, models.Foo, session).
                        filter_by(id=foo_id).
                        first())
            (model_query(context, models.Bar, session).
                        filter_by(id=foo_ref['bar_id']).
                        update({'bar': newbar}))
    

    Note

    update_bar is a trivially simple example of using with session.begin. Whereas create_many_foo is a good example of when a transaction is needed, it is always best to use as few queries as possible.

    The two queries in update_bar can be better expressed using a single query which avoids the need for an explicit transaction. It can be expressed like so:

    def update_bar(context, foo_id, newbar):
        subq = (model_query(context, models.Foo.id).
                filter_by(id=foo_id).
                limit(1).
                subquery())
        (model_query(context, models.Bar).
                filter_by(id=subq.as_scalar()).
                update({'bar': newbar}))
    

    For reference, this emits approximately the following SQL statement:

    UPDATE bar SET bar = ${newbar}
        WHERE id=(SELECT bar_id FROM foo WHERE id = ${foo_id} LIMIT 1);
    

    Note

    create_duplicate_foo is a trivially simple example of catching an exception while using with session.begin. Here create two duplicate instances with same primary key, must catch the exception out of context managed by a single session:

    def create_duplicate_foo(context):
        foo1 = models.Foo()
        foo2 = models.Foo()
        foo1.id = foo2.id = 1
        session = sessionmaker()
        try:
            with session.begin():
                session.add(foo1)
                session.add(foo2)
        except exception.DBDuplicateEntry as e:
            handle_error(e)
    
  • Passing an active session between methods. Sessions should only be passed to private methods. The private method must use a subtransaction; otherwise SQLAlchemy will throw an error when you call session.begin() on an existing transaction. Public methods should not accept a session parameter and should not be involved in sessions within the caller’s scope.

    Note that this incurs more overhead in SQLAlchemy than the above means due to nesting transactions, and it is not possible to implicitly retry failed database operations when using this approach.

    This also makes code somewhat more difficult to read and debug, because a single database transaction spans more than one method. Error handling becomes less clear in this situation. When this is needed for code clarity, it should be clearly documented.

    def myfunc(foo):
        session = sessionmaker()
        with session.begin():
            # do some database things
            bar = _private_func(foo, session)
        return bar
    
    def _private_func(foo, session=None):
        if not session:
            session = sessionmaker()
        with session.begin(subtransaction=True):
            # do some other database things
        return bar
    

There are some things which it is best to avoid:

  • Don’t keep a transaction open any longer than necessary.

    This means that your with session.begin() block should be as short as possible, while still containing all the related calls for that transaction.

  • Avoid with_lockmode('UPDATE') when possible.

    In MySQL/InnoDB, when a SELECT ... FOR UPDATE query does not match any rows, it will take a gap-lock. This is a form of write-lock on the “gap” where no rows exist, and prevents any other writes to that space. This can effectively prevent any INSERT into a table by locking the gap at the end of the index. Similar problems will occur if the SELECT FOR UPDATE has an overly broad WHERE clause, or doesn’t properly use an index.

    One idea proposed at ODS Fall ‘12 was to use a normal SELECT to test the number of rows matching a query, and if only one row is returned, then issue the SELECT FOR UPDATE.

    The better long-term solution is to use INSERT .. ON DUPLICATE KEY UPDATE. However, this can not be done until the “deleted” columns are removed and proper UNIQUE constraints are added to the tables.

Enabling soft deletes:

  • To use/enable soft-deletes, the SoftDeleteMixin must be added to your model class. For example:

    class NovaBase(models.SoftDeleteMixin, models.ModelBase):
        pass
    

Efficient use of soft deletes:

  • There are two possible ways to mark a record as deleted: model.soft_delete() and query.soft_delete().

    The model.soft_delete() method works with a single already-fetched entry. query.soft_delete() makes only one db request for all entries that correspond to the query.

  • In almost all cases you should use query.soft_delete(). Some examples:

    def soft_delete_bar():
        count = model_query(BarModel).find(some_condition).soft_delete()
        if count == 0:
            raise Exception("0 entries were soft deleted")
    
    def complex_soft_delete_with_synchronization_bar(session=None):
        if session is None:
            session = sessionmaker()
        with session.begin(subtransactions=True):
            count = (model_query(BarModel).
                        find(some_condition).
                        soft_delete(synchronize_session=True))
                        # Here synchronize_session is required, because we
                        # don't know what is going on in outer session.
            if count == 0:
                raise Exception("0 entries were soft deleted")
    
  • There is only one situation where model.soft_delete() is appropriate: when you fetch a single record, work with it, and mark it as deleted in the same transaction.

    def soft_delete_bar_model():
        session = sessionmaker()
        with session.begin():
            bar_ref = model_query(BarModel).find(some_condition).first()
            # Work with bar_ref
            bar_ref.soft_delete(session=session)
    

    However, if you need to work with all entries that correspond to query and then soft delete them you should use the query.soft_delete() method:

    def soft_delete_multi_models():
        session = sessionmaker()
        with session.begin():
            query = (model_query(BarModel, session=session).
                        find(some_condition))
            model_refs = query.all()
            # Work with model_refs
            query.soft_delete(synchronize_session=False)
            # synchronize_session=False should be set if there is no outer
            # session and these entries are not used after this.
    

    When working with many rows, it is very important to use query.soft_delete, which issues a single query. Using model.soft_delete(), as in the following example, is very inefficient.

    for bar_ref in bar_refs:
        bar_ref.soft_delete(session=session)
    # This will produce count(bar_refs) db requests.
    
class oslo.db.sqlalchemy.session.EngineFacade(sql_connection, slave_connection=None, sqlite_fk=False, autocommit=True, expire_on_commit=False, **kwargs)

Bases: object

A helper class for removing of global engine instances from oslo.db.

As a library, oslo.db can’t decide where to store/when to create engine and sessionmaker instances, so this must be left for a target application.

On the other hand, in order to simplify the adoption of oslo.db changes, we’ll provide a helper class, which creates engine and sessionmaker on its instantiation and provides get_engine()/get_session() methods that are compatible with corresponding utility functions that currently exist in target projects, e.g. in Nova.

engine/sessionmaker instances will still be global (and they are meant to be global), but they will be stored in the app context, rather that in the oslo.db context.

Note: using of this helper is completely optional and you are encouraged to integrate engine/sessionmaker instances into your apps any way you like (e.g. one might want to bind a session to a request context). Two important things to remember:

  1. An Engine instance is effectively a pool of DB connections, so it’s meant to be shared (and it’s thread-safe).
  2. A Session instance is not meant to be shared and represents a DB transactional context (i.e. it’s not thread-safe). sessionmaker is a factory of sessions.
classmethod from_config(conf, sqlite_fk=False, autocommit=True, expire_on_commit=False)

Initialize EngineFacade using oslo.config config instance options.

Parameters:
  • conf (oslo.config.cfg.ConfigOpts) – oslo.config config instance
  • sqlite_fk (bool) – enable foreign keys in SQLite
  • autocommit (bool) – use autocommit mode for created Session instances
  • expire_on_commit (bool) – expire session objects on commit
get_engine(use_slave=False)

Get the engine instance (note, that it’s shared).

Parameters:use_slave (bool) – if possible, use ‘slave’ database for this engine. If the connection string for the slave database wasn’t provided, ‘master’ engine will be returned. (defaults to False)
get_session(use_slave=False, **kwargs)

Get a Session instance.

Parameters:use_slave (bool) – if possible, use ‘slave’ database connection for this session. If the connection string for the slave database wasn’t provided, a session bound to the ‘master’ engine will be returned. (defaults to False)

Keyword arugments will be passed to a sessionmaker instance as is (if passed, they will override the ones used when the sessionmaker instance was created). See SQLAlchemy Session docs for details.

class oslo.db.sqlalchemy.session.Query(entities, session=None)

Bases: sqlalchemy.orm.query.Query

Subclass of sqlalchemy.query with soft_delete() method.

soft_delete(synchronize_session='evaluate')
class oslo.db.sqlalchemy.session.Session(bind=None, autoflush=True, expire_on_commit=True, _enable_transaction_accounting=True, autocommit=False, twophase=False, weak_identity_map=True, binds=None, extension=None, info=None, query_cls=<class 'sqlalchemy.orm.query.Query'>)

Bases: sqlalchemy.orm.session.Session

Custom Session class to avoid SqlAlchemy Session monkey patching.

oslo.db.sqlalchemy.session.create_engine(sql_connection, sqlite_fk=False, mysql_sql_mode=None, idle_timeout=3600, connection_debug=0, max_pool_size=None, max_overflow=None, pool_timeout=None, sqlite_synchronous=True, connection_trace=False, max_retries=10, retry_interval=10, thread_checkin=True)

Return a new SQLAlchemy engine.

oslo.db.sqlalchemy.session.get_maker(engine, autocommit=True, expire_on_commit=False)

Return a SQLAlchemy sessionmaker using the given engine.

oslo.db.sqlalchemy.session.select(columns=None, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, **kwargs)

Construct a new Select.

Similar functionality is also available via the FromClause.select() method on any FromClause.

All arguments which accept ClauseElement arguments also accept string arguments, which will be converted as appropriate into either text() or literal_column() constructs.

See also

coretutorial_selecting - Core Tutorial description of select().

Parameters:
  • columns

    A list of ClauseElement objects, typically ColumnElement objects or subclasses, which will form the columns clause of the resulting statement. For all members which are instances of Selectable, the individual ColumnElement members of the Selectable will be added individually to the columns clause. For example, specifying a Table instance will result in all the contained Column objects within to be added to the columns clause.

    This argument is not present on the form of select() available on Table.

  • whereclause – A ClauseElement expression which will be used to form the WHERE clause.
  • from_obj – A list of ClauseElement objects which will be added to the FROM clause of the resulting statement. Note that “from” objects are automatically located within the columns and whereclause ClauseElements. Use this parameter to explicitly specify “from” objects which are not automatically locatable. This could include Table objects that aren’t otherwise present, or Join objects whose presence will supersede that of the Table objects already located in the other clauses.
  • autocommit – Deprecated. Use .execution_options(autocommit=<True|False>) to set the autocommit option.
  • bind=None – an Engine or Connection instance to which the resulting Select object will be bound. The Select object will otherwise automatically bind to whatever Connectable instances can be located within its contained ClauseElement members.
  • correlate=True – indicates that this Select object should have its contained FromClause elements “correlated” to an enclosing Select object. This means that any ClauseElement instance within the “froms” collection of this Select which is also present in the “froms” collection of an enclosing select will not be rendered in the FROM clause of this select statement.
  • distinct=False

    when True, applies a DISTINCT qualifier to the columns clause of the resulting statement.

    The boolean argument may also be a column expression or list of column expressions - this is a special calling form which is understood by the Postgresql dialect to render the DISTINCT ON (<columns>) syntax.

    distinct is also available via the distinct() generative method.

  • for_update=False
    when True, applies FOR UPDATE to the end of the resulting statement.

    Deprecated since version 0.9.0: - use GenerativeSelect.with_for_update() to specify the structure of the FOR UPDATE clause.

    for_update accepts various string values interpreted by specific backends, including:

    • "read" - on MySQL, translates to LOCK IN SHARE MODE; on Postgresql, translates to FOR SHARE.
    • "nowait" - on Postgresql and Oracle, translates to FOR UPDATE NOWAIT.
    • "read_nowait" - on Postgresql, translates to FOR SHARE NOWAIT.

    See also

    GenerativeSelect.with_for_update() - improved API for specifying the FOR UPDATE clause.

  • group_by – a list of ClauseElement objects which will comprise the GROUP BY clause of the resulting select.
  • having – a ClauseElement that will comprise the HAVING clause of the resulting select when GROUP BY is used.
  • limit=None – a numerical value which usually compiles to a LIMIT expression in the resulting select. Databases that don’t support LIMIT will attempt to provide similar functionality.
  • offset=None – a numeric value which usually compiles to an OFFSET expression in the resulting select. Databases that don’t support OFFSET will attempt to provide similar functionality.
  • order_by – a scalar or list of ClauseElement objects which will comprise the ORDER BY clause of the resulting select.
  • use_labels=False

    when True, the statement will be generated using labels for each column in the columns clause, which qualify each column with its parent table’s (or aliases) name so that name conflicts between columns in different tables don’t occur. The format of the label is <tablename>_<column>. The “c” collection of the resulting Select object will use these names as well for targeting column members.

    use_labels is also available via the apply_labels() generative method.

Previous topic

oslo.db.sqlalchemy.provision

Next topic

oslo.db.sqlalchemy.test_base

This Page