Transactions for web developers - Aymeric Augustin¶

Database¶

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.

Interface: the python client libraries¶

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

Framework (django)¶

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/