Best way to use SERIALIZABLE READ ONLY DEFERRABLE transaction with the ORM?

[Donald Stufft]

I am currently using the SQLAlchemy ORM and I'd like to be able to start up transactions using PostgreSQL's SERIALIZABLE READ ONLY DEFERRABLE isolation level. I don't want every single transaction to use this isolation level nor do I have the ability to change the server configuration so I need to do it in my application. My question is, what is the best way to go about achieving this? I tried setting the execution_option to "SERIALIZABLE READ ONLY DEFERRABLE" but that fails with the error message of "Valid isolation levels for postgresql are REPEATABLE READ, AUTOCOMMIT, SERIALIZABLE, READ COMMITTED, READ UNCOMMITTED".

[Mike Bayer]

so the built-in way to do one-offs like these w/ an ORM session is like
this:


conn = session.connection()
conn.detach()
conn.execute("set session isolation <whatever>")

< work with connection>


what detach() does means it's going to throw away the DBAPI connection
when we're done with it, it won't be used again. This might be all you
need, or then you might need to do this a lot and don't want to degrade
the efficiency of the connection pool.

The other way public-API wise is to use events, e.g. like checkin
events, to reset the isolation level when the connection comes back to
the pool. There's some internal systems that do this also which I'm
sure you've noticed, poking around at them I think the idea of a way to
add custom ad-hoc isolation settings which get reset by the usual
"reset_isolation_level" hook might be handy. I'm still underwater
with work meetings all week this week so if you can ping me w/ the code
you were working on towards the end of the week maybe we can look at
that possibility.




>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Tuesday, November 17, 2015 at 9:14:51 AM UTC-5, Michael Bayer wrote:

conn = session.connection()
conn.detach()
conn.execute("set session isolation <whatever>")

< work with connection>

I use this strategy, but without `detatch`.  I do this with SqlAlchemy in a webapp context, so I have a "setup" and cleanup hook.