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

Question

我们正在使用SQLAlchemy的声明性基础，并且我有一个方法想要隔离事务级别。简单来说，有两个进程同时在写入数据库，我必须让它们在一个事务中执行它们的逻辑。默认的事务隔离级别是“已提交读”，但我需要能够使用“可串行化”隔离级别来执行一段代码。

那么，如何在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

隔离级别是在一个事务中设置的，比如：

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

来自 PostgreSQL 文档：

如果在没有先执行 START TRANSACTION 或 BEGIN 的情况下执行 SET TRANSACTION，这个命令看起来就没有效果，因为事务会立即结束。

Answer 2

确实，在以前，我们只能在 create_engine 时全局设置隔离级别。现在最新版本的 SQLAlchemy 允许你在以下几个地方设置：

整个引擎：

eng = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/test",
    isolation_level="REPEATABLE READ",
)

单独的会话：

plain_engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

autocommit_engine = plain_engine.execution_options(isolation_level="AUTOCOMMIT")

# will normally use plain_engine
Session = sessionmaker(plain_engine)

# make a specific Session that will use the "autocommit" engine
with Session(bind=autocommit_engine) as session:
    # work with session
      ...

单独的事务：

sess = Session(bind=engine)

sess.connection(execution_options={"isolation_level": "SERIALIZABLE"})

# ... work with session in SERIALIZABLE isolation level...

# commit transaction.  the connection is released
# and reverted to its previous isolation level.
sess.commit()

想了解更多细节，可以查看 文档

Answer 3

这是来自SQLAlchemy维护者Michael Bayer的建议：

请在使用create_engine()时，使用“isolation_level”这个参数。同时，建议你使用最新版本的SQLAlchemy，直到0.6.4版本发布，因为最近修复了一个与psycopg2相关的关于隔离级别的bug。

你下面提到的方法不会影响到后面用于查询的同一个连接。你应该使用一个PoolListener，这样在创建每个连接时都能设置set_isolation_level。