Database Transactions

Overview of Transactions

Sometimes, developers will want to have database writes that are dependent upon the results of other database writes. A Drupal example would be programmatically creating a Node with an entity reference to a Media item. The developer will create the Media entity, then create the Node entity and set the Media entity as a reference on the Node entity. However, if creation of the Node entity fails, the database should be rolled back to before the Media item was created, so that it is not orphaned in the database without being linked to the Node it was supposed to be linked to.

This is done in Drupal using database transactions. A transaction is started, database writes are made, and if any errors should arise, the database is rolled back to the state when the transaction was opened.

Procedure

1. Start a transaction and store the return value in a variable $transaction.
2. Perform the database procedures
   • If an error occurs, call rollback() on the $transaction variable.
3. When processing is complete, unset() the $transaction variable, to commit the transaction to the database.

Note that if your transaction is nested inside of another, Drupal will track each transaction and only commit the outer-most transaction when the last transaction object has gone out of scope (ie. all relevant queries completed successfully).

Usage

To start a transaction in procedural code, use:

$transaction = \Drupal::database()->startTransaction();

In object oriented code, inject the database service as $this->database, and use:

$transaction = $this->database->startTransaction();

You must assign the return value of $connection->startTransaction(); to a variable, as in the example. If you call the method without assigning the return value to a variable, your transaction will commit instantly, making it useless.

To roll back a transaction, call the rollBack() method on the transaction (eg $transaction->rollBack()).

Example:

// Start the transaction. Make sure to store the result in a variable, else the
// transaction will be written to the database immediately, rendering it useless.
$transaction = $connection->startTransaction();

try {
  // Do some thing that writes to the database, such as creating an entity.
  $media->save();

  // Do another database write that depends upon the first.
  $node->save();
}
catch (Exception $e) {
  // There was an error in writing to the database, so the database is rolled back
  // to the state when the transaction was started.
  $transaction->rollBack();
}

// Commit the transaction by unsetting the $transaction variable.
unset($transaction);

Warning

The database connection object also has a rollBack() method ($connection->rollBack()), however it should be avoided in most situations, and the rollBack() method on the transaction ($transaction->rollBack()) should be used as explained above. The reason this should be done via the transaction's rollBack() method is because it rolls back that transaction based on its name, where $connection->rollBack() defaults the name to drupal_transaction and may yield undesirable results when used with nested transactions.

Deeper Under the Hood

Transactions can get quite complicated when you try and start two transactions at the same time. The behavior in that case also varies between databases.

A similar problem exists with nesting locks in C/C++. If the code has already acquired lock A and attempts to acquire lock A, the code will deadlock. If you write code that checks if it already has the lock and doesn't attempt to acquire it again, you avoid the deadlock but could release the lock prematurely.

In MySQL, we have the same problem. If your code is already in a transaction, starting a new transaction has the surprising and unfortunate consequence of committing the current transaction and starting a new one. In PostgreSQL, starting a transaction while a transaction is open has no effect except that a warning is emitted; the already-open transaction continues.

Java solves the nesting problem with its locks by implementing support for a nesting structure similar to the example given above. Java allows you to mark functions as "synchronized," which causes the function to wait for lock acquisition before running and release the lock when no longer needed. If one synchronized function calls another in the same class, Java track the lock nesting. The outer function acquires the lock, the inner function performs no locking operations, and the outer function releases the lock when it returns.

While we cannot declare functions "transactional" in PHP, we can emulate Java's nesting logic by using objects with constructors and destructors. A function simply calls "$transaction = $connection->startTransaction()" as its first (or nearly first) operation to make itself transactional. If one transactional function calls another, our transaction abstraction layer nests them by performing no transactional operations (as far as the database sees) within the inner nesting layers.