如何正确使用MySQL事务

Question

I need some help.

I have different files. For example:

• index.php, news.php
• include-scripts like: facebook.php, pay.php and more.

On all the pages (except news.php) a transaction is started at the beginning of the script, and ending at the end of the script.

On pages like index.php and news.php, I include pay.php. But the problem is: when I'm visiting index.php a transaction is already started, so now two transactions are active! But I can't remove the transaction-start from pay.php, because if I call the script from news.php there is no active transaction.

(My website is much more complex with many more pages and things).

Can someone help me please? Thanks!

Answer 1

Your best bet is to emulate nested transactions. To do this write a wrapper for your database access.(pseduocode)

class MyMysqli {
    protected $realDb;
    protected $transaction_count =0;

    public function __construct ($host, $username , $passwd, $dbname){
        $this->realDb = new Mysqli($host, $username, $passwd, $dbname);
    }
    public function __get($property){
        return $this->realDb->$property;
    }

    public function __set($property, $value){
        return $this->realDb->$property = $value;
    }

    public function __call($method, $args){
        return call_user_func_array(array($this->realDb, $method), $args);
    }

    // overload begin_transaction, commit and rollback
    public function begin_transaction(){
         $this->transaction_count++;
         if ($this->transaction_count == 1){
               $this->realDb->begin_transaction();
         }
    }
    public function commit(){
         $this->transaction_count--;
         if($this->transaction_count == 0){
              $this->realDb->commit();
         }
    }

    public function rollback(){
         throw new Exception("Error");
    }