Database transactions

MediaWiki uses database transactions to assure consistency in the database, but also to improve performance.

Some general information about database transactions can be found here:


 * On Wikipedia, see Database transaction and ACID
 * For MySQL, see the transaction statement and the InnoDB transaction model

MediaWiki is using transactions in two ways:
 * 1) Using "traditional" begin/commit pairs to protect critical sections. Note that as of version 1.21, nested transactions are not supported.
 * 2) Using a buffer-and-flush mode to improve performance and overall consistency, if DBO_TRX is enabled (this is the case per default on web requests, but not for maintenance mode or unit tests).

In MediaWiki 1.20 and 1.21, several changes were made to this system to make it easier to find and avoid problems with transactions, especially problems arising from the two modes above being mixed. Among other things, Database::onTransactionIdle was introduced. The current expected behavior is:


 * calling begin( __METHOD__ ) starts an explicit transaction. If another explicit transaction is already open, a warning is issued (visible if $wgDevelopmentWarnings is on) and the previous transaction is committed before starting the new one.
 * calling commit( __METHOD__ ) ends an explicit transaction. If no explicit transaction is currently open, a warning is issued (visible if $wgDevelopmentWarnings is on).
 * calling query (or otherwise performing any database operation) starts an implicite transaction, if DBO_TRX is set and no transaction is currently open.
 * calling commit( __METHOD__, "flush" ) flushes an implicit transaction. If an explicit transaction is currently open, a warning is issued (visible if $wgDevelopmentWarnings is on).

This means effectively that every begin should be paired with a commit, and commit should not be called with the "flush" in between them, nor should begin be called. Also, to just flush out any open implicit transaction, use commit with the "flush" flag.

For some databases transaction lifecycle can be traced in the debug log when $wgDebugDBTransactions is set to true.

This is the result of some conversation on the wikitech-l mailing list and subsequent discussion on the bugzilla. Some relevant discussions are:


 * Nested database transactions
 * Can we kill DBO_TRX? It seems evil!
 * Transaction warning: WikiPage::doDeleteArticleReal
 * Transaction warning: WikiPage::doEdit (User::loadFromDatabase) (TranslateMetadata::get)

In one mail, Tim Starling explained the reasoning behind the DBO_TRX system. Here is a redacted version of his explanation:

DBO_TRX provides the following benefits: * It provides improved consistency of write operations for code which is not transaction-aware, for example rollback-on-error. * It provides a snapshot for consistent reads, which improves application correctness when concurrent writes are occurring. DBO_TRX was introduced when we switched over to InnoDB, along with the introduction of Database::begin and Database::commit. [...]   Initially, I set up a scheme where transactions were "nested", in the sense that begin incremented the transaction level and commit decremented it. When it was decremented to zero, an actual COMMIT was issued. So you would have a call sequence like: * begin -- sends BEGIN * begin -- does nothing * commit -- does nothing * commit -- sends COMMIT This scheme soon proved to be inappropriate, since it turned out that the most important thing for performance and correctness is for an   application to be able to commit the current transaction after some particular query has completed. Database::immediateCommit was introduced to support this use case -- its function was to immediately reduce the transaction level to zero and commit the underlying transaction. When it became obvious that that every Database::commit call should really be Database::immediateCommit, I changed the semantics, effectively renaming Database::immediateCommit to   Database::commit. I removed the idea of nested transactions in   favour of a model of cooperative transaction length management: * Database::begin became effectively a no-op for web requests and was sometimes omitted for brevity. * Database::commit should be called after completion of a sequence of write operations where atomicity is desired, or at the earliest opportunity when contended locks are held. [...]   When transactions too long, you hit performance problems due to lock contention. When transactions are too short, you hit consistency problems when requests fail. The scheme I introduced favours performance over consistency. It resolves conflicts between callers and callees by using the shortest transaction time. I think was an   appropriate choice for Wikipedia, both then and now, and I think it is    probably appropriate for many other medium to high traffic wikis. Savepoints were not available at the time the scheme was introduced. But they are a refinement of the abandoned transaction nesting scheme, not a refinement of the current scheme which is optimised for reducing lock contention. In terms of performance, perhaps it would be feasible to use short transactions with an explicit begin with savepoints for nesting. But then you would lose the consistency benefits of DBO_TRX that I   mentioned at the start of this post. -- Tim Starling