transaction : report bug and Solution !

[eluser]Nima A.[/eluser]
Hi,
some weeks ago, I post a thread which I described that may be there's a bug in the CI's mysql transactions, you can see it from here :

http://ellislab.com/forums/viewthread/84082/

but , unfortunately, no one even post a comment , nor test it ,....
among these days , I didn't have the free time inorder to have a closer look at this problem , and also I was totally depressed by the CI community that never gave any attention to my bug report, till today, I decided to take a closer look to this bug,...
I dont re-mention the bug, you can read it from my previous post, just let's have a concise summary :

concise summary : suppose 3 queries in a transaction (for example 3 update queries) :
query1 executes successfully , query2 fails and query3 is right; in this situation query1 executes, query2 fails and thus query3 never executes and in a situation which they're packed in a transaction , query1 do not affect database . so then, remove query1 (which is right) & query2 (which is wrong) and execute the transaction with query3 by refreshing

the page >> query1 and query3 affects database [ > executing query1 in this situation is our problem ]

I'm not familiar with CI core code, ... I just take a look at the contents of database classes and found

DB_driver:imple_query method that executes a query, so I put a echo command there inorder to show the queries which executes inorder to perform a transaction ,

consider this as my transaction placed on a model :

Code:

$this->db->trans_start();

$query1 = "right update";
$lnk1 = $this->db->query1();

$query2 = "wrong update";
$lnk2 = $this->db->query2();

$query3 = "right update";
$lnk3 = $this->db->query3();

$this->db->trans_complete();

if($this->db->trans_status() === false){
return false;
}
else{
return true;
}

here's the output for the given transaction :

Code:

*SET AUTOCOMMIT=0
*START TRANSACTION
*right update - query no 1;
*wrong update - query no 2;
A DataBase Error was encountered

[as I said, in this case , query1 will not affect the database and that's fine !]

so remove query1 and query2 from model and try to execute the transaction , as follows :

Code:

$this->db->trans_start();


$query3 = "right update";
$lnk3 = $this->db->query3();

$this->db->trans_complete();

if($this->db->trans_status() === false){
return false;
}
else{
return true;
}

in this case , here's the output for the echo command which has been placed on

Code:

DB_driver::simple_query :
*SET AUTOCOMMIT=0
*START TRANSACTION
*right update - query no 3;
*COMMIT
*SET AUTOCOMMIT=1

[ problem : in this case the previous query1 in the previous transaction will also affect database like the existing

query3 ]

-----
SOLUTION :
as I said, I'm a kind of newbie in CI , and it's the first time that I dig into ci's core code, but I just try to fix it (well may be there's some more effective ways to change it, I didn't think about that, just tried to fix it ):

1.
I added the following property in the CI_DB_driver class :

Code:

var $trans_status = false;

2. I modified CI_DB_Driver:imple_query as follows :

Code:

function simple_query($sql)
    {
        if ( ! $this->conn_id)
        {
            $this->initialize();
        }
        
        
        $qResult = $this->_execute($sql);
        if(empty($qResult) && ($this->trans_stat)){
            $this->trans_rollback();
        }
        return $qResult;
        

        // return $this->_execute($sql);
    }

3. I modified CI_DB_mysql_driver::trans_begin as follows :

Code:

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;
        
        // *********** added by nima *************
        $this->trans_stat = true;
        // ***************************************
        
        
        $this->simple_query('SET AUTOCOMMIT=0');
        $this->simple_query('START TRANSACTION'); // can also be BEGIN or BEGIN WORK
        return TRUE;
    }

4. I modified CI_DB_mysql_driver::trans_commit as follows :

Code:

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 > 0)
        {
            return TRUE;
        }
        
        // *********** added by nima *************
        $this->trans_stat = false;
        // ***************************************

        $this->simple_query('COMMIT');
        $this->simple_query('SET AUTOCOMMIT=1');
        return TRUE;
    }

5. I modified CI_DB_mysql_driver::trans_rollback as follows :

Code:

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 > 0)
        {
            return TRUE;
        }
        
        
        // *********** added by nima *************
        $this->trans_stat = false;
        // **************************************

        $this->simple_query('ROLLBACK');
        $this->simple_query('SET AUTOCOMMIT=1');
        return TRUE;
    }

and It works ,
with the best wishes for the CI community,

[eluser]Nima A.[/eluser]
any opinons ? (esp. from CI developers)

[eluser]Nima A.[/eluser]
I'm so sorry for the CI community

[eluser]smatakajr[/eluser]
Hey Nima. I havent tested your fix as i found another one i just implimented
Im quite new to CI as well but im a certified PHP expert.. so i found another
fix you may want to take a look at as far as nested transactions

http://ellislab.com/forums/viewthread/82771/

Anyhow. I will def lookinto your fix.. my question is

Can I do a transaction like this..

Code:

$this->db->trans_start();

$this->db->insert(table,$data);

//then call model
//which has more insert and update queries..
$this->mymodel->insert_function();

$this->db->trans_complete();

What I see is that you can do

Code:

$this->db->trans_start();

query1
query2
ect..

$this->db->trans_complete();

Would this keep track of transactions from external
modules

Thanks
Rick

[eluser]davidbehler[/eluser]
I think as long as your library uses the CI database class and does not open a connection to the db server on its own, it should work just like you say it would...at least that's what I would expect how it works.

[eluser]smatakajr[/eluser]
I tried to do transactions within a hierarchy of models and it doesnt work
the queries execute but it doesnt keep track or rollback.. any ideas?

Rick