Setting the isolation level seems to have no effect

[charlax]

Hi,

I'm using sqlalchemy version 0.7.8 and MySQL Ver 14.14 Distrib 5.5.25, for osx10.6 (i386) using readline 5.1. All my tables are using InnoDB.

I'm trying to change the isolation_level, following http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html

    engine = create_engine(

        '[redacted]',

        echo=True,

        isolation_level="READ UNCOMMITTED"

    )

    connection = engine.connect()

    # Twice to make sure ...

    connection.execution_options(isolation_level="READ UNCOMMITTED")

    # Begin a non-ORM transaction

    trans = connection.begin()

    # Bind the session to the connection

    Session.configure(bind=connection)

    from model.toaster import Toaster

    t = Toaster()

    Session.add(t)

    Session.commit()

It does not seem to be working, I can't query object created in this transaction from another transaction. I put "READ UNCOMMITTED" twice for testing.

Here is the echo output:

    SELECT DATABASE()

    ()

    SHOW VARIABLES LIKE 'character_set%%'

    ()

    SHOW VARIABLES LIKE 'lower_case_table_names'

    ()

    SHOW COLLATION

    ()

    SHOW VARIABLES LIKE 'sql_mode'

    ()

    BEGIN (implicit)

    INSERT INTO toaster (created_at) VALUES (UTC_TIMESTAMP)

    (...)

I guess I should see "SET SESSION TRANSACTION ISOLATION LEVEL <level>" but it does not appear.

Thanks for your help!

Chx

[Michael Bayer]

the isolation level is set on a cursor that's not within the usual "logged" stream in this case.   But you can see what it's set to by selecting these variables:

from sqlalchemy import create_engine

e = create_engine("mysql://scott:tiger@localhost/test", echo=True, isolation_level="READ UNCOMMITTED")

c = e.connect()

print c.execute("SELECT @@GLOBAL.tx_isolation, @@tx_isolation;").fetchall()

for me that returns:

[('REPEATABLE-READ', 'READ-UNCOMMITTED')]

as far as the rows being readable, I've never been one to try to anticipate the exact effects of isolation levels like that, usually I need to open up two consoles and do the equivalent operation manually to see which transaction sees what.    

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/J2Ww_oAfMs0J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[charlax]

Thanks!

The reason I was asking this is that I can't see the effect of this setting. I'm using Sequel Pro and I get the same result as you in sqlalchemy. I forgot that you needed to also set the transaction isolation setting in Sequel, or to set it globally, which I did:

        connection.execute("SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;")

Thanks a lot Michael!

Charles