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 On Sat, Aug 21, 2010 at 10:51 AM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > 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 sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > 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 sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com> > . > 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
