Nested DB transactions

[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.