CN-122019557-A - Method, device, equipment and medium for realizing transaction consistency

CN122019557ACN 122019557 ACN122019557 ACN 122019557ACN-122019557-A

Abstract

The invention belongs to the technical field of databases, and particularly relates to a method, a device, equipment and a medium for realizing transaction consistency. The method comprises the steps of capturing change event data by taking a transaction as a unit from a transaction log of an Oracle database, extracting an operation type, a table name, a row of primary keys, a primary transaction ID and a change value based on the change event data, packaging the change event data into a structured message, routing the structured message corresponding to the same primary transaction ID or the same row of primary keys to the same Kafka partition through a preset partition by a Kafka cluster, consuming the structured message by the Storm cluster from the Kafka partition, pulling the structured message according to a transaction batch, distributing a unique transaction ID, carrying out data conversion on the structured message, then cooperatively submitting the structured message with a target non-relational database, realizing idempotent writing, carrying out inverse-normative aggregation design on the table structure of the non-relational database, and aggregating service data of a plurality of association tables in the Oracle database into a single-row data structure in the non-relational database.

Inventors

HONG YUANSHEN
Ding Zhezhao
YIN YIWEI
Ying Hongchen
ZHANG YANG
YU LIANG
LU JINGJING
XING QIJING
LIU JUN
LIU ZHIXIN
YANG SHUO
Yu Qianao
XU YUQING

Assignees

国网北京市电力公司

Dates

Publication Date: 20260512
Application Date: 20260204

Claims (10)

1. A method for implementing database transaction consistency, comprising: Capturing change event data by taking a transaction as a unit from a transaction log of a relational database, wherein the change event data comprises operation records of all association tables related to the transaction; based on the change event data, extracting an operation type, a table name, a main key of a row, a primary transaction ID and a change value corresponding to each operation record, and packaging the operation type, the table name, the main key of the row, the primary transaction ID and the change value into a structured message; Routing the structured message by the Kafka cluster by adopting a preset partition rule to obtain a plurality of Kafka partitions, wherein the preset partition rule is that the structured message corresponding to the same original transaction ID or the same row of main keys is routed to the same Kafka partition; The Storm cluster consumes the standardized structured message from each Kafka partition, carries out transaction batch division on the structured message according to the original transaction ID, and distributes unique global transaction ID for each transaction batch; based on the global transaction ID, an atomic write operation of a single row of aggregated data is performed to a non-relational database.
2. The method for implementing database transaction consistency of claim 1, further comprising: And configuring a data loss compensation mechanism, wherein the data loss compensation mechanism comprises two modes of manual synchronization or timing automatic comparison synchronization based on synchronous data of the Oracle and the non-relational database.
3. The method for implementing database transaction consistency of claim 1, wherein in the step of capturing change event data in transaction units from a transaction log of a relational database, OGG is used to capture data changes in transaction units from the transaction log of the relational database, and the order of the data changes is maintained in the order of relational database transaction submissions.
4. The method for implementing database transaction consistency of claim 1, wherein the step of extracting the operation type, table name, primary key of row, native transaction ID and change value based on the change event data, and encapsulating as a structured message comprises: Based on the change event data, extracting operation types, table names, primary keys of rows, original transaction IDs and change values through the user-defined UserExit program execution, and packaging the operation types, the table names, the primary keys, the original transaction IDs and the change values into a structured message in a JSON or Avro format; the UserExit program supports to specify Oracle table names and field lists to be extracted, unpacks automatically when the data quantity of the change event exceeds a preset threshold, and marks the unpacking state through unpacking marks, unpacking sequence numbers and last unpacking marks.
5. The method for implementing database transaction consistency of claim 4, wherein the step of obtaining a plurality of Kafka partitions by routing the structured message by using a preset partition rule by the Kafka cluster comprises: acquiring an encapsulated structured message, and extracting a primary transaction ID or a primary key of a row from the structured message as a partition key; Loading a preset partition strategy based on the partition key, and performing mapping calculation on the partition key to obtain a target partition number; And based on the target partition numbers, sending the structured message to the target partition of the subject corresponding to the Kafka cluster, and finishing the directional routing of the same original transaction ID or the same line of primary key messages.
6. The method for implementing database transaction consistency according to claim 1, wherein the step of performing data conversion and aggregation on values before and after field change of all operation records in the transaction batch based on a preset single-row aggregate data structure to obtain single-row aggregate data comprises the steps of: And executing collaborative commit operation by the Storm cluster and the target non-relational database, verifying whether the structured message corresponding to the original transaction ID is written or not, completing full data commit if the structured message is not written, and skipping if the structured message is written.
7. The method for implementing transaction consistency of claim 1, further comprising performing an inverse-normative aggregation design on a table structure of the non-relational database, comprising: Obtaining a structured message with a unique transaction ID output after Storm cluster data conversion, and simultaneously obtaining core association fields and data association relations of a plurality of association service tables in an Oracle database to obtain a table association rule; based on the table association rule, formulating an aggregation mapping rule of a single-row data structure of the non-relational database, and determining a core identification field of the single-row data structure; Based on the aggregation mapping rule and the core identification field, mapping and merging the structured messages corresponding to the Oracle association tables under the same original transaction ID into single-row aggregation data of a non-relational database; and calling a single-row atomic operation interface of the non-relational database based on the single-row aggregate data, writing the single-row data into the non-relational database at one time, and realizing transaction consistency by utilizing the characteristics of single-row atomic operation.
8. An apparatus for implementing transaction consistency, comprising: the system comprises a capturing module, a processing module and a processing module, wherein the capturing module is used for capturing change event data by taking a transaction as a unit from a transaction log of a relational database, wherein the change event data comprises operation records of all association tables related to the transaction; The extraction module is used for extracting the operation type, the table name, the main key of the row, the original transaction ID and the change value corresponding to each operation record based on the change event data, and packaging the operation type, the table name, the main key of the row, the original transaction ID and the change value into a structured message; the partition module is used for routing the structured message by the Kafka cluster by adopting a preset partition rule to obtain a plurality of Kafka partitions, wherein the preset partition rule is that the structured message corresponding to the same original transaction ID or the same row of main key is routed to the same Kafka partition; The aggregation module is used for consuming the standardized structured information from each Kafka partition by the Storm cluster, dividing the information into transaction batches according to the original transaction ID, and distributing unique global transaction ID for each transaction batch; And the writing module is used for executing an atomic writing operation of single-row aggregated data to the non-relational database based on the global transaction ID.
9. An electronic device comprising a processor and a memory, the processor being configured to execute a computer program stored in the memory to implement the method of achieving transaction consistency of any of claims 1 to 7.
10. A computer readable storage medium storing at least one instruction which when executed by a processor performs a method of achieving transaction consistency as claimed in any of claims 1 to 7.

Description

Method, device, equipment and medium for realizing transaction consistency Technical Field The invention belongs to the technical field of databases, and particularly relates to a method, a device, equipment and a medium for realizing transaction consistency. Background In the big data age, along with the continuous expansion of the data scale and the continuous evolution of the business form of various application systems, the data processing requirements increasingly show diversified and high-complexity characteristics. Traditional relational databases, such as Oracle, have long provided solid and reliable data storage and transaction security for enterprise-level applications in critical industries such as electricity, finance, telecommunications, government, etc., by virtue of their mature and stable architecture and strict compliance with transactional ACID properties, i.e., atomicity (Atomicity), consistency (Consistency), isolation (Isolation), and persistence (Durability). However, with the explosive development of internet and mobile internet services, the data generation speed is rapidly accelerated, the data form is also becoming diversified, and the non-relational database (NoSQL) gradually becomes an indispensable component in a large data platform due to the significant advantages of the non-relational database in mass data storage, high concurrent read-write throughput, flexible and expandable data model and the like. In a plurality of NoSQL databases, HBase is used as an open source project under Apache, is a high-quality implementation of GoogleBigtable architecture, and has the distributed characteristics of high reliability, high performance, column-oriented storage and high scalability. The method is widely applied to large data scenes such as user behavior log storage, real-time query, recommendation systems and the like of internet enterprises, and can effectively be applied to application environments with huge data volume and intensive read-write requests. However, HBase is focused on scalability and high throughput at the beginning of design, and its capability in terms of transaction processing is relatively limited, especially lacking cross-row, cross-table transaction guarantees, and it is difficult to support complex transaction operations with strict consistency as Oracle. Although partial transaction mechanisms, such as limited row-level transactions, are introduced in the subsequent HBase versions, there is still a significant gap compared to the strong coherency transaction guarantee mechanism provided by Oracle. This lack of transactional capability is particularly prominent in business scenarios where data accuracy, integrity, and transactional consistency requirements are extremely stringent, such as power system monitoring, power billing, financial transactions, telecommunications billing, and the like. In these areas, any subtle data inconsistency or transaction loss can lead to serious business errors and even systematic risks. Thus, although HBase has significant advantages in terms of scalability and performance, its limitations in critical transaction scenarios also limit its further application expansion. Currently, although some technical solutions for data synchronization between Oracle and HBase exist in the industry, they often have difficulty meeting the dual requirements of low latency and strong consistency in a distributed environment. The common problems of data loss, disordered sequence, higher synchronization delay and the like make maintaining transaction strict consistency at the same level as Oracle in a distributed architecture a very challenging and urgent technical problem to be solved. Disclosure of Invention The invention aims to provide a method, a device, equipment, a medium, a device, equipment and a medium for realizing transaction consistency, which solve the problem that the low delay and the strong consistency are difficult to meet in a distributed environment in the background technology. In order to achieve the above purpose, the present invention adopts the following technical scheme: the invention provides a method for realizing database transaction consistency, which comprises the following steps: Capturing change event data by taking a transaction as a unit from a transaction log of a relational database, wherein the change event data comprises operation records of all association tables related to the transaction; based on the change event data, extracting an operation type, a table name, a main key of a row, a primary transaction ID and a change value corresponding to each operation record, and packaging the operation type, the table name, the main key of the row, the primary transaction ID and the change value into a structured message; Routing the structured message by the Kafka cluster by adopting a preset partition rule to obtain a plurality of Kafka partitions, wherein the preset partition rule is that the structured message c