+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Nested Transactions in MySQL database does not work (possible bug fix included) (/showthread.php?tid=9274)
Nested Transactions in MySQL database does not work (possible bug fix included) - El Forum - 06-19-2008
[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;
}
Nested Transactions in MySQL database does not work (possible bug fix included) - El Forum - 06-19-2008
[eluser]gtech[/eluser]
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.
[url="http://ellislab.com/forums/viewthread/61979/"]http://ellislab.com/forums/viewthread/61979/[/url]
I don't know if this issue has ever been logged.
Nested Transactions in MySQL database does not work (possible bug fix included) - El Forum - 09-04-2008
[eluser]Unknown[/eluser]
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;
}