> I've placed an initial patch for this
> inhttp://www.sqlalchemy.org/trac/ticket/2001.
Thanks for this!
It seems you are using the existing code, which means it will use the
"SET SESSION CHARACTERISTICS..." type of code. Correct? ie: not at
the transaction level still.
I can't tell from the diff in Trac if this will be directly settable
when instantiating a Session, or whether you need to get a hold of the
connection directly. Also from the PostgreSQL docs [1] it isn't clear
to me if this isolation setting form applies to the current session
that has begun, or only to subsequent sessions.
> 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).
This is interesting. I don't quite understand the DBAPI limitation,
though... can't you just emit transaction specific SQL directly?
Since I'm mainly looking for transaction-level isolation adjustments,
I hope that any automatic setting the DBAPI is doing is only at
connection creation and that transaction-level adjustments can still
be made. I had pretty much resolved to first use the isolation_level
argument to create_engine() to set my overall default, and then simply
override the isolation per transaction with engine/dialect dependent
direct SQL statements in my application layer. eg: something like
this:
def SetSerialIsolation(SessionObj):
global EngineType
if EngineType in (POSTGRES, SQLSERVER, ORACLE):
sess.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
else:
#other DBMS syntax/compatibility not checked yet
pass
Are there any problems people can foresee with this? It obviously
makes more sense to put this at a lower level, but I don't yet know
how to do customize SQLA dialects yet (or even a good way to extract
the engine type from the Session instance for hacks like this).
One problem I see is that the transaction needs to have already been
started with "BEGIN" (or whatever), otherwise the command does
nothing. But SQLA doesn't emit a BEGIN until you do a SELECT/UPDATE/
etc, after which you can't change the isolation level...
What would be really nice is a parameter for Session() that would, for
dialects that support it (it has been in the SQL standard since '92 it
seems [2]), set the isolation level the session should use and queue
up a "SET TRANSACTION ISOLATION LEVEL ..." command to be emitted
immediately after the decision is made to emit the "BEGIN" that is
relevant for the dialect. For non-standard implementations like
sqlite some interpretation would obviously be needed, though.
Russ
[1] http://www.postgresql.org/docs/9.0/interactive/sql-set-transaction.html
[2] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
