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:
$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
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
/**
* 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
/**
* 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;
}