A method, apparatus, storage medium, and electronic device for outputting database transactions.

Abstract

This specification discloses a method for outputting database transactions. For each log stream, after determining the transaction fragments and their order, each transaction fragment in the log stream is added to an assembly queue according to the order. The order of the most recently added transaction fragments in the assembly queue is recorded as the assembly progress for that log stream. A global safety point is determined based on the current assembly progress for each log stream. After the assembly queue assembles the transaction fragments into transactions, for each transaction, based on the transaction's commit version and the current global safety point, it is determined whether to output the transaction to downstream nodes for consumption. By controlling the output of transactions through the global safety point, the consistency and continuity of transactions output to downstream nodes can be guaranteed, thereby enabling the entire system to have good recovery capabilities.

Description

Technical Field

[0001] This specification relates to the field of computer technology, and in particular to a method, apparatus, storage medium, and electronic device for outputting database transactions. Background Technology

[0002] Currently, distributed database systems are widely used in various industries. In accordance with the Write-Ahead Logging (WAL) principle of modern database system storage engines, log streams are usually used to manipulate the data in the database instance stored by the database system.

[0003] Specifically, when a distributed database system receives an SQL statement for manipulating data, it first generates a log record based on the SQL statement and adds the log record to the log stream. Then, it assembles the log records in the log stream into a database transaction. Finally, it commits the database transaction while ensuring atomicity, and executes the database transaction based on the log records in the database transaction to complete the data operation.

[0004] Besides serving the internal database environment, enabling data manipulation within the distributed database system, log streams can also be used by external ecosystems. Change Data Capture (CDC) tools can capture these log streams, identify changed data within the distributed database system, and provide this information to downstream nodes in the form of transactions. For example, a downstream node could act as a backup database, synchronizing changed data from the distributed database system to that backup database via the log stream. Downstream nodes can also serve as data auditing nodes, auditing the data change process based on transactions.

[0005] However, in real-world applications, system anomalies are almost unavoidable. Therefore, ensuring a system's robust recovery capabilities is a pressing issue that needs to be addressed. Summary of the Invention

[0006] This specification provides a database transaction output method, apparatus, storage medium, and electronic device to partially solve the problems existing in the prior art.

[0007] The embodiments in this specification adopt the following technical solutions: This specification provides a method for outputting database transactions, the method comprising: Obtain the log streams generated by the coordinator in the distributed database system; For each log stream, determine the transaction shards in that log stream and their order. According to the order of each transaction fragment, each transaction fragment is added to the assembly queue in sequence, and the order of the transaction fragment most recently added to the assembly queue in the log stream is recorded as the assembly progress of the log stream. Determine the global security location based on the assembly progress of each log stream. After the transaction fragments in the assembly queue are assembled into a transaction through the assembly queue, for each assembled transaction, the commit version of the transaction is determined according to the order of each transaction fragment in its respective log stream. Based on the commit version of the transaction and the current global security position, it is determined whether to output the transaction to the downstream node for consumption.

[0008] This specification provides a database transaction output device, the device comprising: The acquisition module is used to acquire the log streams generated by the coordinator in the distributed database system; The sharding determination module is used to determine the transaction shards and their order for each log stream. The assembly module is used to add each transaction fragment to the assembly queue in sequence according to the order of each transaction fragment, and record the order of the transaction fragments that have been most recently added to the assembly queue in the log stream as the assembly progress of the log stream. The site determination module is used to determine the global security site based on the assembly progress of each log stream at present. The output module is used to determine the commit version of each assembled transaction based on the sorting of the transaction fragments in their respective log streams after the transaction fragments in the assembly queue are assembled into a transaction, and to determine whether to output the transaction to downstream nodes for consumption based on the commit version of the transaction and the current global security position.

[0009] This specification provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described database transaction output method.

[0010] This specification provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the above-described database transaction output method.

[0011] This specification provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described database transaction output method.

[0012] The above-described at least one technical solution used in the embodiments of this specification can achieve the following beneficial effects: This specification discloses a method for outputting database transactions. For each log stream, after determining the transaction fragments and their order, the method sequentially adds each transaction fragment to an assembly queue according to this order. It records the order of the most recently added transaction fragment in the log stream as the assembly progress for that log stream. Based on the current assembly progress for each log stream, a global safety point is determined. After the assembly queue assembles the transaction fragments into transactions, for each transaction, the commit version is determined based on the order of the transaction fragments in their respective log streams. Based on the commit version and the current global safety point, it is determined whether to output the transaction to downstream nodes for consumption. By controlling the output of transactions through the global safety point, the consistency and continuity of transactions output to downstream nodes can be guaranteed, thereby enabling the entire system to have good recovery capabilities. Attached Figure Description

[0013] The accompanying drawings, which are included to provide a further understanding of this specification and form part of this specification, illustrate exemplary embodiments and are used to explain this specification, but do not constitute an undue limitation thereof. In the drawings: Figure 1 A flowchart of the database transaction output method provided in the embodiments of this specification; Figure 2 A schematic diagram of a database transaction output device provided in the embodiments of this specification; Figure 3 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this specification. Detailed Implementation

[0014] To make the objectives, technical solutions, and advantages of this specification clearer, the technical solutions of this specification will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this specification, and not all of them. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this specification.

[0015] The technical solutions provided in the various embodiments of this specification are described in detail below with reference to the accompanying drawings.

[0016] Figure 1 The flowchart of the database transaction output method provided in the embodiments of this specification specifically includes the following steps: S100: Obtain the log streams generated by the coordinator in the distributed database system.

[0017] In the embodiments of this specification, the following are used: Figure 1 The device that outputs the database transaction in the method shown can be an electronic device carrying the CDC tool. Specifically, the electronic device may include a personal computer, a server, etc. This specification does not limit this, and the electronic device will be referred to as the CDC device below.

[0018] The CDC device can acquire log streams generated by the coordinator in a distributed database system.

[0019] Distributed database systems mainly include two roles: coordinator and database nodes.

[0020] The coordinator is user-facing, communicating with user devices and receiving SQL statements from them. These SQL statements are used to manipulate data stored in the distributed database system. Upon receiving an SQL statement, the coordinator generates a log stream based on that statement. Specifically, the coordinator can generate a log based on the received SQL statement and add it to the log stream corresponding to the partition where the data being manipulated resides.

[0021] Database nodes are used to store tenant data. A tenant is a type of database instance. In the embodiments of this specification, a regular tenant is a database instance, which can be of various types, such as MySQL or other types of database instances. A database instance consists of several data tables. To facilitate the management of each data table in the database instance, a data table is usually divided into several partitions. Specifically, the partitions can be based on the primary key of the data in the data table, including partitions based on the first letter of the primary key, numbers, the number of data entries, etc. For example, data whose primary key starts with the letter AJ can be partitioned into one partition, data whose primary key starts with KZ into another partition, or data whose primary key starts with the numbers 0-5 into one partition, data whose primary key starts with the numbers 6-9 into another partition, or partitioning the data table into one partition for every 1 million data entries, etc. The embodiments of this specification do not impose any limitations on this.

[0022] Typically, a log stream corresponds to one partition; that is, logs that operate on data within a partition exist only in the log stream corresponding to that partition. Of course, a log stream can also correspond to more than two partitions; this specification does not impose this limitation. Besides operating on data within the distributed database using the WAL mechanism in the form of database transactions, these log streams can also be acquired by the CDC tool and provided to downstream nodes for consumption, also in the form of database transactions. The CDC tool can be deployed on the same device as the downstream nodes or deployed separately.

[0023] The application scenarios of these log streams in the embodiments of this specification are precisely those used to enable the CDC tool to identify the data that has changed in the distributed database system and provide it to downstream nodes for consumption in the form of transactions. Thus, the CDC device can obtain the various log streams generated by the coordinator in the distributed database system.

[0024] S101: For each log stream, determine the transaction shards in that log stream and their order.

[0025] After the CDC device obtains the log stream, it can identify each log in the log stream, assemble transaction fragments based on each log, and use them as transaction fragments in the log stream.

[0026] A database transaction may contain not just one log, but multiple logs, which means multiple SQL statements. Different logs may operate on different data, and these data may reside in different partitions; this is what constitutes a distributed transaction. For example, if user A pays user B 100 yuan, this database transaction requires two SQL statements: one to decrease user A's balance by 100 yuan, and the other to increase user B's balance by 100 yuan. User A's balance data resides in partition 1, while user B's balance data resides in partition 2, and the log streams corresponding to the two partitions are also different.

[0027] If log streams are applied to operate on the monthly data of users A and B stored in a distributed database, then in the distributed database, assuming the master node of partition 1 is node a and the master node of partition 2 is node b, both nodes a and b need to participate in this database transaction. Typically, based on a pre-defined consensus method (such as Raft), a coordinator needs to perform a two-phase commit with these two participants, ensuring that nodes a and b either both successfully operate on the data in their respective partitions, or both fail and roll back to their pre-operation state, thus guaranteeing data atomicity.

[0028] Therefore, logs from different log streams can be assembled into transaction shards, and different transaction shards can be assembled into transactions. In the example above, the transaction shard assembled from the logs in the log stream corresponding to partition 1 that decreases user A's balance by 100, and the transaction shard assembled from the logs in the log stream corresponding to partition 2 that increases user B's balance by 100, belong to the same database transaction.

[0029] The two-phase commit mentioned above includes two phases: a preparation phase and a commit phase. In the embodiments of this specification, when the log stream is applied to provide changed data in the distributed database to downstream nodes for consumption in the form of database transactions, the preparation and commit phases in the two-phase commit process can also be simulated. Then, the CDC device can determine the order of each transaction shard in a log stream based on the commit preparation timestamp corresponding to the log that assembles the transaction shard.

[0030] The commit preparation timestamp is the moment when the CDC device simulates the preparation phase described above; that is, the moment when the CDC device has assembled all transaction fragments within the transaction to which the transaction fragment belongs and persisted them. The earlier the commit preparation timestamp of the log that is assembled into a transaction fragment, the earlier the transaction fragment is ranked among all transaction fragments in the log stream.

[0031] For each log stream, the CDC device assembles the logs in the log stream into transaction fragments. After determining the order of each transaction fragment in the log stream, it can also set a corresponding sequence number for each transaction fragment in the log stream. The smaller the sequence number, the earlier the transaction fragment is ordered in the log stream.

[0032] It should be noted that the sorting sequence numbers of transaction fragments in different log streams are sorted independently, rather than being globally sorted across all log streams.

[0033] For example, in log stream 1, transaction shard A has a sort number of 1 and transaction shard B has a sort number of 2, while in log stream 2, transaction shard H has a sort number of 1 and transaction shard I has a sort number of 2.

[0034] S102: According to the order of each transaction fragment, add each transaction fragment to the assembly queue in sequence, and record the order of the transaction fragment that has been most recently added to the assembly queue in the log stream as the assembly progress corresponding to the log stream.

[0035] After assembling and sorting the transaction fragments in each log stream, the CDC device can, for each log stream, add each transaction fragment in the log stream to the assembly queue sequentially according to the sorting order of the transaction fragments in that log stream, and record the sorting order of the most recently added transaction fragment in the log stream as the assembly progress for that log stream. Specifically, the CDC device can, for each log stream, record the sequence number of the most recently added transaction fragment in the log stream as the assembly progress for that log stream.

[0036] In the embodiments of this specification, although there are multiple log streams, there is only one assembly queue. The assembly queue is used to assemble transactions belonging to the same database transaction from different added log streams into a transaction.

[0037] Although the sort order numbers of transaction fragments in different log streams are independent, they are not added to the assembly queue at the same pace. Instead, some log streams add transaction fragments to the assembly queue faster than others. In the example above, the most recently added transaction fragment to the assembly queue in log stream 1 is transaction fragment A, with a sort order number of 1. However, the most recently added transaction fragment to the assembly queue in log stream 2 is transaction fragment I, with a sort order number of 2. In other words, the assembly progress of log stream 1 is 1, and the assembly progress of log stream 2 is 2, meaning log stream 2 is faster than log stream 1.

[0038] S103: Determine the global security location based on the assembly progress of each log stream.

[0039] In the embodiments of this specification, in order to avoid the disruption of the consistency and continuity of transactions received by downstream nodes due to the failure and restart of the CDC device or the device that simultaneously carries the CDC tool and downstream nodes, the CDC device can determine the global safe position in real time according to the assembly progress corresponding to each log stream recorded in step S102.

[0040] Specifically, the CDC device can determine the sequence number with the smallest assembly progress in each current log stream as the global safe point.

[0041] S104: After the transaction fragments in the assembly queue are assembled into a transaction through the assembly queue, for each assembled transaction, the commit version of the transaction is determined according to the sorting of each transaction fragment in its respective log stream, and the transaction commit version and the current global security position are used to determine whether to output the transaction to the downstream node for consumption.

[0042] After determining the global safe point through step S103, the CDC device needs to ensure that all database transactions before the global safe point can be completely transmitted to the downstream node. Furthermore, when the CDC device uses the global safe point as the recovery starting point, the CDC will ensure that all database transactions after the global safe point will still be output.

[0043] Therefore, in step S104, after the CDC device assembles transaction fragments into database transactions through the assembly queue, it can determine the commit version of each assembled transaction based on the sorting of each transaction fragment in its respective log stream, and determine whether to output the transaction to downstream nodes for consumption based on the commit version of the transaction and the current global security position.

[0044] The CDC device can determine whether the commit version of a transaction is not greater than the current global safety point. If so, the transaction is output to downstream nodes for consumption; otherwise, the transaction is rejected from being output to downstream nodes. In this specification, the commit version of a transaction depends on the maximum sequence number of the transaction fragments assembled into the transaction within their respective log streams. That is, the CDC device can determine the commit version of the transaction by the maximum sequence number of the transaction fragments assembled into the transaction within their respective log streams.

[0045] For example, suppose transaction shards A and A' belong to the same database transaction but are not in the same log stream. After assembling these two transaction shards into a single database transaction using an assembly queue, if transaction shard A has a sort number of 30 in its log stream and transaction shard A' has a sort number of 35, then the commit version of the assembled database transaction can be determined to be 35. If the global safety point at this time is 35, meaning the minimum assembly progress of all current log streams is 35, then the commit version of the transaction is no greater than the current global safety point and can be output to downstream nodes for consumption. If the global safety point at this time is 34, then the commit version of the transaction is greater than the current global safety point. Although the transaction has been assembled, it still cannot be output to downstream nodes for consumption.

[0046] This ensures that when the CDC device (or the device that simultaneously carries the CDC tool and downstream nodes) crashes, all transactions can be interrupted from being output to downstream nodes until the device is restarted, at which point all transactions can continue to be output downstream by continuously updating the global safety point.

[0047] Furthermore, in practical applications, there may be situations where the upstream node has not crashed and restarted, but no new logs have been added to the log stream. In such cases, using the above method will cause the global safety point to remain stuck at the most recent assembly progress of the log stream, preventing further progress and causing transactions assembled from transaction fragments in other log streams to fail to be output to downstream nodes. To avoid this problem, in the embodiments of this specification, the CDC device can, for each log stream, generate a heartbeat task corresponding to that log stream and insert it into the log stream at a set period when no new logs are generated. This heartbeat task is an empty transaction fragment. That is, when no new logs are generated in the log stream, the CDC device periodically generates and inserts an empty transaction fragment into the log stream, and continues to set a sorting number for the empty transaction fragment according to the sorting of all transaction fragments in the log stream. This ensures that the assembly progress of the log stream can still advance through the inserted empty transaction fragment even when no new logs are generated in the log stream, preventing it from becoming a tail in all log streams.

[0048] In addition, when outputting database transactions to downstream nodes using the above method, the actual committed version of the output database transaction will always lag behind the update of the global security position. Therefore, in order to avoid causing excessive delay to the user experience of downstream nodes, in this embodiment of the specification, the CDC device may only provide the committed version of the transaction that has been most recently output to the downstream node for consumption to be displayed to the downstream node, instead of providing the current global security position to the downstream node for display.

[0049] When using the above method to output changed data in the distributed database to downstream nodes for consumption in the form of database transactions, the log stream obtained in step S100 can be all log streams of the entire distributed database. Therefore, the global security point determined in step S103 is the global security point of the entire distributed database. Alternatively, the log stream obtained in step S100 can be the log streams corresponding to all partitions in the same database instance stored in the distributed database system. In this case, the global security point determined in step S103 is the global security point of that database instance (or that tenant), and different tenants have different global security points.

[0050] After receiving a database transaction from the CDC device, if the downstream node is serving as a backup database for the distributed database system, it can update the data stored in its own database based on the received transaction to achieve data synchronization between the downstream node and the distributed database system. If the downstream node is used for data auditing, it can audit the data operations within the database transaction according to preset audit rules to determine whether the data operations within the database transaction are compliant. Those skilled in the art should understand that after receiving a database transaction from the CDC device, in addition to the aforementioned data synchronization and auditing, the downstream node can perform other necessary consumption of the database transaction; this specification does not limit such consumption.

[0051] The above is an example of a database transaction output method provided in this specification. Based on the same idea, this specification also provides corresponding devices, storage media and electronic devices.

[0052] Figure 2 This is a schematic diagram of a database transaction output device provided in an embodiment of this specification. The device includes: The acquisition module 201 is used to acquire the log streams generated by the coordinator in the distributed database system; The sharding determination module 202 is used to determine each transaction shard in the log stream and the order of each transaction shard for each log stream. Assembly module 203 is used to add each transaction fragment to the assembly queue in sequence according to the order of each transaction fragment, and record the order of the transaction fragments that have been most recently added to the assembly queue in the log stream as the assembly progress of the log stream. The site determination module 204 is used to determine the global security site based on the assembly progress corresponding to each log stream at present; The output module 205 is used to determine the commit version of each assembled transaction based on the sorting of the transaction fragments in their respective log streams after the transaction fragments in the assembly queue are assembled into a transaction through the assembly queue, and to determine whether to output the transaction to downstream nodes for consumption based on the commit version of the transaction and the current global security position.

[0053] Optionally, the sharding determination module 202 is specifically used to determine each log in the log stream, assemble transaction shards in the log stream according to each log; for each transaction shard in the log stream, determine the order of the transaction shard according to the commit preparation timestamp corresponding to the log that assembles the transaction shard, wherein the earlier the commit preparation timestamp corresponding to the log that assembles the transaction shard, the earlier the order of the transaction shard among all transaction shards in the log stream.

[0054] Optionally, the assembly module 203 is specifically used to record the sequence number of the transaction shards that have been most recently added to the assembly queue in the log stream, wherein the smaller the sequence number, the earlier the transaction shard is in the log stream; The site determination module 204 is specifically used to determine the sequence number with the smallest assembly progress in each current log stream as the global safe site.

[0055] Optionally, the output module 205 is specifically used to determine whether the commit version of the transaction is not greater than the current global security position; if so, the transaction is output to the downstream node for consumption; otherwise, the transaction is refused to be output to the downstream node for consumption.

[0056] Optionally, the output module 205 is specifically used to determine the maximum value of the sequence number of each transaction fragment assembled into the transaction in its respective log stream as the commit version of the transaction.

[0057] Optionally, the output module 205 is further configured to provide the commit version of the transaction that has been most recently output to the downstream node for consumption to the downstream node for display, and refuse to provide the current global security position to the downstream node for display.

[0058] Optionally, each acquired log stream corresponds to a different partition in the same database instance stored in the distributed database system.

[0059] This specification also provides a computer-readable storage medium storing a computer program that, when executed by a processor, can be used to perform the database transaction output method provided above.

[0060] This specification also provides a computer program product comprising a computer program that, when executed by a processor, implements the database transaction output method provided above.

[0061] based on Figure 1 The database transaction output method shown in this specification is further illustrated in the embodiments. Figure 3 The diagram shows the structure of the electronic device. Figure 3 At the hardware level, the electronic device includes a processor, internal bus, network interface, memory, and non-volatile storage, and may also include other hardware required for business operations. The processor reads the corresponding computer program from the non-volatile storage into memory and then runs it to implement the database transaction output method described above.

[0062] The above description is merely an embodiment of this specification and is not intended to limit this specification. Various modifications and variations can be made to this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of the claims of this specification.

Claims

1. A method for outputting database transactions, the method comprising: Obtain the log streams generated by the coordinator in the distributed database system; For each log stream, determine the transaction shards in that log stream and their order. According to the order of each transaction fragment, each transaction fragment is added to the assembly queue in sequence, and the order of the transaction fragment most recently added to the assembly queue in the log stream is recorded as the assembly progress of the log stream. Determine the global security location based on the assembly progress of each log stream. After the transaction fragments in the assembly queue are assembled into a transaction through the assembly queue, for each assembled transaction, the commit version of the transaction is determined according to the order of each transaction fragment in its respective log stream. Based on the commit version of the transaction and the current global security position, it is determined whether to output the transaction to the downstream node for consumption.

2. The method as described in claim 1, wherein determining each transaction shard in the log stream specifically includes: Identify each log entry in the log stream; Assemble the transaction fragments in the log stream based on each log; Determining the order of transaction shards in the log stream specifically includes: For each transaction shard in the log stream, the order of the transaction shard is determined based on the commit preparation timestamps corresponding to the logs that assemble the transaction shard. The earlier the commit preparation timestamps corresponding to the logs that assemble the transaction shards, the higher the order of the transaction shards among all transaction shards in the log stream.

3. The method as described in claim 1, wherein recording the order of the transaction shards most recently added to the assembly queue in the log stream, specifically includes: Record the sequence number of the transaction fragment that has been most recently added to the assembly queue in the log stream, wherein the smaller the sequence number, the earlier the transaction fragment is in the log stream; Based on the current assembly progress of each log stream, determine the global security point, specifically including: The sequence number with the smallest assembly progress in each current log stream is determined as the global safe point.

4. The method as described in claim 3, wherein determining whether to output the transaction to downstream nodes for consumption based on the transaction's commit version and the current global security position, specifically includes: Determine whether the committed version of the transaction is not greater than the current global safe point; If so, then the transaction will be output to the downstream node for consumption; Otherwise, refuse to output the transaction to downstream nodes for consumption.

5. The method as described in claim 3 or 4, wherein the commit version of the transaction is determined based on the order of the transaction shards assembled into the transaction in their respective log streams, specifically including: The maximum value of the sequence number of each transaction fragment assembled into the transaction in its respective log stream is determined as the commit version of the transaction.

6. The method of claim 4, further comprising: Provide the commit version of the transaction that has been most recently output to downstream nodes for consumption to be shown to downstream nodes, and refuse to provide the current global safe position to downstream nodes.

7. The method as described in claim 1, wherein each obtained log stream corresponds to a different partition in the same database instance stored in the distributed database system.

8. A database transaction output device, the device comprising: The acquisition module is used to acquire the log streams generated by the coordinator in the distributed database system; The sharding determination module is used to determine the transaction shards and their order for each log stream. The assembly module is used to add each transaction fragment to the assembly queue in sequence according to the order of each transaction fragment, and record the order of the transaction fragments that have been most recently added to the assembly queue in the log stream as the assembly progress of the log stream. The site determination module is used to determine the global security site based on the assembly progress of each log stream at present. The output module is used to determine the commit version of each assembled transaction based on the sorting of the transaction fragments in their respective log streams after the transaction fragments in the assembly queue are assembled into a transaction, and to determine whether to output the transaction to downstream nodes for consumption based on the commit version of the transaction and the current global security position.

9. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the method described in any one of claims 1-7.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the method described in any one of claims 1-7.

11. A computer program product comprising a computer program that, when executed by a processor, implements the method described in any one of claims 1-7.

Images