A database synchronization method and apparatus based on column cluster and global central lock

Abstract

The application relates to the technical field of data synchronization, in particular to a database synchronization method and device based on column clusters and global central locks. The method comprises the following steps: dividing all columns of a wide table into multiple column clusters based on the probability that any two columns in the wide table are simultaneously modified in the same transaction, and constructing a column cluster mask table; identifying a column cluster G1 involved in a row R1 based on the column cluster mask table; granting the column cluster G1 involved in the row R1 to a target data center; under the condition that the column cluster G1 in the row R1 is locked, updating a field corresponding to the column cluster G1 in the row R1, and generating a change record; and sending the change record to a data synchronization entrance of other data centers. The application can eliminate pseudo conflicts in the wide table scene in the multi-active architecture in the same city, and avoid a large waste of CPU and input / output resources.

Description

Technical Field

[0001] This invention relates to the field of data synchronization technology, and in particular to a database synchronization method and apparatus based on column families and a global central lock. Background Technology

[0002] The new power system based on new energy sources is under accelerated construction. In order to pursue the ultimate business continuity, the system architecture is evolving from the traditional master-slave mode to the city-wide active-active architecture. The city-wide active-active architecture requires the same business application to run simultaneously in two or more data centers tens of kilometers apart, and to be able to process user read and write requests in real time. Under this architecture, how to ensure that the structured data distributed on different active nodes maintains strong consistency is the core challenge of the entire system design. Structured data is usually stored in relational databases or wide table engines.

[0003] The current structured data synchronization faces unprecedented technical pressure. First, the amount of data to be synchronized is enormous, and the production process is almost uninterrupted, which means that the synchronization link must have extremely high throughput. Second, there are numerous table objects; a single system may contain tens of thousands of business tables, and managing the concurrent lock states of these objects is extremely complex. The most significant feature is the wide table structure, that is, a single table contains a large number of columns and fields, and different business logics may simultaneously act on different fields in the same row. Traditional row-level locking mechanisms will lead to serious concurrency bottlenecks in such scenarios.

[0004] To resolve data conflicts caused by concurrent writes across multiple nodes, optimistic concurrency control (OCC) or pessimistic control based on a global central lock is typically employed. The core idea of ​​a global central lock is to coordinate access rights to specific resources across all nodes through an authoritative lock manager. However, in-depth business analysis reveals that existing central lock technologies cannot be directly applied to the complex multi-active synchronization scenarios described above. Their main drawbacks are: traditional locking technologies typically use rows as the smallest locking granularity. In a business table with extremely wide columns, service A might be updating a user's geographic location column, while service B is simultaneously updating the same user's credit score column on another node. Logically, these two operations do not conflict, but because they operate on the same row, row-level locks force serial queuing. This pseudo-conflict caused by overly coarse locking granularity results in severe performance degradation in large-scale concurrent environments, leading to significant waste of the Central Processing Unit (CPU) and input / output resources while waiting for lock release. In summary, in a multi-active architecture within the same city, how to deal with the pseudo-conflict problem in wide table scenarios and avoid a large waste of CPU and I / O resources is a problem that needs to be solved.

[0005] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0006] In response to the above-mentioned defects or improvement needs of existing technologies, this invention proposes a database synchronization method and apparatus based on column families and global central locks, which can eliminate pseudo-conflicts in wide table scenarios in a multi-active architecture within the same city, and avoid a large waste of CPU and input / output resources.

[0007] The embodiments of the present invention adopt the following technical solutions: In a first aspect, the present invention provides a database synchronization method based on column families and a global central lock, specifically: based on the probability that any two columns in a wide table are modified simultaneously in the same transaction, all columns of the wide table are divided into multiple column families, and a column family mask table is constructed. Identify the column family G1 involved in trip R1 based on the column family mask table; Grant a lock to column family G1 involved in row R1 to the target data center; While holding the lock on column family G1 in row R1, update the field corresponding to column family G1 in row R1, generate a change record, and send the change record to the data synchronization entry of other data centers.

[0008] Preferably, the method further includes: During the data synchronization process in other data centers based on the change records, the data source table type of the change records is identified; If the data source table type of the change record is a dense wide table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner. If the data source table type of the change record is a dense narrow table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are continuously stored in physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution; If the data source table type of the change record is a sparse table, then the multiple consecutive data rows in the change record are stored in physical pages in a default manner according to the primary key values ​​of the multiple consecutive data rows in the change record, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

[0009] Preferably, the method further includes: The local lock manager receives a lock request from the target data center for column cluster G1 in row R1 and determines whether it holds a row lease for row R1. If the local lock manager holds a row lease for row R1, the local lock manager directly grants a lock on column family G1 in row R1 to the target data center. If the local pre-lock manager does not hold a row lease for row R1, it initiates a lock request for column family G1 in row R1 to the global arbitration manager. The global arbitration manager determines the global lock status of column family G1 in row R1 and grants locking permission based on the global lock status.

[0010] Preferably, the method further includes: The global arbitration manager checks whether the lock in column cluster G1 of row R1 is already held by another data center; If no other data center holds the lock for column family G1 in row R1, check whether the row lease for row R1 is already held by another data center. If no other data center holds a row lease for row R1, the global arbitration manager grants a lock to the target data center for column family G1 in row R1.

[0011] Preferably, the method further includes: Real-time statistics on the lock request frequency of each row in the wide table within a preset time period; If the lock request frequency of row R1 is greater than or equal to the preset frequency, a row lease for row R1 is requested from the global arbitration manager. The global arbitration manager determines the global lease status of row R1 and grants a row lease for row R1 based on the global lease status.

[0012] Preferably, the method further includes: The Global Arbitration Manager receives the row lease request for row R1 and checks whether the row lease for row R1 is already held by another data center; If no other data center holds a row lease for row R1, check if the lock for row R1 + any column family is already held by another data center. If no other data center holds a lock on row R1 and any column family, the global arbitration manager grants the target data center a row lease for row R1.

[0013] Preferably, the method further includes: Monitor the incremental data capture log of the database in real time, and parse the set of columns involved in the transaction from the incremental data capture log; Calculate the probability that any two columns in the column set appear simultaneously; Merge two columns whose probabilities are greater than a predefined threshold into the same column cluster; Based on the mapping relationship between columns and column families, a column family mask table is constructed.

[0014] Firstly, the formula for calculating the probability that any two columns in the column set appear simultaneously is as follows: ; in, Representative column and column The probability of two events occurring simultaneously in the same transaction. Indicates a column Updated set of transactions, Indicates a column The updated set of transactions.

[0015] In a second aspect, the present invention provides a database synchronization apparatus based on column families and a global central lock, the apparatus comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor for performing the database synchronization method based on column families and a global central lock in the first aspect.

[0016] Thirdly, the present invention also provides a non-volatile computer storage medium storing computer-executable instructions that are executed by one or more processors to perform the database synchronization method based on column families and a global central lock as described in the first aspect.

[0017] Compared with the prior art, the beneficial effects of the present invention are as follows: the smallest locking granularity of the wide table is reconstructed from the row to the column family. During the data synchronization process, if at least two data centers modify the same row at the same time, as long as there is no conflict between the column families involved in the two modifications, the global lock manager will grant locks to both data centers at the same time. This mechanism logically realizes parallel writing of the same row of data, fundamentally eliminating false conflicts in the wide table scenario, thereby avoiding a large waste of CPU and input / output resources.

[0018] Furthermore, this invention prioritizes granting locking permissions through a local pre-lock manager deployed within the data center. Only when the local pre-lock manager lacks the necessary permissions does it grant locking permissions through a global arbitration manager. This design reduces cross-center global requests to some extent, thereby mitigating the cumulative effect of cross-center network latency. Attached Figure Description

[0019] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments of the present invention will be briefly described below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0020] Figure 1This is a flowchart illustrating a database synchronization method based on column families and a global central lock, provided in an embodiment of the present invention. Figure 2 This is a flowchart of a database synchronization method based on column families and a global central lock, provided in an embodiment of the present invention, for data synchronization by primary key. Figure 3 This is a schematic diagram illustrating a specific example of a local sequence number for three consecutive data rows provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of a specific example of a B+ tree provided in an embodiment of the present invention; Figure 5 This is a schematic diagram illustrating a specific example of a primary key structure provided in an embodiment of the present invention; Figure 6 This is a flowchart of a method for constructing a column family mask table in a database synchronization method based on column families and a global central lock, provided in an embodiment of the present invention. Figure 7 This is a flowchart of a database synchronization method based on column families and a global central lock, provided by an embodiment of the present invention, for obtaining lock permissions; Figure 8 This is a flowchart of a method for granting lock permissions to a global arbitration manager in a database synchronization method based on column families and a global central lock, provided in an embodiment of the present invention. Figure 9 This is a flowchart illustrating the process of obtaining row leases in advance in a database synchronization method based on column families and a global central lock, as provided in an embodiment of the present invention. Figure 10 This is a flowchart illustrating the method of granting row leases by the global arbitration manager in a database synchronization method based on column families and a global central lock, provided in an embodiment of the present invention. Figure 11 This is a schematic diagram of the architecture of a database synchronization system based on column families and a global central lock, provided by an embodiment of the present invention; Figure 12 This is a schematic diagram of a database synchronization device based on column families and a global central lock, provided as an embodiment of the present invention. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0022] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0023] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0024] In the description of this invention, the expression “A and / or B” (where A and B are used to formally represent specific features) will be used. The corresponding expression includes the following three combinations: only A, only B, and a combination of A and B.

[0025] As used in this invention, “about,” “approximately,” or “approximately” includes the stated value and the average value within an acceptable range of deviation from a particular value, wherein the acceptable range of deviation is determined by a person skilled in the art taking into account the measurement under discussion and the error associated with the measurement of the particular quantity (i.e., the limitations of the measurement system).

[0026] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0027] Example 1: This invention uses column families as the smallest locking granularity and decouples lock management into a global arbitration manager and a local pre-lock manager. By combining the smallest locking granularity and the lock manager, the lock request path is optimized, enabling the global central lock to adapt to multi-active wide table environments. The global arbitration manager, as the highest global authority source, manages the global ownership of resources (including lock permissions and row leases). Each data center deploys a local pre-lock manager, which is responsible for intercepting local requests and managing local row leases and lock permissions.

[0028] Before introducing the solution of this invention, the following table compares and analyzes the differences in data characteristics between the standard application environment and the specific multi-active application system targeted by this invention. The analysis shows that the global central lock in the standard application environment cannot be directly ported to the specific multi-active application system. Therefore, it is necessary to design an adapted global central lock solution for the specific multi-active application system. The relevant comparisons are shown in Table 1: Table 1. Differences in data characteristics between standard application environments and specific multi-active application systems.

[0029] In standard application environments, data flow is low-frequency, the number of core tables is limited, field widths are less than 50 columns, and deployments are typically in a single data center or primary / standby configuration, with conflicts handled by local transactions. In contrast, specific multi-active application systems receive high-frequency data flows, meaning higher lock request frequency; a large number of table objects (tens of thousands or more) means the lock manager needs multi-tenancy and massive object management capabilities; wide table structures (hundreds to thousands of columns) mean extremely high row-level lock pseudo-conflict rates and limited concurrency; deployments are typically in same-city multi-active and off-site disaster recovery environments, meaning cross-center latency becomes the absolute lower limit for lock acquisition; and cross-node global conflict handling means resolving lock loss, lease expiration, and state drift. From the above comparison, it is clear that the differences in data characteristics between standard application environments and specific multi-active application systems are significant, meaning that the global central lock in standard application environments cannot be directly applied to specific multi-active application systems. In the business logic of same-city multi-active systems, data generation exhibits significant locality. For example, some users may connect to a specific central node A more frequently for operations. However, due to the dynamic adjustment of load balancing, this locality is not absolute. This means that the global central lock needs to reduce unnecessary cross-center interactions while ensuring the absolute security of lock permissions in the event of physical node migration.

[0030] This embodiment will evolve the smallest locking granularity of wide tables from rows to column families to solve the pseudo-conflict problem in wide table scenarios. The specific process is as follows: Based on the probability that any two columns in a wide table are modified simultaneously in the same transaction, all columns of the wide table are divided into multiple column families, and a column family mask table is constructed. Identify the column family G1 involved in trip R1 based on the column family mask table; Grant a lock to column family G1 involved in row R1 to the target data center; While holding the lock on column family G1 in row R1, update the field corresponding to column family G1 in row R1, generate a change record, and send the change record to the data synchronization entry of other data centers.

[0031] In this system, a wide table is divided into multiple rows and multiple column families. Row R1 represents a row of data in the wide table, and column family G1 represents a column family within the wide table. The column family G1 involved in row R1 represents the intersection of row R1 and column family G1, referring to all data units within row R1 that belong to column family G1. For example, a wide table has 100 rows and 1000 columns, where columns 1, 4, and 5 belong to column family 1. If a target data center wants to modify column 4 of row 1 of this wide table, then column family 1 involved in row R1 refers to the intersection of row 1 and column family 1 (i.e., columns 1, 4, and 5 of row 1). After locking this intersection, that is, while the target data center holds the lock on column family 1 of row 1, other data centers cannot modify columns 1, 4, or 5 of row 1.

[0032] In addition, in a multi-active architecture within the same city, although the physical distance between data centers is limited, the round-trip latency of fiber optic transmission and network equipment is usually stable at 1-2 milliseconds. For high-frequency updated data streams, if the conflict detection before each data synchronization must go through a cross-center global lock request, the accumulated latency cost will directly drag down the system's real-time response capability.

[0033] To address the aforementioned cumulative latency issue, this embodiment of the invention further decouples lock management into a global arbitration manager and a local pre-lock manager, enabling the global central lock to adapt to multi-active wide-table environments, thereby mitigating the cumulative effect of cross-center network latency. Figure 1 As shown, this embodiment of the invention provides a database synchronization method based on column families and a global central lock, which specifically includes the following steps: Step 101: Based on the probability that any two columns in the wide table will appear simultaneously in the same transaction, divide all columns of the wide table into multiple column families and construct a column family mask table.

[0034] Among them, belonging to the same column family means that the columns in that column family are more likely to be modified simultaneously in the same transaction (such as exceeding a certain threshold). The column family mask table is the mapping relationship between each column and its column family. The column family mask table includes column identification (ID), column family ID, and table ID, etc.

[0035] To address the pseudo-conflict problem in wide table scenarios, this invention reconstructs the locking dimension, evolving the smallest locking granularity of the wide table from rows to column families. Specifically, the global arbitration manager uses a deep business-aware algorithm to automatically analyze the update correlations between columns in the table, grouping columns with a high probability of appearing simultaneously in the same transaction into the same column family. For example, if columns C1 and C2 are frequently modified in the same business transaction, they are classified into the same column family. After determining the column family mask table, the global arbitration manager synchronizes the column family mask table to the local pre-locking managers in each center.

[0036] Step 102: Receive an update request for row R1, and identify the column family G1 involved in row R1 based on the column family mask table.

[0037] Before executing an update request for row R1, the target data center must first initiate a lock request for row R1 to acquire lock permissions for row R1, ensuring data consistency under the lock permissions for row R1. This embodiment of the invention evolves the smallest locking granularity of the wide table to a column family. That is, before executing a write operation, the target data center needs to initiate a lock request for column family G1 involved in row R1 to acquire lock permissions for column family G1 involved in row R1. Furthermore, if the target data center modifies multiple column families of row R1, it needs to apply for lock permissions for each column family involved in row R1. Hereinafter, column family G1 in row R1 has the same meaning as the column family G1 involved in row R1.

[0038] Specifically, the local lock manager of the target data center intercepts the update request for row R1 and identifies the column family G1 involved in updating row R1 by querying the column family mask table. As a result, a lock request is initiated for column family G1 involved in row R1.

[0039] Step 103: First, grant the lock on column family G1 in row R1 to the target data center through the local pre-lock manager. If the local pre-lock manager does not have permission, grant the lock on column family G1 in row R1 to the target data center through the global arbitration manager.

[0040] In this embodiment of the invention, the global central lock is decoupled into a global arbitration manager and a local pre-lock manager. First, the local pre-lock manager intercepts the lock request sent to the global arbitration manager. If the local pre-lock manager has the authority to grant a lock on column cluster G1 in row R1 to the target data center, it is not necessary to apply for locking authority from the global arbitration manager across centers, thereby mitigating the cumulative effect of cross-center network latency.

[0041] To efficiently manage each row-column family lock and row lease, both the global manager and the local pre-lock manager maintain a row-column family lock waiting queue and a row lease queue. When a row-column family requests lock permissions and a lock conflict occurs, the request is added to the row-column family lock waiting queue, and subsequent requests are made only after the corresponding lock resource is released. When the global manager distributes a row lease for a row to the data center, it maintains that row lease in the row lease queue.

[0042] Step 104: While holding the lock on column family G1 in row R1, update the field corresponding to column family G1 in row R1 and generate a change record. Send the change record to the data synchronization entry of other data centers so that other data centers can complete data synchronization based on the change record.

[0043] In this embodiment, after the target data center holds the lock on column family G1 in row R1, the database of the target data center performs a Structured Query Language (SQL) operation to update the field corresponding to column family G1. The synchronization engine on the target data center side captures the changed data and generates a Logical Change Record (LCR) containing the column family ID and the changed data. The synchronization engine on the target data center side sends the LCR to the synchronization engines of other data centers. The databases of other data centers atomically merge the incremental data into their databases and return a Sync_Ack to the target data center. After receiving the Sync_Ack from other data centers, the target data center sends a lock release request to the local pre-lock manager. After the local pre-lock manager releases the lock, it initiates a lock release request to the global arbitration manager, indicating that the lock resource has entered an allocatable state. This completes the data synchronization of the multi-center power dispatching platform in different locations.

[0044] In this embodiment, the smallest locking granularity of the wide table is reconstructed from rows to column families. During data synchronization, if at least two data centers modify the same row at the same time, as long as there is no conflict between the column families involved in the two modifications, the global lock manager will grant locks to both data centers simultaneously. This mechanism logically realizes parallel writing of the same row of data, fundamentally eliminating false conflicts in the wide table scenario, thereby avoiding a large waste of CPU and input / output resources.

[0045] In addition, this invention prioritizes granting locking permissions through a local pre-lock manager deployed within the data center. Only when the local pre-lock manager lacks the necessary permissions does it grant locking permissions through a global arbitration manager. This design reduces cross-center global requests to some extent, thereby mitigating the cumulative effect of cross-center network latency.

[0046] In one possible implementation, the target data center may simultaneously modify multiple data rows of the same table. After the locking and change operations in step 103, the generated change record contains change information for multiple data rows. Each change record includes at least the table ID, field name, primary key, column family ID, original field value, changed field value, and change time. The primary key is a unique primary key for each data row. Furthermore, the multiple data rows may be consecutive or non-consecutive.

[0047] In the actual synchronization process, the data center's synchronization engine generates corresponding SQL statements based on the change information. The execution of the SQL statements is essentially writing the changed data into the underlying physical storage structure. For example, when processing structured data with wide column characteristics, the database storage engine usually uses a B+ tree as the physical carrier of the clustered index, and the data rows are stored in the leaf nodes in the order of the primary key.

[0048] In existing technologies, to address the primary key uniqueness problem in distributed environments, the industry commonly employs partition-based algorithms, such as the classic Snowflake algorithm and its various variants. While the primary keys generated by existing Snowflake algorithms and their variants are macroscopically incremental, they may exhibit microscopically out-of-order behavior under multi-node concurrency and network latency fluctuations. For example, node A generates a primary key with a value of 100 at time T. Node B, submitting its primary key slightly later due to network latency within the same millisecond, may generate a primary key with a value less than 100 due to differences in the machine ID bit.

[0049] For database tables, wide tables have large row widths, with each row occupying thousands of kilobytes (KB). A 16KB physical storage page (or physical page) can only store 2 to 4 rows of data from a wide table. A physical page in a database storage engine is a fixed-size input / output (I / O) unit on disk. In a B+ tree index, each leaf node corresponds to one such physical page, used to actually store the data rows in the table. However, in a multi-active data synchronization scenario within the same city, multiple active-active centers (i.e., nodes) may concurrently modify the same row of a database table. When multiple nodes concurrently write to the same database table, even with a trend-increasing Snowflake primary key, due to minor differences in network transmission and the microscopically disordered interleaving of primary keys generated by multiple instances, the database must frequently insert records into the middle of full or nearly full physical pages when merging these wide table records from different nodes. That is, because the corresponding primary keys from different nodes are interleaved in time, new data rows are not simply appended to the last page, but are forced to be inserted into the middle of full or half-full physical pages, frequently triggering B+ tree page splits. However, each split of the B+ tree requires moving a large amount of data, resulting in decreased disk space utilization, increased index height, increased random I / O, and ultimately a precipitous drop in system throughput. Even when non-contiguous data rows are inserted into physical pages, they may still encounter full or nearly full physical pages, i.e., B+ tree page splits still need to be triggered, leading to a precipitous drop in system throughput.

[0050] To ensure compatibility with wide tables and prevent write performance degradation, narrow tables with small row widths suffer from sparse primary key values ​​due to the large number of data rows stored per physical page. This results in low page space utilization and requires skipping multiple non-contiguous pages during sequential scans, reducing cache hit rate and sequential I / O efficiency. Furthermore, sparse tables have extremely low write frequencies, making page splits and write hotspots virtually nonexistent. Enabling complex step-size calculations and state maintenance for them would incur unnecessary CPU and memory overhead. Narrow tables strictly adhere to database design paradigms (e.g., third normal form), have fewer columns (typically 10 or less), each representing a single attribute, a clear primary key, and highly structured data. Narrow tables exhibit less data redundancy, compact storage, high query efficiency, and fast insert / update operations. Sparse tables, on the other hand, are designed for data sparsity, with many fields containing null or zero values. They use a "non-zero element + index" storage approach to avoid storing invalid null values, resulting in low space consumption. However, queries require locating non-zero data through the index, potentially sacrificing some real-time update flexibility.

[0051] In summary, existing methods, which store data in leaf nodes in primary key order, cannot accommodate various database tables and cannot meet the write performance requirements of multi-active architectures.

[0052] To address the aforementioned write performance issues, in this embodiment of the invention, the target data center performs locking and modification operations and sends the generated modification records to the target data center. Other data centers, for different types of database tables, store data rows in physical pages in primary key order according to different write methods. This further improves write performance while avoiding significant waste of CPU and I / O resources.

[0053] The following describes in detail the data synchronization method of this invention embodiment for the case where multiple consecutive data rows are stored in the change record. (See attached document for details.) Figure 2 The database synchronization method based on column families and a global central lock further includes the following steps: Step 201: During the data synchronization process in other data centers based on the change record, identify the data source table type of the change record.

[0054] Among them, the data source table types include dense wide tables, dense narrow tables, and sparse tables. Dense wide tables are characterized by large row widths and frequent writes, dense narrow tables are characterized by small row widths and frequent writes, and sparse tables are characterized by very few writes.

[0055] In one embodiment, the wide table metadata detector in each data center performs metadata retrieval. In one embodiment, it can periodically retrieve the database data dictionary to obtain the number of fields and data type definitions for each table. Then, it calculates the physical column width: the wide table metadata detector calculates the expected average physical length of a single row record based on the data type. The wide table metadata detector evaluates the column width of each table in real time. Next, it performs load intensity grading: the load collector collects the frequency of record generation (i.e., Figure 3 The database tables are categorized into dense wide tables, dense narrow tables, or sparse tables based on the data generation frequency and the expected average physical length. A mapping relationship between database table IDs and corresponding table types is established. During subsequent data synchronization, only the table ID needs to be queried to determine the table type. In one embodiment, other data centers determine the data source table type of the change record by parsing the change record and extracting the table ID.

[0056] Step 202: If the data source table type of the change record is a dense wide table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner.

[0057] The local sequence number is used to distinguish multiple data entities generated within the same time window, that is, to distinguish the primary keys of different data rows generated by the same active-active node within the same millisecond. The specific method for generating the local sequence number shall be selected by those skilled in the art based on the specific use case with reference to existing technologies.

[0058] The primary key in this embodiment of the invention consists of multiple parts, including local sequence number positions. For data rows in a dense wide table, the local sequence numbers in these primary key local sequence number positions are distributed in a jump-incrementing pattern. The specific jump-incrementing method is determined by those skilled in the art based on the specific use case. For example, the local sequence numbers of consecutive data rows generated by jump-incrementing with a step size of 171 can be 0, 171, 342, and 513, and so on.

[0059] The specific granularity of the time window is determined by those skilled in the art based on the specific application scenario. In one embodiment, the time window can be 1 millisecond. In this case, for primary key generation requests received within the same millisecond: the local sequence number increases by a logical padding step each time until it reaches its maximum value, then wraps around and waits for the next millisecond. A specific example of a local sequence number is: when the initial value of the local sequence number is 0, the local sequence number of the first primary key generation request received within the same millisecond can be 0, the local sequence number of the second primary key generation request received can be 171, and the local sequence number of the third primary key generation request received can be 342. Furthermore, for primary key generation requests received outside of this millisecond (e.g., the next millisecond): the sequence number is reset to the initial value (i.e., 0), and then increments again by the logical padding step starting from this initial value. The purpose of resetting is to avoid excessive accumulation of sequence numbers between different milliseconds, while maintaining the independence of the hash pattern within each millisecond.

[0060] This invention, through controlling the increment of local sequence numbers, ensures that the temporally consecutive primary keys generated by dense wide tables fall within the address range of the same storage page in physical storage. Temporally consecutive means that the generation times of the primary keys are closely linked on the timeline without intervals; that is, the same node receives primary key generation requests continuously within a very short time window (e.g., within the same millisecond or several consecutive milliseconds), and subsequently needs to respond to each primary key generation request sequentially to generate the corresponding primary key. For dense wide tables, the Physical Page Alignment (PPA) optimization mode of this invention is enabled, which uses locally incrementing sequence numbers to generate primary key values. In one embodiment, by setting the increment step of the local sequence number to a logical fill step greater than 1, the local sequence numbers of multiple primary key values ​​generated by the same node within a continuous time window exhibit a jump-like change. In one embodiment, for consecutive primary key generation requests, a jump in the local sequence number ensures a larger interval between the primary key values ​​generated for these data rows, allowing these primary keys to naturally fall within different leaf nodes (i.e., physical pages) in the B+ tree clustered index. Since the B+ tree allocates data rows to physical pages in order of primary key value size, and each physical page has a limited capacity, when the local sequence number increases consecutively (e.g., step size equals 1), the primary key values ​​of consecutive data rows are closely adjacent and are likely to be sequentially inserted into the same physical page, quickly filling the page. When new data rows are subsequently inserted, the page is already full, necessitating a page split (e.g., moving half the data to a new page and updating the index). However, a jump-incrementing sequence (e.g., step size much greater than 1) results in primary key values ​​that differ significantly between consecutive data rows, such as 1000, 1171, and 1342. These fall into different primary key ranges and are thus allocated to different physical pages by the database storage engine. Each page only writes a small number of rows, far from being full, and subsequent writes do not require frequent splits.

[0061] Step 203: If the data source table type of the change record is a dense narrow table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are continuously stored in physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution.

[0062] This invention employs a compact auto-incrementing local sequence numbering method to tightly arrange primary key values, thereby storing consecutive data rows contiguously in the same physical page or adjacent pages, maximizing space utilization and sequential read performance. The specific compact auto-incrementing method is determined by those skilled in the art based on the specific application scenario. For example, the local sequence numbers of consecutive data rows generated by compact auto-incrementing with a step size of 1 can be 1, 2, 3, and 4, and so on. In one embodiment, when storing multiple consecutive data rows contiguously in a physical page according to the primary key value, the data of these consecutive data rows is first stored sequentially in the same physical page. When that physical page is full, the data continues to be stored sequentially in the next physical page.

[0063] Step 204: If the data source table type of the change record is a sparse table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the multiple consecutive data rows are stored in the physical page in the default manner, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

[0064] Sparse tables are database tables with a small number of transactions per second (TPS) that is less than a processing threshold, which is determined by those skilled in the art based on the specific use case. The default method and default distribution are determined by those skilled in the art based on the specific use case; the default distribution means storing data as if it were a regular database table, without any special optimization, in order to save system resources.

[0065] It should be noted that when storing data from the data rows to physical pages in steps 201 to 204 above, the operation is performed in the B+ tree based on the primary key value. The distribution of the corresponding local sequence number is used to control the primary key value, resulting in different control effects for dense wide tables, dense narrow tables, and sparse tables.

[0066] In one embodiment, a PPA optimization strategy is shown in Table 2 below: Table 2 Comparison of PPA Optimization Strategies

[0067] in, This represents the average physical row width of the database table, and its calculation method is determined by those skilled in the art based on the specific use case.

[0068] This invention divides database tables into dense wide tables, dense narrow tables, or sparse tables. For dense wide tables, due to their large row widths and frequent writes, a distributed local sequence number with a jump-incrementing pattern is used to avoid page splits. For dense narrow tables, due to their small row widths and frequent writes, compact auto-incrementing sequences are used to maintain contiguous storage and improve space utilization. For sparse tables, due to their infrequent writes, the default database processing method is used to save resources. The jump-incrementing sequence number prevents multiple consecutive data rows from occupying the same physical page from the source, significantly reducing the frequency and overhead of physical page splits. This avoids B+ tree page splits and performance collapses caused by dense primary key storage during high-concurrency writes in wide tables, while maintaining efficient and compact storage in narrow tables and minimizing the impact of other processing methods on write performance in sparse tables.

[0069] Furthermore, to overcome the shortcomings of existing wide table index fragmentation and B+ tree splitting failure mechanisms, this invention introduces a physical page fill alignment optimization algorithm in the local sequence number generation stage. Instead of treating the local sequence number as a simple auto-increment, it treats it as a physical space reservation function. Specifically, in one embodiment, as... Figure 3 As shown, the local sequence numbers of consecutive data rows within the same time window are sequentially increased according to the logical padding step. In one embodiment, for multiple consecutive data rows, when the time window changes (e.g., entering the next millisecond), the local sequence number is reset (e.g., reset to 0), and then incremented again according to the logical padding step. This sequential increment only applies within the same time window (e.g., milliseconds); the sequence number is reset when crossing milliseconds, and it does not increment globally indefinitely. For example, when the logical padding step is 171, the local sequence numbers corresponding to three consecutive data rows within the same time window can be 0, 171, and 342, respectively.

[0070] In one embodiment, the logical fill step size is determined based on the number of data rows that a single physical page can carry and the number of active data centers.

[0071] Specifically, in one embodiment, the number of data rows is the absolute value of the ratio of effective page capacity to row storage cost; the effective page capacity is the database storage page size. With target page fill rate The product; the row storage cost is the average physical row width. With auxiliary space overhead The sum of these values. In this embodiment of the invention, the logical fill step (LFS) is calculated based on the average physical row width obtained from the wide table metadata detector and the standard storage page size of the database (e.g., 16KB).

[0072] In one embodiment, the number of data rows can be expressed as follows: ; in, This indicates the number of records of the dense wide table that a single physical page can hold, i.e., the number of data rows mentioned above. This indicates the size of the database storage page, which is determined by those skilled in the art based on the specific use case. The target page fill rate is determined by those skilled in the art based on the specific use case; in one embodiment, the target page fill rate can be 0.7 to 0.85 to reserve synchronization buffer space. This represents the average physical row width. The auxiliary space overhead is determined by those skilled in the art based on the specific use case.

[0073] The logical fill step size is the maximum of one and the jump step size; the jump step size is the absolute value of the ratio of the serial number space size to the page capacity after correction; the serial number space size is: based on a preset number of bits and the number of bits occupied by the local serial number. The value is a power of the exponent; the corrected page size is the number of data rows. Site Correction Factor The product of these factors; the site correction factor is determined based on the number of active centers.

[0074] The preset bit length can be 2. Both the preset bit length and the site correction factor are determined by those skilled in the art based on the specific application scenario. In one embodiment, an expression for the logic fill step size is: ; in, This indicates the number of bits occupied by the local sequence number. This represents the site correction factor; in one embodiment, the site correction factor is dynamically adjusted according to the number of active-active centers to isolate the landing areas of primary keys on different active-active nodes.

[0075] The local sequence number for the primary key used in dense wide tables does not simply increment from 0, but rather dynamically calculates a logical fill step based on the average physical width of the table records. If the monitoring module detects that the current table is a wide table with extremely high write frequency, the generation engine ensures that the local sequence numbers corresponding to the dense wide tables generated by the same node fall within the address range of the same disk page (i.e., Page) in terms of physical storage distribution. By reserving tiny gap bits in the primary key, the physical layout of the primary key resonates with the fill rate of the B+ leaf nodes, thereby improving the write performance stability of dense wide tables in scenarios with continuously high-frequency data generation. PPA step control is a continuously effective incrementing strategy. As long as it is enabled for dense wide tables, the increment of the local sequence number strictly follows the rule of "current value + logical fill step," and is reset only during millisecond switching. This ensures that regardless of the timing of data row write requests, the primary key can be distributed across physical pages, thereby steadily reducing the frequency of page splits.

[0076] In a practical application scenario with 16KB physical pages, for a dense wide table with approximately 8KB of data per row, the primary key values ​​generated for five consecutive data rows are 1000, 1171, 1342, 1513, and 1684, respectively, with a logical padding step size of 171. For ease of explanation of the effects of this embodiment, only the local sequence number is shown here as the primary key value. In actual use, placing the local sequence number in the least significant bit achieves the same technical effect as in this specific example.

[0077] like Figure 4 The diagram shows a B+ tree, including a root node and multiple internal nodes belonging to the root node, as well as physical pages belonging to the corresponding internal nodes (including pages A, B, C, D, E, and F). When storing consecutive data rows into this B+ tree using the method of this embodiment, the insertion steps can be as follows: Insert 1000 → locate page A (range [0, 1100)), write; Insert 1171 → locate page B (range [1100, 1250)), write; Insert 1342 → locate page C (range [1250, 1400)), write; Insert 1513 → locate page D (range [1400, 1550)), write; Insert 1684 → locate page E (range [1550, 1700)), write. Each physical page stores only one row, and none are full (each page can store two rows), so no page splits are triggered. Subsequent insertions can continue to use the free space of these pages.

[0078] The following is combined with Figures 6-10 This paper details the collaborative use of column families and a global central lock in data synchronization. Firstly, in this embodiment of the invention, a global arbitration manager divides the columns of a wide table into column families, such as... Figure 6As shown, based on the probability that any two columns in a wide table will appear simultaneously in the same transaction, all columns of the wide table are divided into multiple column families, and a column family mask table is constructed. The specific steps include the following: Step 301: Monitor the incremental data capture log of the database in real time, and parse the set of columns involved in the transaction from the incremental data capture log.

[0079] The incremental data capture log is used to track row-level change events (including inserts, updates, and deletes) in the database and records these events in the order they occur.

[0080] The global arbitration manager continuously monitors the incremental data capture logs of the business system and parses out the set of columns C{c1,c2,...ck} modified by transaction Tn.

[0081] Step 302: Calculate the probability that any two columns in the column set appear simultaneously.

[0082] For each transaction, the probability that any two columns in the column set involved in the transaction appear simultaneously is calculated, and this is used as the basis for division. In this embodiment of the invention, the Jaccard similarity coefficient is used to calculate the probability that any two columns in the column set appear simultaneously. The formula for the Jaccard similarity coefficient is as follows: ; in, Representative column and column The probability of two events occurring simultaneously in the same transaction. Indicates a column Updated set of transactions, Indicates a column The updated set of transactions.

[0083] Step 303: Merge the two columns whose probabilities are greater than a predefined threshold into the same column cluster.

[0084] The predefined threshold can be set empirically, for example, 0.8. For each transaction, if the probability of two columns appearing simultaneously is greater than the predefined threshold, they will be classified into the same column family. For example, with a predefined threshold of 0.8, if transaction T1 modifies the column set C{c1,c2,c3,c4}, and the probability of c1 and c2 appearing simultaneously is 0.9, then c1 and c2 are classified into the same column family G2. Further calculations show that the probability of c1 and c3 appearing simultaneously is 0.7, and the probability of c1 and c4 appearing simultaneously is 0.9. This means that in the column set involved in transaction T1, the columns classified into the same column family G2 are c1, c2, and c4.

[0085] Step 304: Construct a column family mask table based on the mapping relationship between columns and column families.

[0086] In this embodiment of the invention, the column family mask table is also updated periodically (e.g., every hour or during off-peak hours) to avoid performance fluctuations in online services caused by high-frequency metadata changes, while ensuring eventual consistency of configuration changes.

[0087] This invention optimizes the lock request path by combining a global arbitration manager and a local pre-lock manager, such as... Figure 7 As shown, the lock on column family G1 in row R1 is granted to the target data center first through the local pre-lock manager. When the local pre-lock manager does not have permission, the lock on column family G1 in row R1 is granted to the target data center through the global arbitration manager. The specific steps are as follows: Step 401: The local prelock manager receives the lock request from the target data center for column family G1 in row R1 and determines whether it holds a row lease for row R1.

[0088] The row lease is effectively exclusive, meaning that during the valid lease period, no other data center will be granted a row lease for row R1. This embodiment of the invention optimizes the lock request path by pre-applying for a row lease for row R1 through a local pre-lock manager.

[0089] Step 402: If the local lock manager holds a row lease for row R1, the local lock manager directly grants a lock on column family G1 in row R1 to the target data center.

[0090] In this embodiment, if the local pre-lock manager holds a row lease for row R1, it means that the local pre-lock manager has the right to grant lock permissions to all column families in row R1. At this time, there is no need to apply for a lock request from the global arbitration manager across centers. The local pre-lock manager can complete the granting of lock permissions. This design can effectively alleviate the cumulative effect of cross-center network latency.

[0091] Step 403: If the local pre-lock manager does not hold a row lease for row R1, it initiates a lock request for column family G1 in row R1 to the global arbitration manager. The global arbitration manager determines the global lock status of column family G1 in row R1 and grants locking permission based on the global lock status.

[0092] In this embodiment, if the local pre-lock manager does not hold a row lease for row R1, it means the local pre-lock manager is not authorized to grant locking permissions for all column families in row R1. In this case, the global arbitration manager is responsible for the lock request for column family G1 in row R1. See also Figure 8 The global arbitration manager determines the global lock status of column family G1 in row R1 and grants locking permissions based on the global lock status, specifically including the following steps: Step 501: The Global Arbitration Manager checks whether the lock of column family G1 in row R1 is already held by another data center.

[0093] In this embodiment, the global arbitration manager checks whether there is a lock conflict in column cluster G1 in row R1, that is, whether the lock of column cluster G1 in row R1 has been held by another data center.

[0094] Step 502: If no other data center holds the lock for column family G1 in row R1, check whether the row lease for row R1 has been held by another data center.

[0095] In this embodiment, if another data center holds a lock on column cluster G1 in row R1, the current lock request for column cluster G1 in row R1 is added to the lock waiting queue. After the data center holding the lock on column cluster G1 in row R1 releases the resources, it is further determined whether the row lease of row R1 has been held by another data center.

[0096] If no other data center holds the lock for column cluster G1 in row R1, then further check whether there is a resource conflict in the row lease of row R1, that is, whether the row lease of row R1 has been held by another data center.

[0097] Step 503: If no other data center holds a row lease for row R1, the global arbitration manager grants a lock to the target data center for column family G1 in row R1.

[0098] In this embodiment, if other data centers do not hold a row lease for row R1, it means that there is neither a lock conflict nor a resource conflict for column cluster G1 in row R1. That is, the lock resource for column cluster G1 in row R1 is not occupied. At this time, the global arbitration manager will grant the target data center a lock for column cluster G1 in row R1.

[0099] If another data center holds a row lease for row R1, the global arbitration manager will send a lease revocation signal to the local pre-lock manager that currently holds the row lease for R1. Upon receiving the revocation signal, the local pre-lock manager will mark the lease as entering a pending release state. After the local pre-lock manager completes the ongoing data change and synchronization tasks related to row R1, it will not renew the lease and will smoothly return the row-level permissions to the global arbitration manager. The global arbitration manager will then decide whether to grant the target data center a lock on column family G1 in row R1.

[0100] When the local pre-lock manager holds a row lease for row R1, it directly handles the granting of locking permissions. This is an optimization of the lock request path in this embodiment of the invention. When a data center has a high frequency of lock requests for a particular row, it pre-applies for a row lease for that specific row for the data center. Figure 9 As shown, the database synchronization method based on column families and a global central lock further includes the following steps: Step 601: Real-time statistics on the lock request frequency of each row in the wide table within a preset time period.

[0101] Specifically, the local pre-lock manager first checks whether it is tracking the access frequency of row R1. If it is not tracking, it starts tracking the access frequency of row R1 and calculates the lock request frequency f(R1) of row R1 in real time within a preset time ΔT.

[0102] Step 602: If the lock request frequency of row R1 is greater than or equal to the preset frequency, then request a row lease for row R1 from the global arbitration manager, determine the global lease status of row R1 through the global arbitration manager, and grant a row lease for row R1 according to the global lease status.

[0103] In this embodiment, the lock request frequency of row R1 is greater than or equal to the preset frequency, which means that the data center requests locks for row R1 frequently. If a global lock request is made across the data center every time, the accumulated latency cost will directly drag down the real-time response capability of the system. Therefore, this embodiment of the invention obtains the row lease of row R1 in advance so that in the next lock request for row R1, the lock permission can be granted directly through the local pre-lock manager.

[0104] If the lock request frequency for row R1 is greater than or equal to a preset frequency, the local pre-lock manager also enables asynchronous heartbeat to maintain the lease duration. If the lock request frequency for row R1 is less than the preset frequency, the local pre-lock manager, after acquiring a row lease for row R1, will no longer maintain the row lease for row R1. In this case, after the row lease for row R1 expires, if a lock request for any column family of row R1 is received again, the global arbitration manager will be responsible for granting the lock permission. In one embodiment, if the global arbitration manager rejects the request, it will reset the access frequency tracking for row R1 and record one failure. After reaching the number of failures, it will no longer request the lease and will abandon tracking the access frequency of row R1 for a period of time.

[0105] like Figure 10 As shown, the global lease status of row R1 is determined by the global arbitration manager, and a row lease for row R1 is granted based on the global lease status. The specific steps include the following: Step 701: The Global Arbitration Manager receives the row lease request for row R1 and checks whether the row lease for row R1 is already held by another data center.

[0106] In this embodiment, the global arbitration manager checks whether there is a resource conflict in row R1, that is, it checks whether the row lease of row R1 has been held by other data centers. If other data centers have already held the row lease of row R1, the global arbitration manager will not grant the target data center the row lease for row R1.

[0107] Step 702: If no other data center holds a row lease for row R1, check whether the lock for row R1 + any column family has been held by another data center.

[0108] In this embodiment, if other data centers do not hold a row lease for row R1, the global arbitration manager further checks whether the lock for row R1 + any column family has been held by other data centers. If other data centers have held the lock for row R1 + any column family, it means that other data centers also have a need to lock row R1 + any column family. At this time, the global arbitration manager will not grant the target data center a row lease for row R1.

[0109] Step 703: If no other data center holds a lock on row R1 + any column family, the global arbitration manager grants the target data center a row lease for row R1.

[0110] In this embodiment, when the row lease of row R1 is not held by other data centers, and the lock of row R1 + any column family is not held by other data centers, it means that other data centers do not have a locking requirement for row R1 + any column family. At this time, the target data center has a locking requirement for row R1 + any column family. The global arbitration manager will grant the target data center a row lease for row R1 to accelerate the target data center's locking request for any column family of row R1 while ensuring that other data centers are not blocked.

[0111] In environments with multiple active nodes or multiple tables, the number of lock objects that need to be managed grows exponentially. Traditional single-machine central lock managers quickly exceed critical limits in terms of memory usage, hash collision rate, and lock request processing queue depth when faced with a large number of active row locks, making the lock manager itself a single point of performance bottleneck. Furthermore, the lock holding and releasing process involves frequent network heartbeats and state synchronization; under high traffic conditions, this management traffic can lead to severe resource contention with the actual data synchronization traffic.

[0112] To address the aforementioned issues, this embodiment of the application distributes the lock and lease management rights for different table objects to different arbitration nodes based on consistent hashing. Each arbitration node receives lock requests for column families within a row and grants locking permissions to those column families. When multiple data centers subsequently send lock requests to the global arbitration manager, distributing each lock request to different arbitration nodes reduces the pressure on the global arbitration manager, thus eliminating single-point bottlenecks. Specifically, an update request from a data center for row R1 is first intercepted by the data center's local pre-lock manager. The local pre-lock manager identifies the column family ID and table ID involved in the update request. After determining that the local pre-lock manager does not hold a row lease for row R1, it sends the corresponding lock request to the corresponding arbitration node based on the table ID. The arbitration node then determines the global lock status according to steps 401-403 and grants lock permissions. Each arbitration node manages locks and leases for multiple different table objects and maintains the lease duration through asynchronous heartbeats.

[0113] While distributing each lock request to different arbitration nodes alleviates the pressure on the global arbitration manager, certain issues remain for each arbitration node when maintaining lease durations via asynchronous heartbeats. Specifically, arbitration nodes typically maintain leases and heartbeats at the service instance or database table partition level. In large systems involving massive amounts of business tables, if each arbitration node needs to maintain a heartbeat independently for each database table, the signaling scale will reach "number of nodes × number of database tables," leading to a scale explosion. The resulting massive requests will instantly saturate network bandwidth and cause backend coordination services (such as Redis or etcd) to enter a denial-of-service state due to overloaded query rates per second. For example, with 100 service nodes, each responsible for 10,000 database tables, the heartbeat scale becomes "100 × 10,000," reaching a million levels. Each database table needs periodic renewal (e.g., at the second level), resulting in millions of queries per second for the coordination service.

[0114] If the snowflake algorithm and its variants are used to generate the primary key, the primary key can only guarantee global uniqueness, and cannot solve the problems of signaling storms and delayed fault recovery caused by massive table objects. Therefore, this embodiment of the invention also proposes a preferred primary key structure: the primary key follows the arrangement of dynamic timestamp bits, table group identifier bits, and local sequence number bits from high to low order. Furthermore, based on this, such as... Figure 9 As shown, the primary key structure of this embodiment is divided into the following parts from high to low: dynamic timestamp bit (i.e., ), geographic node identifier (i.e., ), table group identifier bit (i.e., ), disturbance position (i.e., ) and local sequence number position (i.e., It should be noted that when the local sequence number is placed in the least significant bit, the numerical difference of the primary key is mainly determined by the jump of the local sequence number when the higher bits such as the timestamp are the same. This allows these primary keys to be distributed and stored in different physical pages in the B+ tree clustered index. In other words, placing the local sequence number in the least significant bit can achieve the same technical effect as in this specific example.

[0115] The dynamic timestamp is the time interval elapsed since the time the data row was generated in the corresponding database table, starting from a preset start time. The preset start time is determined by those skilled in the art based on the specific use case. In one embodiment, it can represent the number of milliseconds elapsed since the time the data row was generated, starting from the system-set start time.

[0116] In one embodiment, its mathematical expression is as follows: ; in, This represents the final 64-bit distributed unique primary key. This indicates a dynamic timestamp bit. This indicates the geographic node identifier. This indicates the table group identifier, used to represent a set of logical tables with the same business attributes or synchronization strategies. This represents the perturbation bit (i.e., the entropy bit). Indicates the local sequence number position. , , and These represent the displacement step size for each component, used to control the physical position of each part in 64-bit space. "This indicates the bitwise left shift operator." " indicates the bitwise OR operator.

[0117] In the primary key generated in this embodiment of the invention, the table group identifier needs to be located between the dynamic timestamp and the local sequence number, so that the primary key itself can carry the business or priority semantics represented by the logical table group without destroying the global temporal order of the primary key. When the arbitration node in this embodiment of the invention maintains the lease term through asynchronous heartbeat, it no longer requests the partition primary key of database table A, but determines a set of logical tables with the same business attributes or synchronization strategy through the table group identifier bit in the primary key generated in this embodiment, and requests a lease container containing all logical table groups carried by the arbitration node through the table group aggregation coordinator, wherein the lease container contains the authorization information of all logical table groups in the list.

[0118] To illustrate this plan more clearly, the following will be combined with... Figure 11 The system architecture diagram provides a detailed description of the database synchronization method based on column families and a global central lock.

[0119] See Figure 11The system architecture includes a global arbitration layer, multi-active center A, and multi-active center B. The global arbitration manager is deployed in the global arbitration layer. Each of multi-active center A and multi-active center B deploys a local lock manager. The global arbitration layer distributes table management rights to multiple shards (including shard 1 to shard n), with each shard granting lock permissions. Each shard includes a row lease queue and a row + column family lock queue. The row lease queue stores lease information for row leases held by each center, and the row + column family lock queue stores lock information for the column families involved in the rows held by each center. The local lock manager includes a row + column family lock queue and a row lease queue. When each shard maintains the lease term, it uses the table group identifier bit in the primary key to determine a set of logical tables with the same business attributes or synchronization strategy, and uses a lease container to store the authorization information of all logical table groups. During data synchronization, taking the operation of shard 1 as an example, when the upper-layer application in multi-active center A writes data into database A, the upper-layer application sends a lock request to the local lock manager of multi-active center A. Local lock manager 1 determines whether a row lease for the corresponding row exists locally. If it exists, it directly grants lock permission; if it does not exist, it requests lock permission from shard 1. Shard 1 checks whether there is a lock conflict in its internal row column family lock queue. If there is no lock conflict, it checks whether there is a resource conflict in the row lease queue. If there is no resource conflict, it grants lock permission. Shard 1 grants lock permission to multi-active center A. After the deadline, multi-active center A writes data to database A. At this time, the synchronization engine of multi-active center A captures the updated data and sends the corresponding change record to the synchronization engine of multi-active center A. The synchronization engine of multi-active center B then writes the same data to database B according to the change record, thereby ensuring the data consistency of the remote multi-center power dispatching platform. When multi-active center B's synchronization engine writes data, it also needs to first determine the data source table type of the change record. According to the different types of database tables, the data is stored in the leaf node in primary key order according to different writing methods to further improve the writing performance.

[0120] Example 2: Based on the database synchronization method based on column families and a global central lock provided in the foregoing embodiments, the present invention also provides an apparatus for implementing the above-mentioned database synchronization method based on column families and a global central lock, such as... Figure 12 The diagram shown is a schematic representation of the device architecture according to an embodiment of the present invention. The database synchronization device based on column families and a global central lock in this embodiment includes one or more processors 21 and a memory 22. Figure 12 Take a processor 21 as an example.

[0121] Processor 21 and memory 22 can be connected via a bus or other means. Figure 12 Taking the example of a connection between China and Israel via a bus.

[0122] The memory 22, as a non-volatile computer-readable storage medium for database synchronization based on column families and a global central lock, can be used to store non-volatile software programs and non-volatile computer-executable programs, such as the database synchronization method based on column families and a global central lock in the foregoing embodiments. The processor 21 executes various functional applications and data processing of the database synchronization device based on column families and a global central lock by running the non-volatile software programs, instructions, and modules stored in the memory 22, thereby implementing the database synchronization method based on column families and a global central lock in the foregoing embodiments.

[0123] Memory 22 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, memory 22 may include memory remotely located relative to processor 21, which can be connected to processor 21 via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0124] The program instructions / modules are stored in memory 22 and, when executed by one or more processors 21, execute the database synchronization method based on column families and global central locks as described in the previous embodiments.

[0125] This invention also provides a non-volatile computer storage medium storing computer-executable instructions that are executed by one or more processors, for example... Figure 12 One of the processors 21 enables one or more of the processors to execute the database synchronization method based on column families and a global central lock as described in the foregoing embodiments.

[0126] It is worth noting that the information interaction and execution process between the modules and units in the above-mentioned device and system are based on the same concept as the processing method embodiment of the present invention. For details, please refer to the description in the method embodiment of the present invention, and will not be repeated here.

[0127] Those skilled in the art will understand that all or part of the steps in the various methods of the embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, which may include: read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0128] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A database synchronization method based on column families and a global central lock, characterized in that, include: Based on the probability that any two columns in a wide table are modified simultaneously in the same transaction, all columns of the wide table are divided into multiple column families, and a column family mask table is constructed. Identify the column family G1 involved in trip R1 based on the column family mask table; Grant a lock to column family G1 involved in row R1 to the target data center; While holding the lock on column family G1 in row R1, update the field corresponding to column family G1 in row R1, generate a change record, and send the change record to the data synchronization entry of other data centers.

2. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: During the data synchronization process in other data centers based on the change records, the data source table type of the change records is identified; If the data source table type of the change record is a dense wide table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are distributed and stored in different physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are distributed in a jump-incremental manner. If the data source table type of the change record is a dense narrow table, then according to the primary key values ​​of multiple consecutive data rows in the change record, the corresponding multiple consecutive data rows are continuously stored in physical pages, wherein the local sequence numbers used to constitute the primary key values ​​are in a compact auto-incrementing distribution; If the data source table type of the change record is a sparse table, then the multiple consecutive data rows in the change record are stored in physical pages in a default manner according to the primary key values ​​of the multiple consecutive data rows in the change record, wherein the local sequence numbers used to constitute the primary key values ​​are distributed by default.

3. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: The local lock manager receives a lock request from the target data center for column family G1 in row R1 and determines whether it holds a row lease for row R1. If the local lock manager holds a row lease for row R1, the local lock manager directly grants a lock on column family G1 in row R1 to the target data center. If the local pre-lock manager does not hold a row lease for row R1, it initiates a lock request for column family G1 in row R1 to the global arbitration manager. The global arbitration manager determines the global lock status of column family G1 in row R1 and grants locking permission based on the global lock status.

4. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: The global arbitration manager checks whether the lock in column cluster G1 of row R1 is already held by another data center; If no other data center holds the lock for column family G1 in row R1, check whether the row lease for row R1 is already held by another data center. If no other data center holds a row lease for row R1, the global arbitration manager grants a lock to the target data center for column family G1 in row R1.

5. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: Real-time statistics on the lock request frequency of each row in the wide table within a preset time period; If the lock request frequency of row R1 is greater than or equal to the preset frequency, a row lease for row R1 is requested from the global arbitration manager. The global arbitration manager determines the global lease status of row R1 and grants a row lease for row R1 based on the global lease status.

6. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: The Global Arbitration Manager receives the row lease request for row R1 and checks whether the row lease for row R1 is already held by another data center; If no other data center holds a row lease for row R1, check if the lock for row R1 + any column family is already held by another data center. If no other data center holds a lock on row R1 and any column family, the global arbitration manager grants the target data center a row lease for row R1.

7. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The method further includes: Monitor the incremental data capture log of the database in real time, and parse the set of columns involved in the transaction from the incremental data capture log; Calculate the probability that any two columns in the column set appear simultaneously; Merge two columns whose probabilities are greater than a predefined threshold into the same column cluster; Based on the mapping relationship between columns and column families, a column family mask table is constructed.

8. The database synchronization method based on column families and a global central lock according to claim 1, characterized in that, The formula for calculating the probability that any two columns in the column set appear simultaneously is as follows: ； in, Representative column and column The probability of two events occurring simultaneously in the same transaction. Indicates a column Updated set of transactions, Indicates a column The updated set of transactions.

9. A database synchronization device based on column families and a global central lock, characterized in that, include: At least one processor; And a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor for performing the database synchronization method based on column families and a global central lock as described in any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the database synchronization method based on column families and a global central lock as described in any one of claims 1-8.

Images