CodeIgniter Forums
Nested Transactions in MySQL database does not work (possible bug fix included) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Nested Transactions in MySQL database does not work (possible bug fix included) (/thread-9274.html)



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;
}