On Feb 10, 2016, at 8:06 PM, Frank Millman <fr...@chagford.com> wrote: > > "Israel Brewster" wrote in message > news:92d3c964-0323-46ee-b770-b89e7e7e6...@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 -- https://mail.python.org/mailman/listinfo/python-list
