add "BEGIN" workaround to pysqlite docs (was: sqlalchemy transactions aren't Consistent with sqlite.)

[Former user]

Sqlalchemy relies on the sqlite3 module for its transactions, it appears.

However, the sqlite3 module does not implicitely begin a transaction until a write operation occurs. This leads to consistency errors in which is assumed to be the same transaction.

I have an sqlite database, with a single table 'Test': CREATE TABLE test ( a integer primary key );

Now, I have a test script, with this at the core: (s is a session) print(s.query(test).all()) time.sleep(30) print(s.query(test).all())

Since I did not commit or rollback the transaction, both queries should return the same data.

However, if I modify the database in another process during the 30 second sleep, I get different results.

Here is the log: 2011-07-12 17:32:11,909 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2011-07-12 17:32:11,909 INFO sqlalchemy.engine.base.Engine SELECT test.a AS test_a FROM test 2011-07-12 17:32:11,910 INFO sqlalchemy.engine.base.Engine () (2,), (3,), (7,), (23,) 2011-07-12 17:32:41,939 INFO sqlalchemy.engine.base.Engine SELECT test.a AS test_a FROM test 2011-07-12 17:32:41,939 INFO sqlalchemy.engine.base.Engine () (2,), (3,), (7,), (23,), (43,)

You will note that without an intervening COMMIT or ROLLBACK, I get different data from the selects. (I added the 43 in another process).

I believe the sqlite dialect should implement do_begin and manually handle transaction isolation, and not rely on the sqlite3 module to handle transactions, since it will wait until UPDATE/DELETE/INSERT to start the transaction.

[Former user]

Test script.

[Mike Bayer]

Thanks for your report and test case. This is a well known issue with Pysqlite which we'd like them to fix, and a very easy workaround in the meantime is below.

The primary history of this issue as regards SQLAlchemy can be viewed at:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/2f47e28c1fcdf9e6/0ef1666759ce0724#0ef1666759ce0724

This is a well-known Pysqlite bug, it's been accepted by the maintainer and I would urge you to contact the maintainers and argue/lobby vociferously that this long-standing issue (with patch attached, no less) finally be addressed:

http://code.google.com/p/pysqlite/issues/detail?id=21

It is also on the Python bugtracker - vote, comment, lobby vociferously:

http://bugs.python.org/issue9924

DBAPI has no begin() method, leaving transaction management to the DBAPI itself. SQLAlchemy defers to this feature in all cases, and it would be inappropriate for SQLAlchemy to replace pysqlite's system by default, which can only be accomplished by emitting the word "BEGIN" on the connection, note that usually DBAPI's commit() and rollback() methods handle these tasks transparently. We would very quickly have lots of users complaining that their SQLite databases are locking like crazy, so Pysqlite's default behavior here is not without merit - the issue on the tracker suggests that this be an optional behavior.

Emitting "BEGIN" directly also starts to cause problems if the isolation_level parameter of Pysqlite is changed, which is just further reason that it would be preferable for Pysqlite to deal with the details of transactional behavior.

As a workaround, you can easily establish the "BEGIN" event yourself:

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "begin")
def do_begin(conn):
    conn.execute("BEGIN")

To limit the event to only SQLite:

@event.listens_for(Engine, "begin")
def do_begin(conn):
    if conn.engine.name == 'sqlite':
        conn.execute("BEGIN")

[Mike Bayer]

I will add this workaround to http://www.sqlalchemy.org/docs/dialects/sqlite.html#module-sqlalchemy.dialects.sqlite.pysqlite

[Former user]

Thank you for response. This workaround will probably work for us.

Is there any way to see the connection's connect options (ie, the isolation_level connect option), so I can do something like: if conn.(...).connection_options'isolation_level' == 'IMMEDIATE': conn.execute("BEGIN IMMEDIATE") else: conn.execute("BEGIN")

?

If not, I can set this elsewhere (since now SQLA will be managing all the transactions)

[Mike Bayer]

you'd be best off checking the DBAPI connection itself, its ".connection" on the SQLA Connection object (the one passed to the event)

[Mike Bayer]

bd4c1dd38c503df467e9297dfc6dcfa35b9df9c3

[Mike Bayer]

Removing milestone: 0.7.4 (automated comment)