Handling transactions in Python DBI module

[Israel Brewster]

On Feb 10, 2016, at 8:06 PM, Frank Millman <frank at chagford.com> wrote:
> 
> "Israel Brewster"  wrote in message news:92D3C964-0323-46EE-B770-B89E7E7E6D36 at ravnalaska.net...
> 
>> I am working on implementing a Python DB API module, and am hoping I can get some help with figuring out the workflow of handling transactions. In my experience (primarily with
>> psycopg2) the workflow goes like this:
>> 
>> - When you open a connection (or is it when you get a cursor? I *think* it is on opening a connection), a new transaction is started
>> - When you close a connection, an implicit ROLLBACK is performed
>> - After issuing SQL statements that modify the database, you call commit() on the CONNECTION object, not the cursor.
>> 
>> My primary confusion is that at least for the DB I am working on, to start/rollback/commit a transaction, you execute the appropriate SQL statement (the c library I'm using doesn't
>> have any transactional commands, not that it should). However, to execute the statement, you need a cursor. So how is this *typically* handled? Does the connection object keep an > internal cursor that it uses to manage transactions?
>> 
>> I'm assuming, since it is called on the connection, not the cursor, that any COMMIT/ROLLBACK commands called affect all cursors on that connection. Is that correct? Or is this DB
>> specific?
>> 
>> Finally, how do other DB API modules, like psycopg2, ensure that ROLLBACK is called if the user never explicitly calls close()?
> 
> Rather than try to answer your questions point-by-point, I will describe the results of some investigations I carried out into this subject a while ago.
> 
> I currently support 3 databases, so I use 3 DB API modules - PostgreSQL/psycopg2, Sql Server/pyodbc, and sqlite3/sqlite3. The following applies specifically to psycopg2, but I applied the lessons learned to the other 2 as well, and have had no issues.
> 
> A connection has 2 possible states - 'in transaction', or 'not in transaction'. When you create the connection it starts off as 'not'.
> 
> When you call cur.execute(), it checks to see what state it is in. If the state is 'not', it silently issues a 'BEGIN TRANSACTION' before executing your statement. This applies for SELECT as well as other statements.
> 
> All subsequent statements form part of the transaction, until you issue either conn.commit() or conn.rollback(). This performs the required action, and resets the state to 'not'.
> 
> I learned the hard way that it is important to use conn.commit() and not cur.execute('commit'). Both succeed in committing, but the second does not reset the state, therefore the next statement does not trigger a 'BEGIN', with possible unfortunate side-effects.

Thanks - that is actually quite helpful. So the way I am looking at it now is that the connection would have an internal cursor as I suggested. From your response, I'll add a "state" flag as well. If the state flag is not set when execute is called on a cursor, the cursor itself will start a transaction and set the flag (this could happen from any cursor, though, so that could potentially cause a race condition, correct?). In any case, there is now a transaction open, until such a time as commit() or rollback() is called on the connection, or close is called, which executes a rollback(), using the connection's internal cursor.

Hopefully that all sounds kosher. 

> 
> HTH
> 
> Frank Millman
> 
> 
> -- 
> https://mail.python.org/mailman/listinfo/python-list