On Dec 14, 2010, at 1:51 PM, Russell Warren wrote:
>> 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.
for psycopg2 it would call:
from psycopg2 import extensions
psycopg2_connection.set_isolation_level(extensions.ISOLATION_LEVEL_READ_UNCOMMITTED)
That is what's available to us with psycopg2, and any DBAPI at best would have
some similar notion. DBAPI doesn't have a distinct "transaction" object and
only offers a non-autocommiting connection in all cases. Since a connection
only has one transaction at a time you pretty much set this on the DBAPI
connection.
>
> This is interesting. I don't quite understand the DBAPI limitation,
> though... can't you just emit transaction specific SQL directly?
Yes but psycopg2 emits its own isolation level statements on every
cursor.execute() call, canceling out whatever you told it. Run it with PG
statement logging on to see the exact sequence.
>
> 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.
psycopg2 is doing it continuously. Other DBAPIs may do something else. Most
I believe do absolutely nothing, however. But again since there's no
"transaction" object, these settings must be made on the connection, and
reverted when the state is complete.
>
> 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).
The above type of thing is already accounted for in our system of dialects.
It is only implemented for pysqlite and psycopg2 so far, but there's no
technical issue extending it to support oracle, sql server, etc. Particularly
with SQL Server a lot of testing would be needed - messing around with SQL
Servers transactions frequently causes ODBC and DBAPI-related issues. For
example we can't use SQL Server's two-phase commit functionality with pyodbc as
of yet - the settings required confuse pyodbc and it ends up not working.
>
> One problem I see is that the transaction needs to have already been
> started with "BEGIN" (or whatever), otherwise the command does
> nothing.
DBAPI has no "BEGIN". When you get at a connection, "BEGIN" has already been
emitted if required by the underlying database (note some databases are
autocommit=False by default), or the connection is otherwise responsible for
emitting "BEGIN" at the appropriate time. There are bugs in pysqlite related
to this (see http://code.google.com/p/pysqlite/issues/detail?id=21 ).
> But SQLA doesn't emit a BEGIN until you do a SELECT/UPDATE/
> etc, after which you can't change the isolation level...
We don't emit BEGIN since there is no BEGIN in DBAPI. It's implicit in the
driver.
>
> 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.
This is what ticket 2001 proposes.
session.connection().execution_options(isolation_level='foo') and you're good
to go.
--
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.
