A method for processing ElasticSearch and MySQL distributed transaction problems based on transaction tables

Abstract

The invention discloses a method for processing ElasticSearch and MySQL distributed transaction problems based on a transaction table. The index name, document ID, changed field, before-image json format and after-image json format of ES data that needs to be operated by ElasticSearch are used as a record of the ES transaction table and inserted into a database; if the insertion fails, roll back; if the insertion succeeds, perform the ES operation; if the operation fails, roll back. The table name, primary key name, primary key value, before-image json format and after-image json format of MySQL data that needs to be operated by MySQL are used as a record of the MySQL transaction table and inserted into the database; if the insertion fails, roll back; if the insertion succeeds, perform the MySQL operation; if the operation fails, roll back; if the operation succeeds, delete the records related to the current transaction in the ES transaction table and the MySQL transaction table.

Description

Technical Field

[0001] The present invention relates to a method for processing ES and MySQL distributed transaction problems based on a transaction table, belonging to the technical field of distributed transaction problem processing. Background Art

[0002] Existing solutions to distributed transaction problems can only be applied to different MySQL databases, such as a distributed transaction processing method device and system with patent number: CN202010947573.0, which controls the version number of the distributed transaction that the client can query in the Mysql shard database through the TM transaction manager. The TM transaction manager receives the version number of the sub-transaction of the distributed transaction sent by the Mysql shard database, and sends the version number of each sub-transaction of the completed distributed transaction to the preset cache database when receiving the preset trigger instruction, so that the cache database can update the version number. When receiving the query instruction sent by the client, the TM transaction manager sends the query instruction and the target version number pointed to by the query instruction in the cache database to the Mysql shard database, so that the execution result of the sub-transaction of the distributed transaction fed back by the Mysql shard database does not exceed the range indicated by the target version number, thereby ensuring the isolation of the distributed transaction. At present, there is no better solution for the problem of ElasticSearch (abbreviated as ES) and MySQL distributed transactions, and the existing technology must require developers to manually write compensation plans. Summary of the invention

[0003] The technical problem to be solved by the present invention is to provide a method for processing ES and MySQL distributed transaction problems based on transaction tables, thereby overcoming the shortcomings of the prior art.

[0004] The present invention adopts the following technical solutions to solve the above technical problems:

[0005] A method for processing ES and MySQL distributed transaction problems based on transaction tables includes the following steps:

[0006] Step 1: Create a unique ID for this transaction;

[0007] Step 2: Query the index name, document ID, and changed fields of the ES data that needs to be operated by ElasticSearch, and convert the pre-image of the ES data that needs to be operated by ElasticSearch into json format;

[0008] Step 3: record the data obtained after the ElasticSearch operation as the after-image, convert the after-image into json format, and insert the index name, document ID, changed fields, before-image json format and after-image json format into the database as a record of the ES transaction table;

[0009] Step 4: If the insertion fails, roll back the transaction. If the insertion succeeds, perform the ElasticSearch operation.

[0010] Step 5: If the ElasticSearch operation fails, roll back the transaction. If the ElasticSearch operation succeeds, proceed to step 6.

[0011] Step 6, query the table name, primary key name and primary key value of the MySQL data that needs to be operated on, and convert the pre-image of the MySQL data that needs to be operated on into json format;

[0012] Step 7, record the data obtained after the MySQL operation as the after-image, convert the after-image into json format, and insert the table name, primary key name, primary key value, before-image json format and after-image json format into the database as a record of the MySQL transaction table;

[0013] Step 8: If the insertion fails, roll back the transaction. If the insertion succeeds, perform the MySQL operation.

[0014] Step 9: If the MySQL operation fails, roll back the transaction. If the MySQL operation succeeds, delete the records related to the transaction in the ES transaction table and the MySQL transaction table.

[0015] As a preferred solution of the present invention, the specific steps of the rollback are as follows:

[0016] Step A, query all ES transaction data of the transaction ID created in step 1, and traverse all ES transaction data according to steps C to G;

[0017] Step B, determine whether the traversal is completed, if it is completed, go to step H, otherwise go to step C;

[0018] Step C: Get the current ES data based on the index name, document ID, and changed fields queried in step 2;

[0019] Step D, determine whether there is after-image data in the currently traversed ES transaction data; if there is no after-image data, determine whether the current ES data exists; if the current ES data does not exist, create a document that is the same as the before-image in the currently traversed ES transaction data, delete the currently traversed ES transaction data, and return to step B; if the current ES data exists, delete the currently traversed ES transaction data, and return to step B; if there is after-image data, determine whether the current ES data exists;

[0020] Step E: If the current ES data does not exist, delete the currently traversed ES transaction data and return to step B; if the current ES data exists, obtain the json format of the current ES data;

[0021] Step F, determine whether the current ES data is consistent with the after-image in the currently traversed ES transaction data. If not, delete the currently traversed ES transaction data and return to step B. If consistent, determine whether the before-image in the currently traversed ES transaction data has data.

[0022] Step G: If the previous image has no data, delete the current ES data and the currently traversed ES transaction data, and return to step B; if the previous image has data, update the current ES data to the previous image format, delete the currently traversed ES transaction data, and return to step B;

[0023] Step H, query all MySQL transaction data of the current transaction ID created in step 1, and traverse all MySQL transaction data according to steps J to L;

[0024] Step I, determine whether the traversal is completed, if it is completed, roll back to the end, otherwise go to step J;

[0025] Step J, determining whether there is after-image data in the currently traversed MySQL transaction data; if there is no after-image data, obtaining the current MySQL data according to the table name, primary key name and primary key value queried in step 6, and determining whether the current MySQL data exists; if the current MySQL data does not exist, creating a row identical to the before-image in the currently traversed MySQL transaction data, deleting the currently traversed MySQL transaction data, and returning to step I; if the current MySQL data exists, deleting the currently traversed MySQL transaction data, and returning to step I; if there is after-image data, determining whether the current MySQL data can be queried according to the before-image in the currently traversed MySQL transaction data;

[0026] Step K, if the current MySQL data cannot be queried according to the pre-image in the currently traversed MySQL transaction data, then delete the currently traversed MySQL transaction data and return to step I; if the current MySQL data can be queried according to the pre-image in the currently traversed MySQL transaction data, then determine whether the pre-image in the currently traversed MySQL transaction data exists;

[0027] Step L, if the previous image in the currently traversed MySQL transaction data does not exist, delete the current MySQL data and the currently traversed MySQL transaction data at the same time, and return to step I; if the previous image in the currently traversed MySQL transaction data exists, update the current MySQL data to the previous image format, delete the currently traversed MySQL transaction data at the same time, and return to step I.

[0028] A computer device includes a memory, a processor, and a computer program stored in the memory and capable of running on the processor. When the processor executes the computer program, the steps of the method for processing ES and MySQL distributed transaction problems based on transaction tables as described above are implemented.

[0029] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for processing ES and MySQL distributed transaction problems based on transaction tables as described above are implemented.

[0030] Compared with the prior art, the present invention adopts the above technical solution and has the following technical effects:

[0031] The existing solutions to the distributed transaction problem can only be applied to different MySQL databases, and cannot be applied to ES and MySQL at the same time. The present invention provides a general method for solving the distributed transaction problem of ES and MySQL. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a flow chart of a method for processing ES and MySQL distributed transaction problems based on transaction tables of the present invention;

[0033] Figure 2 This is the rollback flow chart proposed by the present invention. DETAILED DESCRIPTION

[0034] The embodiments of the present invention are described in detail below, and examples of the embodiments are shown in the accompanying drawings. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and cannot be interpreted as limiting the present invention.

[0035] like Figure 1As shown, the present invention proposes a method for processing ES and MySQL distributed transaction problems based on transaction tables, and the specific steps are as follows:

[0036] S1. Before performing any operations, obtain the transaction ID, which must be unique.

[0037] S2. Before performing the ElasticSearch operation, query the data that needs to be operated on ES, obtain its index name, document ID and changed fields, and convert the pre-image of the data to be changed into json;

[0038] S3. Then name the data after the operation as after-image, convert it to json, and use the index name, document ID, changed field, before-image json and after-image json obtained from the query as a record of the ES transaction table, and then insert it into the database;

[0039] S4. If the insertion fails, roll back the transaction;

[0040] S5. If successful, operate ES. If the ES operation fails, roll back the transaction. If successful, continue with the following steps.

[0041] S6. Before performing MySQL operations, query the data that needs to be operated on MySQL, obtain its table name, primary key name and primary key value, and convert the previous image of the data to be changed into json;

[0042] S7, then name the data that will be transformed after the operation as after-image, convert it into json, and use the table name, primary key name, primary key value, before-image json and after-image json as a record of the MySQL transaction table, and then insert it into the database;

[0043] S8. If the insertion fails, roll back the transaction;

[0044] S9. If successful, operate MySQL. If the MySQL operation fails, roll back the transaction. If successful, delete the records related to the transaction in the ES transaction table and the MySQL transaction table.

[0045] like Figure 2 The following is a flowchart of the rollback operation:

[0046] Step A, query all ES transaction data of the transaction ID created in step 1, and traverse all ES transaction data according to steps C to G;

[0047] Step B, determine whether the traversal is completed, if it is completed, go to step H, otherwise go to step C;

[0048] Step C: Get the current ES data based on the index name, document ID, and changed fields queried in step 2;

[0049] Step D, determine whether there is after-image data in the currently traversed ES transaction data; if there is no after-image data, determine whether the current ES data exists; if the current ES data does not exist, create a document that is the same as the before-image in the currently traversed ES transaction data, delete the currently traversed ES transaction data, and return to step B; if the current ES data exists, delete the currently traversed ES transaction data, and return to step B; if there is after-image data, determine whether the current ES data exists;

[0050] Step E: If the current ES data does not exist, delete the currently traversed ES transaction data and return to step B; if the current ES data exists, obtain the json format of the current ES data;

[0051] Step F, determine whether the current ES data is consistent with the after-image in the currently traversed ES transaction data. If not, delete the currently traversed ES transaction data and return to step B. If consistent, determine whether the before-image in the currently traversed ES transaction data has data.

[0052] Step G: If the previous image has no data, delete the current ES data and the currently traversed ES transaction data, and return to step B; if the previous image has data, update the current ES data to the previous image format, delete the currently traversed ES transaction data, and return to step B;

[0053] Step H, query all MySQL transaction data of the current transaction ID created in step 1, and traverse all MySQL transaction data according to steps J to G;

[0054] Step I, determine whether the traversal is completed, if it is completed, roll back to the end, otherwise go to step J;

[0055] Step J, determining whether there is after-image data in the currently traversed MySQL transaction data; if there is no after-image data, obtaining the current MySQL data according to the table name, primary key name and primary key value queried in step 6, and determining whether the current MySQL data exists; if the current MySQL data does not exist, creating a row identical to the before-image in the currently traversed MySQL transaction data, deleting the currently traversed MySQL transaction data, and returning to step I; if the current MySQL data exists, deleting the currently traversed MySQL transaction data, and returning to step I; if there is after-image data, determining whether the current MySQL data can be queried according to the before-image in the currently traversed MySQL transaction data;

[0056] Step K, if the current MySQL data cannot be queried according to the pre-image in the currently traversed MySQL transaction data, then delete the currently traversed MySQL transaction data and return to step I; if the current MySQL data can be queried according to the pre-image in the currently traversed MySQL transaction data, then determine whether the pre-image in the currently traversed MySQL transaction data exists;

[0057] Step L, if the previous image in the currently traversed MySQL transaction data does not exist, delete the current MySQL data and the currently traversed MySQL transaction data at the same time, and return to step I; if the previous image in the currently traversed MySQL transaction data exists, update the current MySQL data to the previous image format, delete the currently traversed MySQL transaction data at the same time, and return to step I.

[0058] Based on the same inventive concept, an embodiment of the present application provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the aforementioned method for processing ES and MySQL distributed transaction problems based on transaction tables are implemented.

[0059] Based on the same inventive concept, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the steps of the aforementioned method for processing ES and MySQL distributed transaction problems based on transaction tables are implemented.

[0060] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0061] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1A device that provides the functions specified in a block or multiple blocks.

[0062] These computer program instructions may also be stored in a computer readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0063] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0064] The above embodiments are only for illustrating the technical idea of ​​the present invention, and cannot be used to limit the protection scope of the present invention. Any changes made on the basis of the technical solution in accordance with the technical idea proposed by the present invention shall fall within the protection scope of the present invention.

Claims

1. A method for processing ES and MySQL distributed transaction problems based on transaction tables, characterized in that: The steps include: Step 1: Create a unique ID for this transaction; Step 2: Query the index name, document ID, and changed fields of the ES data that needs to be operated by ElasticSearch, and convert the pre-image of the ES data that needs to be operated by ElasticSearch into json format; Step 3: record the data obtained after the ElasticSearch operation as the after-image, convert the after-image into json format, and insert the index name, document ID, changed fields, before-image json format and after-image json format into the database as a record of the ES transaction table; Step 4: If the insertion fails, roll back the transaction. If the insertion succeeds, perform the ElasticSearch operation. Step 5: If the ElasticSearch operation fails, roll back the transaction. If the ElasticSearch operation succeeds, proceed to step 6. Step 6, query the table name, primary key name and primary key value of the MySQL data that needs to be operated on, and convert the pre-image of the MySQL data that needs to be operated on into json format; Step 7, record the data obtained after the MySQL operation as the after-image, convert the after-image into json format, and insert the table name, primary key name, primary key value, before-image json format and after-image json format into the database as a record of the MySQL transaction table; Step 8: If the insertion fails, roll back the transaction. If the insertion succeeds, perform the MySQL operation. Step 9: If the MySQL operation fails, roll back the transaction. If the MySQL operation succeeds, delete the records related to the transaction in the ES transaction table and the MySQL transaction table.

2. The method for processing ES and MySQL distributed transaction problems based on transaction tables according to claim 1 is characterized in that: The specific steps of the rollback are as follows: Step A, query all ES transaction data of the transaction ID created in step 1, and traverse all ES transaction data according to steps C to G; Step B, determine whether the traversal is completed, if it is completed, go to step H, otherwise go to step C; Step C: Get the current ES data based on the index name, document ID, and changed fields queried in step 2; Step D, determining whether there is after-image data in the currently traversed ES transaction data; If there is no after-image data, determine whether the current ES data exists; if the current ES data does not exist, create a document that is the same as the before-image in the currently traversed ES transaction data, delete the currently traversed ES transaction data, and return to step B; if the current ES data exists, delete the currently traversed ES transaction data, and return to step B; if there is after-image data, determine whether the current ES data exists; Step E: If the current ES data does not exist, delete the currently traversed ES transaction data and return to step B; if the current ES data exists, obtain the json format of the current ES data; Step F, determine whether the current ES data is consistent with the after-image in the currently traversed ES transaction data. If not, delete the currently traversed ES transaction data and return to step B. If consistent, determine whether the before-image in the currently traversed ES transaction data has data. Step G: If the previous image has no data, delete the current ES data and the currently traversed ES transaction data, and return to step B; if the previous image has data, update the current ES data to the previous image format, delete the currently traversed ES transaction data, and return to step B; Step H, query all MySQL transaction data of the current transaction ID created in step 1, and traverse all MySQL transaction data according to steps J to L; Step I, determine whether the traversal is completed, if it is completed, roll back to the end, otherwise go to step J; Step J, determining whether there is after-image data in the currently traversed MySQL transaction data; If the after-image data does not exist, the current MySQL data is obtained according to the table name, primary key name and primary key value queried in step 6, and it is determined whether the current MySQL data exists; if the current MySQL data does not exist, a row identical to the before-image in the currently traversed MySQL transaction data is created, and the currently traversed MySQL transaction data is deleted, and the process returns to step I; if the current MySQL data exists, the currently traversed MySQL transaction data is deleted, and the process returns to step I; if the after-image data exists, it is determined whether the current MySQL data can be queried according to the before-image in the currently traversed MySQL transaction data; Step K, if the current MySQL data cannot be queried according to the pre-image in the currently traversed MySQL transaction data, then delete the currently traversed MySQL transaction data and return to step I; if the current MySQL data can be queried according to the pre-image in the currently traversed MySQL transaction data, then determine whether the pre-image in the currently traversed MySQL transaction data exists; Step L, if the previous image in the currently traversed MySQL transaction data does not exist, delete the current MySQL data and the currently traversed MySQL transaction data at the same time, and return to step I; if the previous image in the currently traversed MySQL transaction data exists, update the current MySQL data to the previous image format, delete the currently traversed MySQL transaction data at the same time, and return to step I.

3. A computer device comprising a memory, a processor, and a computer program stored in the memory and capable of running on the processor, characterized in that: When the processor executes the computer program, the steps of the method for processing ES and MySQL distributed transaction problems based on the transaction table as described in any one of claims 1 to 2 are implemented.

4. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method for processing ES and MySQL distributed transaction problems based on transaction tables as described in any one of claims 1 to 2 are implemented.

Images