Transaction Isolation Level
Tomasz Nazar
What (and how) is the option of setting custom transaction isolation
level for a particular transaction in SQLAlchemy?
I see this ticket, and can't believe: http://www.sqlalchemy.org/trac/ticket/443
don't tell you don't have this.. ?
As a user I expect:
* simple way of setting one of four standard isolation levels per transaction
* setting of default isolation level per all transactions
* see how Hibernate/Spring does it
For now I am fairly new to Python/Pylons/dbapi, can you give a quick
tip how to specify isolation levels by hand?
Tomasz
--
_i______'simplicity_is_the_key'__________tomasz_nazar
_ii____'i_am_concern_oriented'________________JKM-UPR
_iii__'patsystem.sf.net'___________________linux_user
_'aspectized.com'___________________________prevayler
Michael Bayer
On Sep 26, 6:49 pm, "Tomasz Nazar" <tomasz.na...@gmail.com> wrote:
> Hi there!
>
> What (and how) is the option of setting custom transaction isolation
> level for a particular transaction in SQLAlchemy?
>
> I see this ticket, and can't believe:http://www.sqlalchemy.org/trac/ticket/443
> don't tell you don't have this.. ?
>
> As a user I expect:
> * simple way of setting one of four standard isolation levels per transaction
> * setting of default isolation level per all transactions
> * see how Hibernate/Spring does it
Unfortuantely, Hibernate/Spring aren't of much help here since they
are not based on the DBAPI, which has no transaction isolation API;
they (specifically hibernate) use JDBC which has a well-published and
very narrowly defined method of setting transaction isolation.
Additionally, I'm not aware of any way to set transaction isolation
with either spring or hibernate on a per-session basis; both can only
set transaction isolation on a per-transaction-manager basis, which
SQLAlchemy can achieve as well (on a per-engine basis) by providing a
custom connect function (assuming the DBAPI supports it). Setting it
per-connection with hibernate isn't really possible; you'd have to
drop down to individual JDBC connections. So the trac ticket is in
fact pointing to a feature that hibernate doesn't have at all, that is
to be able to set the isolation level per connection in a DBAPI-
agnostic way.
Like the ticket says, only psycopg2 offers anything remotely like the
"four standard isolation levels" (though in fact postgres i think has
only three of them) at least at the DBAPI level. MySQLDB does not,
cx_oracle does not, and pysqlite has its three isolation levels of its
own which don't exactly map to the "standard" levels. So the API here
could not be based on four simple constants and may very well need to
be database-specific.
Right now, people who need transaction isolation management simply
procure the underlying connection from the Connection class (i.e.
conn.connection), and set it using the methods available for that
DBAPI. To set isolation level for an engine in general is achieved
using a custom connect function:
def connect():
conn = pysqlite.connect(args)
conn.isolation_level = "IMMEDIATE"
engine = create_engine(creator=connect)
So the widely varied availability of transaction isolation settings in
DBAPI as well as the fact that sqlalchemy already allows raw access to
these settings right now is why this ticket is not very "hot", but
certainly with proper contribution it can be completed very quickly.
To build a generic version of transaction isolation, or at least a
nicer API for it, requires information on how cx_oracle, MS-SQL,
MySQLDB (and its various backends) can affect transaction isolation; I
work with cx_oracle and MySQLDB and neither have any DBAPI-level way
of setting transaction isolation. There's probably commands which
need to be sent over the wire as SQL strings in order to set the
levels. In the case of MySQL, the backend in use will completely
change what isolation modes are available, if any. As far as MS-SQL I
know that it has a more traditional notion of isolation but I'm not
familiar with what APIs are available within our three supported
DBAPIs to do it (perhaps our MS-SQL maintainers have more info on
this).
SQLite has its own isolation levels DEFERRED, IMMEDIATE, EXCLUSIVE
which I'm not sure how they'd map to the "standard" modes, if at all.
Some thought on how this might work would also be helpful.
As it stands now, the API I'd have in mind would not map to four
constants and would instead use database-specific options which can be
set on a per-connection or per-engine level, thereby granting maximum
control over isolation (and also freeing us from having to strangle
each DBAPI/database backend into a rigid set of modes which they were
not designed for); even the same "isolation" mode on one database
versus another has a different implication for behavior (since every
DB does isolation and locking slightly differently, etc.) so i think
this setting is always going to be made on a vendor-specific basis
anyway. I have experience with JDBC's settings and they are anything
but "vendor-neutral" - SERIALIZABLE means something very different on
MS-SQL versus postgres (the former locking in a much more coarse-
grained way than the latter).
The architecture is certainly in place to support this and its not a
lot of work, it would probably look along the lines of
connection.set_options(isolation='repeatable-read'), where the dialect
in use would interpret the meaning of "isolation" if at all (and also
provide a hook to revert the isolation when the connection is
returned...this also might require some guesswork/assumptions if no
default was explicitly specified). The main effort to be done here,
for which we would welcome contributions, is defining how transaction
isolation can be achieved with each individual DBAPI, as well as
defining some way to either "revert" to the "default" isolation level
and/or deciding what the "default" mode should be for each DBAPI,
considering the particular quirks of each database.