consider supporting DBAPI-level autocommit
Issue #1750
resolved
if turning autocommit on and off is cheaper than unconditional rollback, we should consider it for those DBAPIs where this is the case:
import time
def timeit(description, fn, count, *args, **kw):
now= time.time()
for i in xrange(count):
fn(*args, **kw)
total = time.time() - now
perop = total / count
print "%s: Time: %f Per Operation: %f" % (description, total, perop)
return total
def test(description, conn):
def exec_with_rollback():
cursor = conn.cursor()
cursor.execute("select 1")
cursor.fetchall()
cursor.close()
conn.rollback()
def exec_without_rollback():
cursor = conn.cursor()
cursor.execute("select 1")
cursor.fetchall()
cursor.close()
rollback_total = timeit("%s execute with rollback" % description, exec_with_rollback, 10000)
norollback_total = timeit("%s execute without rollback" % description, exec_without_rollback, 10000)
print "On %s, the rollback takes up %f%% of the total time." % (description, ((rollback_total - norollback_total) / rollback_total) * 100)
print ""
import MySQLdb
test("MySQL db",
MySQLdb.connect(user='scott', passwd='tiger', host='localhost', db='test')
)
import oursql
test("OurSQL",
oursql.connect(user='scott', passwd='tiger', host='localhost', db='test')
)
import psycopg2
test(
"Psycopg2",
psycopg2.connect(user='scott', password='tiger', host='localhost', database='test')
)
MySQL db execute with rollback: Time: 1.939238 Per Operation: 0.000194
MySQL db execute without rollback: Time: 1.415616 Per Operation: 0.000142
On MySQL db, the rollback takes up 27.001422% of the total time.
OurSQL execute with rollback: Time: 3.128836 Per Operation: 0.000313
OurSQL execute without rollback: Time: 2.642131 Per Operation: 0.000264
On OurSQL, the rollback takes up 15.555460% of the total time.
Psycopg2 execute with rollback: Time: 3.543528 Per Operation: 0.000354
Psycopg2 execute without rollback: Time: 1.302341 Per Operation: 0.000130
On Psycopg2, the rollback takes up 63.247336% of the total time.
Comments (3)
-
reporter -
reporter - changed milestone to blue sky
this idea is deferred for now.
-
reporter - changed status to wontfix
as a generalized solution we won't be doing this. we are starting to support enabling of autocommit per-dialect using execution options, and that's fine, though this is always in the realm of "the user wants to do this explicitly".
- Log in to comment
the attached patch is innocent enough, but of course when the transactional tests are run it deadlocks all over the place (though plain query tests pass). It's not clear from psycopg2's docs what actually goes on when you change the isolation level around.