Transactions

Last updated on
7 February 2017

Drupal also supports transactions, including a transparent fallback for databases that do not support transactions. However, 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 SQL, 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.

Java solves the nesting problem with its locks by implementing support for a nesting structure similar to what we test below. 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 = db_transaction();" 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.

To start a new transaction, simply call $transaction = db_transaction(); in your own code. The transaction will remain open for as long as the variable $transaction remains in scope. When $transaction is destroyed, the transaction will be committed. If your transaction is nested inside of another then Drupal will track each transaction and only commit the outer-most transaction when the last transaction object goes out out of scope, that is, all relevant queries completed successfully.

You must assign the return value of db_transaction() to a variable, as in the example. If you call the function without assigning the return value to a variable, your transaction will commit instantly, making it useless.

Example:

function my_transaction_function() {
  // The transaction opens here.
  $transaction = db_transaction();

  try {
    $id = db_insert('example')
      ->fields(array(
        'field1' => 'mystring',
        'field2' => 5,
      ))
      ->execute();

    my_other_function($id);

    return $id;
  }
  catch (Exception $e) {
    $transaction->rollback();
    watchdog_exception('my_type', $e);
  }

  // $transaction goes out of scope here.  Unless it was rolled back, it
  // gets automatically commited here.
}

function my_other_function($id) {
  // The transaction is still open here.

  if ($id % 2 == 0) {
    db_update('example')
      ->condition('id', $id)
      ->fields(array('field2' => 10))
      ->execute();
  }
}