Nested DB transactions

[eluser]Unknown[/eluser]
Hi!

In model1 i start transaction and in the middle of it I call method from otherModel which also has transaction. Do those 2 nested transactions count as one?

Code:

-load someModel
$this->db->trans_start();

     $this->db->query('come query');

    
     -call some function from someModel which has another transaction start and complete in it

$this->db->trans_complete();

And sorry for my poor English please!

[eluser]Phil Sturgeon[/eluser]
Yup, same transaction. As long as the model doesn't start or stop any transactions of its own.

[eluser]Unknown[/eluser]
But that second (inner) model started another transaction. Will it break transaction that started first? Maybe I wasn't clear enough in my example

[eluser]phazei[/eluser]
I was just about to post a very similar question, so thought I'd just tag it on to this thread.

How exactly will this work:

Code:

$this->db->trans_start();

    $this->db->query($sql);
    $this->db->query($sql2);

    $this->db->trans_start();
        $this->db->query($sql3);
        $this->db->query($sql4);
    $this->db->trans_complete();

    $this->db->query($sql5);    
    $this->db->query($sql6);

$this->db->trans_complete();

will the embedded/nested trans_complete() end the entire transaction, or will it still be in the outer transaction?

I $sql4 fails, will it be rolled back to before $sql1?

If $sql6 fails, will it be rolled back to before $sql1? Or will the inner trans_complete() commit $sql3 and $sql4? Thus only $sql5, $sql2, $sql1 will be rolled back?




This question might be covered by "Strict Mode" that is mentioned in the documentation. But it is not clear on defining what exactly a "group" is. I was also hoping to get that clarified as well.

From the docs:

When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back. If strict mode is disabled, each group is treated independently, meaning a failure of one group will not affect any others.

Does multiple groups mean one after another?
EG

Code:

$this->db->trans_start();
$this->db->trans_complete();
$this->db->trans_start();
$this->db->trans_complete();

?

Will a failure in the second transaction roll back the first? That wouldn't make sense to me. Could someone clarify what that means as well?

I might not understand it fully because I don't quite have a very solid grasp of transactions themselves outside of CI, with MySQL.


The reason I'm asking is the same reason of the OP. I have a model method that runs transactionally that needs to be called both on its own, and from other model methods that also need to run transactionally.




Thanks.

[eluser]TheFuzzy0ne[/eluser]
Here's the trans_start() method from the DB_driver.php file:

Code:

function trans_start($test_mode = FALSE)
    {    
        if ( ! $this->trans_enabled)
        {
            return FALSE;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->_trans_depth > 0)
        {
            $this->_trans_depth += 1;
            return;
        }
        
        $this->trans_begin($test_mode);
    }

Hopefully this answers your question.

[eluser]phazei[/eluser]
Yes it does, thanks. I guess I should have poked my nose in the code, hehe.


Though it only answers 3/4 of the question, the other half isn't directly related to the op. It was about the "Strict Mode" and what a "group" or multiple groups were.

Though perhaps if I look at the trans_strict() code I'll find my answer.

[eluser]phazei[/eluser]
Ok, so after looking at the code, groups are transactions side by side, eg:

Code:

$this->db->trans_start();     //group1
$this->db->trans_complete();  //group1
$this->db->trans_start();     //group2
$this->db->trans_complete();  //group2

If group1 fails, then group2 would also fail to commit.
(EDIT: but if group2 fails, group1 will NOT roll back since it's already committed. Basically in strict mode, once a transaction fails, any future transactions will not commit)

Though when looking at the code, I either found a bug, or totally missed something. From the snippet you posted:

Code:

if ($this->_trans_depth > 0)
        {
            $this->_trans_depth += 1;
            return;
        }

In order to start increasing depth, the _trans_depth needs to have begun, so it needs to be at least 1. Well, its default value is zero, so when it goes the first time, it ought to become 1...
I searched high and low, all over that file and I just could not find where it initially becomes 1 to be able to get into that if statement. So as far as I can tell, it never begins incrementing because it never gets past 0.
If it's there, please direct me to a line number. (I'm viewing CI1.7.0)

I think it should be

Code:

if ($this->_trans_depth >= 0)
        {
            $this->_trans_depth += 1;
            return;
        }

or simply remove the if statement altogether.

[eluser]Unknown[/eluser]
trans_start() method from the DB_driver.php (CodeIgniter 1.7.1)

Code:

function trans_start($test_mode = FALSE)
    {    
        if ( ! $this->trans_enabled)
        {
            return FALSE;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->_trans_depth > 0)
        {
            $this->_trans_depth += 1;
            return;
        }
        
        $this->trans_begin($test_mode);
    }

trans_complete() method from the DB_driver.php (CodeIgniter 1.7.1)

Code:

function trans_complete()
    {
        if ( ! $this->trans_enabled)
        {
            return FALSE;
        }
    
        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->_trans_depth > 1)
        {
            $this->_trans_depth -= 1;
            return TRUE;
        }
        
        // The query() function will set this flag to FALSE in the event that a query failed
        if ($this->_trans_status === FALSE)
        {
            $this->trans_rollback();

            // If we are NOT running in strict mode, we will reset
            // the _trans_status flag so that subsequent groups of transactions
            // will be permitted.
            if ($this->trans_strict === FALSE)
            {
                $this->_trans_status = TRUE;
            }

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

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

trans_complete() method from the DB_driver.php (CodeIgniter 1.7.1)

Following code is a part of trans_begin, trans_commit, trans_rollback from mysql_driver.php(same with other driver files) (CodeIgniter 1.7.1)

Code:

if ($this->_trans_depth > 0)
        {
            return TRUE;
        }

so... if you want to use mutiple transactions.
you should modify trans_start() method and trans_complete() method from DB_driver.php

it should be

Code:

function trans_start($test_mode = FALSE)
    {    

        if ( ! $this->trans_enabled)
        {
            return FALSE;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->_trans_depth > 0)
        {
            $this->_trans_depth += 1;
            return;
        }
        
        $this->trans_begin($test_mode);
        $this->_trans_depth += 1;
    }

    function trans_complete()
    {
        if ( ! $this->trans_enabled)
        {
            return FALSE;
        }
    
        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->_trans_depth > 1)
        {
            $this->_trans_depth -= 1;
            return TRUE;
        }elseif($this->_trans_depth==1){
            $this->_trans_depth -= 1;
        }
        
        // The query() function will set this flag to FALSE in the event that a query failed
        if ($this->_trans_status === FALSE)
        {
            $this->trans_rollback();

            // If we are NOT running in strict mode, we will reset
            // the _trans_status flag so that subsequent groups of transactions
            // will be permitted.
            if ($this->trans_strict === FALSE)
            {
                $this->_trans_status = TRUE;
            }

            log_message('debug', 'DB Transaction Failure');
            return FALSE;
        }
        
        $this->trans_commit();
        return TRUE;
    }

and you can use it

Code:

$this->db->trans_start();

    $this->db->trans_start();
    ....
    $this->db->trans_complete();

    $this->db->trans_start();
    ....
    $this->db->trans_complete();

$this->db->trans_complete();

[eluser]phazei[/eluser]
Ahh, yeah, I see the bug with my suggested fix. That makes more sense

I hope that gets fixed in the next version.

[eluser]Unknown[/eluser]
That fixed it for me

Thanks!