How do I set the transaction isolation level in SQLAlchemy for PostgreSQL?

Question

We're using SQLAlchemy declarative base and I have a method that I want isolate the transaction level for. To explain, there are two processes concurrently writing to the database and I must have them execute their logic in a transaction. The default transaction isolation level is READ COMMITTED, but I need to be able to execute a piece of code using SERIALIZABLE isolation levels.

How is this done using SQLAlchemy? Right now, I basically have a method in our model, which inherits from SQLAlchemy's declarative base, that essentially needs to be transactionally invoked.

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE

class OurClass(SQLAlchemyBaseModel):

    @classmethod
    def set_isolation_level(cls, level=ISOLATION_LEVEL_SERIALIZABLE):
        cls.get_engine().connect().connection.set_isolation_level(level)

    @classmethod
    def find_or_create(cls, **kwargs):
        try:
            return cls.query().filter_by(**kwargs).one()
        except NoResultFound:
            x = cls(**kwargs)
            x.save()
            return x

I am doing this to invoke this using a transaction isolation level, but it's not doing what I expect. The isolation level still is READ COMMITTED from what I see in the postgres logs. Can someone help identify what I'm doing anythign wrong?

I'm using SQLAlchemy 0.5.5

class Foo(OurClass):

    def insert_this(self, kwarg1=value1):
        # I am trying to set the isolation level to SERIALIZABLE
        try:
            self.set_isolation_level()
            with Session.begin():
                self.find_or_create(kwarg1=value1)
        except Exception:  # if any exception is thrown...
            print "I caught an expection."
            print sys.exc_info()
        finally:
            # Make the isolation level back to READ COMMITTED
            self.set_isolation_level(ISOLATION_LEVEL_READ_COMMITTED)

Periwig

Answer 1

From Michael Bayer, the maintainer of SQLAlchemy:

Please use the "isolation_level" argument to create_engine() and use the latest tip of SQLAlchemy until 0.6.4 is released, as there was a psycopg2-specific bug fixed recently regarding isolation level.

The approach you have below does not affect the same connection which is later used for querying - you'd instead use a PoolListener that sets up set_isolation_level on all connections as they are created.

Periwig

It seems that the isolation_level argument to create_engine() only affects the main connection manager so you get that isolation level on each and every connection. Did you figure out a connection-pooling-compatible way to achieve this on a per session/connection basis? Your original question seemed like you only wanted it on a certain method. – Liturgical

Answer 2

Indeed, back then there was only the possibility to specify isolation level globally on create_engine. The newest versions of SQLAlchemy allow you to specify it on:

Engine Wide:

eng = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/test",
    isolation_level="REPEATABLE READ",
)

Individual Sessions:

plain_engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

autocommit_engine = plain_engine.execution_options(isolation_level="AUTOCOMMIT")

# will normally use plain_engine
Session = sessionmaker(plain_engine)

# make a specific Session that will use the "autocommit" engine
with Session(bind=autocommit_engine) as session:
    # work with session
      ...

Individual Transactions:

sess = Session(bind=engine)

sess.connection(execution_options={"isolation_level": "SERIALIZABLE"})

# ... work with session in SERIALIZABLE isolation level...

# commit transaction.  the connection is released
# and reverted to its previous isolation level.
sess.commit()

See the documentation for more details

Bernabernadene

Answer 3

The isolation level is set within a transaction, e.g.

try:
    Session.begin()
    Session.execute('set transaction isolation level serializable')
    self.find_or_create(kwarg1=value1)
except:
    ...

From PostgreSQL doc:

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it will appear to have no effect, since the transaction will immediately end.

Befoul

I'll test your answer tomorrow and accept this if it works :) I have a feeling it will. – Periwig

Your answer does not work. According to Michael Bayer, it looks like transaction isolation level isn't affecting the same connection which is later used for querying. – Periwig

I believe Michael Bayer's comment is about your code, not mine. The difference is that yours set the isolation level before starting the transaction. Have you really tried the code before claiming it doesn't work? – Befoul

Yes, it didn't work. The problem is, when I execute it within a Session, the query will not be within the same transaction. – Periwig