Transactions in Drupal 7

To start a new transaction it is necessary to write $txn = db_transaction(); in the code. The transaction will remain open for as long as the variable $txn is executed. When the variable $txn is removed, the transaction will be performed. If your transaction is embedded into another one, then Drupal will perform each operation separately and an external transaction will be completed only when all the objects, that is all requests, are completed successfully.

When one of the queries in a transaction fails, a "rollback" (an operation that returns the data to its initial state before the transaction starts) of all changes happens.

Example:

<?php
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();
  }
}
?>

If it is necessary to finish one of the transactions prematurely, one needs to remove the variable $transaction using the function unset().

<?php
  unset($transaction);
?>

Let’s work with the transaction using an example.

Imagine a situation when it is required to process a large amount of data for users and the operation for them should be grouped. Problem: withdrawal and transfer of funds from internal user accounts on the basis of records in the temporary table.

<?php
  function update_users_accounts() {
    // The transaction opens here.
    $accounts_transaction = db_transaction();
    try {
      // Select all users, that should be processed.
      $query = db_select(‘account_tmp_table’, 'at');
      $query->fields('at', array('uid', ‘decrease’, ‘increase’));
      $query->condition('at.timestamp', $time, '>');
      $result = $query->execute();
      while($record = $result->fetchAssoc()) {
        // Remove money from  user account.
        decrease_users_accounts($record[‘uid’], $record[‘decrease’]);
        // Add money to user account.
        increase_users_accounts($record[‘uid’], $record[‘increase’]);
        // Delete temp record from table.
        delete_users_account_record($record[‘uid’]);
      }	
      return TRUE;
    }
    catch (Exception $e) {
      $accounts_transaction->rollback();
      watchdog_exception('users_accounts', $e);
    }
  // $transaction goes out of scope here.  Unless it was rolled back, 
  // it gets automatically commited here.
  }

  function decrease_users_accounts($uid, $decrease) {
    // The transaction opens here.
    // Get current user account.
    $current_account = get_user_account($uid);
    $updated_account = $current_account - $decrease;
    // Update user's account record.
    db_update('account_data_table')
      ->condition('uid', $uid)
      ->fields(array('account' => $updated_account))
      ->execute();
  }
  
  function increase_users_accounts($uid, $increase) {
    // Get current user account.
    $current_account = get_user_account($uid);
    $updated_account = $current_account + $increase;
    // Update user's account record.
    db_update('account_data_table')
      ->condition('uid', $uid)
      ->fields(array('account' => $updated_account))
      ->execute();
  }
  
  function delete_users_account_record($uid) {
    // The transaction opens here.
    db_delete('account_tmp_table')
      ->condition('uid', $uid)
      ->execute();
  }
}

?>

Such function realization through transactions will prevent the changes in the user account by another functionality during the code execution, as well as will guarantee the integrity of the operation by calling the rollback method which returns all data to its original state, if some errors occur.

This article goes about transactions and how Drupal can support them. For more detailed information, see documentation.