Hi,

I am having problems nesting code igniter transactions with MySql.  When you have many nested transactions, it doesn’t work properly.  For example:

$this->MT_DB->trans_start();
       
      $this->MT_DB->trans_start();
        INSERT 1
      $this->MT_DB->trans_complete();

      INSERT ERROR
       
$this->MT_DB->trans_complete();       

In the example above, INSERT 1 would still go thru even though there was an insert error.  This is due to MySql not being able to handle nested transactions.

POSSIBLE BUG FIX: I dug into the code igniter db driver code and noticed that code igniter is trying to fix this by keeping track of the number of trans_start and trans_complete calls and only starting/stopping a transaction at the root level. There was a bug in that code.

In mysql_driver.php, updated the following functions:

function trans_begin($test_mode = FALSE)
  {
      if ( ! $this->trans_enabled)
      {
        return TRUE;
      }
     
      // When transactions are nested we only begin/commit/rollback the outermost ones
      if ($this->_trans_depth > 0)
      {
        return TRUE;
      }

      // Reset the transaction failure flag.
      // If the $test_mode flag is set to TRUE transactions will be rolled back
      // even if the queries produce a successful result.
      $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
     
      $this->simple_query(‘SET AUTOCOMMIT=0’);
      $this->simple_query(‘START TRANSACTION’); // can also be BEGIN or BEGIN WORK
      $this->_trans_depth=1;  //LINE UPDATED
      return TRUE;
  }

function trans_commit()
  {
      if ( ! $this->trans_enabled)
      {
        return TRUE;
      }

      // When transactions are nested we only begin/commit/rollback the outermost ones
      if ($this->_trans_depth > 1)  //LINE UPDATED
      {
        return TRUE;
      }

      $this->simple_query(‘COMMIT’);
      $this->simple_query(‘SET AUTOCOMMIT=1’);
      $this->_trans_depth=0;  //LINE UPDATED
      return TRUE;
  }

hi good catch, I found your problem when I first used codeigniter just over half a year ago, however I did not diagnose the root cause of the problem like you have.

http://codeigniter.com/forums/viewthread/61979/

I don’t know if this issue has ever been logged.

Thanks for posting this fix. I haven’t tried it out but it makes total sense. I think you also need to add the line to set the depth=0 after a rollback was executed at the top nested level (depth=1);

  function trans_rollback()
  {
      if ( ! $this->trans_enabled)
      {
        return TRUE;
      }

      // When transactions are nested we only begin/commit/rollback the outermost ones
      if ($this->_trans_depth > 1)
      {
        return TRUE;
      }

      $this->simple_query(‘ROLLBACK’);
      $this->simple_query(‘SET AUTOCOMMIT=1’);
      $this->_trans_depth=0; //line updated
      return TRUE;
  }

This has been fixed by Pull Request #691 and will be part of CI 3.0.