Initially he didn’t know a lot about transactions, so he researched them in depth. A quote by Christophe Pettus: “transaction management tools are often made to seem like a black art”.
He moves from the database (postgres and sqlite) to the interface (psycopg2 and sqlite3) to the framework (django).
A definition: an SQL transaction is a sequence of SQL statements that is
atomic with respect to recovery. In SQL 92, a transaction begins with a
transaction-initiating statement (almost everything can start a transaction)
and it ends with a commit, an explicit rollback (
ROLLBACK) or an
SQL 1999 changed this a bit. It has savepoints. After a savepoint, you can
rollback to that savepoint, to a previous savepoint or you can set a new
savepoint. Oh, and there is an explicit transaction start statement (
Remember the dreaded “current transaction is aborted, commands ignored until end of transaction block” postgresql fault? What it actually means is “a previous statement failed, the application must perform a rollback”. You cannot let postgres do any auto-recovery, that would break transactional integrity. It is your application that needs to do it (and it should always do it).
(I didn’t hear what the actual solution is). Update: Diederik says in his comment that the solution is to just switch on autocommit for postgres in the database settings.
There’s also AUTOCOMMIT. Most databases default to it. It commits every single statement automatically. Normally, you are either in auto-commit mode or inside transactions.
Psycopg2 and sqlite3 are wrappers around C libraries. They use the DB API 2.0, PEP 249. It defines connections and cursors. Connections implement transactions, cursors do fetching and setting.
Note: the PEP wants the auto-commit to be off, initially!
Psycopg2 handles it by inserting a BEGIN before every statement, unless there’s already a transaction in progress. Even for SELECTs.
Sqlite3 also inserts
BEGIN, but not for a
SELECT. All other statements get a
COMMIT. Even a statement like
SAVEPOINT: this is broken by design
Django 1.5 and earlier runs with an open transaction. For updates/deletes/saves, django does a commit. More or less auto-commit.
There’s transaction middleware. One http request = one transaction. Commit on success, roll back on exception. It only works for the default database, though. And depending on the order of your middleware, it may or may not apply.
Django provides a couple of high-level APIs.
transaction.commit_manually():. There is also a low-level API for doing
Django 1.6 uses database-level autocommit, which is what you’d normally expect. There are atomic transactions for requests: only for the view functions. Again. one transaction per http requests.
The high level API is now called
atomic. Usable as a decorator and as a
context manager. It can be safely nested.
My name is Reinout van Rees and I work a lot with Python (programming language) and Django (website framework). I live in The Netherlands and I'm happily married to Annie van Rees-Kooiman.
Most of my website content is in my weblog. You can keep up to date by subscribing to the automatic feeds (for instance with Google reader):