Using BEGIN TRANSACTION / ROLLBACK / COMMIT over various cursors / connections

Question

I'm wondering how Transactions behave when the various commands (Begin/Start Transaction, Commit etc) are given over various cursors / connections. I.e., which of the following sets of statements actually introduce a single transaction and commit it at the end?

connection = pyodbc.connect(...)
cursor = connection.cursor()
cursor.execute('START TRANSACTION')
cursor.execute('INSERT ....')
cursor.execute('COMMIT')

vs.

connection = pyodbc.connect(...)
connection.cursor().execute('START TRANSACTION')
connection.cursor().execute('INSERT ....')
connection.cursor().execute('COMMIT')

vs.

pyodbc.connect(...).cursor().execute('START TRANSACTION')
pyodbc.connect(...).cursor().execute('INSERT ....')
pyodbc.connect(...).cursor().execute('COMMIT')

(In practice, these commands are dispersed over my code, and I'm trying to figure out on which levels to introduce singletons)

Of course, I can find it out to some degree by "trying", but I prefer a somewhat more authorative answer so I know stuff won't break a week from now.

I'm using Python's Database API, though I suppose this question is not necessarily python specific. I can imagine (though I do hope for the opposite) that the question is DB-specific. For what it's worth: we're using MsSQL Server 2000.

Answer 1

The thing I came up with so far is to dance around my own question by using Python's Database API's transaction methods on connection as opposed to creating cursors for them. I haven't tested it yet in a thorough manner, will posts answers here as soon as I do that.

I.e.

connection = pyodbc.connect(...)
connection.begin() # superfluous, but for illustration purposes;
cursor = connection.cursor()
cursor.execute('INSERT ....')
connection.commit() # or rollback    

Answer 2

I am not sure about pyodbc-- i would imagine it depends on which specific database engine you're connecting to. Unfortunately, it seems like a lot of modules that implement the DB API 2.0 don't specify in their documentation whether transactions are specific to connections or cursors, nor does the specification of the DB API 2.0 itself (http://legacy.python.org/dev/peps/pep-0249/)

However, there are several databases where transactions include all statements executed by all cursors on a single connection (meaning both your first and second examples will work). For example the psycopg2 documentation explicitly states the following:

[D]atabase commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection.

(under "Transactions control" in http://initd.org/psycopg/docs/usage.html)

Similarly, MySQL doesn't support cursors-- they are emulated at the Python level in the module-- so by definition a transaction encapsulates a whole connection level, not just a single cursor.