Coincidentally I've been dealing with issue lately as I've tried to
further integrate sqlalchemy into a legacy code base and replace
our existing database abstraction.
In MySQL at least, there is a concept of "session" isolation level and
"transaction" isolation level. Meaning you can set the isolation
level for just a specific transaction, as well as the default
isolation level for the whole connection.
Essentially I need to be able to do something like:
with session.begin(isolation_level=LEVEL_SERIALIZABLE):
... do stuff ...
Unfortunately there seems to be no way to pass any additional
arguments into the dialect to get this behavior. I've
been hacking around it by basically having a custom Session class that
just assumes it's using MySQL only and issues extra
SQL to set (and unset) the isolation level at transaction boundaries.
This approach is of course completely impossible to pass upstream.
Rhett
On Tue, Dec 14, 2010 at 8:21 AM, Michael Bayer <mike...@zzzcomputing.com> wrote:
> There's really two concepts of "isolation level" at play.
>
> The "isolation_level" parameter in SQLA is usually linked to
> database-specific SQL commands that we issue, in the case of PG its "SET
> SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL", in sqlite its
> "PRAMGA read_committed".
>
> But the psycopg2 and pysqlite DBAPIs also support ways to modify the
> isolation characteristics via connection-specific commands, which also change
> the DBAPIs behavior a bit. In sqlite its the "autocommit" flag, and
> psycopg2 its the set_isolation_level() method. In the latter case, we have
> to use psycopg2's API since psycopg2 itself works against the
> connection-level settings if it hasn't been told about them (I think it
> resets them on each command).
>
> On a per-connection basis, the difficulty there has always been that we
> didn't have a nice extensible API with which to apply modifiers to Connection
> objects. As of 0.6 we do, known as execution_options(), and isolation levels
> are exactly the kind of function that would be available via
> execution_options(). So at this point its an unimplemented feature with a
> fairly clear path. I've placed an initial patch for this in
> http://www.sqlalchemy.org/trac/ticket/2001 .
>
>
>
>
>
> On Dec 13, 2010, at 10:10 PM, Russell Warren wrote:
>
>>> Do other dialects support this, but just not indicate it in the
>>> documentation?
>>
>> On further investigation I see this old ticket:
>>
>> http://www.sqlalchemy.org/trac/ticket/443
>>
>> ... which implies it is, in fact, only for PostgreSQL and Sqlite at
>> the moment.
>>
>> I'm also quite interested in setting the isolation level on a per
>> session/connection basis. However - it seems like the isolation_level
>> argument to create_engine() sets the isolation level for all managed
>> connections. I recognize that there may (at least) be some issues
>> with this based on the connection pooling scheme in use, but is there
>> a way (even a hack) to do a one-time set of the isolation level to
>> SERIALIZABLE (for example) for a created session?
>>
>> This thread makes it seem like per-session/connection isolation
>> setting is not as simple as it might seem at first...
>> http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/653b6b2f49a940b8
>>
>> And this StackOverflow question makes it seem like it isn't easy to
>> set the isolation level per session...
>> http://bit.ly/eG5DUl
>>
>> Still digging...
>>
>> Russ
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
