consider supporting DBAPI-level autocommit

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)
