consider supporting DBAPI-level autocommit

Issue #1750 resolved
Mike Bayer repo owner created an issue

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)

  1. Mike Bayer reporter

    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.

  2. Mike Bayer reporter

    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".

  3. Log in to comment