[DB-SIG] autocommit support in pep-249

Federico Di Gregorio fog at dndg.it
Wed Sep 14 14:16:22 CEST 2011

On 13/09/11 18:39, Chris Clark wrote:
>>> The behaviour of setting autocommit when a transaction is already open
>>> is also to be seen: there are three basic option:
>>> - have a commit
>>> - have a rollback
>>> - have an error
>>> I feel the implicit commit is the most dangerous option. An implicit
>>> rollback may be more acceptable (it's the same behaviour of closing
>>> the connection with a transaction in progress): I proposed it for
>>> psycopg but, in the discussion, raising an exception proved the most
>>> popular option.
>> I don't think I know how to even determine whether there is a
>> transaction in progress in ADO.(*)  If there is a way, then checking
>> on it just so that I can raise an exception seems expensive.  Remember
>> that most of the time the resulting stack trace would not be seen by
>> the programmer who goofed up, but a completely innocent user -- and to
>> her it looks like the program just crashed for no reason.  The
>> rollback would be easy to program and user friendly.  I vote rollback.
> The exception route has the potential to be the easiest for most DBMS
> implementations, most DBMSs will raise an error, so the driver doesn't
> need to track state and can just propagate the DBMS error. For some
> backends, state tracking would be required which is why I suggested the
> "easy" commit option. Rollback seems semi safe BUT when setting
> autocommit to ON the default behavior of a rollback seems a little
> surprising.
> I'm not in love with committing when the autocommit state is changed but
> for the "enable auto commit" operation it seems logical, the user is
> requesting commits take place under the covers. Conversely when
> disabling autocommit (after it has previously been enabled) we are
> either not in a transaction (due to auto commit being on) or we could
> still be fetching mid select, in which case a commit would be the same
> as  a rollback. I.e. only impact on locking, any writes to the database
> would have been already auto committed.

Client code can set .autocommit (yes, I am +1 for the attribute) to true
by two different code paths: the "correct" one and the "buggy" one. The
correct code path is not a problem: as long as the programmer knows the
behaviour she can do the right thing:

  1. exception => make sure to commit() or rollback() before,
                  depending on the wanted outcome;
  2. commit    => rollback() if you don't want to commit changes,
                  do nothing in the other case;
  3. rollback  => as (2), reversed.

What I am interested in is the behaviour of the driver when the code
sets .autocommit to true following a buggy code path, i.e., when there
is a pending transaction and the code is unaware of it:

  1. exception => pending transaction is lost, user (and eventually
                  also the programmer) gets a stack trace: this is
                  good because the user is sure about the outcome (data
                  is lost) _and_ has information about the problem;
  2. commit    => pending transaction is commited but it is what the
                  code was really supposed to do? noboby will know until
                  someone peeks at the database and _then_ a possibly
                  difficult bug hunting session begins;
  3. rollback  => as (2), but data is lost.

Having commit or rollbacks happen because of a bug really scares me:
spurious data starts to popup into the database and finding the bugs
usually isn't a piece of cake. That's why I vote for the exception.


Federico Di Gregorio                                       fog at initd.org
 If nobody understand you, that doesn't mean you're an artist.
                                                            -- anonymous

More information about the DB-SIG mailing list