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
--
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.
