CODEWITHSUNDEEP
pythonpostgresqltransactionssqlalchemyisolation-level
Mahmoud Abdelkader
Mahmoud Abdelkader

Reputation: 24919

How do I set the transaction isolation level in SQLAlchemy for PostgreSQL?

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)

Upvotes: 19

Views: 25652

Answers (3)

funnydman
funnydman

Reputation: 11326

Indeed, back then there was only the possibility to specify isolation level globally on create_engine. The newest versions of SQLAlchemy allow you to specify it on:

Engine Wide:

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

Individual Sessions:

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

Individual Transactions:

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()

See the documentation for more details

Upvotes: 4

Mahmoud Abdelkader
Mahmoud Abdelkader

Reputation: 24919

From Michael Bayer, the maintainer of SQLAlchemy:

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.

Upvotes: 16

sayap
sayap

Reputation: 6277

The isolation level is set within a transaction, e.g.

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

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

Upvotes: -5

Related Questions