Path: csiph.com!fu-berlin.de!uni-berlin.de!not-for-mail
From: Israel Brewster <israel@ravnalaska.net>
Newsgroups: comp.lang.python
Subject: Re: Handling transactions in Python DBI module
Date: Thu, 11 Feb 2016 08:05:31 -0900
Lines: 79
Message-ID: <mailman.53.1455210335.22075.python-list@python.org>
References: <92D3C964-0323-46EE-B770-B89E7E7E6D36@ravnalaska.net> <n9h4s7$aqb$1@ger.gmane.org>
Mime-Version: 1.0 (Mac OS X Mail 9.1 \(3096.5\))
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <n9h4s7$aqb$1@ger.gmane.org>
Precedence: list
Xref: csiph.com comp.lang.python:102817

On Feb 10, 2016, at 8:06 PM, Frank Millman <frank@chagford.com> wrote:
>=20
> "Israel Brewster"  wrote in message =
news:92D3C964-0323-46EE-B770-B89E7E7E6D36@ravnalaska.net...
>=20
>> 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:
>>=20
>> - 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.
>>=20
>> 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?
>>=20
>> 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?
>>=20
>> Finally, how do other DB API modules, like psycopg2, ensure that =
ROLLBACK is called if the user never explicitly calls close()?
>=20
> 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.
>=20
> 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.
>=20
> A connection has 2 possible states - 'in transaction', or 'not in =
transaction'. When you create the connection it starts off as 'not'.
>=20
> 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.
>=20
> 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'.
>=20
> 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. =
=46rom 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.=20

>=20
> HTH
>=20
> Frank Millman
>=20
>=20
> --=20
> https://mail.python.org/mailman/listinfo/python-list