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

[Mahmoud Abdelkader]

NOTE: I originally posted this question to stack overflow, http://stackoverflow.com/questions/3518863/how-do-i-set-the-transaction-isolation-level-in-sqlalchemy-for-postgresql , but I haven't received an answer yet. If I find the answer here, I'll send it over to stackoverflow for thoroughness.

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)

Appreciate the assistance!

Thanks

Mahmoud

[Michael Bayer]

On Aug 19, 2010, at 6:32 PM, Mahmoud Abdelkader wrote:

NOTE: I originally posted this question to stack overflow, http://stackoverflow.com/questions/3518863/how-do-i-set-the-transaction-isolation-level-in-sqlalchemy-for-postgresql , but I haven't received an answer yet. If I find the answer here, I'll send it over to stackoverflow for thoroughness.

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.

Please use the "isolation_level" argument to create_engine() (http://www.sqlalchemy.org/docs/reference/dialects/postgresql.html?highlight=isolation_level#transaction-isolation-level)  and use the latest tip of SQLAlchemy (http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz) 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.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Mahmoud Abdelkader]

Hi Michael, 

Thanks for responding. Will there be any backward incompatibility issues with a SQLAlchemy upgrade to the tip? We're using SQLAlchemy in a rather large project.

Thanks!

Mahmoud

[Michael Bayer]

On Aug 22, 2010, at 10:44 PM, Mahmoud Abdelkader wrote:

Hi Michael, 

Thanks for responding. Will there be any backward incompatibility issues with a SQLAlchemy upgrade to the tip? We're using SQLAlchemy in a rather large project.

Thanks!

Mahmoud

If your app sticks to traditional SQLAlchemy patterns, the upgrade to 0.6 shouldn't have issues - the biggest one could be that you'd have to upgrade your sqlalchemy-migrate if you're on an older version, and I believe they had an API change.    

An overview of SQLAlchemy changes from 0.5 -> 0.6 is at http://www.sqlalchemy.org/trac/wiki/06Migration .

Note that the PoolListener approach will work perfectly fine in 0.5, that's what the "isolation_level" argument in 0.6 uses in any case.   Docs are http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?highlight=poollistener#sqlalchemy.interfaces.PoolListener .