MySQL transaction Learning -- & gt; Distributed Transaction

Distributed transactions

The Innodb Storage engine supports XA transactions and distributed transactions. Distributed Transactions allow multiple independent transaction resources to participate in a global transaction. Transaction resources are usually relational database systems or other types of resources.

 

Global transactions require that all the transactions involved in the transaction be either committed or rolled back. This improves the original ACID Requirements of the transaction. In addition, when using distributed transactions, the transaction isolation level of the InnoDB Storage engine must be set to serialiable.

 

XA Transactions allow distributed transactions between different databases. For example, one server is a mysql database, the other is an Oracle database, and the other may be an sqlserver, as long as each node in the global transaction supports XA transactions. Distributed transactions may be more common in the bank system transfer, for example, a user needs to transfer from Shanghai to a user account in Beijing for RMB 1000:

# Bank ofshanghai:

Updateuser_account set money = money-10000 where user = 'xiaozhang ';

# Bank ofBeijing:

Updateuser_account set money = money + 10000 where user = 'xiaoli ';

In this case, distributed transactions are required, either committed or rolled back. If a problem occurs on any node, the result is: 1 xiaozhang's account is deducted, but xiaoli does not receive the money; 2 xiaozhang's account is not deducted, but xiaoli receives the money.

 

A distributed Transaction is composed of one or more Resource managers, One Transaction Manager, and one Application Program.

Resource Manager: provides methods to access transaction resources. Generally, a database is a resource manager.

Transaction Manager: coordinates various transactions involved in global transactions. You need to communicate with the resource manager involved in the global transaction.

Application: defines the transaction boundary and specifies the operations in the global transaction.

In a Distributed Transaction in mysql, the resource manager is the mysql database, and the transaction manager is the Client Connected to the mysql server. As shown in:

 

 

Distributed transactions use the two-phase commit method. In the first phase, all nodes involved in the global transaction begin to prepare and tell the Transaction Manager that they are ready for submission. In the second stage, the transaction manager tells the resource manager to execute rollback or commit. If any node cannot be displayed as commit, all nodes must be rollback.

The current java jta java transaction API can well support mysql distributed transactions. For details, refer to the jta manual. The following example shows how to use jta to call mysql distributed transactions.

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration