如何在SQLAlchemy中为PostgreSQL设置事务隔离级别？

Question

我们使用的是SQLAlchemy声明基，我有一个方法，我想为它隔离事务级别。为了解释，有两个进程同时写入数据库，我必须让它们在事务中执行它们的逻辑。默认的事务隔离级别是READ COMMITTED，但我需要能够使用可序列化的隔离级别执行一段代码。

如何使用SQLAlchemy完成此操作？现在，我在我们的模型中基本上有一个方法，它继承了SQLAlchemy的声明性基础，本质上需要事务性地调用它。

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

我这样做是为了使用事务隔离级别调用它，但它并没有达到我期望的效果。隔离级别仍然是从我在postgres日志中看到的读取提交的。有人能帮我找出我做错了什么吗？

我使用的是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)

Answer 1

来自SQLAlchemy的维护者Michael Bayer：

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.

Answer 2

隔离级别是在事务中设置的，例如

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

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