agasti:mayon:transactional_processing

You are here: start » agasti » mayon » transactional_processing
This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Transactional Processing ======
In database terminology, [[http://en.wikipedia.org/wiki/Database_transaction|transactions]] are used to ensure that your data is modified consistently; we call these, [[http://en.wikipedia.org/wiki/ACID|ACID]] operations.

== Why is this important? ==
Well, let's say you have a table of users, and a table of user_passwords. You don't want a password to exist without a user, and you don't want a user to exist without a password. If you attempt to INSERT into both tables and one of the inserts fails, you have an opportunity for that bad scenario to appear. Similarly, the same can happen for DELETE and UPDATE operations.

== How do transactions help? ==
Transactions offer developers a way to ensure that several operations all happen together or not at all. They write all changes to a temporary transaction log and wait for a commit() statement. When the database receives its commit() it applies all of the changes written in the log as permanent changes. In this way, if the INSERT into both the users table and the user_passwords is wrapped in a transaction, they are both commit()-ed together, or not at all.

== Are there any downsides to using Transactions? ==
Sort-of. Transactional processing can add headaches to the developer's processes because they add more code and require more thought and planning of data manipulation ([[http://en.wikipedia.org/wiki/Data_Manipulation_Language|DML]]) operations. Developers must remember to issue rollback() operations when inserts fail or else the transaction log and write locks will grow to the point of affecting system stability. There are also minor performance concerns for heavily nested transactions as each beginTransaction() statement opens a new transaction log.

All of these issues can be mitigated through well-executed planning an application design so the benefits of transactional process far outweigh its negatives.

===== Basic Usage =====
The Doctrine ORM that underlies Agasti is has a great transactions system that is flexible yet powerful-enough to be used in critical systems like emergency management software.

Before we go further, I wholly recommend you read the [[http://www.doctrine-project.org/projects/orm/1.2/docs/manual/transactions/1_1|official Doctrine ORM Transactions documentation]].

==== Gotchas ====
The single most-important 'gotcha' of transaction-based systems, is the fact that, in many engines, a rollback() rolls back **every** open transaction on a connection. Why is this a gotcha?

Consider the following nested set of transactions:

<code php>
$conn->beginTransaction() ; // open our first transaction
  try {
    // ... do stuff

    $conn->beginTransaction() // here's a second, nested transaction
    try {
      // ... do stuff
      $conn->commit() ; // this commits the inner transaction
    }
    catch(Exception $e) {
      $conn->rollback() ; // execute a rollback because we had a problem
      throw $e ;
    }  
    
    $conn->commit() ; // this commits the outer transaction
  }
  catch(Exception $e) {
    $conn->rollback() ; // !! We fail right here because we no longer have a transaction
    throw $e ;
  }
</code>

If you'll notice, because the nested/inner/second transaction executed a rollback() the outer transaction's rollback() would fail because a rollback immediately kills **all** transactions.

There are a number of ways to avoid this, of varying degrees of correctness. Often times, beginner programmers will resort to avoiding throwing exceptions in their catch blocks but this is considered a bad practice partly because it hides problems from the user but also because you never know who or what will be calling your method as part of a larger data manipulation operation that might call many similar methods in one big try/catch. We'll discuss the correct way to deal with this conundrum in the [[agasti:mayon:transactional_processing#Rolling Back]] portion of this page.

===== Agasti Transaction & Method Patterns =====
Because of the complexity surrounding transactions, the patterns listed in this section are **strongly** recommended for Agasti developers.

We'll be using the following code for our example:
<file php myTransactionsClass.class.php>
<?php
/**
 * An example class for transactions processing.
 *
 * PHP Version 5.3
 *
 * LICENSE: This source file is subject to LGPLv2.1 license
 * that is available through the world-wide-web at the following URI:
 * http://www.gnu.org/copyleft/lesser.html
 *
 * @author Chad Heuschober, CUNY SPS
 *
 * Copyright of the Sahana Software Foundation, sahanafoundation.org
 */
class myTransactionsClass
{
  /**
   * A method to delete users.
   * 
   * @param Doctrine_Connection $conn An optional doctrine connection object. If one is not passed
   * the default will be used.
   */
  public function delUsers (Doctrine_Connection $conn = NULL)
  {
    // here you can pick up the default connection if not passed one explicitly
    if (is_null($conn)) { $conn = Doctrine_Manager::connection() ; }

    // a doctrine query where we pass the connection parameter
    $q = agDoctrineQuery::create($conn)
      ->delete('Users') ;
    
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
    
    try // always wrap transactional statements in a try/catch block
    {
      $deleted = $q->execute() ; // execute our doctrine query
      
      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
    }
    catch(Exception $e) // if we have a problem...
    {
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }

      // ALWAYS log rollbacks with as much useful information as possible
      $err = 'Couldn\'t delete users\'! Rolled back changes!' ;
      sfContext::getInstance()->getLogger()->err($err) ;

      throw $e ; // always remember to throw an exception after rollback
    }
    
    // except where other output is desired, return the number of operations performed
    return $deleted ;
  }
  
  /**
   * A method to add users
   * 
   * @param array $users An indexed array of user names to be added to the Users table.
   * <code>
   * array( $username1, $username2, ... )
   * </code>
   * @param Doctrine_Connection $conn An optional doctrine connection object. If one is not passed
   * the default will be used.
   */
  public function addUsers ($users, Doctrine_Connection $conn = NULL)
  {
    // here you can pick up the default connection if not passed one explicitly
    if (is_null($conn)) { $conn = Doctrine_Manager::connection() ; }

    $coll = new Doctrine_Collection('Users') ; // set up our collection
      
    foreach ($users as $user) // loop through our users array
    {
      // create a new doctrine record object for the Users table
      $newUser = new User() ;
      $newUser['name'] = $user ;
      
      $coll->add($newUser) ; // add the new user object to our collection
    }
    
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }

    try // always wrap transactional statements in a try/catch block
    {
      $coll->save($conn) ; // save our collection

      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
    }
    catch(Exception $e) // if we have a problem...
    {
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }

      // ALWAYS log rollbacks with as much useful information as possible
      $err = sprintf('Couldn\'t insert users %s! Rolled back changes!', json_encode($users)) ;
     sfContext::getInstance()->getLogger()->err($err) ;

      throw $e ; // always remember to throw an exception after rollback
    }
    
    // except where other output is desired, return the number of operations performed
    return count($coll) ; 
  }

  /**
   * A method to delete all users, then add new ones from the $users parameter.
   * 
   * @param array $users An indexed array of user names to be added to the Users table.
   * <code>
   * array( $username1, $username2, ... )
   * </code>
   * @param Doctrine_Connection $conn An optional doctrine connection object. If one is not passed
   * the default will be used.
   */
  public function deleteAndAddUsers($users, Doctrine_Connection $conn = NULL)
  {
    $results = array() ; // our results array
    
    // here you can pick up the default connection if not passed one explicitly
    if (is_null($conn)) { $conn = Doctrine_Manager::connection() ; }
    
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
    
    try // always wrap transactional statements in a try/catch block
    {
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection

      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
    }
    catch(Exception $e) // if we have a problem...
    {
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }

      // ALWAYS log rollbacks with as much useful information as possible
      $err = sprintf('Failed to execute %s.', __FUNCTION__) ;
      sfContext::getInstance()->getLogger()->err($err) ;

      throw $e ; // always remember to throw an exception after rollback
    }
    
    return $results ; // just return our results array
  }
}

$users = array('Fester', 'Bester', 'Lester', 'Esther', 'Chester') ; // array of users
$mtc = new myTransactionsClass() ;
$mtc->delUsers() ;
$mtc->addUsers($users) ;
$mtc->deleteAndAddUsers($users) ;
</file>

==== When to Use Transactions ====
Put simply, any time you intend to change related data in the system (by way of UPDATE, INSERT, or DELETE), you should use a transaction. In the case of simple forms, the Symfony framework automatically wraps all of those actions in a single transaction, but leaves developers to their own decisions regarding business actions. These business actions can be found in complex forms, listeners, data import engines, or mass-data actions such as, disabling all of the shifts for a given facility resource, when the facility resource is taken offline.

This rule also applies to any time you're calling other, related methods that affect data. Note how the following two methods are wrapped in a transaction:
<code php>
    // because we want both of these to happen synchronously, we wrap them both in a transaction
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
    
    try // always wrap transactional statements in a try/catch block
    {
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection
      
      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
</code>

==== Method Parameters ====
Any method or function that will manipulate data **MUST** include an optional parameter that allows developers to pass a Doctrine Connection object. This pattern will enable those calling your function from elsewhere to pass a currently open connection (and its open transactions) and enable transactions to be nested across methods.

A in our above example, you can see this represented in the following lines:
<code php>
  public function delUsers (Doctrine_Connection $conn = NULL)
  {
    // here you can pick up the default connection if not passed one explicitly
    if (is_null($conn)) { $conn = Doctrine_Manager::connection() ; }
</code>

If you look closely at deleteAndAddUsers(), you can see our nesting happen in the try/catch block where we pass the current connection to the child methods. This ensures that they all happen under the same transaction.
<code php>
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection
</code>

==== Consistent Use in DML ====
As a rule, the connection object should be declared at the instantiation of any queries or related entities.

<code php>
    // a doctrine query where we pass the connection parameter
    $q = agDoctrineQuery::create($conn)
      ->delete('Users') ;
</code>

Sometimes this will not be possible (specifically regarding collections) and in those cases, the connection object should be passed at the execution of the action. You also want to be consistent about your transaction declarations. If processing multiple records, declare your transaction once, then execute any loops outside of it.

<code php>    
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }

    try // always wrap transactional statements in a try/catch block
    {
      $coll->save($conn) ; // save our collection
</code>


==== Using Collections ====
Collections are unique and powerful objects in Doctrine. Think of collections as special database caches. A collection stores its state as a snapshot at instantiation. It then processes a diff of its state at snapshot and it's state when you execute a save() or replace() operation. This means that the collection will only execute the minimal INSERT's, UPDATE's, and DELETE's it needs to when it's saved. Since new collections don't have any objects, their snapshot is initially empty and only INSERT's are processed, however, collections sourced from DQL queries can also process UPDATE's and DELETE's.

This method of diff processing also means that all of those statements are executed at once. For these reasons, collections are the preferred means of performing data operations.

Because collection objects won't let us specify the connection upfront, we pass it at the save(), after having done the foreach() loop that adds data to our collection.

<code php>
    $coll = new Doctrine_Collection('Users') ; // set up our collection
      
    foreach ($users as $user) // loop through our users array
    {
      // create a new doctrine record object for the Users table
      $newUser = new User() ;
      $newUser['name'] = $user ;
      
      $coll->add($newUser) ; // add the new user object to our collection
    }
    
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }

    try // always wrap transactional statements in a try/catch block
    {
      $coll->save($conn) ; // save our collection
</code>

==== Method Returns ====
A good general rule is that any DML operations should return the number of operations performed.

Use your best judgment if you want to return a single value or an array of values, but remember that simple is always best.

<code php>
    // except where other output is desired, return the number of operations performed
    return count($coll) ;
</code>

<code php>
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection
</code>
<code php>
    return $results ; // just return our results array
</code>

==== Transaction Nesting ====
Like many database engines, Doctrine supports transaction nesting. To be honest, Doctrine doesn't //really// support nesting as much as it doesn't get in the way of nested transactions ([[agasti:mayon:transactional_processing#why none of this will help you today|Click Here]] to read more about Doctrine's interpretation of nesting). Consider our earlier example:

<code php>
$conn->beginTransaction() ; // open our first transaction
  try {
    // ... do stuff

    $conn->beginTransaction() // here's a second, nested transaction
    try {
      // ... do stuff
      $conn->commit() ; // this commits the inner transaction
    }
    catch(Exception $e) {
      $conn->rollback() ; // execute a rollback because we had a problem
      throw $e ;  // throw an exception as standard practice
    }  
    
    $conn->commit() ; // this commits the outer transaction
  }
  catch(Exception $e) {
    $conn->rollback() ; // !! We fail right here because we no longer have a transaction
    throw $e ;
  }
</code>

If the first commit executed without a hitch, then it would have only committed the records executed since its inner (the second) beginTransaction() statement. Commit() statements are executed one at a time and (generally) only complete on transaction level per execution. This means that your records are not written to the database until the outermost transaction receives its commit(). This can wreak havoc on a debug process if you've accidentally created more transactions than you have commits!

This sort of transaction nesting is somewhat silly because it's operating in the same file, however, consider this snippet from deleteAndAddUsers().

<code php>
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
    
    try // always wrap transactional statements in a try/catch block
    {
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection

      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
</code>

Since both the delUsers() and addUsers() methods have transactions inside of them, they each occupy a second-level nesting position inside the larger transaction inside deleteAndAddUsers(). Since both delUsers() and addUsers() also commit once internally, the transaction scope is returned to 1 after they finish executing (saving addUsers() from a third-level nest inside delUsers()).

Good transactional programming should assume that it can or will be nested inside other blocks and should automatically resolve its own scope.

== Savepoints, what?? ==
Now, since we're talking about nesting levels, there's one more type of pseudo-transaction you should be aware of: savepoints.

<code php>
$savepoint = 'a_savepoint_name' ;
$conn->beginTransaction() ; // create a transaction
$conn->beginTransaction($savepoint) ; // also create a savepoint
</code>

Savepoints can be thought of as named transactions, but don't confuse them for one! A savepoint cannot exist or be declared outside the scope of a transaction (meaning you can't replace a transaction with one). Like a transaction, savepoints also have commit($savepointName) and rollback($savepointName) methods available to them. Each of these rollback()'s or commit()'s actions functions like a true transaction but //only within the scope of the open transaction//. Additionally, the parent transaction's commit() or rollback() actions deletes any savepoints and commit()'s or rollback()'s any records in the savepoint.

Savepoints and nested transactions have varying levels of support and different implementations in each database engine. Generally speaking, the use of a single transaction layer with several nested savepoints, is becoming a de-facto standard with support in MySQL, SQL Lite and several other data systems.

Some database engines do allow indefinite nesting of real transactions and in those cases, savepoints might not seem to have much of a use case, but don't let that deceive you. Since each transaction nesting level creates a new transaction buffer (whether savepoint or full transaction), you can quickly fill up that buffer if too many transactions are issued.

== Savepoint? Transaction? Just tell me What I'm supposed to use! ==
Both, of course! There are few standards regarding nested transaction handling in the database world. The most-supported implementation  is the concept of a single beginTransaction(), with many savepoints. This works across most implementations, however, it's not without its issues.

If we consider how we call delUsers() and addUsers(), you can see how each of these functions could be called independently, or nested inside a larger operation.

<code php>
$users = array('Fester', 'Bester', 'Lester', 'Esther', 'Chester') ; // array of users
$mtc = new myTransactionsClass() ;
$mtc->delUsers() ;
$mtc->addUsers($users) ;
$mtc->deleteAndAddUsers($users) ;
</code>

Since both delUsers() and addUsers() have DML operations, we want them to use transactions. But since deleteAndAddUsers() calls each of them inside it, we should also assume that its larger operation should be issued as a transaction unto itself.

<code php>
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
    
    try // always wrap transactional statements in a try/catch block
    {
      $results['deleted'] = $this->delUsers($conn) ; // execute delUsers, passing it our connection
      $results['added'] = $this->addUsers($users, $conn) ; // execute addUsers, passing our connection
</code>

Luckily for us, Doctrine does at least provide us a method to get our transaction level. The getTransactionLevel() method of a connection returns the transaction level as an integer. If it returns a 0, it means there is no open transaction, 1 means 1 open transaction, 2 equals 2 and so on and so forth. Using this, we can guesstimate whether or not we should be using a savepoint (because there is already an open transaction) or begin a new transaction.

<code php>
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }
</code>

Since we need to care about scope resolution in the case of commits and rollbacks we also want to re-check our $useSavepoint boolean at those times.

<code php>
      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
</code>

<code php>
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }
</code>

== Okay, I understand that, but what's with this __FUNCTION__ stuff? ==
Quite simply, <nowiki>__FUNCTION__</nowiki> is a magic constant that returns the name of the function / method being called. Since I don't generally advise going crazy with savepoints I only need one at a time and it seems as-appropriate a name as any for my savepoints. At the very least, I can look through my transaction log and see which function is being called. Consider it a best-practice when you only need the one savepoint.

==== Rolling Back ====
As discussed earlier, rolling back is a tricky process when one considers nesting. Some database engines (notably SQL Server), execute rollbacks inverse to the way transactions work. In these cases rollback() operations rollback **all** open transactions. This can be a bit of a brain teaser when you try to put it together in actual code because the number of operations you want to perform is somewhat variable upon the engine.

It's easy enough to maintain when the scope is only one function, but when functions are well-designed they can be re-used which is where the rollback() limitation can get confusing. Let's look back at our main example on this page and see how we handle it in the delUsers() method.

<code php>
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
</code>
<code php>
    catch(Exception $e) // if we have a problem...
    {
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }
</code>

In this case, we very explicitly check our transaction level. If the transaction nesting level is 0 at the start of our operation, we know we are the outermost transaction and can safely execute a full rollback(). If not we just rollback our savepoint.

==== Catching Errors ====
Probably the single most important rule of handling database transactions is that **all** exceptions must be caught at the save() or commit() stages and **all** rollback()'s must be followed with a write to the logfile and, finally, an exception throw.

The order of events is fairly simple:
  - start a transaction (or savepoint)
  - start a try block
    - issue a save/execute a query
    - commit() your transaction (or savepoint)
  - set up a catch block in case the save or commit failed
    - rollback() the transaction (or savepoint)
    - write to the log
    - throw an exception

You can follow this in our addUsers example below:

<code php>
    // here we check our current transaction scope and create a transaction or savepoint based on need
    $useSavepoint = ($conn->getTransactionLevel() > 0) ? TRUE : FALSE ;
    if ($useSavepoint) { $conn->beginTransaction(__FUNCTION__) ; } else { $conn->beginTransaction() ; }

    try // always wrap transactional statements in a try/catch block
    {
      $coll->save($conn) ; // save our collection

      // commit our transactions or the savepoint
      if ($useSavepoint) { $conn->commit(__FUNCTION__) ; } else { $conn->commit() ; }
    }
    catch(Exception $e) // if we have a problem...
    {
      // if we started with a savepoint, let's end with one, otherwise, rollback globally
      if ($useSavepoint) { $conn->rollback(__FUNCTION__) ; } else { $conn->rollback() ; }

      // ALWAYS log rollbacks with as much useful information as possible
      $err = sprintf('Couldn\'t insert users %s! Rolled back changes!', json_encode($users)) ;
     sfContext::getInstance()->getLogger()->err($err) ;

      throw $e ; // always remember to throw an exception after rollback
    }
</code>

Notably, we continue to check the scope of our transaction throughout to make sure we're issuing the right type of commit or rollback.

== WARNING ==
**Failure to throw an exception inside your catch block can potentially leave your data in a irrecoverable state.**

Even if you can recover your data, there's a good chance your database administrator will quit and tell all of his or her other DBA friends not to work with you. Exceptions are required so that any methods calling your method, will know something wrong has occurred and will trigger their messages up the chain. Because of some [[agasti:mayon:transactional_processing#why none of this will help you today|odd behaviour on the part of Doctrine]] it only knows how to rollback on the outermost transaction level and needs to know (failing fast), when something goes wrong.

==== Isolation Levels ====
// This section is currently being fleshed out. If you have some information to share concerning Doctrine ORM's isolation levels, please feel free to share it! //

==== Why None of this Will Help You (Today) ====
So this is the sad point where I have to tell you, that //almost// none of this matters to Doctrine ORM. I'll start by letting the makers of Doctrine give their explanation, then will try to summarize it for you.

// Please keep in mind that the following was taken from the Doctrine 2.0 documentation found at [[http://www.doctrine-project.org/docs/dbal/2.0/en/reference/transactions.html]]. Doctrine 1.2 documentation makes no mention of this, but the implementation is, nonetheless, identical.//

> A Doctrine\DBAL\Connection also adds support for nesting transactions, or rather propagating transaction control up the call stack. For that purpose, the Connection class keeps an internal counter that represents the nesting level and is increased/decreased as beginTransaction(), commit() and rollback() are invoked. beginTransaction() increases the nesting level whilst commit() and``rollback()``decrease the nesting level. The nesting level starts at 0. Whenever the nesting level transitions from 0 to 1,``beginTransaction()``is invoked on the underlying driver connection and whenever the nesting level transitions from 1 to 0,``commit()``or``rollback()``is invoked on the underlying driver, depending on whether the transition was caused by``Connection#commit()``or``Connection#rollback()`.

> What this means is that transaction control is basically passed to code higher up in the call stack and the inner transaction block is ignored, with one important exception that is described further below. Do not confuse this with “real” nested transactions or savepoints. These are not supported by Doctrine. There is always only a single, real database transaction.

> To visualize what this means in practice, consider the following example:

> <code php>
<?php
// $conn instanceof Doctrine\DBAL\Connection
$conn->beginTransaction(); // 0 => 1, "real" transaction started
try {

    ...

    // nested transaction block, this might be in some other API/library code that is
    // unaware of the outer transaction.
    $conn->beginTransaction(); // 1 => 2
    try {
        ...

        $conn->commit(); // 2 => 1
    } catch (Exception $e) {
        $conn->rollback(); // 2 => 1, transaction marked for rollback only
        throw $e;
    }

    ...

    $conn->commit(); // 1 => 0, "real" transaction committed
} catch (Exception $e) {
    $conn->rollback(); // 1 => 0, "real" transaction rollback
    throw $e;
}
</code>

> However, a rollback in a nested transaction block will always mark the current transaction so that the only possible outcome of the transaction is to be rolled back. That means in the above example, the rollback in the inner transaction block marks the whole transaction for rollback only. Even if the nested transaction block would not rethrow the exception, the transaction is marked for rollback only and the commit of the outer transaction would trigger an exception, leading to the final rollback. This also means that you can not successfully commit some changes in an outer transaction if an inner transaction block fails and issues a rollback, even if this would be the desired behavior (i.e. because the nested operation is “optional” for the purpose of the outer transaction block). To achieve that, you need to restructure your application logic so as to avoid nesting transaction blocks. If this is not possible because the nested transaction blocks are in a third-party API you’re out of luck.

> All that is guaruanteed to the inner transaction is that it still happens atomically, all or nothing, the transaction just gets a wider scope and the control is handed to the outer scope.

> Note: The transaction nesting described here is a debated feature that has it’s critics. Form your own opinion. We recommend avoiding nesting transaction blocks when possible, and most of the time, it is possible. Transaction control should mostly be left to a service layer and not be handled in data access objects or similar.

== In short? ==
Long-winded explanation aside, what it effectively means is that Doctrine ignores any commits, rollbacks or transactions that are not the outermost transaction. Every nested savepoint or beginTransaction effectively ups a little transactionLevel counter found on the connection object. Doctrine only takes action when the counter moves from 0->1 or 1->0.

== Since nesting isn't really supported, and savepoints require an open transaction, why support savepoints? ==
Honestly, I have no idea. That said, perhaps it's a hook being left for a brighter future where the drivers dictate transaction handling. This is documented in some detail in the [[http://www.doctrine-project.org/jira/browse/DC-526|Doctrine ORM bugtracker]].

== So does this mean I should ignore transactions in my development? ==
Abosolutely not! Following the patterns established here are not only good practice for general transactional programming, they're also safe (as-in do no harm, simply being ignored), and could some day in the future be picked up by enhancements to the database drivers, introducing a more ACID compliant pattern without having to change a whit of code. Additionally, many of these practices ensure that, at the very least, your outer block commit will be attempted and succeed or failure properly, regardless of how you choose to reuse your methods. It's a good safety net to have!
Trace:
Navigation

Quick Links:

Search

Toolbox

QR Code
