SQLAlchemy session and connection relationship

Question

Do queries executed with the same SQLAlchemy session object use the same underlying connection? If not, is there a way to ensure this?

Some background: I have a need to use MySQL's named lock feature, i.e. GET_LOCK() and RELEASE_LOCK() functions. As far as the MySQL server is concerned, only the connection that obtained the lock can release it - so I have to make sure that I either execute these two commands within the same connection or the connection dies to ensure the lock is released.

To make things nicer, I have created a "locked" context like so:

@contextmanager
def mysql_named_lock(session, name, timeout):
    """Get a named mysql lock on a session
    """
    lock = session.execute("SELECT GET_LOCK(:name, :timeout)",
                           name=name, timeout=timeout).scalar()
    if lock:
        try:
            yield session
        finally:
            session.execute("SELECT RELEASE_LOCK(:name)", name=name)
    else:
        e = "Count not obtain named lock {} within {} sections".format(
            name, timeout)
        raise RuntimeError(e)

def my_critical_section(session):
    with mysql_named_lock(session, __name__, 10) as lockedsession:
        thing = lockedsession.query(MyStuff).one()
    return thing

I want to make sure that the two execute calls in mysql_named_lock happen on the same underlying connection or the connection is closed.

Can I assume this would "just work" or is there anything I need to be aware of here?

Answer 1

it will "just work" if (a) your session is a scoped_session and (b) you are using it in a non-concurrent fashion (same pid / thread). If you're too paranoid, make sure (assert) you're using the same connection ID via

session.connection().connection.thread_id()

also, there is no point to pass session as an argument. Init it once, somewhere in your application’s global scope, then call anywhere in a code, you will get the same connection ID.