A method, system and device for realizing non-stop heterogeneous of a table without primary key based on database isolation level

Abstract

This invention relates to the field of database data synchronization and migration technology, and provides a method, system, and device for achieving non-disruptive heterogeneous synchronization of keyless tables based on database isolation levels. The method includes: in the snapshot initialization phase, establishing a main connection with the source database, recording a first log position and applying a short-term read lock to the target keyless table, recording a second log position, setting a repeatable read isolation level through a working sub-connection to obtain a consistent snapshot, and releasing the read lock; in the full synchronization phase, reading all data according to the consistent snapshot and writing it to the target heterogeneous database to form a data baseline; in the incremental synchronization phase, collecting incremental data from the source database starting from the first log position, filtering the incremental data according to the second log position, and synchronizing only changes that occurred after the consistent snapshot to the target database. This invention can achieve non-disruptive, strongly consistent, dependency-free, low-intrusion, highly versatile, and highly reliable heterogeneous synchronization of keyless tables.

Description

Technical Field

[0001] This invention relates to the field of database data synchronization and migration technology, and in particular to a method, system and device for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels. Background Technology

[0002] In scenarios involving cross-database, heterogeneous migration, and incremental synchronization, traditional data synchronization solutions generally rely on table primary keys or unique indexes for data sharding, incremental identification, and duplicate filtering. This achieves relatively stable full and incremental synchronization.

[0003] In real-world business scenarios, there are numerous tables without primary keys, such as business transaction tables, operation log tables, historical archive tables, and intermediate processing tables. Because these tables were not designed with primary keys initially, or because the use of primary keys was intentionally avoided for performance reasons, they lack a unique identifier during data synchronization, making traditional synchronization solutions difficult to apply.

[0004] Currently, for data synchronization of tables without primary keys, existing technologies mainly fall into the following two categories:

[0005] The first type is the full data migration solution with downtime. This solution involves exporting all data first, then transferring incremental data, through methods such as locking tables and pausing write operations. Specific operations typically include: pausing business writes, applying table-level locks, exporting all data, importing it into the target database, and finally transferring the incremental data generated during the downtime. The drawbacks of this solution are: long business interruption time, failing to meet the 24 / 7 continuous operation requirements of high-availability systems; business writes being blocked during table locking, significantly impacting the production system; and the lack of a strict consistency boundary between full and incremental data, easily leading to data duplication or loss.

[0006] The second type is log-based incremental synchronization. This approach relies on database incremental logs (such as MySQL's binlog and PostgreSQL's WAL) for real-time data collection and playback. However, for tables without primary keys, the lack of unique identifiers means that each change in the incremental log cannot be precisely correlated with records in the full dataset. This makes it impossible to accurately distinguish which data has been synchronized and which is newly added during the transition between full and incremental data. Specifically, after full synchronization is complete, the incremental synchronization phase cannot determine from which point in time to apply the changes; update operations in the incremental log may fail to synchronize or result in data corruption due to the inability to locate the corresponding rows; and repeated execution of incremental data can easily cause data redundancy or conflicts in the target database.

[0007] Chinese patent CN117235043A discloses a database migration method, device, electronic device, and medium. Although this solution has made some optimizations in the exception handling of migration of tables without primary keys, its technical path still remains within the traditional framework of "full migration with downtime". It has defects such as business interruption, missing incremental data, weak consistency guarantee, crude verification methods, and insufficient heterogeneous support. It cannot achieve seamless full and incremental migration of tables without primary keys under continuous business write conditions, and it is difficult to meet the core requirements of modern enterprise-level data migration for high availability, strong consistency, and zero interruption.

[0008] Therefore, how to achieve data consistency and synchronization reliability of tables without primary keys during full and incremental heterogeneous synchronization without interrupting business operations, relying on primary keys, or intruding on the source database has become a technical challenge that urgently needs to be solved in this field. Summary of the Invention

[0009] In view of this, in order to overcome the shortcomings of the prior art, the present invention aims to provide a method, system and device for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels.

[0010] According to a first aspect of the present invention, a method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels is provided, the method comprising:

[0011] During the snapshot initialization phase, a main connection to the source database is established, the first log position is recorded, a short-term read lock is applied to the target table without a primary key, the second log position is recorded, the repeatable read isolation level is set through the working sub-connection to obtain a consistent snapshot, and the read lock is released.

[0012] During the full synchronization phase, all data is read based on the consistency snapshot and written to the target heterogeneous database to form a data baseline.

[0013] During the incremental synchronization phase, incremental data from the source database is collected starting from the first log position. The incremental data is then filtered based on the second log position, and only changes that occurred after the consistency snapshot are synchronized to the target database.

[0014] Optionally, in the method of implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels of the present invention, the first log position is the current log position obtained from the source database before applying a read lock, which serves as the starting point for subsequent incremental synchronization; the second log position is the log position recorded after applying a read lock and before obtaining a consistent snapshot, which is used to identify the snapshot time and serve as the boundary benchmark between full and incremental data.

[0015] Optionally, the method of implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels of the present invention applies a short-term read lock in the snapshot initialization phase as follows: a database table-level lock command is executed through the main connection to apply a read lock to the target table without primary keys. The lock duration only covers the time window required for the second log position record and the consistency snapshot trigger operation. After the lock is released, the source database immediately restores all write capabilities.

[0016] Optionally, the method of the present invention for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels obtains a consistent snapshot in the snapshot initialization phase as follows: the transaction isolation level is set to repeatable read through a working sub-connection, and a lightweight query statement is executed to trigger the database to generate a consistent view, which reflects the global data state at a certain moment after the read lock is applied and before the read lock is released.

[0017] Optionally, in the method of implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels of the present invention, the working sub-connections are created by the main connection and included in the connection pool for unified management, supporting multi-threaded concurrent reading of full data in a consistent snapshot; each working sub-connection independently reads a portion of the data shards, and automatically releases connection resources after the full data reading is completed.

[0018] Optionally, the method of the present invention for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels reads full data based on a consistent snapshot during the full synchronization phase in the following manner: data is obtained in batches using cursor traversal or pagination query, and each batch of data is written to the target heterogeneous database after being read. Automatic data type conversion and abnormal retry mechanism are supported during the writing process.

[0019] Optionally, in the method of implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels of the present invention, the incremental data collected starting from the first log position in the incremental synchronization step includes the database's binary log, write-ahead log, or change data capture log, and the collection process supports breakpoint resume and position persistence.

[0020] Optionally, in the method of implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels of the present invention, incremental data is filtered according to the second log position in the incremental synchronization step as follows: the log position associated with each data change in the incremental log is compared with the second log position, and only the changed data with log positions greater than or equal to the second log position is retained, while historical changes with positions less than the second log position are discarded.

[0021] According to a second aspect of the present invention, a system for implementing non-disruptive heterogeneous databases with no primary key tables based on database isolation levels is provided. The system includes:

[0022] The snapshot initialization module is used to establish a main connection with the source database, record the first log position and apply a short-term read lock to the target table without a primary key, record the second log position, set the repeatable read isolation level through the working sub-connection to obtain a consistent snapshot, and release the read lock;

[0023] The full synchronization module is used to read all data based on the consistency snapshot and write it to the target heterogeneous database to form a data baseline.

[0024] The incremental synchronization module collects incremental data from the source database starting from the first log position, filters the incremental data based on the second log position, and synchronizes only the changed data that occurred after the consistency snapshot to the target database.

[0025] According to a third aspect of the present invention, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first aspect of the present invention.

[0026] This invention relates to a method, system, and device for implementing non-disruptive heterogeneous databases with tables lacking primary keys based on database isolation levels, and has the following beneficial technical effects:

[0027] I. Achieve true non-stop synchronization for tables without primary keys.

[0028] During the snapshot initialization phase, only short-term read locks are applied to the target table. The lock duration only covers the time window required for the second log position record and the consistent snapshot trigger operation, typically in milliseconds or seconds. Compared to traditional downtime migration solutions that require long-term table locking or even suspension of business writes, this invention minimizes business impact. After the lock is released, the source database immediately resumes full write capability. The business system is unaware of and uninterrupted throughout the entire synchronization process, meeting the requirements for continuous operation of high-availability systems. By cleverly utilizing the repeatable read isolation level of the database and replacing the traditional long-term table locking mechanism with consistent snapshots, the technical bias of requiring downtime for synchronization of tables without primary keys is fundamentally resolved.

[0029] Second, establish strict consistency boundaries between full and incremental data to ensure that data is not duplicated, lost, or disordered.

[0030] This invention fundamentally eliminates data duplication, loss, or corruption caused by overlapping or discontinuous time windows during the full and incremental data synchronization process, achieving seamless integration and eventual consistency between full and incremental data. In scenarios involving the synchronization of keyless tables with millions of data entries, this invention can achieve synchronization with zero data loss and zero duplicate records.

[0031] Third, it completely eliminates the reliance on table primary keys or unique indexes, solving the industry-wide technical challenge of synchronizing tables without primary keys.

[0032] This invention is widely applicable to various table structures without primary keys, including business transaction tables, operation log tables, historical archive tables, intermediate processing tables, audit tables, and other table types that have long plagued the field of data synchronization.

[0033] Fourth, it has low invasiveness to the source database and minimal impact on performance, ensuring stable operation of the production system.

[0034] This invention follows the principle of "zero intrusion and low impact," which minimizes the performance impact on the source database during the synchronization process, ensuring the stable operation of the production system and business continuity.

[0035] Fifth, it has good versatility and scalability, and is easy to integrate into existing data synchronization systems.

[0036] It does not rely on the proprietary features of any specific database product, requiring only that the database supports repeatable read isolation level and log location acquisition capabilities, thus ensuring broad compatibility with mainstream relational databases such as MySQL, PostgreSQL, and Oracle. Furthermore, it can be directly integrated into existing data migration tools, data synchronization platforms, and change data capture systems, or deployed as a standalone data synchronization component.

[0037] VI. Improve the stability, accuracy, and observability of synchronization for tables without primary keys.

[0038] Multiple safeguards are in place during the synchronization process, enabling stable, accurate, and traceable synchronization services in large-scale enterprise-level data migration scenarios. Attached Figure Description

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

[0040] Figure 1 An example architecture diagram of a heterogeneous system with no primary key table that achieves uninterrupted operation based on database isolation levels according to an exemplary embodiment 2 of the present invention;

[0041] Figure 2 This is a timing diagram illustrating a method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to Exemplary Embodiment 3 of the present invention.

[0042] Figure 3 This is a schematic diagram of the structure of the device provided by the present invention. Detailed Implementation

[0043] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0044] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0045] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0046] Example 1

[0047] Exemplary Example 1 of the present invention provides a method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels. The method is implemented in the following manner:

[0048] During the snapshot initialization phase, a main connection to the source database is established, the first log position is recorded, a short-term read lock is applied to the target table without a primary key, the second log position is recorded, the repeatable read isolation level is set through the working sub-connection to obtain a consistent snapshot, and the read lock is released to restore the write capability of the source database, achieving business-unobservable operation.

[0049] In this embodiment, the first log position is the current log position obtained from the source database before applying the read lock, serving as the starting point for subsequent incremental synchronization; the second log position is the log position recorded after applying the read lock but before obtaining the consistency snapshot, used to identify the snapshot time and serving as the boundary between full and incremental data. In practical applications, the first log position is obtained before applying the read lock by executing a database log position query command, used to identify the starting position of incremental synchronization; the second log position is obtained after applying the read lock but before obtaining the consistency snapshot, used to accurately identify the database global state time corresponding to the consistency snapshot, serving as the benchmark boundary for subsequent incremental data filtering.

[0050] During the snapshot initialization phase, this embodiment applies a short-term read lock in the following manner: a database table-level lock command is executed through the main connection to apply a read lock to the target table without a primary key. The lock duration only covers the time window required for the second log point record and the consistency snapshot trigger operation. After the lock is released, the source database immediately restores all write capabilities, thereby achieving non-stop synchronization.

[0051] During the snapshot initialization phase, this embodiment obtains a consistent snapshot in the following manner: the transaction isolation level is set to repeatable read through a working sub-connection, and a lightweight query statement is executed to trigger the database to generate a consistent view. This consistent view reflects the global data state at a certain moment after a read lock is applied and before the read lock is released, ensuring that subsequent full data readings are based on the same data version and avoiding phantom reads and non-repeatable reads.

[0052] It should be noted that in this embodiment, the working sub-connection is created by the main connection and included in the connection pool for unified management, supporting multi-threaded concurrent reading of the full data in the consistent snapshot; each working sub-connection independently reads a portion of the data shards, and automatically releases connection resources after the full data is read. The parallel reading mechanism improves the efficiency of full synchronization while reducing the connection pressure on the source database.

[0053] During the full synchronization phase, all data is read based on the consistent snapshot and written to the target heterogeneous database to form a data baseline.

[0054] As an optional example, in the full synchronization phase, this embodiment reads the full data based on the consistency snapshot in the following manner: data is obtained in batches by using cursor traversal or pagination query, and each batch of data is written to the target heterogeneous database after being read. During the writing process, automatic data type conversion and abnormal retry mechanism are supported to ensure that the data is written completely without duplication or loss.

[0055] During the incremental synchronization phase, incremental data from the source database is collected starting from the first log position. The incremental data is then filtered based on the second log position, and only changes that occurred after the consistency snapshot are synchronized to the target database, thus achieving eventual consistency between the full and incremental data.

[0056] In the incremental synchronization step, the incremental data collected starting from the first log position in this embodiment includes the database's binary log, write-ahead log, or change data capture log. The collection process supports breakpoint resume and position persistence, ensuring that the incremental synchronization task can continue collecting from the last recorded position after an abnormal interruption, avoiding data loss or duplicate consumption.

[0057] This embodiment filters incremental data based on the second log position in the following way: the log position associated with each data change in the incremental log is compared with the second log position, and only the changed data with a log position greater than or equal to the second log position is retained, while historical changes with a position less than the second log position are discarded. This ensures that data changes before the consistency snapshot time are not repeatedly synchronized to the target database, and achieves strict connection between full and incremental data.

[0058] It should be noted that the method in this embodiment is applicable to various database table structures without primary keys, including transaction tables, log tables, historical tables, intermediate tables, and other table types that do not have primary keys or unique indexes; it is also compatible with transactional relational databases that support repeatable read isolation levels, such as MySQL, PostgreSQL, and Oracle, and has good versatility and portability.

[0059] Example 2

[0060] Exemplary embodiment 2 of the present invention provides a system for implementing non-disruptive heterogeneous systems with no primary key tables based on database isolation levels. Figure 1 An example architecture diagram of a heterogeneous system with no primary key tables that operates without downtime based on database isolation levels, according to Exemplary Embodiment 2 of the present invention, is shown below. Figure 1 As shown in this embodiment, the system for implementing non-disruptive heterogeneous systems with no primary key tables based on database isolation levels includes:

[0061] The snapshot initialization module is used to establish a main connection with the source database, record the first log position and apply a short-term read lock to the target table without a primary key, record the second log position, set the repeatable read isolation level through the working sub-connection to obtain a consistent snapshot, and release the read lock;

[0062] The full synchronization module is used to read all data based on the consistency snapshot and write it to the target heterogeneous database to form a data baseline.

[0063] The incremental synchronization module collects incremental data from the source database starting from the first log position, filters the incremental data based on the second log position, and synchronizes only the changed data that occurred after the consistency snapshot to the target database.

[0064] It should be noted that the target database in this embodiment supports a variety of data storage systems, including relational databases, non-relational databases, data warehouses, or data lakes. During the data writing process, the storage system automatically completes the structure conversion, field mapping, and data type adaptation between the source table and the target table according to predefined mapping rules, and supports automatic retries and exception alarms when writing fails, ensuring the stable operation of the synchronization task.

[0065] Example 3

[0066] Exemplary Example 3 of the present invention provides a method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels. The method of this embodiment will be further described in detail below in a specific scenario.

[0067] Figure 2 This is a timing diagram illustrating a method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to Exemplary Embodiment 3 of the present invention. Figure 2 As shown, in this embodiment, the method of the present invention is implemented in the following manner:

[0068] Phase 1: Snapshot Initialization

[0069] 1. Create a main connection and record position 1 (migration starting point) from the source database. Taking MySQL as an example, execute SHOWMASTER STATUS to obtain the current position information of the binlog.

[0070] 2. Execute LOCK TABLE to prevent DDL operations. Taking MySQL as an example, execute FLUSH TABLES table_a WITHREAD LOCK and you can set a timeout.

[0071] 3. Read point 2 from the source database and record it (as the boundary point between the full and incremental data).

[0072] 4. The main connection creates and starts sub-connections, which are managed through a thread pool.

[0073] 5. Set the transaction isolation level of the worker subconnection to REPEATABLE READ. Taking MySQL as an example, execute SETTRANSACTION ISOLATION LEVEL REPEATABLE READ to set the repeatable read isolation level.

[0074] 6. Execute lightweight queries to obtain a consistent view. Taking MySQL as an example, executing `SELECT * FROM target table LIMIT 0` triggers the generation of a consistent snapshot, obtaining a data view at the same point in time.

[0075] 7. The main connection executes `UNLOCK TABLE` to release table locks, restoring the source database's write capability and achieving uninterrupted operation. Taking MySQL as an example, this is done by executing `UNLOCK TABLES`.

[0076] 8. Release the main connection to avoid consuming database resources.

[0077] Phase Two: Full Synchronization

[0078] 9. Working sub-connections read the full data based on consistent snapshots.

[0079] 10. The working sub-connect writes the read data into the target database, forming a full data baseline.

[0080] After full synchronization is complete, release all working sub-connections.

[0081] Phase 3: Incremental Synchronization

[0082] 11. Start the incremental synchronization process.

[0083] 12. The incremental collector starts collecting source database logs from point 1 to obtain incremental data.

[0084] 13. Filter out incremental data that occurred before site 2 based on the information at site 2.

[0085] 14. Continuously write the filtered data into the target database.

[0086] Ultimately, this achieves eventual data consistency between the source and target databases.

[0087] The method and system for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels in this invention have the following technical effects by constructing a core technical framework of "consistent snapshots + log position demarcation":

[0088] I. Achieve true non-stop synchronization for tables without primary keys.

[0089] During the snapshot initialization phase, only short-term read locks are applied to the target table. The lock duration only covers the time window required for the second log position record and the consistent snapshot trigger operation, typically in milliseconds or seconds. Compared to traditional downtime migration solutions that require long-term table locking or even suspension of business writes, this invention minimizes business impact. After the lock is released, the source database immediately resumes full write capability. The business system is unaware of and uninterrupted throughout the entire synchronization process, meeting the requirements for continuous operation of high-availability systems. By cleverly utilizing the repeatable read isolation level of the database and replacing the traditional long-term table locking mechanism with consistent snapshots, the technical bias of requiring downtime for synchronization of tables without primary keys is fundamentally resolved.

[0090] Second, establish strict consistency boundaries between full and incremental data to ensure that data is not duplicated, lost, or disordered.

[0091] By employing a dual-logging mechanism, the first log point identifies the incremental synchronization start point, and the second log point identifies the consistency snapshot time, thus constructing a precise data boundary benchmark. All full data originates from a consistency snapshot generated at the same time, ensuring data version consistency. Incremental data is collected starting from the first log point and rigorously filtered based on the second log point, only recording actual business changes occurring after the synchronization snapshot time. This fundamentally eliminates data duplication, loss, or corruption caused by overlapping or discontinuous time windows during the full and incremental data integration process, achieving seamless integration and eventual consistency between full and incremental data. In scenarios involving millions of data points and no primary key table synchronization, this invention can achieve synchronization with zero data loss and zero duplicate records.

[0092] Third, it completely eliminates the reliance on table primary keys or unique indexes, solving the industry-wide technical challenge of synchronizing tables without primary keys.

[0093] This invention eliminates the need for primary keys in data reading and synchronization, eliminating the need for primary key-based data sharding, duplicate filtering, and change location. Full data is read using cursors or pagination based on consistent snapshots, while incremental data is filtered and applied based on log positions. The entire process is completely decoupled from the primary key definition in the table structure, making this invention widely applicable to various primary key-less table structures, including business transaction tables, operation log tables, historical archive tables, intermediate processing tables, audit tables, and other table types that have long plagued data synchronization.

[0094] Fourth, it has low invasiveness to the source database and minimal impact on performance, ensuring stable operation of the production system.

[0095] This invention adheres to the principle of "zero intrusion and low impact," specifically manifested in the following ways: no modification to the source database table structure is required, no new fields need to be added, and no triggers or stored procedures need to be created; read lock operations during the snapshot initialization phase only block DDL changes and do not affect DML write operations; when the working sub-connection reads the full amount of data, it adopts a batch reading and connection reuse approach to avoid putting excessive query pressure on the source database; the incremental collector performs bypass reading based on the database log, without consuming source database computing resources. This minimizes the performance impact on the source database during the synchronization process, ensuring the stable operation of the production system and business continuity.

[0096] Fifth, it has good versatility and scalability, and is easy to integrate into existing data synchronization systems.

[0097] This invention does not rely on the proprietary features of any specific database product; it only requires the database to support repeatable read isolation level and log location acquisition capabilities. Therefore, it is widely compatible with mainstream relational databases such as MySQL, PostgreSQL, and Oracle. Furthermore, the invention features a clear technical architecture and a reasonable modular division. The three stages—snapshot initialization, full synchronization, and incremental synchronization—are functionally independent and have clearly defined interfaces. It can be directly integrated into existing data migration tools, data synchronization platforms, and change data capture systems, or deployed as a standalone data synchronization component.

[0098] VI. Improve the stability, accuracy, and observability of synchronization for tables without primary keys.

[0099] This invention incorporates multiple safeguards during the synchronization process: full synchronization supports breakpoint resumption to prevent duplicate data readings due to network or system anomalies; incremental synchronization supports point persistence and breakpoint resumption to ensure data collection can resume from the last recorded point after recovery from an anomaly; the data writing process supports type conversion, anomaly retries, and failure alarms to improve the robustness of the synchronization task; synchronization progress and data consistency can be monitored and verified in real time through point information, enhancing the observability and controllability of the synchronization process. This enables the invention to provide stable, accurate, and traceable synchronization services in enterprise-level large-scale data migration scenarios.

[0100] like Figure 3 As shown, the present invention also provides a device including a processor 310, a communication interface 320, a memory 330 for storing processor-executable computer programs, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the aforementioned method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels.

[0101] The computer program in memory 330, when implemented as a software functional unit and sold or used as an independent product, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0102] The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected based on actual needs to achieve the purpose of this embodiment. Those skilled in the art can understand and implement this without any creative effort.

[0103] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods of various embodiments or some parts of embodiments.

[0104] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for implementing non-disruptive heterogeneous databases with no primary key based on database isolation levels, characterized in that, The method includes: During the snapshot initialization phase, a main connection to the source database is established, the first log position is recorded, a short-term read lock is applied to the target table without a primary key, the second log position is recorded, the repeatable read isolation level is set through the working sub-connection to obtain a consistent snapshot, and the read lock is released. During the full synchronization phase, all data is read based on the consistency snapshot and written to the target heterogeneous database to form a data baseline. During the incremental synchronization phase, incremental data from the source database is collected starting from the first log position. The incremental data is then filtered based on the second log position, and only changes that occurred after the consistency snapshot are synchronized to the target database.

2. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 1, characterized in that, The first log position is the current log position obtained from the source database before applying the read lock, serving as the starting point for subsequent incremental synchronization; the second log position is the log position recorded after applying the read lock but before obtaining the consistent snapshot, used to identify the snapshot time and serve as the boundary benchmark between full and incremental data.

3. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 1, characterized in that, During the snapshot initialization phase, short-term read locks are applied as follows: a database table-level lock command is executed through the main connection to apply a read lock to the target table without a primary key. The lock duration only covers the time window required for the second log position record and the consistency snapshot trigger operation. After the lock is released, the source database immediately resumes full write capability.

4. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 1, characterized in that, During the snapshot initialization phase, a consistent snapshot is obtained as follows: the transaction isolation level is set to repeatable read through a working subconnection, and a lightweight query statement is executed to trigger the database to generate a consistent view, which reflects the global data state at a certain moment after a read lock is applied and before the read lock is released.

5. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 4, characterized in that, Worker sub-connections are created by the main connection and included in the connection pool for unified management. They support multi-threaded concurrent reading of full data from a consistent snapshot. Each worker sub-connection independently reads a portion of the data shards, and automatically releases connection resources after the full data reading is completed.

6. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 1, characterized in that, During the full synchronization phase, the full data is read based on the consistent snapshot in the following manner: data is obtained in batches using cursor traversal or pagination query, and each batch of data is written to the target heterogeneous database after being read. Automatic data type conversion and exception retry mechanism are supported during the writing process.

7. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 1, characterized in that, In the incremental synchronization step, the incremental data collected starting from the first log position includes the database's binary log, write-ahead log, or change data capture log. The collection process supports breakpoint resume and position persistence.

8. The method for implementing non-disruptive heterogeneous tables without primary keys based on database isolation levels according to claim 7, characterized in that, In the incremental synchronization step, incremental data is filtered based on the second log position as follows: the log position associated with each data change in the incremental log is compared with the second log position, and only the changed data with a log position greater than or equal to the second log position is retained, while historical changes with a position less than the second log position are discarded.

9. A system for implementing non-disruptive heterogeneous databases with no primary key tables based on database isolation levels, characterized in that, The system includes: The snapshot initialization module is used to establish a main connection with the source database, record the first log position and apply a short-term read lock to the target table without a primary key, record the second log position, set the repeatable read isolation level through the working sub-connection to obtain a consistent snapshot, and release the read lock; The full synchronization module is used to read all data based on the consistency snapshot and write it to the target heterogeneous database to form a data baseline. The incremental synchronization module collects incremental data from the source database starting from the first log position, filters the incremental data based on the second log position, and synchronizes only the changed data that occurred after the consistency snapshot to the target database.

10. A computer device, characterized in that, The computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method according to any one of claims 1-8.

Images