[eluser]Unknown[/eluser]
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;
}