Transaction Management #
Transaction Principle #
While SphereEx-DBPlusEngine aims to support all distributed transaction scenarios and achieve optimal performance, it’s important to consider that distributed transactions are guided by the CAP theorem, and trade-offs are inevitable. The most suitable distributed transaction solution varies depending on the application scenario. Developers need to carefully consider the trade-offs between performance and functionality for different distributed transactions based on their application scenarios.
It’s important to note that strongly consistent transactions and flexible transactions have different APIs and functionalities, and there is no easy switch between them. Developers need to choose between strongly consistent transactions and flexible transactions at the development stage, which can increase the design and development costs significantly.
Strongly consistent XA-based transactions are relatively easy to use, but they are not well suited to the high concurrency of the Internet or the long transactions of complex systems. Flexible transactions require developers to adapt the application and can be expensive to access, as well as requiring developers to implement resource locking and reverse compensation themselves.
Transaction Classification #
SphereEx-DBPlusEngine provides three transaction modes: LOCAL, XA and BASE, to cope with different scenarios.
LOCAL: for scenarios that do not require high data consistency.XA: provides a guarantee of atomicity, ensuring that no data is lost and no snapshot reads are guaranteed. Suitable for scenarios with relatively high requirements for consistency and no snapshot read requirements. In scenarios with high consistency requirements, the better option is to use the Narayana implementation of XA. Strong consistency semantics can be achieved if the underlying storage node’s database isolation level is Serializable.BASE: There is a trade-off between consistency and performance. See the Seata website for details.
Usage #
SphereEx-DBPlusEngine can be easily configured using the server.yaml configuration file. The primary purpose of this file is to configure the transaction processing method and the distributed transaction processing mechanism.
In the transaction processing method section of the configuration file, developers can choose between two options: XA-based transactions or flexible transactions. XA-based transactions are strongly consistent but may not be suitable for high-concurrency Internet applications or complex systems that involve long transactions. On the other hand, flexible transactions require developers to adapt their applications and implement resource locking and reverse compensation, which can be more costly and time-consuming.
In the distributed transaction processing mechanism section of the configuration file, developers can set the parameters for distributed transactions, including transaction timeout, retry times, and transaction log storage location.
By configuring these options in the server.yaml file, developers can optimize their use of SphereEx-DBPlusEngine for their specific application scenarios:
- defaultType
Use LOCAL Transactions
transaction:
defaultType: LOCAL
Use XA Transactions
transaction:
defaultType: XA
Use BASE Transactions
transaction:
defaultType: BASE
- providerType
Only applicable in case of defaultType=XA. SphereEx-DBPlusEngine supports several distributed transaction processors: Atomikos, Narayana, Bitronix. And Narayana is the recommended choice for users.
transaction:
defaultType: XA
providerType: Narayana
Notes #
Local Transactions #
- Supported Items:
- Full support for non-cross-database transactions, such as split tables only or split but routed results in a single database.
- Fully supported cross-database transactions due to logical exceptions, such as updates across two databases in the same transaction. When the update is complete and a null pointer is thrown, the contents of both databases can be rolled back.
- Unsupported Items:
- Cross-database transactions caused by network or hardware exceptions are not supported. For example, if an update is made across two databases in the same transaction and the first database goes down after the update is complete and before it is committed, only the second database data is committed and
XA Transaction #
- Support Items:
- Support cross-database transactions after data slicing to ensure seamless data transactions between different libraries.
- Ensure atomicity of operations and strong data consistency with two-phase commits.
- Enable automatic recovery of transactions in commit/rollback after service downtime and restart for uninterrupted service.
- Allow the simultaneous use of XA and non-XA connection pools for flexible usage.
- Unsupported Items:
- Recovery of data from a commit/rollback on other machines after a service downtime is not supported.
- MySQL transaction blocks within which SQL execution is abnormal and Commit is executed and data remains consistent are not supported.
- The maximum length of a storage unit name is limited to 45 characters after configuring an XA transaction.
- Permissions Required for XA Transactions:
- The required permission for XA transactions in MySQL 8 is to grant the user the XA_RECOVER_ADMIN privilege. Without this privilege, the XA transaction manager may encounter errors when executing the XA RECOVER statement.
Base Transactions #
- Support Items:
- Support cross-library transactions after data sharding for seamless data transactions between different libraries.
- Support for Read Committed (RC) isolation levels for enhanced data consistency and reliability.
- Enable transaction rollback via undo snapshots for improved data recovery and error handling.
- Allow automatic recovery of committed transactions after service downtime for uninterrupted service.
- Unsupported Items:
- Isolation levels other than RC are not supported at this time.
- Items to be Optimized:
- SphereEx-DBPlusEngine and SEATA have duplicate SQL parsing that could be optimized for improved performance and efficiency.
Distributed Transactions in Practice #
Narayana Usage #
For common distributed transaction requirements, SphereEx-DBPlusEngine recommends using Narayana’s implementation of distributed transactions as follows.
Although Narayana’s configuration can be cumbersome, DBPlusEngine-Proxy provides an automated configuration function. With this function, users no longer need to manually configure the jbossts-properties.xml configuration file. Instead, DBPlusEngine will automatically generate the corresponding jbossts-properties.xml configuration file based on the Narayana transaction configuration specified by the user in server.yaml.
- server.yaml
transaction:
defaultType: XA
providerType: Narayana
When using Narayana as the XA Transaction Manager and configured to store XA Recovery information using the DB method, DBPlusEngine-Proxy supports the transfer of unrecovered transactions from the failed Proxy instance to another Proxy for recovery.
The configuration is as follows:
transaction:
defaultType: XA
providerType: Narayana
props:
recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true# mysql8 使用 com.mysql.cj.jdbc.MysqlDataSourcerecoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
recoveryStoreUser: databaseUser
recoveryStorePassword: databasePwd
Distributed Transactions Controlled by XA statements #
XA transactions can be opened manually via XA START, noting that the transaction is entirely user managed and DBPlusEngine only forwards the statements to the back-end database.
When the service is down, uncommitted or rolled back transactions need to be fetched via XA RECOVER, or PERPARE can be skipped using ONE PHASE at COMMIT.
MySQL [(none)]> use test1 │MySQL [(none)]> use test2
Reading table information for completion of table and column names │Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A │You can turn off this feature to get a quicker startup with -A
│
Database changed │Database changed
MySQL [test1]> XA START '61c052438d3eb'; │MySQL [test2]> XA START '61c0524390927';
Query OK, 0 rows affected (0.030 sec) │Query OK, 0 rows affected (0.009 sec)
│
MySQL [test1]> update test set val = 'xatest1' where id = 1; │MySQL [test2]> update test set val = 'xatest2' where id = 1;
Query OK, 1 row affected (0.077 sec) │Query OK, 1 row affected (0.010 sec)
│
MySQL [test1]> XA END '61c052438d3eb'; │MySQL [test2]> XA END '61c0524390927';
Query OK, 0 rows affected (0.006 sec) │Query OK, 0 rows affected (0.008 sec)
│
MySQL [test1]> XA PREPARE '61c052438d3eb'; │MySQL [test2]> XA PREPARE '61c0524390927';
Query OK, 0 rows affected (0.018 sec) │Query OK, 0 rows affected (0.011 sec)
│
MySQL [test1]> XA COMMIT '61c052438d3eb'; │MySQL [test2]> XA COMMIT '61c0524390927';
Query OK, 0 rows affected (0.011 sec) │Query OK, 0 rows affected (0.018 sec)
│
MySQL [test1]> select * from test where id = 1; │MySQL [test2]> select * from test where id = 1;
+----+---------+ │+----+---------+
| id | val | │| id | val |
+----+---------+ │+----+---------+
| 1 | xatest1 | │| 1 | xatest2 |
+----+---------+ │+----+---------+
1 row in set (0.016 sec) │1 row in set (0.129 sec)
MySQL [test1]> XA START '61c05243994c3'; │MySQL [test2]> XA START '61c052439bd7b';
Query OK, 0 rows affected (0.047 sec) │Query OK, 0 rows affected (0.006 sec)
│
MySQL [test1]> update test set val = 'xarollback' where id = 1; │MySQL [test2]> update test set val = 'xarollback' where id = 1;
Query OK, 1 row affected (0.175 sec) │Query OK, 1 row affected (0.008 sec)
│
MySQL [test1]> XA END '61c05243994c3'; │MySQL [test2]> XA END '61c052439bd7b';
Query OK, 0 rows affected (0.007 sec) │Query OK, 0 rows affected (0.014 sec)
│
MySQL [test1]> XA PREPARE '61c05243994c3'; │MySQL [test2]> XA PREPARE '61c052439bd7b';
Query OK, 0 rows affected (0.013 sec) │Query OK, 0 rows affected (0.019 sec)
│
MySQL [test1]> XA ROLLBACK '61c05243994c3'; │MySQL [test2]> XA ROLLBACK '61c052439bd7b';
Query OK, 0 rows affected (0.010 sec) │Query OK, 0 rows affected (0.010 sec)
│
MySQL [test1]> select * from test where id = 1; │MySQL [test2]> select * from test where id = 1;
+----+---------+ │+----+---------+
| id | val | │| id | val |
+----+---------+ │+----+---------+
| 1 | xatest1 | │| 1 | xatest2 |
+----+---------+ │+----+---------+
1 row in set (0.009 sec) │1 row in set (0.083 sec)
MySQL [test1]> XA START '61c052438d3eb';
Query OK, 0 rows affected (0.030 sec)
MySQL [test1]> update test set val = 'recover' where id = 1;
Query OK, 1 row affected (0.072 sec)
MySQL [test1]> select * from test where id = 1;
+----+---------+
| id | val |
+----+---------+
| 1 | recover |
+----+---------+
1 row in set (0.039 sec)
MySQL [test1]> XA END '61c052438d3eb';
Query OK, 0 rows affected (0.005 sec)
MySQL [test1]> XA PREPARE '61c052438d3eb';
Query OK, 0 rows affected (0.020 sec)
MySQL [test1]> XA RECOVER;
+----------+--------------+--------------+---------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+---------------+
| 1 | 13 | 0 | 61c052438d3eb |
+----------+--------------+--------------+---------------+
1 row in set (0.010 sec)
MySQL [test1]> XA RECOVER CONVERT XID;
+----------+--------------+--------------+------------------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------------------------------+
| 1 | 13 | 0 | 0x36316330353234333864336562 |
+----------+--------------+--------------+------------------------------+
1 row in set (0.011 sec)
MySQL [test1]> XA COMMIT 0x36316330353234333864336562;
Query OK, 0 rows affected (0.029 sec)
MySQL [test1]> XA RECOVER;
Empty set (0.011 sec)