On Sep 19, 2010, at 9:54 AM, Randall Nortman wrote:
>
> Unfortunately, the isolation_level parameter to pysqlite2 only
> controls the type of BEGIN issued, not when it is issued (I just
> tested to be sure). It still waits until there is an INSERT/UPDATE/
> DELETE to begin the transaction. I expect the semantics of
> serializable transactions to mean the transaction begins also with a
> SELECT. The vast majority of database operations, I'd wager, begin
> with a SELECT.
certainly. The pysqlite connection should be in a transaction immediately.
this is what all the other DBAPIs do.
>
> Call this a bug in pysqlite2 if you like,
I think this is a bug in Pysqlite2. I can reproduce it with sqlite3
directly.
> though the DBAPI spec seems
> to be silent on when transactions are started, so I think the
> developers will defend their choice.
I think you should give it a try, the Pysqlite folks are very on top of things
and really want to do things correctly, and this is a case of them doing
plainly the wrong thing for serializable isolation. SQLA really, really
doesn't want to get into correcting DBAPI behavior. I've attached a test case
for you to post on their tracker at
http://code.google.com/p/pysqlite/issues/list .
> (I don't see where the spec says
> that a DBAPI connection is always in a transaction.)
Well, that you can't/shouldn't call "BEGIN" is implied by this paragraph,
though weakly, but isn't specific about isolation. I also bet if you emailed
the DB-SIG list, they'd be interested in this detail.
.commit()
Commit any pending transaction to the database. Note that
if the database supports an auto-commit feature, this must
be initially off. An interface method may be provided to
turn it back on.
> SA can provide
> the behavior I want simply by implementing do_begin in SQLiteDialect
> as a one-liner: connection.execute("BEGIN").
I'd much rather hear the rationale from the Pysqlite folks why they have
specifically chosen not to do this, and get their advice on what we should be
doing. Your above sentence can be restated as "pysqlite2 can provide the
behavior I want simply by moving auto-begin after any non-DML statement, not
just INSERT/UPDATE/DELETE, a [probable] one-liner". Why SQLA's issue and not
pysqlite's?
SQLAlchemy is heavily constructed around the "implicit BEGIN" paradigm of DBAPI
and I'm not too comfortable making an exception in the case of one DBAPI that
doesn't work like all the others. I tried setting isolation_level=None and
calling "BEGIN", commit(), rollback() explicitly, we get seven failures.
Caling "COMMIT", "ROLLBACK" instead of the connection provided version and
virtually all tests error out, so already strange that conn.commit() is doing
something we don't see - even when isolation_level=None.
Without the isolation_level change, and just calling "BEGIN", things are
"fine", but then again we don't have in depth testing of SQLite's transactional
quirks...because ....its one of the very few things DBAPI allows us not to
worry about ! If pysqilte could document, "hey its OK to call 'BEGIN' to
delineate when the trans should be begin if pysqlite's auto-trans is too late",
or if they could add a "begin()" call, or an option to just call BEGIN as soon
as SELECT is issued, then we are good. If they tell us to call "BEGIN" I'd
probably add this as an option to the engine just to make sure the overall
userbase isn't exposed to surprises (and also that I don't have to write a
crapload of new tests).
> At the very least, I beg you, don't have the echo code output BEGIN if
> no BEGIN is being issued. That had me barking up the wrong tree for
> many hours yesterday. The echo feature is great for troubleshooting,
> but only if it doesn't lie!
Well we like that people are familiar with DBAPI and that it has no begin(), so
with that knowledge it should be clear that SQLA's log message of "BEGIN" only
represents when SQLA first starts using a DBAPI connection that has no existing
transactional state, and that the DBAPI is expected to have done what it needs
to ensure that a BEGIN has occurred. The "BEGIN" message can say something
like "BEGIN (implicit)" or "BEGIN (virtual)", "BEGIN (assumed that the DBAPI is
doing this!)" but you can be sure most people aren't going to know what the
heck that means and you'll just have more people complaining.
I will gladly add documentation verbiage clarifying this where you think its
appropriate. Most likely on this page
http://www.sqlalchemy.org/docs/core/connections.html . If we are adding
options to pysqlite then that's more verbiage that will be present in any case.
import sqlite3
import os
if os.path.exists("file.db"):
os.unlink("file.db")
conn1 = sqlite3.connect("file.db")
c1 = conn1.cursor()
c1.execute("PRAGMA read_uncommitted=SERIALIZABLE")
c1.execute("""create table foo (id integer primary key, data varchar(30))""")
c1.execute("insert into foo(id, data) values (1, 'data1')")
c1.close()
conn1.commit()
c1 = conn1.cursor()
c1.execute("select * from foo where id=1")
row1 = c1.fetchone()
c1.close()
conn2 = sqlite3.connect("file.db")
c2 = conn2.cursor()
c2.execute("PRAGMA read_uncommitted=SERIALIZABLE")
# sqlite3 should be doing this automatically.
# when called, conn1's commit blocks
#c2.execute("BEGIN")
c2.execute("select * from foo where id=1")
row2 = c2.fetchone()
c2.close()
c1 = conn1.cursor()
c1.execute("update foo set data='data2'")
print "About to commit conn1..."
conn1.commit()
--
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.
