php + mysql distributed transaction (xa)

Transaction is a program execution unit that accesses and possibly updates various data items in the database;

Transactions should have 4 attributes: atomicity, consistency, isolation, persistence

atomicity. A transaction is an indivisible unit of work. All operations included in the transaction are either done or none.

Consistency. A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.

Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.

Durability. Continuity, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on it.

Distributed transaction: Participants, resource managers, transaction managers, etc. of distributed transactions are located on different nodes. These different nodes cooperate with each other to complete a transaction with logical integrity.

Correct a misunderstanding about mysql. Mysql supports XA DataSource starting from 5.0. The Connector/J version must use version 5.0, and those below 5.0 are not supported.

　The XA protocol was first proposed by Tuxedo and handed over to the X/Open organization as an interface standard for resource managers (databases) and transaction managers. Currently, major database manufacturers such as Oracle, Informix, DB2 and Sybase provide support for XA. The XA protocol uses a two-phase commit method to manage distributed transactions. The XA interface provides a standard interface for communication between resource managers and transaction managers. The XA protocol includes two sets of functions, those starting with xa_ and those starting with ax_.

　The following functions allow the transaction manager to perform operations on the resource manager:

　1) xa_open, xa_close: Establish and close the connection with the resource manager.

　2)xa_start,xa_end: Start and end a local transaction.

　3) xa_prepare, xa_commit, xa_rollback: Pre-commit, commit and rollback a local transaction.

　4) xa_recover: Roll back a pre-committed transaction.

　5) The functions starting with ax_ enable the resource manager to dynamically register in the transaction manager and operate on XID (TRANSACTION IDS).

　6) ax_reg, ax_unreg; allows a resource manager to dynamically register or deregister in a TMS (TRANSACTION MANAGER SERVER).

MySQL XA is divided into two categories, internal XA and external XA; internal XA is used for transactions across multiple engines under the same instance, with the familiar Binlog as the coordinator; external XA is used for distributed transactions across multiple MySQL instances , the application layer needs to intervene as the coordinator (suspended transactions during a crash, global commit or rollback, need to be decided by the application layer, and the implementation requirements of the application layer are higher);

Binlog serves as the coordinator of the internal XA, in the binlog The internal xid that appears will be submitted by binlog during crash recover. (This is because the binlog does not prepare, but only commits, so the internal xid that appears in the binlog must be guaranteed to have been prepared in the underlying storage engines).

MySQL database external

Example

public function testAction(){
        $goods_id=1;
        $goods_name = "大西瓜";
        $num = 1;
        $rs_order = $this->test->createorder($goods_id,$goods_name,$num);
        $rs_goods = $this->test->deduction($goods_id,$num);
        if($rs_order['status'] =="success" && $rs_goods['status']=="success"){
            $this->test->commitdb($rs_order['XA']);
            $this->test->commitdb1($rs_goods['XA']);
        }else{
            $this->test->rollbackdb($rs_order['XA']);
            $this->test->rollbackdb1($rs_goods['XA']);
        }
        
        print_r($rs_order);
        echo "<br />";
        print_r($rs_goods);
        die("dddd");
    }
    
    public function createorder($goods_id,$goods_name,$num){
        $XA = uniqid("");
        $this->_db->query("XA START &#39;$XA&#39;");
        $_rs = true;
        try {
            $data = array();
            $data[&#39;order_id&#39;] = "V".date("YmdHis");
            $data[&#39;goods_name&#39;] = $goods_name;
            $data[&#39;goods_num&#39;] = $num;
            $this->_db->insert("temp_orders",$data);
            $rs =  $this->_db->lastInsertId();
            if($rs){
                $_rs = true;
            }else{
                $_rs = false;
            }
        } catch (Exception $e) {
            $_rs = false;
        }
        $this->_db->query("XA END &#39;$XA&#39;");
         if($_rs){
                 $this->_db->query("XA PREPARE &#39;$XA&#39;");
                 return array("status"=>"success","XA"=>$XA);
         }else{
                 return array("status"=>"nosuccess","XA"=>$XA);
         }
    }
    
    public function deduction($id){
        $XA = uniqid("");
        $this->db1->query("XA START &#39;$XA&#39;");
        $last_rs = true;
        try {
                $sql = "select * from temp_goods where id = &#39;$id&#39; and goods_num>0";
                $rs = $this->db1->fetchRow($sql);
                if(!empty($rs)){
                    $sql = "update temp_goods set goods_num = goods_num-1 where id = &#39;$id&#39;";
                    $rd = $this->db1->query($sql);
                    if($rd){
                        $last_rs = true;
                    }else{
                        $last_rs = false;
                    }
                }else{
                        $last_rs = false;;
                }
        } catch (Exception $e) {
             $last_rs = false;;
        }
         $this->db1->query("XA END &#39;$XA&#39;");
         
         if($last_rs){
                 $this->db1->query("XA PREPARE &#39;$XA&#39;");
                 return array("status"=>"success","XA"=>$XA);
         }else{
                 return array("status"=>"nosuccess","XA"=>$XA);
         }
    
    }
    //提交事务！
    public function commitdb($xa){
        return $this->_db->query("XA COMMIT &#39;$xa&#39;");
    }
    
    //回滚事务
    public function rollbackdb($xa){
        return $this->_db->query("XA ROLLBACK &#39;$xa&#39;");
    }
    
    //提交事务！
    public function commitdb1($xa){
        return $this->db1->query("XA COMMIT &#39;$xa&#39;");
    }
    
    //回滚事务
    public function rollbackdb1($xa){
        return $this->db1->query("XA ROLLBACK &#39;$xa&#39;");
    }