Yii2.0 operates transactions with multiple databases

I am like this now. The project has a lot of databases, all under the same mysql server, and there is db,db2,db3 in main.php. Now there is a storage operation like this. Table A comes from db2,B table and comes from db3,. When using transactions, it is this sentence:

$transaction = \Yii::$app->get("db2")->beginTransaction();

I don"t know whether to write get ("db2") or get (" db3") or anything else.

Transaction mysql yii2 php

Oct.23,2021

mysql pure transactions are not supported across libraries. If you really want to do it, you can use XA Transactions

1. Confirm whether XA transaction support is enabled

as above, "innodb_support_xa" means "ON" means it is enabled. Otherwise, you need to enable

first.

SET innodb_support_xa = ON

2.XA transaction implementation

$xid = uniqid("xa_"); // 

//id
\Yii::$app->db2->createCommand("XA START '$xid'")->execute();
\Yii::$app->db3->createCommand("XA START '$xid'")->execute();

try {

    // 
    $affectCount = \Yii::$app->db2->createCommand("UPDATE test SET aa='aa' WHERE id=1")->execute();
    if($affectCount == false) {
        throw new \Exception("db2");
    }

    $affectCount = \Yii::$app->db3->createCommand("UPDATE test2 SET bb='bb' WHERE id=1")->execute();
    if($affectCount == false) {
        throw new \Exception("db3");
    }

    //1:db2
    \Yii::$app->db2->createCommand("XA END '$xid'")->execute();
    \Yii::$app->db2->createCommand("XA PREPARE '$xid'")->execute();
    //1:$db3
    \Yii::$app->db3->createCommand("XA END '$xid'")->execute();
    \Yii::$app->db3->createCommand("XA PREPARE '$xid'")->execute();

    //2:
    \Yii::$app->db3->createCommand("XA COMMIT '$xid'")->execute();
    \Yii::$app->db3->createCommand("XA COMMIT '$xid'")->execute();

} catch(\Exception $e) {

    \Yii::$app->db2->createCommand("XA ROLLBACK '$xid'")->execute();
    \Yii::$app->db3->createCommand("XA ROLLBACK '$xid'")->execute();
    die($e->getMessage());
}

Code implementation refers to the following article for reference.
https://blog.csdn.net/soonfly.

is not tested.

in fact, the use of transactions can greatly affect performance, such as non-business is special enough, be sure to use distributed transactions is not recommended, even single-library transactions are also used with caution.

XA transaction support is limited to the InnoDB storage engine.
XA transactions are only supported in the InnoDB storage engine
Prior to MySQL 5.7.7, XA transactions were not compatible with replication at all. This was because an XA transaction that was in PREPARED state would be rolled back on clean server shutdown or client disconnect. Similarly, an XA transaction that was in PREPARED state would still exist in PREPARED state in case the server was shutdown abnormally and then started again, but the contents of the transaction could not be written to the binary log. In both of these situations the XA transaction could not be replicated correctly.
official documentation states that before mysql version 5.5.7, XA did not fully guarantee consistency.

it is reasonable to ensure the ultimate consistency of transactions by asynchronous means.

mysql should not support distributed transactions.

Previous: Why is the definition of bootstrap raster less source code added to avoid compilation

Next: Vue-cli3.0 add package error 4058 recoveryrename