Data synchronization method and device, equipment and storage medium
Through the combination of batch insertion and updates one by one, combined with batch reading and error processing, the resource occupation and speed problems during data synchronization are solved, and efficient data synchronization is achieved.
Patent Information
- Application Number
- CN202411194849.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-28
- Publication Date
- 2024-11-29
AI Technical Summary
When faced with large amounts of data, existing data synchronization methods lead to large-scale occupation of database resources and memory, slow synchronization speed and long synchronization time.
Insert the local cached batch data to be synchronized into the database through batch insertion. If the insertion fails, update one by one. If the update fails, batch insertion is again carried out, combining batch reading and error processing to reduce memory usage and improve synchronization speed. .
Through batch reading and batch insertion, the use of memory and database resources is reduced, synchronization speed and success rate are improved, and synchronization time is saved.
Smart Images

Figure CN119046381A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of computer technology, and in particular to a data synchronization method, device, equipment and storage medium. Background Art
[0002] With the rapid growth of data volume and the diversification of data sources, how to ensure the consistency of data between different storage media and different systems has become an urgent problem to be solved. In order to ensure that data can be consistent between different systems or devices, thereby meeting the needs of business continuity and data reliability, a data synchronization method is proposed. However, when faced with a large amount of data, the existing data synchronization method will lead to a large amount of database resources and memory occupation, slow synchronization speed and long synchronization time. Summary of the invention
[0003] Based on the above problems, the present application provides a data synchronization method, device, equipment and storage medium, with the purpose of improving data synchronization speed, saving data synchronization time, reducing the occupation of database resources, and reducing the occupation of memory space.
[0004] The embodiments of the present application disclose the following technical solutions:
[0005] The first aspect of the present application provides a data synchronization method, the method comprising:
[0006] Inserting locally cached batch data to be synchronized into the first database of the first device by batch insertion; the batch data to be synchronized is a batch of data in the data set to be synchronized in the second database of the second device, the data set to be synchronized includes data to be synchronized to the first database, and the data in the data set to be synchronized is read into the local memory in batches;
[0007] If the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database again in a single update manner;
[0008] If there is data in the batch of data to be synchronized that has not been successfully updated by the single update method, the data that has not been successfully updated is inserted into the first database again by the batch insertion method.
[0009] In a possible implementation, before inserting the locally cached batch data to be synchronized into the first database of the first device in batch insertion, the data synchronization method further includes:
[0010] Obtaining a first processing time reference value corresponding to processing batch data in the batch insert mode, and obtaining a second processing time reference value corresponding to processing the batch data in the single update mode;
[0011] The method of inserting the locally cached batch data to be synchronized into the first database of the first device by batch inserting is specifically as follows:
[0012] If the first processing time reference value is less than the second processing time reference value, the locally cached batch data to be synchronized is inserted into the first database of the first device in a batch insert manner.
[0013] In a possible implementation, the obtaining of a first processing time reference value corresponding to processing batch data in the batch insert mode, and the obtaining of a second processing time reference value corresponding to processing the batch data in the single update mode, include:
[0014] Obtaining a data batch insertion failure rate and a data batch insertion success rate corresponding to the batch insertion method;
[0015] Obtaining a first data synchronization speed corresponding to the batch insertion mode, a second data synchronization speed corresponding to the single update mode, and a third data synchronization speed corresponding to the single update mode after the batch insertion mode fails;
[0016] Obtaining the first processing time reference value according to the data batch insertion success rate, the number of data items in the batch data, the first data synchronization speed, the data batch insertion failure rate and the third data synchronization speed;
[0017] The second processing time reference value is obtained according to the number of data items in the batch data and the second data synchronization speed.
[0018] In a possible implementation, after inserting the unsuccessfully updated data into the first database again by the batch insert method, the data synchronization method further includes:
[0019] If there is still data that failed to be inserted in the second batch among the data that failed to be updated, the data that failed to be inserted in the second batch are inserted into the first database one by one in a single row insertion manner.
[0020] In a possible implementation, after inserting the data that failed in the second batch insertion into the first database one by one in a single-row insertion manner, the data synchronization method further includes:
[0021] If there is still a single piece of data that fails to be inserted, then the single piece of data that fails to be inserted is updated one by one to the first database again by the single piece update method;
[0022] If, after the second single update operation, the batch of data to be synchronized still has target data that has not been successfully synchronized to the first database, error processing is performed on the target data.
[0023] In a possible implementation, if the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database one by one in a single update manner includes:
[0024] If the batch data to be synchronized fails to be inserted in the batch insertion manner, based on a batch insertion failure message fed back by the first device, obtaining a data identifier of the problem data that triggers the generation of the batch insertion failure message;
[0025] Divide the batch data to be synchronized into batches to obtain multiple sub-batches of data;
[0026] Based on the data identifier, determining the target sub-batch data to which the problematic data belongs among the plurality of sub-batches of data;
[0027] The target sub-batch data are updated one by one into the first database by a single update method; the remaining sub-batch data among the multiple sub-batch data except the target sub-batch data are re-inserted into the first database by a batch insert method.
[0028] In a possible implementation, if the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database one by one in a single update manner includes:
[0029] If the batch data to be synchronized fails to be inserted in the batch insertion manner, the batch data to be synchronized is divided into batches to obtain multiple sub-batches of data;
[0030] Insert the sub-batch data into the first database by batch inserting;
[0031] If there are sub-batch data that fail to be inserted in batch, the sub-batch data that fail to be inserted in batch are updated to the first database one by one in a single update manner.
[0032] A second aspect of the present application provides a data synchronization device, the device comprising:
[0033] A data receiving module, used for receiving a data set to be synchronized from a second database of a second device;
[0034] A data cache module is used to read the data in the data set into a local memory in batches for caching, forming multiple batches of batch data to be synchronized;
[0035] A batch insert module, used to communicate with a first database of a first device, and batch insert locally cached batches of data to be synchronized into the first database by batch insert;
[0036] A batch insertion detection module is used to detect whether the batch insertion fails after the batch insertion module performs the batch insertion operation;
[0037] A single update module, which is started if a batch insertion failure is detected, and is used to update at least part of the failed batch data to be synchronized to the first database one by one through a single update method;
[0038] An update detection module, used to detect whether there is a single data that has failed to be updated after the single update module executes a single update operation;
[0039] The batch insert module is also used for: when the update detection module detects that there is a single piece of data that has not been successfully updated, inserting the single piece of data that has not been successfully updated into the first database by batch insert.
[0040] The third aspect of the present application provides a data synchronization device, the device comprising: a processor and a memory:
[0041] The memory is used to store program code and transmit the program code to the processor;
[0042] The processor is used to execute the steps of the data synchronization method provided in any implementation of the first aspect according to the instructions in the program code.
[0043] A fourth aspect of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium is used to store program code, and the program code is used to execute the steps of the data synchronization method provided in any implementation of the first aspect.
[0044] Compared with the prior art, this application has the following beneficial effects:
[0045] In the technical solution of the present application, firstly, the locally cached batch data to be synchronized is inserted into the first database of the first device by batch insertion. If the batch data to be synchronized fails to be inserted by the batch insertion, at least part of the data in the batch data to be synchronized is updated to the first database again by a single update. If there is data in the batch data to be synchronized that is not successfully updated by the single update, the data that is not successfully updated is inserted into the first database again by the batch insertion. It can be seen that in the technical solution of the present application, the memory usage is reduced by batch reading, the synchronization speed is improved by batch insertion, and the final synchronization success rate is improved by first updating the data that failed to be inserted in batches, and then inserting the data that failed to be updated in batches for the second time. This saves synchronization time and reduces the usage of database resources. BRIEF DESCRIPTION OF THE DRAWINGS
[0046] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0047] Figure 1 A schematic diagram of a data synchronization scenario provided in an embodiment of the present application;
[0048] Figure 2 A flow chart of a data synchronization method provided in an embodiment of the present application;
[0049] Figure 3 A partial flow chart of a data synchronization method provided in an embodiment of the present application;
[0050] Figure 4 A partial flow chart of another data synchronization method provided in an embodiment of the present application;
[0051] Figure 5 A flowchart of another data synchronization method provided in an embodiment of the present application;
[0052] Figure 6 A partial flow chart of another data synchronization method provided in an embodiment of the present application;
[0053] Figure 7 A data synchronization method implementation scenario diagram provided in an embodiment of the present application;
[0054] Figure 8 A schematic diagram of the structure of a data synchronization device provided in an embodiment of the present application. DETAILED DESCRIPTION
[0055] As described above, data synchronization currently plays a key role in ensuring data consistency, business continuity, and cross-platform operations. However, in actual applications, when faced with large amounts of data, data synchronization methods will cause a large amount of database resources and memory to be occupied, resulting in slow synchronization speeds and long synchronization times.
[0056] After research, the inventor proposed a data synchronization method.
[0057] First, insert the batch data to be synchronized in the local cache into the first database of the first device by batch insertion (see Figure 1 As shown in the scenario diagram, if the batch data to be synchronized fails to be inserted in the batch insertion manner, at least part of the data in the batch data to be synchronized is updated to the first database one by one through a single update manner; if there is data in the batch data to be synchronized that is not successfully updated through the single update manner, the data that was not successfully updated is inserted into the first database again through the batch insertion manner.
[0058] The technical solution of the present application reduces memory usage by reading in batches, improves synchronization speed by batch insertion, and then improves the success rate of the final synchronization by first updating the data that failed to be inserted in batches individually and then inserting the data that failed to be updated in batches for the second time, thereby saving synchronization time, reducing the usage of database resources, and further improving the synchronization speed.
[0059] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0060] See also Figure 2 , which is a flow chart of a data synchronization method provided by an embodiment of the present application. The method can be executed on a computing device or a server. Figure 2 As shown, the method comprises the following steps:
[0061] S201. Insert locally cached batches of to-be-synchronized data into a first database of a first device by batch insertion.
[0062] In the embodiment of the present application, the batch of data to be synchronized is a batch of data in the data set to be synchronized in the second database of the second device, the data set to be synchronized includes data to be synchronized to the first database, and the data in the data set to be synchronized is read into the local memory in batches. Since the method can be executed on a computing device or a server, the local memory can be the local memory of the computing device or the memory of the server in combination with different scenarios, which is not specifically limited here.
[0063] The database may be any database, for example, an Oracle database, a MySQL database, a SQL Server database, etc.
[0064] In an example implementation, there are 100 million data in the data set to be synchronized in the second database of the second device to be synchronized to the first database of the first device, and the local memory of the computing device can cache 20,000 data. The computing device reads the data in the data set to be synchronized from the second database of the second device in batches, each batch contains 20,000 data to be synchronized, and the 20,000 data are divided into 20 batches of batch data to be synchronized, each batch contains 1,000 data to be synchronized, and a batch of batch data to be synchronized is written into the first database of the first device through a Batch Insert statement to perform data synchronization. When the 20,000 data in the batch cached in the local memory are all synchronized, the computing device reads another batch of 20,000 data to be synchronized from the second database, and writes them into the first database of the first device again through batch Batch Insert, until the 100 million data to be synchronized in the second database are all synchronized. It should be noted that the number of data to be synchronized in each batch can be configured.
[0065] By reading and writing in batches, the data does not need to be saved in full to the memory, which takes up less memory and reduces the memory requirements. For example, if the full storage method in the prior art is used, 100 million pieces of data may require about 60G of memory, while in the embodiment of the present application, by batch caching the data, only about 15M of memory is needed. In practical applications, the price of memory usually increases with the increase of capacity, so the method of batch caching data in the embodiment of the present application also reduces the cost of hardware during data synchronization.
[0066] S202: Determine whether the batch insertion fails.
[0067] In the embodiment of the present application, if the batch of data to be synchronized fails to be inserted through Batch Insert, step S203 is performed; if the batch of data to be synchronized is successfully inserted through Batch Insert, data synchronization is completed.
[0068] S203: Re-update at least part of the batch of data to be synchronized to the first database one by one in a single update manner.
[0069] In an embodiment of the present application, if the batch of data to be synchronized fails to be inserted through Batch Insert, the first database will feedback an error message and roll back the entire batch of data to the local cache, and then update at least part of the data in this batch of data to be synchronized to the first database one by one through the Single Update statement.
[0070] The failure of inserting the batch data to be synchronized through Batch Insert may be caused by various reasons. In an example implementation, the failure of inserting the batch data to be synchronized through Batch Insert may be because data that conflicts with one or more data in the batch data to be synchronized on a unique constraint (such as a primary key, a unique index, etc.) already exists in the second database.
[0071] S204: Determine whether there is any data that has not been successfully updated by a single update method.
[0072] In the embodiment of the present application, if there is data that is not successfully updated through the Single Update statement, proceed to step S205; if there is no data that is not successfully updated through the Single Update statement, data synchronization is completed.
[0073] S205: Insert the data that failed to be updated into the first database again by batch insertion.
[0074] In an embodiment of the present application, the data in the entire batch of data that has been successfully updated by the Single Update statement has been successfully synchronized to the first database of the first device. If there is data that has been unsuccessfully updated by the Single Update statement, the first database will feedback an error message and roll back the data in the entire batch of data that has been unsuccessfully updated by the Single Update statement to the local cache, and then insert it into the first database again through the Batch Insert statement.
[0075] Similarly, the reason why the data Single Update fails may be caused by a variety of reasons. In an exemplary implementation, the reason why the data Single Update fails may be because no records meeting the conditions are found in the first data of the first device to be updated.
[0076] In the embodiment of the present application, the memory usage is reduced by batch reading, and batch insertion is combined with the database to handle errors, which saves synchronization time and reduces the usage of database resources. At the same time, since BatchInsert and Single Update are both standard syntax, this data synchronization method is compatible with various databases.
[0077] Alternatively, if Figure 3 As shown in the flowchart, the above step S203 includes:
[0078] S301. Based on the batch insertion failure message fed back by the first device, obtain a data identifier of the problem data that triggers the generation of the batch insertion failure message.
[0079] The data identifier of the problem data may be a primary key, a unique identifier, a business-related ID, etc.
[0080] In an exemplary implementation, the batch insert failure message includes at least: a data identifier of the problem data that triggers the generation of the batch insert failure message. Obtaining the data identifier of the problem data that triggers the generation of the batch insert failure message helps locate the problem data.
[0081] S302 , dividing the batch data to be synchronized into batches to obtain multiple sub-batches of data.
[0082] In an embodiment of the present application, a large amount of data is divided into smaller batches for processing to reduce the consumption of system resources by a single operation and to facilitate rapid location and processing when problems arise.
[0083] S303: Determine, based on the data identifier, the target sub-batch data to which the problem data belongs among the multiple sub-batches of data.
[0084] The identification information of the problem data is obtained from the batch insertion failure message fed back by the first device.
[0085] In an example implementation, all sub-batch data are traversed, and the problem data is matched using the data identifier to determine the sub-batch (ie, target sub-batch) to which the problem data belongs. The target sub-batch data to which the problem data belongs among multiple sub-batch data can be accurately determined.
[0086] S304, updating the target sub-batch data one by one into the first database by means of single update; and re-inserting the remaining sub-batch data except the target sub-batch data among the multiple sub-batch data into the first database by means of batch insert.
[0087] In the embodiment of the present application, the batch data to be synchronized is divided into batches again, which speeds up the data synchronization. The target sub-batch data is accurately located through the data identification of the problem data, and a separate Single Update operation is actually performed on the target sub-batch data to avoid the failure of the entire batch due to a data error in the batch processing. At the same time, for non-problem data, the batch insertion efficiency of the Batch Insert operation is maintained.
[0088] Alternatively, if Figure 4 As shown in the flowchart, the above step S203 includes:
[0089] S401, dividing the batch data to be synchronized into batches to obtain multiple sub-batches of data.
[0090] Batch data to be synchronized can be divided into smaller batches, which reduces the pressure of a single database operation, improves processing efficiency, and facilitates rapid location and processing when problems arise.
[0091] S402 , inserting sub-batch data into the first database by batch inserting.
[0092] In an exemplary implementation, sub-batch data is inserted into the first database in batches through a Batch Insert operation.
[0093] S403: Determine whether there is sub-batch data that fails to be inserted in batches.
[0094] In the embodiment of the present application, if there is sub-batch data for which Batch Insert fails to be inserted, the process proceeds to step S404; if there is no sub-batch data for which Batch Insert fails to be inserted, data synchronization is completed.
[0095] S404: Update the sub-batch data that failed to be inserted into the first database one by one in a single update manner.
[0096] If there are sub-batch data that failed to be inserted in Batch Insert, the sub-batch data are rolled back to the local memory, and then the sub-batch data that failed to be inserted in Batch Insert are updated to the first database one by one through Single Update.
[0097] In an embodiment of the present application, batches of data to be synchronized are further processed in batches, which reduces the pressure of a single database operation, improves data synchronization efficiency, and facilitates rapid location and processing when problems occur.
[0098] Figure 5 Another data synchronization method flow chart provided in an embodiment of the present application. In the embodiment introduced by this figure, a more detailed description is provided for the implementation of the data synchronization method.
[0099] like Figure 5 The data synchronization method shown includes the following steps:
[0100] S501. Obtain a first processing time reference value corresponding to processing batch data in a batch insert manner, and obtain a second processing time reference value corresponding to processing batch data in a single update manner.
[0101] In the embodiment of the present application, the first processing time reference value refers to the time reference value of performing a Batch Insert operation on a batch of data to be processed; the second processing time reference value refers to the time reference value of performing a Single Update operation on a batch of data to be processed. For example, if the time to perform a Batch Insert operation on a batch of data to be processed is 2s, and the time to perform a Single Update operation on the batch of data is 5s, then the first processing time reference value is 2s, and the second processing time reference value is 5s.
[0102] It should be noted that the first processing time reference value includes two components. The first component is the processing time of Batch Insert when Batch Insert succeeds. The second component is the processing time of Single Update after BatchInsert fails. The first processing time reference value is specifically the sum of the processing times of the above two components.
[0103] Optionally, the specific steps of obtaining the first processing time reference value corresponding to the batch data processed by the batch insert method in step S501 and obtaining the second processing time reference value corresponding to the batch data processed by the single update method can be found in Figure 6 The flow chart shown includes:
[0104] S5011. Obtain a data batch insertion failure rate and a data batch insertion success rate corresponding to the batch insertion method.
[0105] In database operations, the failure rate and success rate of batch data insertion are usually affected by many factors, including database performance, network conditions, conflicts on unique constraints (such as primary keys, unique indexes, etc.), data format correctness, etc. The failure rate in this step mainly considers failures caused by the influence of data uniqueness constraints.
[0106] In the embodiment of the present application, the success rate and failure rate of Batch Insert of data can be obtained by performing Batch Insert on historical batches of data to be synchronized. For example, if there are 100 million pieces of data to be synchronized to the first database, the 100 million pieces of data are divided into 100,000 batches of data to be synchronized according to a batch of 1,000 pieces. Then, the success rate and failure rate of Batch Insert are calculated after the current 10 batches of data to be synchronized are synchronized. It should be noted that as the number of batches of data to be synchronized increases, the success rate and failure rate of Batch Insert will also change dynamically.
[0107] In an exemplary implementation, the failure rate of Batch Insert of data is p, and the success rate of Batch Insert of data is 1-p.
[0108] S5012: Obtain a first data synchronization speed corresponding to the batch insertion method, a second data synchronization speed corresponding to the single update method, and a third data synchronization speed corresponding to the single update method after the batch insertion method fails.
[0109] The synchronization speed refers to the amount of data successfully synchronized to the database per unit time, such as the number of data items inserted or updated per second.
[0110] In an embodiment of the present application, the first data synchronization speed, the second data synchronization speed, and the third data synchronization speed may be obtained by the speed at which corresponding operations are performed on historical batches of data to be synchronized.
[0111] Among them, a single update operation can be performed on the historical batch of data to be synchronized through Single Update, and the timestamps are recorded before and after the Single Update of the historical batch data starts and ends. The first data synchronization speed (the number of data inserted per second) is calculated based on the number of updated data and the time spent.
[0112] Batch Insert can be used to perform batch insert operations on historical batches of data to be synchronized. Timestamps are recorded before and after the start and end of Batch Insert of historical batch data. The second data synchronization speed (the number of data inserted per second) is calculated based on the number of data items inserted and the time spent.
[0113] When Batch Insert fails, you can use Single Update to perform a single update operation on the historical batch of data to be synchronized. When Batch Insert fails for historical batch data, you can use Single Update to record the timestamps before and after the start and end of the operation, and calculate the third data synchronization speed (the number of data inserted per second) based on the number of updated data and the time spent.
[0114] In an exemplary implementation, the first data synchronization speed is V1, the second data synchronization speed is V2, and the third data synchronization speed is V3.
[0115] S5013. Obtain a first processing time reference value according to a data batch insertion success rate, the number of data items in the batch data, a first data synchronization speed, a data batch insertion failure rate, and a third data synchronization speed.
[0116] In an exemplary implementation, the number of data items in the batch data is N, that is, there are a total of N data items that need to be processed. Then the first processing time reference value can be obtained by the following formula:
[0117] T(Batch Insert)=(1-p)×(N / V1)+p×(N / V3).
[0118] Where, (1-p)×(N / V1) represents the processing time of Batch Insert when Batch Insert succeeds, p×(N / V3) represents the processing time of Single Update after Batch Insert fails, p represents the failure rate of Batch Insert, and 1-p represents the success rate of Batch Insert.
[0119] S5014. Obtain a second processing time reference value according to the number of data items in the batch data and the second data synchronization speed.
[0120] In an exemplary implementation, the second processing time reference value may be obtained by the following formula:
[0121] T(Single Update)=N / V2.
[0122] S502: Determine whether the first processing time reference value is less than the second processing time reference value.
[0123] If the first processing time reference value is less than the second processing time reference value, that is,
[0124] T(Batch Insert)<T(Single Update),
[0125] Go to step S504;
[0126] If the first processing time reference value is greater than the second processing time reference value, that is,
[0127] T(Batch Insert)>T(Single Update),
[0128] Proceed to step S503.
[0129] S503: Update the locally cached batch data to be synchronized to the first database of the first device by means of a single update.
[0130] In an embodiment of the present application, when the time required to perform a Batch Insert operation on a batch of data to be processed is longer than the time required to directly perform a Single Update operation on the batch of data to be processed, in order to synchronize data more quickly, the locally cached batch data to be synchronized is updated in the first database of the first device by using a Single Update method that takes a shorter time to execute the operation.
[0131] S504: Insert the locally cached batch data to be synchronized into the first database of the first device in a batch insert manner.
[0132] When the time required to perform a Batch Insert operation on a batch of data to be processed is shorter than the time required to directly perform a Single Update operation on the batch of data to be processed, in order to synchronize data more quickly, the locally cached batch of data to be synchronized is inserted into the first database of the first device by using a Batch Insert method that takes less time to execute the operation.
[0133] S505: Determine whether the batch insertion fails.
[0134] If the batch of data to be synchronized fails to be inserted through Batch Insert, step S506 is performed; if the batch of data to be synchronized is successfully inserted through Batch Insert, data synchronization is completed.
[0135] S506: Re-update at least part of the batch of data to be synchronized to the first database one by one in a single update manner.
[0136] S507: Determine whether there is any data that has not been successfully updated by a single update method.
[0137] In the embodiment of the present application, if there is data that is not successfully updated through the Single Update statement, the process proceeds to step S508; if there is no data that is not successfully updated through the Single Update statement, data synchronization is completed.
[0138] S508: Insert the data that failed to be updated into the first database again by batch inserting.
[0139] S504-S508 are implemented in substantially the same manner as S201-S205 in the method embodiment described above, and will not be elaborated here. For related technical implementations, reference may be made to the description of S201-S205 described above.
[0140] S509: Determine whether there is data that fails in the second batch insertion.
[0141] In the embodiment of the present application, if there is data that fails to be inserted in the second Batch Insert operation, the process proceeds to step S510; if there is no data that fails to be inserted in the second Batch Insert operation, data synchronization is completed.
[0142] S510: insert the data that failed in the second batch insertion into the first database one by one in a single row insertion mode.
[0143] If there is a second Batch Insert operation that fails to insert data again, the first database will feedback an error message and roll back the data that failed to be inserted in the second batch to the local cache, and then re-insert the data that failed to be inserted in the second batch into the first database one by one through the SingleInsert statement.
[0144] S511. Determine whether there is a single piece of data that fails to be inserted.
[0145] In the embodiment of the present application, if there is data for which the Single Insert operation fails to insert, the process proceeds to step S512; if there is no data for which the Single Insert operation fails to insert, the data synchronization is completed.
[0146] S512: Update the single data that failed to be inserted into the first database one by one in a single update manner again.
[0147] If there is a single data that fails to be inserted in the Single Insert operation, the first database will feedback an error message and roll back the data that failed to be inserted in the Single Insert to the local cache, and then update the batch of data that failed to be inserted to the first database one by one through the SingleUpdate statement.
[0148] S513: Determine whether there is target data that has not been successfully synchronized to the first database.
[0149] The target data refers to data in the to-be-synchronized data in the second database of the second device that has not yet been synchronized to the first database of the first device after the above steps.
[0150] In the embodiment of the present application, if there is target data that has not been successfully synchronized to the first database, the process proceeds to step S514; if there is no target data that has not been successfully synchronized to the first database, data synchronization is completed.
[0151] S514: Perform error processing on the target data.
[0152] If there is target data that has not been successfully synchronized to the first database, the reason for the unsuccessful synchronization may be that the data is too long or there is a problem with the data format. In this case, error processing is performed on the target data.
[0153] The error handling refers to the process of processing data that cannot be ultimately written. The main logic is to process the erroneous data according to business requirements, such as error storage, error interruption, error ignoring, etc.
[0154] Figure 7 This is a scene diagram for implementing the data synchronization method in the embodiment of the present application. Figure 7 In the embodiment of the present application, the failure rate and speed of Batch Insert are first detected, and a faster processing method is selected to improve the synchronization speed and save synchronization time.
[0155] When you choose to perform a Batch Insert operation, if the Batch Insert operation fails, the reason for the failure may be that there are already data in the second database that conflict with one or more data in the batch of data to be synchronized in terms of uniqueness constraints. In this case, try to perform Single Update processing on this batch of data, and perform a second Batch Insert processing on the data that failed to be updated with Single Update (if any).
[0156] If the second Batch Insert fails, the entire batch operation fails because the batch insert operation may contain data that violates database constraints (such as primary key uniqueness, foreign key constraints, check constraints, etc.). When inserting a single row, if only one row of data violates the constraint, the other data may still be successfully inserted. Therefore, in order to improve the synchronization speed, the failed batch insert data will be processed as a Single Insert, and the failed Single Insert data (if any) will be updated as a second Single Update.
[0157] If there is still target data that has not been successfully synchronized to the first database, it may be because the data is too long or there is a problem with the data format. In this case, error processing is performed on the target data.
[0158] In the embodiment of the present application, the memory usage is reduced by batch reading, and then a complete multi-mode synchronization failure processing is used to improve the final synchronization success rate, save synchronization time, and reduce the usage of database resources. At the same time, since Batch Insert, Single Update, and Single Insert are all standard syntaxes, the data synchronization method is compatible with various databases.
[0159] Based on the data synchronization method provided in the aforementioned embodiment, the present application also provides a data synchronization device accordingly. Figure 8 A schematic diagram of the structure of a data synchronization device provided in an embodiment of the present application. Figure 8 As shown, the data synchronization device includes:
[0160] The data receiving module 801 is used to receive the data set to be synchronized from the second database of the second device.
[0161] The data cache module 802 is used to read the data in the data set into the local memory in batches for caching, so as to form multiple batches of batch data to be synchronized;
[0162] A batch insert module 803 is used to communicate with a first database of a first device, and batch insert the locally cached batch data to be synchronized into the first database by batch insert;
[0163] The batch insert module 803 is further used for: when the update detection module detects that there is a single piece of data that has not been successfully updated, inserting the single piece of data that has not been successfully updated into the first database by batch inserting.
[0164] A batch insertion detection module 804 is used to detect whether the batch insertion fails after the batch insertion module performs the batch insertion operation;
[0165] A single update module 805 is started if the insertion detection module 804 detects that the batch insertion fails, and is used to update at least part of the failed batch data to be synchronized to the first database one by one through a single update method;
[0166] If the update detection module 806 detects that there is a single data that has not been successfully updated, the batch insertion module 803 is started again to reorganize the data that has not been successfully updated by the single update method into batch data, and try to insert it into the first database again by batch insertion.
[0167] In an optional implementation, the data synchronization device further includes:
[0168] A failure message receiving module, used to receive a batch insertion failure message fed back by the first device;
[0169] A data identification acquisition module is used to obtain the data identification of the problem data that triggers the generation of the batch insertion failure message based on the failure information received by the insertion failure message receiving module, which helps to locate the problem data;
[0170] The data batching module is used to divide the batch data to be synchronized into batches to obtain multiple sub-batches of data;
[0171] The target sub-batch data determination module is used to determine the target value batch data to which the problem data belongs among the multiple sub-batches of data based on the data identifier.
[0172] Optionally, the data synchronization device further includes:
[0173] A processing time acquisition module, used to acquire a first processing time reference value corresponding to processing batch data in a batch insertion manner, and to acquire a second processing time reference value corresponding to processing batch data in a single update manner;
[0174] A processing time acquisition module is specifically used to obtain a data batch insertion failure rate and a data batch insertion success rate corresponding to a batch insertion method; obtain a first data synchronization speed corresponding to the batch insertion method, a second data synchronization speed corresponding to a single update method, and a third data synchronization speed corresponding to the single update method after the batch insertion method fails; obtain a first processing time reference value based on the data batch insertion success rate, the number of data items in the batch data, the first data synchronization speed, the data batch insertion failure rate and the third data synchronization speed; obtain a second processing time reference value based on the number of data items in the batch data and the second data synchronization speed.
[0175] A processing mode selection module is used to compare the first processing time reference value with the second processing time reference value, and select the processing mode with the smaller processing time reference value to process the batch of data to be synchronized;
[0176] In an example implementation, when the time required to perform a Batch Insert operation on a batch of data to be processed is longer than the time required to directly perform a Single Update operation on the batch of data to be processed, in order to synchronize data more quickly, the processing method selection module selects to update the locally cached batch data to be synchronized in the first database of the first device through the Single Update method that takes shorter time to execute the operation.
[0177] Optionally, when the time required to perform a Batch Insert operation on a batch of data to be processed is shorter than the time required to directly perform a Single Update operation on the batch of data to be processed, in order to synchronize data more quickly, the processing method selection module selects the Batch Insert method that takes shorter time to execute the operation to insert the locally cached batch of data to be synchronized into the first database of the first device.
[0178] A single-row insertion module is started if the insertion detection module 804 detects that the second batch insertion fails, and is used to insert the failed batch data to be synchronized into the first database one by one by means of single-row insertion;
[0179] A single-line insertion detection module is used to detect whether there is a single-line data that fails to be inserted after the single-line insertion module performs a single-line insertion operation;
[0180] If the single insertion detection module detects that there is a single data that fails to be inserted, the single update module 805 is started to update the single data that failed to be inserted to the first database one by one through a single update method;
[0181] The error handling module is used to perform error handling on the data that cannot be ultimately written. Optionally, the error handling module performs error storage, error interruption, or error neglect on the data that cannot be ultimately written.
[0182] In addition, an embodiment of the present application further provides a device, which includes a processor and a memory.
[0183] The memory is used to store program code and transmit the program code to the processor;
[0184] The processor is used to execute the steps of the data synchronization method introduced in any of the above method embodiments according to the instructions in the program code.
[0185] In addition, an embodiment of the present application further provides a computer-readable storage medium, in which a computer program is stored. When the program is executed by a processor, a data synchronization method as described in any of the method embodiments is implemented.
[0186] It should be noted that each embodiment in this specification is described in a progressive manner, and the same and similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment. The device embodiment described above is merely schematic, wherein the modules described as separate components may or may not be physically separated, and the components prompted as modules may or may not be physical modules, that is, they may be located in one place, or they may be distributed on multiple network modules. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.
[0187] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.
Claims
1. A data synchronization method, characterized in that: include: Inserting the locally cached batch data to be synchronized into the first database of the first device by batch inserting; The batch data to be synchronized is a batch of data in a data set to be synchronized in a second database of the second device, the data set to be synchronized includes data to be synchronized to the first database, and the data in the data set to be synchronized is read into the local memory in batches; If the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database again in a single update manner; If there is data in the batch of data to be synchronized that has not been successfully updated by the single update method, the data that has not been successfully updated is inserted into the first database again by the batch insertion method.
2. The method according to claim 1, characterized in that Before inserting the locally cached batch data to be synchronized into the first database of the first device in batch insert mode, the method further includes: Obtaining a first processing time reference value corresponding to processing batch data in the batch insert mode, and obtaining a second processing time reference value corresponding to processing the batch data in the single update mode; The method of inserting the locally cached batch data to be synchronized into the first database of the first device by batch inserting is specifically as follows: If the first processing time reference value is less than the second processing time reference value, the locally cached batch data to be synchronized is inserted into the first database of the first device in a batch insert manner.
3. The method according to claim 2, characterized in that The obtaining of a first processing time reference value corresponding to processing batch data in the batch insert mode, and the obtaining of a second processing time reference value corresponding to processing the batch data in the single update mode, include: Obtaining a data batch insertion failure rate and a data batch insertion success rate corresponding to the batch insertion method; Obtaining a first data synchronization speed corresponding to the batch insertion mode, a second data synchronization speed corresponding to the single update mode, and a third data synchronization speed corresponding to the single update mode after the batch insertion mode fails; Obtaining the first processing time reference value according to the data batch insertion success rate, the number of data items in the batch data, the first data synchronization speed, the data batch insertion failure rate and the third data synchronization speed; The second processing time reference value is obtained according to the number of data items in the batch data and the second data synchronization speed.
4. The method according to any one of claims 1 to 3, characterized in that After inserting the unsuccessfully updated data into the first database again by the batch insert method, the method further includes: If there is still data that failed to be inserted in the second batch among the data that failed to be updated, the data that failed to be inserted in the second batch are inserted into the first database one by one in a single row insertion manner.
5. The method according to claim 4, characterized in that After inserting the data that failed in the second batch insertion into the first database one by one in a single-row insertion manner, the method further includes: If there is still a single piece of data that fails to be inserted, then the single piece of data that fails to be inserted is updated one by one to the first database again by the single piece update method; If, after the second single update operation, the batch of data to be synchronized still has target data that has not been successfully synchronized to the first database, error processing is performed on the target data.
6. The method according to claim 1, characterized in that If the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database one by one in a single update manner, comprises: If the batch data to be synchronized fails to be inserted in the batch insertion manner, based on a batch insertion failure message fed back by the first device, obtaining a data identifier of the problem data that triggers the generation of the batch insertion failure message; Divide the batch data to be synchronized into batches to obtain multiple sub-batches of data; Based on the data identifier, determining the target sub-batch data to which the problematic data belongs among the plurality of sub-batches of data; The target sub-batch data are updated one by one into the first database by a single update method; the remaining sub-batch data among the multiple sub-batch data except the target sub-batch data are re-inserted into the first database by a batch insert method.
7. The method according to claim 1, characterized in that If the batch of data to be synchronized fails to be inserted in the batch insertion manner, updating at least part of the batch of data to be synchronized to the first database one by one in a single update manner, comprises: If the batch data to be synchronized fails to be inserted in the batch insertion manner, the batch data to be synchronized is divided into batches to obtain multiple sub-batches of data; Insert the sub-batch data into the first database by batch inserting; If there are sub-batch data that fail to be inserted in batch, the sub-batch data that fail to be inserted in batch are updated to the first database one by one in a single update manner.
8. A data synchronization device, characterized in that: include: A data receiving module, used for receiving a data set to be synchronized from a second database of a second device; A data cache module is used to read the data in the data set into a local memory in batches for caching, forming multiple batches of batch data to be synchronized; A batch insert module, used to communicate with a first database of a first device, and batch insert locally cached batches of data to be synchronized into the first database by batch insert; A batch insertion detection module is used to detect whether the batch insertion fails after the batch insertion module performs the batch insertion operation; A single update module, which is started if a batch insertion failure is detected, and is used to update at least part of the failed batch data to be synchronized to the first database one by one through a single update method; An update detection module, used to detect whether there is a single data that has failed to be updated after the single update module executes a single update operation; The batch insert module is also used for: when the update detection module detects that there is a single piece of data that has not been successfully updated, inserting the single piece of data that has not been successfully updated into the first database by batch insert.
9. A data synchronization device, characterized in that: include: Processor and memory: The memory is used to store program code and transmit the program code to the processor; The processor is used to execute the steps of the data synchronization method according to any one of claims 1 to 7 according to the instructions in the program code.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium is used to store program codes, and the program codes are used to execute the steps of the data synchronization method according to any one of claims 1 to 7.
