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.
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
SET innodb_support_xa = ON2.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.
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 enginePrior 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.