Big transaction exception detection method and device, storage medium and equipment

Abstract

The application provides a large transaction exception detection method and device, a storage medium and equipment, comprising: acquiring any one SQL statement in a large transaction; if the SQL statement is an SQL statement for executing a write operation, extracting a plurality of first set statements from the SQL statement for executing the write operation, wherein the first set statements include any one of an insert statement, an update statement and a delete statement; judging whether the operation number of any one first set statement is greater than a first set threshold; if the operation number of any one first set statement is greater than the first set threshold, judging the large transaction exception. By judging whether the operation number of the first set statement in the SQL statement is greater than the set threshold, whether the large transaction has the condition of too much locked data or too long operation time is judged, so that the large transaction exception is judged.

Description

Technical Field

[0001] This invention relates to the field of database technology, and more specifically to methods, apparatus, storage media, and devices for detecting large transaction anomalies. Background Technology

[0002] In a database, a single SQL statement is called a basic operation. Combining several SQL statements into a single, executed task is called a transaction. A transaction commits all its contained SQL statements to the database as a whole. The entire transaction is considered successful only when all SQL statements have been executed. If any SQL statement fails, the entire transaction fails, and all SQL statements must be rolled back.

[0003] Large transactions are those with long execution times or large amounts of data. The risks associated with large transactions include: excessive data locking, leading to numerous blockages and lock timeouts, and prolonged rollback times; or long execution times, which can easily cause master-slave latency. Therefore, if an anomaly occurs in a large transaction, the system needs a considerable amount of time to report the anomaly, which is detrimental to efficient program execution; therefore, it is urgent to automatically detect anomalies in large transactions before execution. Summary of the Invention

[0004] Based on this, the present invention provides a method, apparatus, storage medium and device for detecting anomalies in large transactions, which can automatically detect whether there are anomalies in large transactions before execution, provide timely feedback on anomalies in large transactions, and improve the execution efficiency of the program.

[0005] In a first aspect, the present invention provides a method for detecting large transaction anomalies, comprising:

[0006] Retrieve any SQL statement from a large transaction;

[0007] If the SQL statement is a SQL statement that performs a write operation, extract several first setting statements from the SQL statement that performs the write operation, wherein the first setting statements include any one of insert statements, update statements and delete statements;

[0008] Determine whether the number of operations performed by any of the first-defined statements exceeds the first-defined threshold.

[0009] If the number of operations of any first-defined statement exceeds the first-defined threshold, then the large transaction is determined to be abnormal.

[0010] Secondly, the present invention provides a large transaction anomaly detection device, comprising:

[0011] The SQL retrieval module is used to retrieve any SQL statement from a large transaction.

[0012] The first extraction module is used to extract several first set statements from the SQL statement that performs a write operation if the SQL statement is an SQL statement that performs a write operation, wherein the first set statements include any one of insert statements, update statements and delete statements;

[0013] The first judgment module is used to determine whether the number of operations of any first set statement is greater than the first set threshold.

[0014] The first exception module is used to determine that the large transaction is abnormal if the number of operations of any first set statement exceeds the first set threshold.

[0015] Thirdly, the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of any of the large transaction anomaly detection methods in the first aspect.

[0016] Fourthly, the present invention provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform any one of the large transaction anomaly detection methods in the first aspect.

[0017] The beneficial effects of adopting the above technical solution are as follows: This application determines whether the number of operations of the setting statement in any SQL statement that performs a write operation in the transaction exceeds the set threshold, whether the number of calls to the setting statement in all SQL statements that perform write operations in the transaction exceeds the set threshold, and whether the transaction calls external interfaces in addition, thereby detecting whether the large transaction being executed is abnormal; through the above settings, the operation of manually intervening to detect whether the large transaction is abnormal can be reduced, and the time for the system to report large transaction abnormalities can be shortened, thereby improving the efficient execution of the program. Attached Figure Description

[0018] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below.

[0019] Figure 1 This is a schematic diagram of a large transaction anomaly detection method in one embodiment of this application;

[0020] Figure 2 This is a schematic diagram of a large transaction anomaly detection device in one embodiment of this application. Detailed Implementation

[0021] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention. To describe the present invention in more detail, the present invention will be specifically described below with reference to the accompanying drawings.

[0022] Large transactions refer to transactions that take a long time to run and involve a large amount of data. Large transactions arise due to the large amount of data being processed, significant lock contention, and other time-consuming operations within the transaction. Under concurrent conditions, large transactions can easily overwhelm the database connection pool due to the massive amount of data being processed; excessive locking can also cause numerous blocking and lock timeouts, and the long execution time can lead to master-slave latency.

[0023] This application provides specific application scenarios for the large transaction anomaly detection method. These application scenarios include the terminal devices provided in the embodiments, which include, but are not limited to, smartphones and computer devices. The computer device can be at least one of a desktop computer, portable computer, laptop computer, tablet computer, etc. When a user operates the terminal device, the terminal device executes the large transaction anomaly detection method of this invention. For details, please refer to the embodiments of the large transaction anomaly detection method.

[0024] Based on this, embodiments of the present invention provide a method for detecting large transaction anomalies. The method is illustrated using an application to a terminal device as an example, in conjunction with the appendix. Figure 1 The diagram shows a method for detecting anomalies in large transactions.

[0025] Step S101: Obtain any SQL statement from the large transaction.

[0026] A large transaction contains several SQL statements, each representing a basic operation in the database. A large transaction needs to commit all its SQL statements as a whole to the database. The entire transaction is considered successful only if all SQL statements have been executed. If any one SQL statement fails, the entire large transaction fails, and all SQL statements must be rolled back.

[0027] In large transactions, the SQL statements can be obtained by listening to the getBoundSql method of the MappedStatement class.

[0028] Step S102: If the SQL statement is an SQL statement that performs a write operation, extract several first setting statements from the SQL statement that performs the write operation, wherein the first setting statements include any one of insert statements, update statements and delete statements.

[0029] Since the purpose of this invention is to detect abnormal SQL statements in large transactions that lock too much data or have excessively long operation times, after randomly selecting any SQL statement, it is first necessary to determine whether the selected SQL statement has data locking or data operation behavior. If there is no data locking or data operation behavior, no further abnormal judgment is needed for the SQL statement. Specifically, SQL statements that have data locking or data operation are set as SQL statements that perform write operations. For SQL statements that perform write operations, a first set statement is extracted. A single SQL statement may contain one or more first set statements.

[0030] Step S103: Determine whether the number of operations of any first set statement is greater than the first set threshold.

[0031] The first threshold, denoted as N, is a preset maximum number of operations. When the number of operations performed by a given statement exceeds this threshold, the SQL statement executing the write operation is considered to have locked too much data or taken too long. In practice, any one of the first threshold statements can be selected for judgment according to the execution order of the SQL statements, or any one of the first threshold statements can be randomly selected from the SQL statements for judgment.

[0032] The methods for determining the number of operations differ depending on whether the first set statement being extracted is of a different type.

[0033] When the first set statement extracted is an insert statement, it is determined whether the number of assignment operations in the insert statement exceeds a first set threshold. Specifically, the number of assignment operations in the insert statement is obtained by counting the number of "value" entries in the insert statement.

[0034] When the first set statement extracted is an update statement, it is determined whether the number of data update operations in the update statement exceeds a first set threshold. Specifically, the number of update operations in the update statement is obtained by counting the commas in the update statement.

[0035] When the first set statement extracted is a deletion statement, it is determined whether the number of data deletion operations in the deletion statement exceeds a first set threshold. Specifically, the number of data deletion operations in the deletion statement is obtained by counting the commas in the deletion statement.

[0036] Step S104: If the number of operations of any first set statement is greater than the first set threshold, then the large transaction is determined to be abnormal.

[0037] If the number of operations of any of the selected SQL statements that perform write operations exceeds the set threshold N, then the SQL statement that performs the write operation is considered to have locked too much data or the operation time is too long, and the large transaction is judged to be abnormal.

[0038] In the above embodiment, any SQL statement that performs a write operation is selected from the large transaction. It is determined whether the number of operations of a single setting statement in the SQL statement that performs the write operation exceeds a first set threshold. In fact, it is determined whether there is too much locked data or too long operation time in a single setting statement in the SQL statement that performs the write operation. Thus, based on the abnormal situation of a single setting statement, it is determined that there is an anomaly in the execution of the large transaction.

[0039] Furthermore, methods for detecting large transaction anomalies also include:

[0040] Step S201: When the number of operations of each of the first set statements is less than the first set threshold, determine whether the total number of operations of all the first set statements is greater than the first set threshold.

[0041] If the number of operations of each first-set statement in the selected SQL statement for writing is less than the threshold, it means that there is no excessive data locking or excessive operation time in a single first-set statement in the SQL statement for writing. In this case, it is necessary to further judge the number of operations of all first-set statements in the SQL statement for writing.

[0042] The total number of operations for each of the first-defined statements in the SQL statement performing the write operation is obtained by summing the operation counts. Similarly, the first-defined statements can be any of the insert, update, or delete statements; the process of obtaining the operation counts is not described in detail here.

[0043] Step S202: If the total number of operations of all the first set statements is greater than the first set threshold, then the large transaction is determined to be abnormal.

[0044] If the total number of operations of all the first-set statements is greater than the first preset threshold, it means that during the execution of the SQL statement for the write operation, any of the insert, update or delete statements has locked too much data or the operation time is too long, thus causing a large transaction exception.

[0045] Furthermore, methods for detecting large transaction anomalies also include:

[0046] Step S301: If the total number of operations of all first-set statements is less than the first-set threshold, obtain all SQL statements that perform write operations in the large transaction, and extract each first-set statement from all SQL statements that perform write operations.

[0047] Step S302: Determine whether the sum of the number of operations of each of the first-defined statements in all SQL statements that perform write operations is greater than the second-defined threshold.

[0048] Step S303: If the sum of the number of operations of each of the first-defined statements in all SQL statements that perform write operations is greater than the second-defined threshold, then the large transaction is determined to be abnormal.

[0049] Based on the above steps, after confirming that each SQL statement performing a write operation can be executed normally individually, the next step is to determine whether the sum of all SQL statements performing write operations within the large transaction can be executed normally. Specifically, the number of operations of each SQL statement performing a write operation is summed up according to a first-defined set of statements. The sum of the summed operation counts is compared with a second-defined threshold. If the sum of the operation counts is greater than the second-defined threshold, it is determined that the large transaction has locked too much data or the operation time is too long during execution, thus indicating that the large transaction is abnormal.

[0050] In addition to locking too much data or taking too long to complete an operation, other abnormal situations involving large transactions include violations of the atomicity property of large transactions, specifically:

[0051] Step S401: If the SQL statement is a SQL statement that performs a write operation, determine whether the large transaction calls an external interface.

[0052] Step S402: If the large transaction calls an external interface, then the large transaction is determined to be abnormal.

[0053] To ensure that large transactions do not result in excessive data locking or prolonged operation time, it is necessary to verify their atomicity. Large transactions possess atomicity; they are logical units of work in the database. Under normal execution, a large transaction should not involve two or more atomic operations. Therefore, if a large transaction contains write operations and also calls external interfaces, it indicates that the large transaction is making external calls while executing write operations, which violates the atomicity requirement of large transactions.

[0054] In practice, since the same thread is used for the same call within the same large transaction, we can determine whether the large transaction makes external calls when executing the SQL statement for the write operation by querying "thread name + transaction name".

[0055] Specifically, the thread name, transaction name, a flag indicating whether the SQL statement in the transaction is a write operation, and a record of whether the transaction calls an external interface are summarized and stored in a global variable. After the large transaction is completed, the value of this global variable is deleted. The stored global variable is obtained by querying "thread name + transaction name". If the global variable contains a flag indicating that the SQL statement in the transaction is a write operation, it is further checked whether there is a record of an external interface call. If so, it means that when the large transaction calls this thread, the large transaction contains both write operations and external interface calls, which violates the atomicity of large transactions, and the large transaction is determined to be abnormal. Similarly, the stored global variable is obtained by querying "thread name + transaction name". If the global variable contains a record of external interface calls, it is further checked whether there is a flag indicating that the SQL statement in the transaction is a write operation. If so, it means that when the large transaction calls this thread, the large transaction contains both external interface calls and write operations, which violates the atomicity of large transactions, and the large transaction is determined to be abnormal.

[0056] The above-described method for detecting large transaction anomalies checks the number of operations performed by each individual write operation SQL statement within a large transaction, as well as the number of insert, update, and delete operations within all write operations SQL statements. Based on the number of operations, it determines whether the large transaction is locking excessive data or requiring excessively long operation times, thus identifying a large transaction anomaly. Additionally, it checks the atomicity of large transactions to determine whether the transaction calls external interfaces while including write operations SQL statements, further confirming anomalies. This automatic detection of large transaction anomalies reduces the need for manual intervention in detecting anomalies, shortens the time it takes for the system to report large transaction anomalies, and improves program efficiency.

[0057] It should be understood that, although attached Figure 1 The steps in the flowchart are shown sequentially according to the arrows, but these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified in this document, there is no strict order requirement for the execution of these steps, and they can be executed in other orders. Furthermore, [the following is a list of steps]. Figure 1 At least some of the steps in the process may include multiple sub-steps or sub-stages. These sub-steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be executed in turn or alternately with other steps or at least some of the sub-steps or stages of other steps.

[0058] The above-described embodiments of the present invention describe a method for detecting large transaction anomalies. Since this method can be implemented using various types of devices, the present invention also discloses a device for detecting large transaction anomalies corresponding to the above method. Figure 2 The following are specific embodiments for detailed explanation.

[0059] SQL retrieval module 501 is used to retrieve any SQL statement in a large transaction.

[0060] The first extraction module 502 is used to extract several first setting statements from the SQL statement that performs a write operation if the SQL statement is an SQL statement that performs a write operation, wherein the first setting statements include any one of insert statements, update statements and delete statements.

[0061] The first judgment module 503 is used to determine whether the number of operations of any first set statement is greater than the first set threshold.

[0062] The first exception module 504 is used to determine that the large transaction is abnormal if the number of operations of any first set statement is greater than the first set threshold.

[0063] Furthermore, the large transaction anomaly detection device also includes:

[0064] The second judgment module is used to determine whether the total number of operations of all first-set statements is greater than the first-set threshold when the number of operations of each of the first-set statements is less than the first-set threshold.

[0065] The second exception module is used to determine that the large transaction is abnormal if the total number of operations of all the first set statements is greater than the first set threshold.

[0066] Specific limitations regarding the large transaction anomaly detection device can be found in the method limitations section above, and will not be repeated here. Each module in the aforementioned device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in hardware or independent of the terminal device's processor, or stored in software within the terminal device's memory, so that the processor can call and execute the corresponding operations of each module.

[0067] In one embodiment, the present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the first aspect of the large transaction anomaly detection method described above.

[0068] The computer-readable storage medium may be an electronic memory such as flash memory, EEPROM (Electrically Erasable Programmable Read-Only Memory), EPROM (Erasable Programmable Read-Only Memory), hard disk, or ROM. Optionally, the computer-readable storage medium includes non-transitory computer-readable storage medium. The computer-readable storage medium has storage space for program code that performs any of the method steps described above. This program code can be read from or written to one or more computer program products, and the program code may be compressed in an appropriate form.

[0069] In one embodiment, the present invention provides a computer device including a memory and a processor, wherein the memory stores a computer program, and the processor executes the steps of the above-described large transaction anomaly detection method when executing the computer program.

[0070] The computer device includes a memory, a processor, and one or more computer programs, wherein the one or more computer programs may be stored in the memory and configured to be executed by one or more processors, and one or more application programs are configured to perform the above-described large transaction anomaly detection method.

[0071] A processor may include one or more processing cores. The processor connects to various parts of the computer device using various interfaces and lines, and performs various functions and processes data by running or executing instructions, programs, code sets, or instruction sets stored in memory, and by calling data stored in memory. Optionally, the processor may be implemented using at least one hardware form of Digital Signal Processing (DSP), Field-Programmable Gate Array (FPGA), or Programmable Logic Array (PLA). The processor may integrate one or a combination of several of the following: Central Processing Unit (CPU), Graphics Processing Unit (GPU), and modem. The CPU primarily handles the operating system, user interface, and applications; the GPU is responsible for rendering and drawing the displayed content; and the modem handles wireless communication. It is understood that the modem may also be implemented separately as a communication chip, without being integrated into the processor.

[0072] The memory may include random access memory (RAM) or read-only memory (ROM). The memory can be used to store instructions, programs, code, code sets, or instruction sets. The memory may include a program storage area and a data storage area. The program storage area may store instructions for implementing an operating system, instructions for implementing at least one function (such as touch functionality, sound playback functionality, image playback functionality, etc.), and instructions for implementing the various method embodiments described above. The data storage area may also store data created by the terminal device during use.

[0073] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for detecting anomalies in large transactions, characterized in that, include: Retrieve any SQL statement from a large transaction; If the SQL statement is a SQL statement that performs a write operation, extract several first setting statements from the SQL statement that performs the write operation, wherein the first setting statements include any one of insert statements, update statements and delete statements; Determine whether the number of operations performed by any of the first-defined statements exceeds the first-defined threshold. If the number of operations of any of the first set statements exceeds the first set threshold, then the large transaction is determined to be abnormal. Specifically, when the number of operations for each of the first set statements is less than the first set threshold, it is determined whether the total number of operations for all the first set statements is greater than the first set threshold; if the total number of operations for all the first set statements is greater than the first set threshold, then the large transaction is determined to be abnormal. If the total number of operations of all first-defined statements is less than the first-defined threshold, obtain all SQL statements that perform write operations in the large transaction, and extract each first-defined statement from all SQL statements that perform write operations; determine whether the sum of the number of operations of each first-defined statement in all SQL statements that perform write operations is greater than the second-defined threshold. If the sum of the number of operations of each of the first-defined statements in all SQL statements that perform write operations is greater than the second-defined threshold, then the large transaction is judged to be abnormal.

2. The method for detecting large transaction anomalies as described in claim 1, characterized in that, Also includes: If the SQL statement is a SQL statement that performs a write operation, determine whether the large transaction calls an external interface; If the large transaction calls an external interface, then the large transaction is deemed to be abnormal.

3. The method for detecting large transaction anomalies as described in any one of claims 1-2, characterized in that, The process of retrieving any SQL statement from a large transaction includes: You can retrieve any SQL statement from a large transaction by listening to the getBoundSql method of the MappedStatement class.

4. The method for detecting large transaction anomalies as described in any one of claims 1-2, characterized in that, The determination of whether the number of operations of any first-defined statement is greater than the first-defined threshold includes: Determine whether the number of assignment operations in the insert statement exceeds a first set threshold; Alternatively, determine whether the number of data update operations in the update statement exceeds a first set threshold; Alternatively, it can determine whether the number of data deletion operations in a deletion statement exceeds a first set threshold.

5. A large transaction anomaly detection device, characterized in that, include: The SQL retrieval module is used to retrieve any SQL statement from a large transaction. The first extraction module is used to extract several first set statements from the SQL statement that performs a write operation if the SQL statement is an SQL statement that performs a write operation, wherein the first set statements include any one of insert statements, update statements and delete statements; The first judgment module is used to determine whether the number of operations of any first set statement is greater than the first set threshold. The first exception module is used to determine that the large transaction is abnormal if the number of operations of any first-defined statement exceeds a first-defined threshold. The second judgment module is used to determine whether the total number of operations of all first-set statements is greater than the first-set threshold when the number of operations of each of the first-set statements is less than the first-set threshold. The second exception module is used to determine that the large transaction is abnormal if the total number of operations of all the first set statements is greater than the first set threshold.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of any one of the large transaction anomaly detection methods in claims 1-4.

7. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it performs any one of the large transaction anomaly detection methods according to claims 1-4.

Images