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
implicit rollback.
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 (START
TRANSACTION).
Key findings:
Statements always run in transactions.
Transactions are opened automatically.
Transactions are advanced technology.
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
(“documentation issue”).
Key findings:
The DB API requires the same transactional behaviour as the SQL standard.
Client libraries for databases that always autocommit have to emulate this behaviour.
But you can turn it off and use autocommit
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. with
transaction.autocommit():, with transaction.commit_on_success():, with
transaction.commit_manually():. There is also a low-level API for doing
stuff manually.
Key findings:
OK to forget it, it will change in 1.6.
The middleware is a reasonable idea.
The decorators/context managers don’t work well, they often cannot be nested.
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.
Key learnings:
django 1.6 will have sane transaction-related functionality.
Read the documentation at https://docs.djangoproject.com/en/dev/topics/db/transactions/
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):
