[DB-SIG] autocommit

Magnus Lycka magnus.lycka@tripnet.se
Thu, 18 Sep 1997 17:14:56 +0200


At 11:45 1997-09-17 -0700, Bill Tutt wrote:
>Erm.. just to be annoying.. ODBC drivers default to autocommiting every
>SQL statement that's executed.
>You have to explicitly tell the ODBC driver to turn autocommiting off.
>
>Currently (as an obnoxious hack) the PythonWin ODBC interface has a
>setautocommit() method that you can call to turn off autocommiting after
>every cursor.execute().
>
>Does this sound like a useful feature or should the ODBC module be
>tweaked to automatically turn off the ODBC driver's autocommit feature?

Most databases have the possibility to set autocommit on or off.
Is this not generally available in the Python DB-API? I think it should be.

Are there supported databases (which support transactions) that can't 
set autocommit on/off? In that case we might think of putting that in
the API. That should be simple.

Frankly, I haven't seen many applications that handled transactions
properly. Most either use autocommit all the time even if it might
cause inconsistencies if a transaction is interrupted due to an error,
and some programmers who use SQL seems to be completely unaware of
transactions in general, and get very confused when it turns out that
their changes in the database are hidden to others until their session
ends...

Since the database must be able to rollback the whole transaction, you
will induce performance penalties if you don't commit as often as you 
could. The best way to make life simple for amateurish programmers is 
certainly to default autocommit on. I imagine that's why ODBC works like 
that.

I think it might be good programming practice to explicitly set 
autocommit on or off as desired for each new session. Whether the
API should implicitly set it on... I don't know... perhaps. It would
make sloppy code more portable.

I think the most practical way to use SQL databases is to turn autocommit
on by default, and to just turn it off where you have several SQL commands
that form a common transaction. Of course, if you work very much with
saving master-detail data etc you will have multi command transactions 
every time that you save, and it might be less job to do all your commits
explicitly.

To be honest, I'm a little uncertain about how to handle SQL the best way.
The thing that makes it tricky is that the database has it's own states
and namespaces which completely ignore your Python (or whatever) code
structures / namespaces. If you don't use cursors this might make things
very confusing if you call other functions containing SQL while in a
transaction... Commit/rollbacks might suddenly appear in wrong places.
I guess the solution to this is to always use cursors if the database 
supports that.


--
Magnus Lycka, Folktrov. 6C, 907 51 Umea, Sweden
+46(0)90 198 498, magnus.lycka@tripnet.se, www1.tripnet.se/~mly


_______________
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________