php处理跨服务器事务

事务只用过最简单的：同个数据库不同表之间的事务。最近遇到了跨DB server的事务，记录下。

同个DB server上的不同数据库的单个事务

<?php

function getConn($ip, $port, $dbbane, $user, $password)
{
    $dsn = "mysql:host=$ip;port=$port;dbname=$dbbane;charset=utf8";
    $mysqli = new PDO($dsn, $user, $password);
    return $mysqli;
}


try {
    $mysqli = getConn('127.0.0.1', '3306', 'test', 'root', '');

    $mysqli->beginTransaction();

    $num1 = 0;
    $num2 = 0;
    try {
        $name = 'test' . rand(1, 999);
        $age = rand(1, 99);
        $dt = date('Y-m-d H:i:s');
        $sql = "INSERT into test.test_table (`name`,`age`,`create_time`) values ('$name',$age,'$dt');";
        $res = $mysqli->query($sql);
        $num1 = $res->rowCount();
        // $num1 = 0;

        $sql = "INSERT into test2.test_table (`name`,`age`,`create_time`)) values ('$name',$age,'$dt');";
        $res = $mysqli->query($sql);
        $num2 = $res->rowCount();
        // $num2 = 0;

        if ($num1 > 0 && $num2 > 0) {
            $mysqli->commit();

            echo 'COMMIT';
        } else {
            $mysqli->rollBack();

            echo 'ROLLBACK';
        }
    } catch (Exception $e) {
        $mysqli->rollBack();

        var_dump($e->getMessage());
    }
} catch (Exception $e) {
    var_dump($e->getMessage());
}
unset($mysqli);

可以正常提交或者回滚，数据记录正常，回滚的事务在DB中会造成实际记录自增id跳过的情况。

XA分布式事务

根据资料显示：只有Innodb引擎且设置隔离级别为可串行化时才可以使用XA事务，然而下面简单demo中隔离级别为可重复读，事务可以正常提交或回滚，数据记录正常。

<?php

function getPdoInfo($mysqli)
{
    $result = $mysqli->query("SELECT VERSION()");
    $row1 = $result->fetch();
    $version = $row1[0];

    $result = $mysqli->query("SELECT @@GLOBAL.TX_ISOLATION;");
    // $result = $mysqli->query("SELECT @@GLOBAL.TRANSACTION_ISOLATION;");
    $row2 = $result->fetch();
    $isolation = $row2[0];
    return compact('version', 'isolation');
}

function getConn($ip, $port, $dbbane, $user, $password)
{
    $dsn = "mysql:host=$ip;port=$port;dbname=$dbbane;charset=utf8";
    $mysqli = new PDO($dsn, $user, $password);
    return $mysqli;
}

try {
    $mysqli1 = getConn('127.0.0.1', '3306', 'test', 'root', '');
    $mysqli2 = getConn('192.168.12.12', '3306', 'test2', 'root', '');

    // var_dump(getPdoInfo($mysqli1));
    // var_dump(getPdoInfo($mysqli2));

    $xid = uniqid();
    $sql = "XA START '$xid'";
    $res = $mysqli1->query($sql);
    $res = $mysqli2->query($sql);

    $num1 = 0;
    try {
        $name = 'test' . rand(1, 999);
        $age = rand(1, 99);
        $dt = date('Y-m-d H:i:s');
        $sql = "INSERT into test.test_table (`name`,`age`,`create_time`) values ('$name',$age,'$dt');";
        $res = $mysqli1->query($sql);
        $num1 = $res->rowCount();
        // $num1 = 0;

        $sql = "INSERT into test2.test_table (`name`,`age`,`create_time`)) values ('$name',$age,'$dt');";
        $res = $mysqli2->query($sql);
        $num2 = $res->rowCount();
        // $num2 = 0;
    } catch (Exception $e) {
        var_dump($e->getMessage());
    }

    $sql = "XA END '$xid'";
    $mysqli1->query($sql);
    $mysqli2->query($sql);

    if ($num1 > 0 && $num2 > 0) {
        $sql = "XA PREPARE '$xid'";
        $mysqli1->query($sql);
        $mysqli2->query($sql);

        $sql = "XA COMMIT '$xid'";
        $mysqli1->query($sql);
        $mysqli2->query($sql);

        echo 'COMMIT';
    } else {
        $sql = "XA ROLLBACK '$xid'";
        $mysqli1->query($sql);
        $mysqli2->query($sql);

        echo 'ROLLBACK';
    }
} catch (Exception $e) {
    var_dump($e->getMessage());
}
unset($mysqli);

多个事务同时操作

突发奇想试了下多个事务同时提交或者回滚，事务可以正常提交或回滚，数据记录正常。

<?php

function getPdoInfo($mysqli)
{
    $result = $mysqli->query("SELECT VERSION()");
    $row1 = $result->fetch();
    $version = $row1[0];

    $result = $mysqli->query("SELECT @@GLOBAL.TX_ISOLATION;");
    // $result = $mysqli->query("SELECT @@GLOBAL.TRANSACTION_ISOLATION;");
    $row2 = $result->fetch();
    $isolation = $row2[0];
    return compact('version', 'isolation');
}

function getConn($ip, $port, $dbbane, $user, $password)
{
    $dsn = "mysql:host=$ip;port=$port;dbname=$dbbane;charset=utf8";
    $mysqli = new PDO($dsn, $user, $password);
    return $mysqli;
}


try {
    $mysqli1 = getConn('127.0.0.1', '3306', 'test', 'root', '');
    $mysqli1 = getConn('192.168.12.12', '3306', 'test2', 'root', '');

    // var_dump(getPdoInfo($mysqli1));
    // var_dump(getPdoInfo($mysqli2));

    $mysqli1->beginTransaction();
    $mysqli2->beginTransaction();

    $num1 = 0;
    $num2 = 0;
    try {
        $name = 'test' . rand(1, 999);
        $age = rand(1, 99);
        $dt = date('Y-m-d H:i:s');
        $sql = "INSERT into test.test_table (`name`,`age`,`create_time`) values ('$name',$age,'$dt');";
        $res = $mysqli1->query($sql);
        $num1 = $res->rowCount();
        // $num1 = 0;

        $sql = "INSERT into test2.test_table (`name`,`age`,`create_time`)) values ('$name',$age,'$dt');";
        $res = $mysqli2->query($sql);
        $num2 = $res->rowCount();
        // $num2 = 0;

        if ($num1 > 0 && $num2 > 0) {
            $mysqli1->commit();
            $mysqli2->commit();

            echo 'COMMIT';
        } else {
            $mysqli1->rollBack();
            $mysqli2->rollBack();

            echo 'ROLLBACK';
        }
    } catch (Exception $e) {
        $mysqli1->rollBack();
        $mysqli2->rollBack();

        var_dump($e->getMessage());
    }
} catch (Exception $e) {
    var_dump($e->getMessage());
}
unset($mysqli1);
unset($mysqli2);

只是不知道在高并发场景下，上面两种跨服务器的事务能否保持数据一致？