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. > > 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 > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
