MySQL Nested Transactions (Work In Progress)

[eluser]Kyle Johnson[/eluser]
Hello everyone,

I started this just yesterday but have made some decent progress in getting nested transactions to work within CodeIgniter with InnoDB using savepoints.

It requires modifying several core files, so right now it will break transactions working with database drivers other than MySQLi (haven't brought changes over to the MySQL driver yet).

There are still some issues I need to work out, such as "test mode" and also "strict mode." However it seems to work if you're using strict mode and aren't using test mode (i.e., people that want nested transactions).

Basically the following code:

Code:

$this->db->trans_start();
   $this->db->trans_start();
    $this->db->trans_begin();
     $this->db->trans_begin();
     $this->db->trans_commit();
    $this->db->trans_rollback();  
   $this->db->trans_complete();  
  $this->db->trans_complete();
  //echo "\n";
  $this->db->trans_start();
   $this->db->trans_start();
    $this->db->trans_begin();
     $this->db->trans_begin();
     $this->db->trans_commit();
     $this->db->trans_begin();
     $this->db->trans_commit();
     $this->db->trans_begin();
     $this->db->trans_commit();
    $this->db->trans_commit();
   $this->db->trans_complete();
  $this->db->trans_complete();

Produces these transaction statements

Code:

START TRANSACTION
SAVEPOINT txn1
  SAVEPOINT txn2
   SAVEPOINT txn3
   RELASE SAVEPOINT txn3
  ROLLBACK TO SAVEPOINT txn2
ROLLBACK TO SAVEPOINT txn1
ROLLBACK

START TRANSACTION
SAVEPOINT txn1
  SAVEPOINT txn2
   SAVEPOINT txn3
   RELASE SAVEPOINT txn3
   SAVEPOINT txn3
   RELASE SAVEPOINT txn3
   SAVEPOINT txn3
   RELASE SAVEPOINT txn3
  RELASE SAVEPOINT txn2
RELASE SAVEPOINT txn1
COMMIT

system/database/DB_driver.php

Code:

/**
  * Start Transaction
  *
  * @access public
  * @return void
  */
function trans_start($test_mode = FALSE)
{
  if ( ! $this->trans_enabled)
  {
   return FALSE;
  }
  $this->trans_begin($test_mode);
}

// --------------------------------------------------------------------

/**
  * Complete Transaction
  *
  * @access public
  * @return bool
  */
function trans_complete()
{
  if ( ! $this->trans_enabled)
  {
   return FALSE;
  }
  // The query() function will set this flag to FALSE in the event that a query failed
  if ($this->_trans_status === FALSE)
  {
   $this->trans_rollback();

   log_message('debug', 'DB Transaction Failure');
   return FALSE;
  }

  $this->trans_commit();
  return TRUE;
}

Uncomment the echo statements to view the nesting statements.
system/database/drivers/mysqli/mysqli_driver.php

Code:

/**
  * Begin Transaction
  *
  * @access public
  * @return bool
  */
function trans_begin($test_mode = FALSE)
{
  if ( ! $this->trans_enabled)
  {
   return TRUE;
  }
  // If the $test_mode flag is set to TRUE transactions will be rolled back
  // reset flag if this is the first transaction
  $this->_trans_status = ($test_mode === TRUE) ? FALSE : (($this->_trans_depth > 0) ? $this->_trans_status : TRUE);
  
  // When transactions are nested we use SAVEPOINTS
  if ($this->_trans_depth > 0)
  {
   //echo str_pad('', $this->_trans_depth,' ').'SAVEPOINT txn'.$this->_trans_depth."\n";
   $this->simple_query('SAVEPOINT txn'.$this->_trans_depth);
   $this->_trans_depth += 1;
   return TRUE;
  }
  
  //echo 'START TRANSACTION'."\n";
  $this->simple_query('SET AUTOCOMMIT=0');
  $this->simple_query('START TRANSACTION'); // can also be BEGIN or BEGIN WORK
  $this->_trans_depth += 1;
  return TRUE;
}

// --------------------------------------------------------------------

/**
  * Commit Transaction
  *
  * @access public
  * @return bool
  */
function trans_commit()
{
  if ( ! $this->trans_enabled)
  {
   return TRUE;
  }
  if($this->trans_strict === TRUE && $this->_trans_status === FALSE) {
   return $this->trans_rollback();
  }
  $this->_trans_depth -= 1;
  if ($this->_trans_depth > 0)
  {
   //echo str_pad('',$this->_trans_depth," ") . 'RELASE SAVEPOINT txn'.$this->_trans_depth."\n";
   $this->simple_query('RELASE SAVEPOINT txn'.$this->_trans_depth);
   return TRUE;
  }
  //echo 'COMMIT'."\n";
  $this->simple_query('COMMIT');
  $this->simple_query('SET AUTOCOMMIT=1');
  return TRUE;
}

// --------------------------------------------------------------------

/**
  * Rollback Transaction
  *
  * @access public
  * @return bool
  */
function trans_rollback()
{
  if ( ! $this->trans_enabled)
  {
   return TRUE;
  }
  $this->_trans_status = FALSE;
  $this->_trans_depth -= 1;
  // When transactions are nested we only begin/commit/rollback the outermost ones
  if ($this->_trans_depth > 0)
  {
   //echo str_pad('',$this->_trans_depth," ") . 'ROLLBACK TO SAVEPOINT txn'.$this->_trans_depth."\n";
   $this->simple_query('ROLLBACK TO SAVEPOINT txn'.$this->_trans_depth);
   return TRUE;
  }
  //echo "ROLLBACK\n";
  $this->simple_query('ROLLBACK');
  $this->simple_query('SET AUTOCOMMIT=1');
  return TRUE;
}

[eluser]Kyle Johnson[/eluser]
Hey everyone,

I've been trying to figure out a way to implement this without breaking the other database drivers, but I keep coming up short with what I'd consider an 'ideal' solution.

I'm thinking of something like this:

1. Add database config variable to enable/disable nested transactions
2. In system/database/DB_driver.php determine if nested_transactions is TRUE.
3. If it is enabled and driver is MySQL or MySQLi, use the code posted above created specifically for nested transactions, otherwise use the non-nested transaction code.
4. Inside system/database/drivers/mysqli/mysqli_driver.php make checks to determine which path to use, nested or non-nested.

Alternatively, we could create additional nested-transaction enabled methods instead of checking the config variable in two places, but that seems like it could easily be forgotten if things get updated.

Would love your feedback.

Thanks,
Kyle

[eluser]Unknown[/eluser]
Has any more thought or work gone into this? I would be interested in helping develop or test this, but didn't know if there is already work being done. Is there a git branch anywhere that has code for this already?