Welcome to Tencent cloud technology community, get more Tencent mass technology practice dry goods oh ~
Recently, Tencent Cloud released distributed database solution (DCDB), one of its most obvious features is to provide higher performance than the open source distributed transaction XA. Big business system with the user, the concurrent high characteristic, in this regard, a centralized database (standalone) database performance is difficult to support, so the mainstream Internet companies often use distributed database (architecture), physical use more low-end equipment, logic level on the table split support business needs.
Although distributed database can solve the performance problem, the problem of transaction Consistency is difficult to be solved on distributed database.
Distributed transactions are the biggest problem
Data consistency is difficult to achieve
As is known to all, updates made by a transaction are completed by multiple independent data nodes within a distributed database system (the local transaction of each node is a transaction branch of the global transaction). During such a global transaction commit, it is possible that some transaction branches cannot commit successfully.
For this problem, although the industry has already existed a theoretical solution — two-phase commit protocol (2PC), and extended the distributed transaction (XA) solution. However, there are few engineering implementation and large-scale application cases in the industry. Tencent cloud distributed database DCDB, but has been used in internal business for many years.
(Figure: Two-stage submission algorithm)
At present, DCDB has been applied in more than 90% of the transaction and billing business of Tencent, and sany Heavy Industry (Shutian Internet), Huitong Tianxia (G7), China Literature group (Qidian/Chuangdian, etc.), Webank, Hetai Life Insurance, Vifortis, etc., are all in this product.
Tencent cloud first distributed database XA
Supports MySQL 5.7
Tencent cloud distributed database DCDB is a distributed database compatible with MySQL protocol based on Tencent financial database (internal code TDSQL) cloud transformation. Now, Tencent Cloud DCDB has officially supported distributed transaction XA in MySQL 5.7(Percona branch) protocol, and has been released in Tencent cloud public cloud, financial cloud for developers to use. Developers can apply for a DCDB instance and run the following SQL to initialize the DCDB instance:
MySQL> xa init;
Query OK, 0 rows affected (0.03 SEC) Note: Before xa is initialized, enable strong synchronous replication and the SQL will create Xa.gtid_log_t. Do not perform any operations on it.
To better support distributed transactions, DCDB also added SQL commands:
1)SELECT gTID (), get the gTID of the current distributed transaction (globally unique identifier of the transaction), return null if the transaction is not a distributed transaction; Gtid format: ‘Gateway ID’ – ‘Random gateway value’ – ‘serial number’ – ‘timestamp’ – ‘partition number’, for example, C46535FE-B6-DD-595DB6B8-25
SELECT gtid_state(” gtid “) from gtid_state(” gtid “) A) COMMIT, indicating that the transaction has been or will eventually be committed b) ABORT, indicating that the transaction will eventually be rolled back c) null. Since the status of the transaction is clear after an hour, there are two possibilities: 1) After one hour, it indicates that the transaction status has been cleared. 2) After one hour, it indicates that the transaction will be rolled back
Xa lockwait: displays the wait relationship of the current distributed transaction (you can use the dot command to convert the output to the wait relationship diagram) XA show: indicates the distributed transaction running on the current gateway
Using Python as an example, you can encode the transfer service as follows:
db = pyMySQL.connect(host=testHost,
port=testPort, user=testUser, password=testPassword, database=testDatabase)
cursor = db.cursor()
try:
cursor.execute("begin")
# Subtract 1 from Bob's balance for one account
query = "update t_user_balance SET balance = balance - 1 where user='Bob' and balance>1) affected = cursor.execute(query) if Affected == 0: rollback transaction cursor.execute("rollback") return # add 1 query ="update t_user_balance SET balance = balance + 1 where user='John')
cursor.execute(query)
SELECT gtid() to obtain the id of the current transaction
# commit transaction
cursor.execute("commit")
except pyMySQL.err.MySQLError as e:
Rollback transaction failed
cursor.execute("rollback")
Copy the codeThe benefit of distributed transactions is that application development is greatly reduced, because in some databases that do not support XA, business systems need to solve problems such as data inconsistencies in transactions through special and clever design rather than using the database. The more complex the business system, the more it will increase the development cost and technical threshold, which is the main reason why most developers in the industry can only be discouraged when facing the distributed database.
Tencent cloud DCDB XA key implementation scheme
1. DCDB architecture Introduction The overall cluster architecture of Tencent cloud DCDB is shown in the following figure. MySQL adopts the configuration of primary and secondary nodes (also known as primary and secondary nodes).
The DCDB backend is MySQL (or its branch version) database. Currently, Tencent Cloud public cloud releases XA supported version based on MySQL 5.7.17 (Percona branch).
2. TProxy and XA gateway are network modules used to receive requests and establish connections with back-end MySQL. The gateway can work in two modes, one called noshard, in which the gateway does not process/parse SQL statements and transparently forwards requests and replies. In another mode, called SHARD (distributed, which supports automatic horizontal shards) mode, TProxy parses the SQL and forwards it to different data shards.
Prior to XA, the gateway was not allowed to send DML statements to multiple sets in a single transaction. Because a transaction takes a one-phase commit when two-phase commit (2PC) is not implemented, the distributed transaction is in an inconsistent state if a SET commit in the distribution fails or is rolled back.
(How the gateway works)
The transaction manager (TM) required in a two-phase commit. In order to solve disaster recovery and simplify the architecture, Tencent Cloud DCDB implements TM in TProxy, and THE gateway of DCDB is a stateless module. Through this architecture, DCDB XA can support:
(1), a distributed transaction of business transparency, compatible with single transaction grammar (start transaction/commit/rollback/savepoint);
(2) Each gateway can independently accept and process transaction requests, and there is no need to coordinate with other gateways to avoid transaction loss due to node failure;
(3) Allow multiple statements in explicit transactions to be sent to multiple fragments;
(4) The gateway does not need persistent state or Dr, and can exit or join the cluster through the scheduling cluster at any time, and its performance can be expanded;
(5) Support autoCOMMIT for a single statement to write access to multiple fragments.
DCDB gateway also allows group by and Order BY to be run in streaming mode, which makes these operations very efficient and efficient. The gateway also supports equivalent joins between two Shards using shardkey and sub-queries using Shardkey.
In the future, Tencent Cloud also plans to support advanced functions such as distributed JOIN, Sparksql, secondary partitioning, and more advanced MySQL syntax.
3. Strong synchronization and XA By default, Tencent cloud DCDB adopts strong synchronous replication, that is, data on the master and slave nodes is completely consistent. Therefore, XA transactions also follow the logic of strong synchronization, that is, they wait for the slave machine to confirm data synchronization before committing services. Based on strong synchronization, DCDB XA can be easily handled in the following two abnormal situations.
(1) When the master node fails, it is confirmed that the transaction data will not be lost: if the master node fails, the slave machine with the latest data and binlog will be selected as the master node, including all the data of transactions that have been confirmed to be completed and submitted to the user.
(2), the owner had recovered to rejoin the cluster nodes and unrecognized transaction automatically flashback: the owner node back to access the cluster, it will be from the machine running, he might have saved excess committed transactions at this time (transaction at this time did not have strong synchronization synchronous confirmation, namely the original standby and no relevant data, then these transactions will be flashback. Although the transaction may have committed within MySQL on the original master node, due to strong synchronization, it does not return a COMMIT statement to the client, meaning it is still considered an unfinished transaction. Therefore, the flashbacks of these transactions do not break the ACID property of the database. Rollback is not the same as database rollback. Rollback is the same as DDL operation.
The strong synchronization of Tencent cloud DCDB is a capability developed by Tencent financial database, and its performance is significantly improved compared with official semi-synchronization, almost equal to asynchronous replication performance. Tencent cloud DCDB has been applied in Tencent for many years, without any data error caused by master-slave switchover or failure. Moreover, in terms of performance, it also supports the massive concurrency of various large-scale operation activities of Tencent company, such as red envelopes and large-scale promotion of various games. The main reason is that strong synchronization adopts asynchronous submission/waiting mode, and does not occupy database working threads.
To balance data consistency and performance, the key to distributed transactions is database isolation control. The isolation level of XA can be up to Serializable, where there will be no phantom read problems, The serialIZABLE level can be SET to all physical fragments of DCDB (and the MySQL database hosted on them) by setting SET global TX_ISOLATION =’ serialIZABLE ‘. Of course, you can adjust the isolation level to tune database instance performance. Theoretically, Read Uncommitted has the highest performance, but dirty and phantom reads can occur.
ANSI/ ISO-defined SQL-92
Four isolation levels defined by the standard
5. Distributed transaction processing algorithm As mentioned above, Tencent cloud DCDB gateway has been able to parse SQL statements in SHARD mode, and Tencent Cloud implements TM on the gateway to make XA most efficient. To this end, we can implement the COORDINATOR in TM in the gateway, maintain the state of each XA in the gateway, record the SET written by each XA, and then commit in two stages in the submission stage. The general process is as follows:
(1) When the gateway executes a transaction insert/update/ DELETE statement, it records which SET the statement modifies;
(2) An XA START is sent on a SET to START the transaction branch; (Note: When an XA transaction is started, it is not determined which commit mode the transaction will be executed, so a transaction is always started with XA Start);
(3) Check whether the number of sets is less than or equal to 1. If so, perform XA Commit one phase directly.
(4) If the number of sets is greater than or equal to 2, change to two-stage commit:
1) The gateway first sends xa prepare ‘gTID’ to the participating SET (at least 2 sets); 2) The SET receives an XA prepare reply OK (confirming success). 3) After receiving the confirmation of success, write the commit log corresponding to XA and send XA commit ‘gTID’ to participate in the SET. 4) If a SET returns an error or fails to write a commit log, then the gateway sends XA rollback ‘gTID’ to the relevant SET, and the global transaction is rolled back.
The commit log of Tencent cloud DCDB is stored in the SET. This step is completed in batches — the gateway background thread will collect the distributed transaction being committed and then complete the writing of each SET in the independent connection and transaction, and the commit log of each transaction is only written into one SET. Therefore, this overhead does not significantly increase transaction commit time or decrease TPS. In addition, depending on the existing strong synchronization and disaster recovery features of Tencent cloud DCDB, as long as XA successfully writes a Commit log, it means that data has been written to the slave machine.
Although the vast majority of XA transactions can execute normally. However, a few exceptions will still affect the stability of the whole cluster. Therefore, Tencent Cloud designed agent (monitoring module) to continue to assist local MySQL to complete the prepared transaction submission after the failure, that is, agent will parse the commit log. And handle local transactions that are still prepared according to the exception. The Agent will also roll back prepared local transactions that have timed out and not been committed if there is no commit decision in the commit log.
Although XA has been implemented in MySQL 5.5, 5.6 and other versions, these two versions still have poor performance compared to 5.7. Therefore, Tencent Cloud only supports XA version on public cloud based on 5.7.17. At present, Tencent Cloud has made a lot of optimization and related bug fixes in MySQL, Percona, MariaDB and other branches (some of them have been submitted to the community to repair patch or open source). In the future, Tencent Cloud will continue to be committed to the development of new features and related bug fixes, so as to help many enterprises in need. Provide better distributed database support.
Has been authorized by the author tencent cloud community released, reproduced please indicate the article source The original link: cloud.tencent.com/community/a… Get more Tencent mass technology practice dry goods, welcome to Tencent cloud technology community