Database transactions

Jump to: navigation, search

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:

Transaction scope[edit]

First, we should distinguish two types of methods:

  • Those with outer transaction scope: methods that structurally are clearly guaranteed to have no callers up the chain that perform transaction operations. Places that have this scope are the execute() method of Maintenance scripts, the run() method of Job classes, and the doUpdate() method of DeferrableUpdate classes. When these methods run, no callers further up the call stack will have any transaction activity. This means that methods with outer transaction scope are free to start and end transactions (given some caveats described below). Callers down the stack do not have outer scope and are expected to respect this fact.
  • Those with unclear/inner transaction scope: these are methods that are not clearly guaranteed to have outer transaction scope. This is most of the methods in MediaWiki core and extensions. Various methods fall under this category, such as those of model/abstraction classes, utility classes, DAO objects, hook handlers, business/control logic classes, and so on. These methods are not free to start/end transactions and must only use transaction semantics that support nesting. If they need to do some updates after commit, then they must register a post-commit callback method.

Basic transaction usage[edit]

MediaWiki is using transactions in a few ways:

  1. Using "traditional" begin()/commit() pairs to protect critical sections and be certain they are committed. Nested transactions are not supported. This should only ever be used from callers that have outer transaction scope and are only affecting one database (accounting for any possible hook handlers too). Valid methods include callbacks to onTransactionIdle() or AutoCommitUpdate where only one DB is updated and no hooks are fired. Always match each begin() with a commit().
  2. Using startAtomic()/endAtomic() pairs to protect critical sections without knowing when they will commit. Nested sections are fully supported. These can be used anywhere, but must be properly nested (e.g. do not open a section and then not close it before a "return" statement). In maintenance scripts, when no atomic sections are open, a commit happens. If the DBO_TRX flag is set, however, the atomic sections join the main DBO_TRX transaction set. Inside AutoCommitUpdate or onTransactionIdle() callbacks, DBO_TRX is turned off for the specified database, meaning the endAtomic() will commit once there are no sections in those callbacks.
  3. Using implicit pivoted transaction rounds if DBO_TRX is enabled (this is the case per default on web requests, but not for maintenance mode or unit tests). The first write on each database connection without a transaction triggers BEGIN. A COMMIT happens at the end of the request for all databases connections with writes pending. If multiple databases that have DBO_TRX set where written to, then they all will do their commit step in rapid succession, at the end of the request. This maximizes cross-DB transaction atomicity. Note that optimistic concurrency control (REPEATABLE-READ or SERIALIZABLE in PostgreSQL) might undermine this somewhat, since SERIALIZATION FAILURE can occur on a proper subset of the commits, even if all the writes appeared to succeed. In any case, DBO_TRX reduces the number of commits which can help site performance (by reducing fsync() calls) and means that all writes in the request are typically either committed or rollback together.
  4. Using explicit pivoted transactions rounds via LBFactory::beginMasterChanges and LBFactory::commitMasterChanges. These rounds are effective in both web and CLI modes and have the same semantics as their implicit counterparts except for the following aspects:
    • Calling commitMaserChanges() from a method that did not start the round will throw an error.
    • They commit any empty transactions on master databases when completed, clearing any REPEATABLE-READ snapshots. This assures that callers relying on LBFactory::flushReplicaSnapshots() in single-DB setups will still have fresh snapshots for DB_REPLICA connections. It also assures that the transaction listener set in Maintenance::setTriggers sees all databases in an idle transaction state, allowing it to run deferred updates.
  5. If at any point, an exception is thrown and not caught by anything else, MWExceptionHandler will catch it and rollback all database connections with transactions. This is very useful when combined with DBO_TRX.

Transaction misuse errors[edit]

Various misuses of transactions will cause exceptions or warnings, for example:

  • Nesting begin() calls will throw an exception
  • Calling commit() on a another method's transaction, started with begin(), will throw an exception.
  • Calling begin() or commit() when an atomic section is active will throw an exception.
  • The use of LBFactory::beginMasterChanges and LBFactory::commitMasterChanges have analogous limitations to the above.
  • Calling commit() when no transaction is open will raise a warning.
  • startAtomic() and endAtomic() expect __METHOD__ as argument and its value must match on each level of atomic section nesting. If it does not match, then an exception is thrown.
  • Calling begin() or commit() when DBO_TRX is set may log a warning and no-op.
  • Calling rollback() when DBO_TRX is set will throw an error, triggering rollback of all DBs.
  • Calling getScopedLockAndFlush() while writes are still pending in a transaction will result in an exception.
  • Catching DBError, DBExpectedError, or DBQueryError exceptions without calling rollbackMasterChanges() can result in an exception.
  • Trying to use begin() or commit() in a SqlDataUpdate that is set to use the transaction support that class provides may cause exceptions. By doing so, outer scope is forfeited so that multiple such updates can be part of a single transaction round.

Specifying an atomic group of writes[edit]

When a set of queries are intimately related in determining a unit of database writes, one should use an atomic section. For example:

$dbw->startAtomic( __METHOD__ );
$res = $dbw->select( 'mytable', '*', ..., __METHOD__, [ 'FOR UPDATE' ] );
// determine $rows based on $res
$dbw->insert( 'mysubtable', $rows, __METHOD__ );
$dbw->update( 'mymetatable', ..., ..., __METHOD__ );
$dbw->endAtomic( __METHOD__ );

Another style of doing this is to use doAtomicSection(), which is useful if there are many return statements.

    function ( IDatabase $dbw ) {
        $res = $dbw->select( 'mytable', '*', ..., __METHOD__, [ 'FOR UPDATE' ] );
        // determine $rows based on $res
        $dbw->insert( 'mysubtable', $rows, __METHOD__ );
        $dbw->update( 'mymetatable', ..., ..., __METHOD__ );

Splitting writes into multiple transactions[edit]

Improving performance[edit]


Suppose you have some code that applies some database updates. Afterwards the method finishes you may want to:

  • a) Apply some highly contentious database updates near the end of the transaction so they don't hold locks too long
  • b) Apply further database updates that happen to be slow, non-timely, and don't need 100% atomicity (e.g. they can be refreshed)


In some cases, code may want to know that data is committed before continuing to the next steps. One way to do this is to put the next steps in callback to onTransactionIdle(), AtomicSectionUpdate, or AutoCommitUpdate. The latter two are DeferredUpdates, which differ somewhat in Maintenance vs web/job request mode:

  • In web requests and jobs (including jobs in CLI mode), deferred updates run after the main transaction round commits. Each update is wrapped in its own transaction round, though AutoCommitUpdate disables DBO_TRX on the specified database handle, committing each query on the fly. If deferred updates enqueue other deferred updates, the extra transaction rounds are simply added.
  • In Maintenance scripts, deferred updates run after any transaction on the local (e.g. "current wiki") database commits (or immediately if there is no open transaction). Deferred updates cannot simply be automatically deferred until no transactions are active as that might lead to out of memory errors for long running scripts where some (possibly "foreign wiki") database always has an active transaction (this would otherwise be ideal). This is why deferred updates are oddly tied only to the local database master. Regardless, since Maintenance::execute() has outer transaction scope and DBO_TRX is off for them, it doesn't usually make sense to directly call DeferredUpdates::addUpdate() from the execute() method, since the code could just run immediately.

Any method with outer transaction scope has the option of calling commitMasterChanges( __METHOD__ ) on the LBFactory singleton to flush all active transactions on all database. This assures that all pending updates are committed before the next lines of code are executed. Also, if such a method wants to start a transaction round, it can use beginMasterChanges( __METHOD__ ) on the singleton, do the updates, and then call commitMasterChanges( __METHOD__ ) on the singleton. This will set DBO_TRX on current and new DB handles during the round, causing implicit transactions to be started up, even in CLI mode. The DBO_TRX flag is reverted to its prior state after the round ends. Note that similar to begin() and commit(), transaction rounds cannot be nested.

Note that some databases, like those handling ExternalStoreDB, usually have DBO_DEFAULT disabled. This means that they remain in auto-commit mode even during transaction rounds.


For the cases above, here are some techniques for handling them:

Case A:

// Update is still atomic by being in the main transaction round, but is near the end
$dbw->onTransactionIdleOrPreCommit( function () use ( $dbw ) {
        [ 'de_day' => substr( wfTimestamp( TS_MW ), 0, 6 ), 'de_count' => 1 ],
        [ 'de_day' ],
        [ 'de_count = de_count + 1' ],
} );

Case B:

    new AtomicSectionUpdate( 
        function ( $dbw, $fname ) {
        ...set of atomic statements...
) );
    new AutoCommitUpdate( 
        function ( $dbw, $fname ) {
        ...set of autocommit statements...
) );

Handling replication lag[edit]


Writes queries (e.g. create, update, delete operations) that affect many rows or have poor index usage take a long time to complete. Worse, is that replica databases often uses serial replication, so they apply master transactions one at a time. This means that a 10 second UPDATE query will block that long on each replica database (sometimes more since replica DBs have to handle read traffic and replicate the master's writes). This creates lag, where other updates on the master do not show for a while to other users. It also slows down users making edits due to ChronologyProtector trying to wait for replicas to catch up.

The main cases where this can happen are:

  • a) Job classes that do expensive updates
  • b) Maintenance scripts that do mass updates to large portions of tables

Another situation that sometimes comes up is when an update triggers a job, and that job must do some heavy queries in order to recompute something. It may not be a good idea to do the queries on the master DB, but the replica DBs might be lagged and not reflect the change that triggered the job. This leads to the following case:

  • c) Jobs needing to wait for a single replica DB to catch up so they can query it to determine the updates

A similar scenario can happen with external services. Suppose a service needs to do expensive API queries in order to reflect changes in the wiki's database. So this leaves another case:

  • d) Callers that do updates before notifying an external service that it needs to query the DBs to update itself


Expensive updates that create lag need to be moved to a Job class and that the job's run() method should batch the updates, waiting for replicas to catch up between each batch.


Case A / B:

$dbw = wfGetDB( DB_MASTER );
$factory = MediaWikiServices::getInstance()->getDBLoadBalancerFactory();
$ticket = $factory->getEmptyTransactionTicket( __METHOD__ );
$rowBatches = array_chunk( $rows, $wgUpdateRowsPerQuery );
foreach ( $rowBatches as $rowBatch ) {
    $dbw->insert( 'mydatatable', $rows, __METHOD__ ); any other hooks or methods...
    $factory->commitAndWaitForReplication( __METHOD__, $ticket );

Case C:

$lb = MediaWikiServices::getInstance()->getDBLoadBalancer();
$dbr = $lb->getConnection( DB_REPLICA );
// Wait for $dbr to reach the current master position
$lb->safeWaitForMasterPos( $dbr );
// Clear any stale REPEATABLE-READ snapshots
$dbr->flushSnapshot( __METHOD__ );

$factory->beginMasterChanges( __METHOD__ );
...query $dbr and do updates...
$factory->commitMasterChanges( __METHOD__ );

Case D:

$dbw = wfGetDB( DB_MASTER ); updates to items in $dbw...
// Use a POSTSEND deferred update to avoid blocking the client
    function () {
        $factory = MediaWikiServices::getInstance()->getDBLoadBalancerFactory();
        // Send idempotent HTTP request to regenerate the changed items
        $http = new MultiHttpClient( [] );
        $http->run( ... );
        // Service will do expensive API queries hitting DB_REPLICA
    $dbw // abort callback on rollback

Updating secondary non-RDBMS stores[edit]


Sometimes changes to the primary data-set demand updates to secondary data stores (that lack BEGIN...COMMIT), for example:

  • a) Enqueue a job that will query some of the affected rows, making the end-user wait on its insertion
  • b) Enqueue a job that will query some of the affected rows, inserting it after the MediaWiki response to the end-user is flushed
  • c) Send a request to a service that will query some of the effected rows, making the end-user wait on the service request
  • d) Send a request to a service that will query some of the effected rows, doing it after the MediaWiki response to the end-user is flushed
  • e) Purge CDN proxy cache for URLs that have content based on the effected rows
  • f) Purge the WANObjectCache entry for a changed row
  • g) Storing a non-derivable text/semi-structured blob to another store
  • h) Storing a non-derivable file to another store
  • i) Account creation hook handler creating an LDAP entry that must accompany the new user
  • j) Updating the database and sending an e-mail to a user's inbox as part of a request by the user


In general, derivable (e.g. can be regenerated) updates to external stores will use some sort DeferrableUpdate class or onTransactionIdle() to be applied post-commit. In cases where the external data is immutable, then it can be referenced by autoincrement ID, UUID, or hash of the externally stored contents; storing the data pre-commit is best in such cases. Updates that do not fall into either category should use onTransactionPreCommitOrIdle(), batch all the update to the external store into one transaction if possible, and throw an error if the update fails (which will trigger RDBMS rollback); this reduces the window that things could go wrong and result in inconsistent data.


Case A:

$job = new MyJobClass( $title, [ ... ] );
// Job insertion will abort if $dbw rolls back for any reason
$dbw->onTransactionIdle( function() use ( $jobs ) {
    JobQueueGroup::singleton()->push( $job );
} );

Case B:

$job = new MyJobClass( $title, [ ... ] );
// Job insertion will abort if $dbw rolls back for any reason
$dbw->onTransactionIdle( function() use ( $jobs ) {
    // End-user is not blocked on the job being pushed
    JobQueueGroup::singleton()->lazyPush( $job );
} );

Case C:

    function () use ( $data ) {
        $http = new MultiHttpClient( [] );
        $http->run( ... );
    DeferredUpdates::PRESEND, // block the end-user
    $dbw // abort update on rollback of this DB

Case D:

    function () use ( $data ) {
        $http = new MultiHttpClient( [] );
        $http->run( ... );
    DeferredUpdates::POSTSEND, // don't block end-user
    $dbw // abort update on rollback of this DB

Case E:

    new CdnCacheUpdate( $urls ),
    DeferredUpdates::PRESEND // block end-user so they don't see stale pages on refresh
) );

Case F:

// Update a row
$dbw->update( 'mytable', ..., [ 'myt_id' => $id ], __METHOD__ ); 
// Invalidate the corresponding cache key
$cache = ObjectCache::getMainWANInstance();
$key = $cache->makeKey( 'my-little-key', $id );
$dbw->onTransactionIdle( function () use ( $cache, $key ) {
    $cache->delete( $key ); // purge/tombstone key right after commit
} );

Case G:

$dbw = wfGetDB( DB_MASTER );
$vrs = MediaWikiServices::getInstance()->getVirtualRESTServiceClient();
// Define the row data
$uuid = UIDGenerator::newUUIDv1();
$row = [ 'myr_text_uuid' => $uuid, ... ];
// First insert blob into the key/value store keyed under $uuid
$status = $vrs->run( [ 'method' => 'PUT', 'url' => "/mystore/map-json/{$uuid}", 'body' => $blob, ... );
if ( !$status->isGood() ) {
   throw new RuntimeException( "Failed to update key/value store." );
// Insert record pointing to blob.
// If we fail to commit, then store will just have a dangling blob.
// However, the user will not see records with broken blobs.
$dbw->insert( 'myrecords', $row, __METHOD__ );

Case H:

$dbw = wfGetDB( DB_MASTER );
$be = FileBackendGroup::singleton()->get( 'global-data' );
// Define the row data
$sha1 = $tempFsFile->getSha1Base36(); // SHA-1 of /tmp file uploaded from user
$row = [ 'maf_text_sha1' => $sha1, ... ];
// Make the container/directory if needed
$status = $be->prepare( [ 'dir' => "mwstore://global-data/mytextcontainer" ] );
// Copy the file into the store
$status->merge( $be->store( [ 'src' => $tempFsFile->getPath(), 'dst' => "mwstore://global-data/mytextcontainer/{$sha1}.png" ] ) );
if ( !$status->isGood() ) {
   throw new RuntimeException( "Failed to update key/value store." );
// Insert record pointing to file.
// If we fail to commit, then store will just have a dangling file.
// However, the user will not see records with broken files.
$dbw->insert( 'myavatarfiles', $row, __METHOD__ );

Case I:

// LDAP will not be updated if $dbw rolls back for any reason
$dbw->onTransactionPreCommitOrIdle( function() use ( $ldap ) {
    $status = $ldap->createUser( $user );
    if ( !$status->isGood() ) {
        // If the user already exists or LDAP is down, 
        // throw a GUI error and rollback all databases.
        $lbFactory = MediaWikiServices::getInstance()->getDBLoadBalancerFactory();
        $lbFactory->rollbackMasterChanges( __METHOD__ );
        throw new ErrorPageError( ... );
    // If COMMIT fails, then we have an LDAP user with no local
    // user row. The code should be able to recover from this.
} );

Case J:

// Email will not be sent if $dbw rolls back for any reason
$dbw->onTransactionIdle( function() use ( $subject, $body, $user ) {
    $status = $user->sendEmail( $subject, $body );
    if ( !$status->isGood() ) {
        // Assuming this mail is critical, throw an error if it fail to send/enqueue.
        // Many setups will use local queuing via exim and bounces are usually not
        // synchronous, so there is no way to know for sure if the email "made it".
        throw new ErrorPageError( ... );
} );

Use of transaction rollback[edit]

The use of rollback() should strongly be avoided, since it affects what all the previous executed code did before the rollback. Outer callers might still consider that an operation was successful and attempt additional updates and/or show bogus success page. Any REPEATABLE-READ snapshot is renewed, causing objects with lazy-loading to possibly not find what they were looking for, or to get unexpectedly newer data than the rest of what was loaded. Using rollback() is particularly bad since other databases might have related changes and it's easy to forget to roll those back too.

Instead, simply throwing an exception is enough to trigger rollback of all databases due to MWExceptionHandler::handleException(). There are two special cases to this rule:

  • Exceptions of the type ErrorPageError (used for human-friendly GUI errors) do not trigger rollback on web requests inside of MediaWiki::run() if thrown before MediaWiki::doPostOutputShutdown(). This lets actions,special pages, and PRESEND deferred updates show proper error messages, while auditing and anti-abuse tools can still log updates to the database.
  • Callers might catch DBError exceptions without either re-throwing them or throwing their own version of the error. Doing so is extremely bad practice and can cause all sorts of problems from partial commits to simply spewing up DBTransactionError errors. Only catch DB errors in order to do some cleanup before re-throwing an error or if the database in question is used exclusively by the code catching errors.

This is how rollback is normally used, as a fail-safe that aborts everything, returns to the initial state, and errors out. However, if directly calling rollback is truly needed, always use rollbackMasterChanges() on the LBFactory singleton to make sure all databases are reverted to the initial state of any transaction round.

Debug logging[edit]

Several channels (log groups) are used to log DB related errors and warnings:

  • DBQuery
  • DBConnection
  • DBPerformance
  • DBReplication
  • exception

At Wikimedia, these logs can be found by querying using +channel:<CHANNEL NAME>.

Old discussions[edit]

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

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

   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 
DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLiteSQL ServerManual:Microsoft SQL Server

Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file

Core tables: archivebot_passwordscategorycategorylinkschange_tagcommentconfigexternallinksfilearchivehitcounterimageimagelinksimage_comment_tempinterwikiiwlinksipblocksip_changesjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionrevision_comment_tempsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist