Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to implement distributed transactions in Mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Mysql has supported distributed transactions since 5. 0

Let's first declare two concepts:

Resource Manager (resource manager): used to manage system resources and is the path to transactional resources. Database is a kind of resource manager. Resource management should also have the ability to manage transaction commit or rollback.

Transaction manager (transaction manager): the transaction manager is the core manager of distributed transactions. Transaction managers and each resource manager (resource

Manager) to communicate, coordinate and complete transaction processing. Each branch of a transaction is identified by a unique name.

When mysql executes distributed transactions (external XA), the mysql server is equivalent to the xa transaction resource manager, and the client linked to mysql is the transaction manager.

The principle of distributed transaction: segmented commit

Distributed transactions usually use 2PC protocol, the full name is Two Phase Commitment Protocol. The main purpose of this protocol is to solve the problem of data consistency among all nodes in the distributed database scenario. The distributed transaction divides the commit into two phases through the 2PC protocol:

Prepare

Commit/rollback

Phase one is the prepare phase. That is, all participants are ready to execute the transaction and lock the required resources. When the participant ready, report to transaction manager that you are ready.

Phase two is the submission phase (commit). When transaction manager confirms that all participants are ready, it sends a commit command to all participants.

Transaction coordinator transaction manager

Because XA transactions are based on a two-phase commit protocol, a transaction coordinator (transaction manager) is needed to ensure that all transaction participants have completed their preparations (phase one). If the transaction coordinator (transaction manager) receives a message that all participants are ready, it notifies that all transactions are ready to commit (phase 2). MySQL plays the role of a participant, not a transaction manager, in this XA transaction.

XA transactions of Mysql are divided into external XA and internal XA.

External XA is used for distributed transactions across multiple MySQL instances, requiring the application layer as the coordinator. In popular terms, for example, if we write code in PHP, then the logic of PHP writing is the coordinator. The application layer is responsible for deciding whether to commit or roll back and suspend transactions in the event of a crash. External XA of MySQL database can be used in distributed database proxy layer to support distributed transactions of MySQL database, such as open source proxy tools: NetEase's DDB, Taobao's TDDL and so on.

Internal XA transactions are used for multi-engine transactions under the same instance, with Binlog as the coordinator. For example, when a storage engine commits, the commit information needs to be written to the binary log, which is a distributed internal XA transaction, except that the participant of the binary log is MySQL itself. Binlog acts as the coordinator of the internal XA, and binlog is responsible for submitting the internal xid that appears in the binlog at the time of crash recover. (this is because binlog does not prepare, only commit, so the internal xid that appears in binlog must be able to guarantee that it has completed prepare in the underlying storage engines.)

Syntax for mysql xa transactions

1. First, make sure that mysql enables XA transaction support.

SHOW VARIABLES LIKE'% xa%'

If the value of innodb_support_xa is ON, mysql has enabled support for XA transactions. If not, execute:

SET innodb_support_xa = ON

The main ones are:

XA START 'any_unique_id'; / /' any_unique_id' is given by the user and uniquely opens a XA transaction in a mysql XA END 'any_unique_id'; / / identifies the end of the operation of the XA transaction XA PREPARE' any_unique_id'; / / tells mysql to prepare to commit the xa transaction XA COMMIT 'any_unique_id'; / / tells mysql to commit the xa transaction XA ROLLBACK' any_unique_id' / / tell mysql to roll back this xa transaction XA RECOVER;// to see which xa transactions in native mysql are currently in prepare state

XA transaction recovery

If the mysql crash of the distributed transaction is executed, the mysql restores according to the following logic:

a. If the xa transaction is commit, then nothing needs to be done.

b. If the xa transaction does not already have a prepare, roll it back directly

c. If the xa transaction is prepare and does not have commit, then restore it to the state of prepare, and it is up to the user to decide whether to commit or rollback

When you restart after mysql crash, execute "XA RECOVER;" to see the xa transactions that are currently in the prepare state, and then commit or rollback them.

Use restriction

A. XA transactions and local transactions and lock table operations are mutually exclusive

Local transactions and locking table operations cannot be used when xa transactions are turned on

Mysql > xa start 't1xa question OK, 0 rows affected (0.04 sec) mysql > begin;ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE statemysql > lock table T1 read;ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state

Xa transactions cannot be used when local transactions are turned on

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > xa start 'rrrr';ERROR 1400 (XAE09): XAER_OUTSIDE: Some work is done outside global transaction

B. you must xa end after xa start, otherwise you cannot execute xa commit and xa rollback

So if there is a statement error during the execution of a xa transaction, you also need to xa end before you can xarollback.

Matters needing attention

A. mysql only provides the interface for xa transactions, and mysql instances in distributed transactions are independent and unaware of each other. So the user must implement the scheduler of distributed transaction by himself.

B. xa transactions have some bug in use, refer to http://www.mysqlops.com/2012/02/24/mysql-xa-optimize.html

Mainly

"the synchronization of the primary and standby databases of the MySQL database is completed through the replication of Binlog. Binlog is the coordinator of XA transactions within the MySQL database, and the MySQL database is optimized for binlog-binlog does not write prepare logs, only commit logs.

All participating nodes prepare completed, crash before xa commit. Crash recover if you choose commit for this transaction. Because binlog was not written in the prepare phase, it seems that the distributed transaction was finally committed in the main database, but the operation of this transaction was not written to the binlog, so it could not be successfully copied to the standby database, resulting in data inconsistency between the master and backup database.

On the other hand, if crash recover chooses rollback, there will be global inconsistency (some of the nodes corresponding to the distributed transaction have been committed and cannot be rolled back, while some nodes are rolled back. Eventually lead to the same distributed transaction, in each participating node, the final state is inconsistent) "

The solution given in the referenced blog is to modify the mysql code, which cannot be used in DBScale. So the alternative is not to use

Master-slave replication is used for backup, but xa transaction implementation is used to write synchronously as backup.

Implementing distributed transaction cases with php+mysql

Make sure the datasheet is innodb

/ / CREATE TABLE `tusername` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'id', `username`username` varchar) NOT NULL DEFAULT' 'COMMENT' username', `money`int (11) NOT NULL DEFAULT'0' COMMENT 'account amount', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 under the db_ financelibrary / / CREATE TABLE `twithholding username _ orders` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `username` varchar (255) NOT NULL DEFAULT'', `money`int (11) NOT NULL DEFAULT'0' COMMENT 'order withholding amount', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 under the order library

Php code

$username = 'gentle wind'; $order_money = 100 leadership addOrderwinds success = addOrder ($username,$order_money); $upAccount_success = updateAccount ($username,$order_money); if ($addOrder_success ['state'] = = "yes" & & $upAccount_success [' state'] = = "yes") {commitdb ($addOrder_success ['xa']); commitdb1 ($upAccount_success [' xa']);} else {rollbackdb ($addOrder_success ['xa']); rollbackdb1 ($upAccount_success [' xa']);} die Function addOrder ($username,$order_money) {$xa = uniqid (""); $sql_xa = "XA START'$xa'"; $db = Yii::app ()-> dborder_readonly; $db- > createCommand ($sql_xa)-> execute (); $insert_sql = "INSERT INTO t_user_orders (`username`, `money`) VALUES ($username,$order_money)"; $id = $db- > createCommand ($insert_sql)-> execute (); $db- > createCommand ("XA END'$xa'")-> execute () If ($id) {$db- > createCommand ("XA PREPARE'$xa'")-> execute (); return ['state' = >' yes', 'xa' = > $xa];} else {return [' state' = > 'no',' xa' = > $xa];} function updateAccount ($username, $order_money) {$xa = uniqid ("); $sql_xa =" XA START'$xa' "; $db = Yii::app ()-> db_finance $db- > createCommand ($sql_xa)-> execute (); $sql = "update t_user_account set money=money-". $order_money. " Where username='$username' "; $id = $db- > createCommand ($sql)-> execute (); $db- > createCommand (" XA END'$xa' ")-> execute (); if ($id) {$db- > createCommand (" XA PREPARE'$xa' ")-> execute (); return ['state' = >' yes', 'xa' = > $xa];} else {return [' state' = > no', 'xa' = > $xa];} / / commit transaction! Function commitdb ($xa) {$db = Yii::app ()-> dborder_readonly; return $db- > createCommand ("XA COMMIT'$xa'")-> execute ();} / / rollback transaction function rollbackdb ($xa) {$db = Yii::app ()-> dborder_readonly; return $db- > createCommand ("XA COMMIT'$xa'")-> execute ();} / / commit transaction! Function commitdb1 ($xa) {$db = Yii::app ()-> db_finance; return $db- > createCommand ("XA COMMIT'$xa'")-> execute ();} / rollback transaction function rollbackdb1 ($xa) {$db = Yii::app ()-> db_finance; return $db- > createCommand ("XA ROLLBACK'$xa'")-> execute ();} after reading the above, do you have any further understanding of how to implement distributed transactions in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.