Database Transactions in Drupal

Database Transactions in Drupal

Difficulty: 
Let's Rock

This article is not about what transactions are, but the particularities of its implementation in Drupal.

Drupal database abstraction layer has the ability to handle transactions and nested transactions.

It uses the PDO transaction capabilities to start/commit/rollback the higher level transaction in the scope, and database specific functions such as SAVENPOINT to handle the nested transactions.

Transactions in Drupal are quite a mess to use:

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.

Read between the lines.... this means that whenever you want to do something transactional you MUST use the following pattern:

$t = db_transaction();

try {
  // Whatever code.
}
catch ($e) {
  $t->rollBack();
  throw $e;
}

This is because, by "design" rollbacks are explicit and commits are implicit. That basically means that whenever you start a transaction it is implicit that - unless you do an explicit rollback - the transaction is to be committed.

I'm not sure where I read this, but that behaviour was implemented because it was "easy" for a developer to forget to call  $transaction->commit(). Isn't it easier to forget to wrap all your code in a try-catch block an explicitly call $transaction->rollBack() ? Just makes no sense.

But not only this flawed design is a matter of convience it is, by no means, ROBUST. Do you know what is going to happen if there is an exception during a commit()? That it won't get catched. That means that the TRUE pattern for using transactions in Drupal is the following mess:

$t = db_transaction();

try {
  // Whatever code.
}
catch ($e) {
  $t->rollBack();
  throw $e;
}

try {
  unset($t);
}
catch ($e) {
  // Do as you wish here...
}

There is another big issue at hand, and is that there is no control on how transactions are nested and there is no concept of what transaction isolation is.

On any professional environment you would have the ability to choose how is transaction nesting going to be managed.

A summary of the issues of current transaction management design in Drupal:

  • Not convenient to use
  • Not robust (requires a double try-catch that is never used)
  • No control on transaction nesting
  • No control on transaction isolation level

In .Net development you have the following options to controll nesting behaviour:

  • RequiredA transaction is required by the scope. It uses an ambient transaction if one already exists. Otherwise, it creates a new transaction before entering the scope. This is the default value.
  • RequiresNew: A new transaction is always created for the scope.
  • Supress: The ambient transaction context is suppressed when creating the scope. All operations within the scope are done without an ambient transaction context

For transaction isolation you have:

  • Chaos: The pending changes from more highly isolated transactions cannot be overwritten.
  • ReadCommitted: Volatile data cannot be read during the transaction, but can be modified.
  • ReadUncommitted: Volatile data can be read and modified during the transaction.
  • RepeatableRead: Volatile data can be read but not modified during the transaction. New data can be added during the transaction.
  • Serializable: Volatile data can be read but not modified, and no new data can be added during the transaction.
  • Snapshot: Volatile data can be read. Before a transaction modifies data, it verifies if another transaction has changed the data after it was initially read. If the data has been updated, an error is raised. This allows a transaction to get to the previously committed value of the data. When you try to promote a transaction that was created with this isolation level, an InvalidOperationException is thrown with the error message "Transactions with IsolationLevel Snapshot cannot be promoted".
  • Unspecified: A different isolation level than the one specified is being used, but the level cannot be determined. An exception is thrown if this value is set.

I cannot imagine a serious project without sooner or later having to fine tune Isolation Level on transactions or nesting behaviour. Looks like Drupal's DTBNG needs some more love to cover more enterprise use cases.

Drupal's database abstraction layer emulates the RequiresNew behaviour. This is not convenient at all because usually you only care about the higher level transaction scope, and having nested transactions (SAVEPOINTS) is quite expensive.

The SQL Server Driver for Drupal already offers an alternative db_transaction_sane() that requires explicit commits and has implicit rollbacks. So transactional code would look like this:

// Start the transaction.
$t = db_transaction_sane();

// Whatever code.

// Commit the transaction.
$t->commit();

We are working on an overloaded version that will take an optional configuration parameter to be able to specify:

  • How are nested transactions going to be managed
  • The isolation level

The  MS SQL Server driver has some extra love thanks to the DatabaseContext class that allows you to define behaviour scopes that control:

  • If queries are being preprocessed or not by the driver (processed by default)
  • If statements should be issued in direct mode (non direct by default)

This is how you use it:

// Bypass query preprocessing and use direct queries.
$ctx = new \DatabaseContext($connection, TRUE, TRUE);

// Do whatever here

// Reset the context to previous state, this will
// be done automatically when $ctx goes out of scope
// and is destructed by PHP.
unset($ctx);

 

Add new comment

By: root Sunday, June 14, 2015 - 00:00