Method and apparatus for processing lock waiting transactions in a database

Abstract

The application discloses a method and device for processing lock waiting transactions in a database. The method comprises the following steps: determining a lock holding transaction in a lock waiting state in the database; determining a target transaction executed before the lock holding transaction; comparing an execution flow of a statement in the target transaction with a benchmark flow, and determining a problem transaction causing the lock waiting state according to a comparison result; and performing an operation corresponding to a statement state of the problem transaction on the problem transaction according to the statement state, so as to complete processing of the lock waiting transaction in the database. The application solves the technical problem of low processing efficiency of the lock waiting transaction caused by the lack of a method for locating analysis and processing of the beginning and end of the entire lock waiting event. The method for processing the lock waiting transaction in the database can be applied to the financial field and used for data processing in a bank data center.

Description

Technical Field

[0001] This application relates to the field of database processing, and more specifically, to a method and apparatus for handling lock-waiting transactions in a database. Background Technology

[0002] MySQL (a relational database management system) achieves transaction isolation through locking mechanisms and isolation levels, with different isolation levels employing different locking strategies. Under high concurrency or improper operation, row lock waiting can occur, which can range from blocking transaction execution to rendering the database unable to provide services.

[0003] The industry generally employs two methods to address lock wait issues. One is to configure MySQL system parameters to control the lock wait timeout; if the timeout occurs, the SQL execution fails—a passive approach. The other is to actively obtain relevant lock wait information from MySQL's system tables, analyze and handle lock wait events. Furthermore, in the current MySQL 5.7.21 version, there are four system databases with a total of 280 tables, recording and statistical information covering MySQL metadata, runtime status, user information, performance and capacity information, etc. Among these, nearly ten tables are related to row locks, resulting in a somewhat disorganized information distribution, requiring the retrieval of useful information across multiple tables. The former method relies solely on MySQL itself to stop the SQL statement from waiting after the lock wait timeout, failing to locate and handle the holding transaction. Setting the timeout too large or too small can impact normal business processes and even database performance. The latter method obtains information from system tables in the system databases, but this table collects limited information, only revealing the lock-waiting transaction and the internal transaction number and lock type, still unable to locate, analyze, and handle the entire lock wait event, impacting development and production. At this point, a lock detection tool is needed to detect lock waits and identify the "culprit" holding the lock, which will facilitate subsequent optimization of business logic and database development.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] This application provides a method and apparatus for processing lock-waiting transactions in a database, which at least solves the technical problem of low efficiency in lock-waiting transaction processing caused by the lack of a method for locating, analyzing, and processing the entire lock-waiting event.

[0006] According to one aspect of the embodiments of this application, a lock-waiting transaction processing method in a database is provided, comprising: determining a lock-holding transaction in the database that is in a lock-waiting state; determining a target transaction that was executed before the lock-holding transaction; comparing the execution flow of the statements in the target transaction with a baseline flow, and determining a problem transaction that caused the lock-waiting state based on the comparison result; and performing an operation corresponding to the statement state on the problem transaction according to the statement state corresponding to the problem transaction, so as to complete the processing of the lock-waiting transaction in the database.

[0007] Optionally, comparing the execution flow of statements in the target transaction with the baseline flow and determining the problematic transaction causing the lock waiting state based on the comparison result includes: obtaining a set number of transactions executed before the lock-holding transaction; in response to a text processing command, removing redundant statements from the set number of transactions; determining the set number of transactions with the redundant statements removed as the target transaction; comparing the execution flow of statements in the target transaction with the baseline flow and determining the problematic transaction causing the lock waiting state based on the comparison result.

[0008] Optionally, the text processing commands include: text processing commands built into the database system; wherein the built-in text processing commands include: text filters, stream editors, and report generators.

[0009] Optionally, before performing the operation corresponding to the statement state on the problematic transaction based on the statement state corresponding to the problematic transaction, the method further includes: determining the statement state corresponding to the problematic transaction, wherein: if the number of modified lines of the statement corresponding to the problematic transaction is greater than a first set line number threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold, the statement state corresponding to the problematic transaction is determined to be a statement performance abnormality; if the number of modified lines of the statement corresponding to the problematic transaction is less than a second set line number threshold and the execution time of the statement corresponding to the problematic transaction is greater than the first set time threshold, the statement state corresponding to the problematic transaction is determined to be executed but not saved.

[0010] Optionally, processing the problematic transaction according to the statement status corresponding to the problematic transaction includes: rolling back the problematic transaction if the statement status corresponding to the problematic transaction is an abnormal statement performance; and saving the problematic transaction if the statement status corresponding to the problematic transaction is executed but not saved.

[0011] Optionally, determining the lock-holding transaction includes: determining the transaction in the target lock waiting state from a first system table in the database; wherein the first system table is used to record the threads of the transaction in the lock waiting state; determining the thread number of the transaction in the target lock waiting state from a second system table in the database; wherein the second system table is used to record the connection number of the transaction; and determining the transaction in the lock waiting state with the smallest thread number as the lock-holding transaction.

[0012] Optionally, determining the transaction in the target lock waiting state includes: determining the transaction in the lock waiting state from the first system table; and determining the transaction in the waiting state whose waiting time exceeds a second set time threshold as the transaction in the target lock waiting state.

[0013] According to another aspect of the embodiments of this application, a lock-waiting transaction processing apparatus for a database is also provided, comprising: an acquisition module, configured to determine a lock-holding transaction in the database that is in a lock-waiting state; an extraction module, configured to determine a target transaction executed before the lock-holding transaction; a determination module, configured to compare the execution flow of statements in the target transaction with a baseline flow, and determine the problematic transaction that caused the lock-waiting state based on the comparison result; and a processing module, configured to perform an operation corresponding to the statement state on the problematic transaction according to the statement state corresponding to the problematic transaction, so as to complete the processing of the lock-waiting transaction in the database.

[0014] According to another aspect of the embodiments of this application, a non-volatile storage medium is also provided, the non-volatile storage medium including a stored program, wherein, when the program is running, it controls the device where the non-volatile storage medium is located to execute the above-described database lock wait transaction processing method.

[0015] According to another aspect of the embodiments of this application, an electronic device is also provided, including: a processor and a memory; the processor is used to run a program, wherein the program executes the above-described database lock wait transaction processing method when it runs.

[0016] In this embodiment, the following approach is adopted: identifying lock-holding transactions in the database that are in a lock-waiting state; identifying target transactions executed before the lock-holding transactions; comparing the execution flow of statements in the target transactions with a benchmark flow; identifying the problematic transaction causing the lock-waiting state based on the comparison results; and performing operations corresponding to the statement state of the problematic transaction on the problematic transaction to complete the processing of lock-waiting transactions in the database. By first identifying the lock-holding transactions, then comparing the transactions executed before the lock-holding transactions with the standard flow to identify the problematic transactions, and finally processing them based on the statement state of the problematic transactions, the aim of quickly locating the problematic transactions and quickly resolving the problems based on the statement state of the problematic transactions is achieved. This achieves the technical effect of quickly and accurately resolving lock-waiting problems, thereby solving the technical problem of low efficiency in lock-waiting transaction processing caused by the lack of methods for locating, analyzing, and processing the entire lock-waiting event. Attached Figure Description

[0017] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0018] Figure 1 This is a hardware structure block diagram of a computer terminal (or mobile device) for implementing lock waiting transactions in a database, according to an embodiment of this application.

[0019] Figure 2 This is a schematic diagram of an optional method for handling lock-waiting transactions in a database according to an embodiment of this application;

[0020] Figure 3 This is a schematic diagram of another optional method for handling lock-waiting transactions in a database according to an embodiment of this application;

[0021] Figure 4 This is a schematic diagram of an optional database lock waiting transaction processing apparatus according to an embodiment of this application;

[0022] Figure 5 This is a schematic diagram of an optional database lock waiting transaction processing tool according to an embodiment of this application. Detailed Implementation

[0023] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0024] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0025] Example 1

[0026] The method embodiment provided in Embodiment 1 of this application can be executed in a mobile terminal, computer terminal or similar computing device. Figure 1 A hardware block diagram of a computer terminal (or mobile device) for implementing a method to handle lock-waiting transactions in a database is shown. Figure 1 As shown, the computer terminal 10 (or mobile device 10) may include one or more processors 102 (shown as 102a, 102b, ..., 102n in the figure) 102 (processor 102 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 104 for storing data, and a transmission module 106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, computer terminal 10 may also include... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0027] It should be noted that the aforementioned one or more processors 102 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be integrated, in whole or in part, into any other element within the computer terminal 10 (or mobile device). As involved in the embodiments of this application, the data processing circuits serve as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0028] The memory 104 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to a database lock waiting transaction processing method in an embodiment of this application. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, thereby implementing the above-mentioned application vulnerability detection method. The memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0029] The transmission module 106 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the computer terminal 10. In one example, the transmission module 106 includes a Network Interface Controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the transmission module 106 may be a Radio Frequency (RF) module, used for wireless communication with the Internet.

[0030] The display can be, for example, a touchscreen liquid crystal display (LCD) that allows the user to interact with the user interface of the computer terminal 10 (or mobile device).

[0031] In the above operating environment, this application embodiment provides a method embodiment for handling lock waiting transactions in a database. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0032] Figure 2 This is a method for handling database lock waiting transactions according to an embodiment of this application, such as... Figure 2 As shown, the method includes the following steps:

[0033] Step S202: Identify the lock-holding transactions in the database that are in a lock-waiting state;

[0034] Step S204: Determine the target transaction to be executed before the lock-holding transaction;

[0035] Step S206: Compare the execution flow of the statements in the target transaction with the baseline flow, and determine the problematic transaction that caused the lock waiting state based on the comparison result;

[0036] Step S208: Perform the operation corresponding to the statement state of the problematic transaction according to the statement state of the problematic transaction, so as to complete the processing of the lock waiting transaction in the database.

[0037] Through the above steps, we can first identify the lock-holding transaction, then compare the transactions executed before the lock-holding transaction with the standard process to identify the problematic transaction, and finally process it according to the statement status of the problematic transaction. This achieves the goal of quickly locating the problematic transaction and quickly resolving the problem based on the statement status of the problematic transaction, thereby achieving the technical effect of quickly and accurately resolving the lock waiting problem. In turn, it solves the technical problem of low efficiency in lock waiting transaction processing caused by the lack of methods for locating, analyzing and processing the entire lock waiting event.

[0038] It should be noted that, in one optional approach, the database can be a relational database like MySQL. The data processing plugins for MySQL can include MyISAM, InnoDB, etc. In this embodiment, a transaction refers to the smallest indivisible unit of work. Typically, a transaction corresponds to a complete business transaction, such as a bank account transfer, which is the smallest unit of work. For MySQL databases, a transaction generally refers to all SQL statements contained between the BEGIN and COMMIT commands. Transactions possess atomicity, consistency, isolation, and durability. To achieve transaction isolation, the concept of isolation levels is proposed. Transactions behave differently for different isolation levels. MySQL has four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. For row locks, isolation levels restrict data through different locks, including row locks. Here, a row lock refers to a mutex lock on a record or index, also known as a write lock.

[0039] In some embodiments of this application, the specific process of comparing the execution flow of statements in the target transaction with a baseline flow and determining the problematic transaction causing the lock waiting state based on the comparison result includes: obtaining a set number of transactions executed before the lock-holding transaction; in response to a text processing command, removing redundant statements from the set number of transactions; determining the set number of transactions after removing redundant statements as the target transaction; comparing the execution flow of statements in the target transaction with the baseline flow, and determining the problematic transaction causing the lock waiting state based on the comparison result. By extracting a set number of executed transactions before the lock-holding transaction, using text processing commands to simplify and extract key information, and then comparing it with a standard flow to determine the problematic transaction causing the lock state, it is easier to analyze the cause of the lock waiting, find the corresponding transaction logic, and thus solve the lock waiting problem.

[0040] It should be noted that the transaction holding the lock is not necessarily the transaction that caused the waiting state. It is necessary to extract a set number of transactions that have most recently been executed by the transaction holding the lock, such as 10 or 20. The comparison should be performed to minimize the number of comparisons and speed up the comparison process.

[0041] In some embodiments of this application, the text processing commands include: text processing commands built into the database system; wherein, the built-in text processing commands include: text filters, stream editors, and report generators. Directly utilizing the built-in text processing commands in the database system eliminates the need for additional devices or processing software, making operation more convenient.

[0042] It should be noted that the database system can be a Linux system; text processing commands in the Linux system can include sed (text filter), awk (stream editor), grep (report generator), etc. Redundant statements to be filtered include, but are not limited to, database operation statements, such as DML statements.

[0043] In some embodiments of this application, the statement status corresponding to a problematic transaction can be determined through the following means: when the number of modified lines of the statement corresponding to the problematic transaction is greater than a first set line number threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold, the statement status corresponding to the problematic transaction is determined to be a statement performance abnormality; when the number of modified lines of the statement corresponding to the problematic transaction is less than a second set line number threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold, the statement status corresponding to the problematic transaction is determined to be executed but not saved.

[0044] It should be noted that statements exceeding the first set threshold are considered performance-critical and cannot be executed, while statements below the second set threshold are considered executed but not saved. Therefore, the first threshold is greater than the second threshold. "Executed but not saved" means the statement has been executed but not committed.

[0045] In some embodiments of this application, the method for processing the problematic transaction based on the statement status corresponding to the problematic transaction includes, but is not limited to, the following methods: If the statement status corresponding to the problematic transaction is "statement performance abnormality," roll back the problematic transaction; if the statement status corresponding to the problematic transaction is "executed but not saved," save the problematic transaction. Committing a transaction refers to a successful completion, synchronously saving all DML statement operation history and underlying disk data. Transaction rollback refers to a failed completion, clearing all DML (insert, update, delete) statement operation history. For example, when a problem occurs in one operation of a transaction, the entire transaction can be rolled back, as if no operation was performed; in other words, when an error occurs or the transaction is canceled, the transaction is rolled back.

[0046] In some embodiments of this application, a lock-holding transaction can be determined by the following methods: determining a transaction in a target lock waiting state from a first system table in the database; wherein the first system table is used to record the threads of the transaction in the lock waiting state; determining the thread number of the transaction in the target lock waiting state from a second system table in the database; wherein the second system table is used to record the connection number of the transaction; and determining the transaction in the lock waiting state with the smallest thread number as the lock-holding transaction.

[0047] The method for determining the transaction in the target lock waiting state includes: determining the transaction in the lock waiting state from the first system table; and determining the transaction in the waiting state whose waiting time exceeds a second set time threshold as the transaction in the target lock waiting state.

[0048] It should be noted that, in one optional approach, the first system table refers to the sys.innodb_lock_waits system table in the MySQL database; the second system table refers to the performance_schema.threads system table in the MySQL database.

[0049] In one alternative approach, taking MySQL as an example, the method for handling lock wait transactions in the database can be as follows: Figure 3 As shown, it includes the following steps:

[0050] Step S301: Obtain the lock waiting status and the thread tid (number name) related to the lock;

[0051] Step S302: Based on the thread tid, obtain the lock-holding SQL (programming language) and the pid (number name) of the connection where the lock-holding SQL is located;

[0052] Step S303: Based on the PID, obtain the currently executing SQL and status of the lock-holding transaction;

[0053] Step S304: Based on the pid and tid obtained above, retrieve the 10 most recently executed SQL statements of the lock-holding transaction;

[0054] Step S305: Based on the obtained historical SQL, filter out suspicious lock-holding SQL.

[0055] In one optional approach, the lock wait status and the thread ID (tid) related to the lock can be obtained by retrieving data from the system table `sys.innodb_lock_waits` to check for lock waits. If a wait occurs, the wait time is retrieved; if it exceeds 2 seconds, it is considered a pending lock wait event. The thread IDs of the lock-holding and lock-waiting SQL statements are then obtained. Based on the thread ID, the lock-waiting SQL statement and the connection ID (pid) of the lock-holding SQL statement are obtained. The connection IDs of the lock-holding and lock-waiting SQL statements can be retrieved from the system table `performance_schema.threads` using the thread ID obtained in step S301. The currently executing SQL statements of the lock-waiting and lock-holding transactions can also be obtained. Finally, based on the pid, the currently executing SQL statement and its status for the lock-holding transaction are retrieved. The currently executing SQL statements of the two transactions can be obtained from the system table `performance_schema.events_statements_current`. Based on the PID and tid obtained above, the 10 most recently executed SQL statements of the lock-holding transaction can be obtained. Based on the connection ID obtained in the above steps, the 10 most recently executed SQL statements of the lock-holding transaction can be obtained from the system table `performance_schema.events_statements_history`. Based on the obtained historical SQL statements, suspicious lock-holding SQL statements are filtered out. Specifically, the 10 most recently executed SQL statements are compared with the transaction SQL model to locate the problematic transaction. The next operation is evaluated based on the statement status, and the transaction is committed or rolled back.

[0056] In one alternative approach, problematic statements can be located using Linux's built-in text processing commands sed, awk, and grep. These commands can filter out DML statements, pinpoint the locking SQL, and provide the developers with the 10 most recent SQL statements. By comparing the execution logic before and after the statement, the location of the transaction can be inferred, and the problem can be identified. This will help determine whether the lock wait is caused by a performance issue or by forgetting to commit after the statement execution.

[0057] In some alternative methods, the program can be deployed in the operating system's scheduled task queue, and the recorded output can be archived to a log to achieve scheduled monitoring. Alternatively, the program can be deployed on a server, and a scheduled task can be set up to periodically capture database lock status information and archive it to the log.

[0058] It's important to note that InnoDB is a storage engine used in MySQL databases. Tables using the InnoDB engine have the following characteristics: Compared to MyISAM, InnoDB supports transactions, row-level locking, offers better data recovery capabilities, better concurrency performance, and provides better support for multi-core, large-memory hardware, as well as hot data backup. Furthermore, InnoDB tables are index-organized tables (IOTs) based on B+ trees, where the index is a clustered index, and all row records are stored in the leaf nodes of the B+ tree. Data provided by the sys system library, a feature introduced in MySQL 5.7, will still be usable in MySQL 8.0, ensuring high compatibility as there are no concerns about tool incompatibility due to database system upgrades.

[0059] In real-world business scenarios, lock wait information is scattered across multiple locations in MySQL system tables, making problem localization and analysis complex. This application aggregates data from multiple MySQL system tables, organizing it into records that developers can understand to assist in problem resolution. Real-time data capture ensures the data reflects the database's internal state at the same moment, preventing invalid data from affecting problem analysis. Furthermore, the method proposed in this application can be deployed on a remote server to retrieve data from the database remotely.

[0060] Example 2

[0061] This application also proposes a lock-waiting transaction processing device in a database, such as... Figure 4 As shown, it includes: an acquisition module 40, used to identify lock-holding transactions in the database that are in a lock-waiting state; an extraction module 42, used to identify a target transaction executed before the lock-holding transaction; a determination module 44, used to compare the execution flow of the statements in the target transaction with the baseline flow, and determine the problematic transaction that caused the lock-waiting state based on the comparison result; and a processing module 46, used to perform operations corresponding to the statement state of the problematic transaction on the problematic transaction to complete the processing of lock-waiting transactions in the database.

[0062] The acquisition module 40 includes: a determination submodule; wherein the determination submodule is used to determine the transactions in the target lock waiting state from the sys.innodb_lock_waits system table in the database; determine the thread number of the transactions in the target lock waiting state from the performance_schema.threads system table in the database; and determine the transaction in the lock waiting state with the smallest thread number as the lock-holding transaction. The determination submodule includes: a determination unit; wherein the determination unit is used to determine the transactions in the lock waiting state from the sys.innodb_lock_waits system table in the database; and determine the transactions in the lock waiting state whose waiting time exceeds a second set time threshold as the transactions in the target lock waiting state.

[0063] The determination module 44 includes: a comparison submodule; wherein the comparison submodule is used to obtain a set number of transactions executed before the lock-holding transaction; in response to a text processing command, remove redundant statements from the set number of transactions; determine the set number of transactions from which redundant statements have been removed as the target transaction; compare the execution flow of the statements in the target transaction with the baseline flow, and determine the problem transaction that caused the lock waiting state based on the comparison result.

[0064] The processing module 46 includes a status submodule and a processing submodule. The status submodule is used to determine the state of the statement corresponding to the problematic transaction as "statement performance abnormal" when the number of modified lines of the statement corresponding to the problematic transaction is greater than a first set line count threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold; and to determine the state of the statement corresponding to the problematic transaction as "executed but not saved" when the number of modified lines of the statement corresponding to the problematic transaction is less than a second set line count threshold and the execution time of the statement corresponding to the problematic transaction is greater than the first set time threshold. The processing submodule is used to roll back the problematic transaction when the state of the statement corresponding to the problematic transaction is "statement performance abnormal" and to save the problematic transaction when the state of the statement corresponding to the problematic transaction is "executed but not saved".

[0065] According to some embodiments of this application, taking a MySQL database as an example, this application also proposes a MySQL row-level locking tool, such as... Figure 5 As shown, it includes: a MySQL client, a data collection module, a text analysis module, and a timer. The data collection module searches for lock-waiting transactions in the system tables of the system database within the MySQL database. The text analysis module processes SQL statements to extract key information. The timer sets the search interval. This implements the aforementioned method for handling lock-waiting transactions.

[0066] It should be noted that the method and apparatus for handling lock waiting transactions in a database disclosed in this application can be used in the field of bank data center data processing in the financial sector, and can also be used in any field other than the financial sector for attention warning. The application field of the method and apparatus for handling lock waiting transactions in a database disclosed in this application is not limited.

[0067] This application also provides a non-volatile storage medium, which includes a stored program, wherein the program controls the device where the non-volatile storage medium is located to execute the above-described database lock wait transaction processing method during runtime.

[0068] An electronic device is provided according to an embodiment of this application, including: a processor and a memory; the processor is used to run a program, wherein the program executes the above-described database lock wait transaction processing method during runtime.

[0069] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0070] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0071] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0072] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0073] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

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

[0075] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for handling lock-waiting transactions in a database, characterized in that, include: Identify the lock-holding transactions in the database that are in a lock-waiting state; Determine the target transaction to be executed before the lock-holding transaction; The execution flow of the statements in the target transaction is compared with the baseline flow, and the problematic transaction that caused the lock wait state is determined based on the comparison result. Based on the statement state corresponding to the problematic transaction, perform the operation corresponding to the statement state to complete the handling of lock-waiting transactions in the database: Before performing the operation corresponding to the statement state of the problematic transaction based on the statement state of the problematic transaction, the method further includes: determining the statement state corresponding to the problematic transaction, wherein: If the number of modified rows of the statement corresponding to the problematic transaction is greater than a first set row number threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold, the state of the statement corresponding to the problematic transaction is determined to be a statement performance abnormality. If the number of modified rows of the statement corresponding to the problematic transaction is less than the second set row number threshold and the execution time of the statement corresponding to the problematic transaction is greater than the first set time threshold, the state of the statement corresponding to the problematic transaction is determined to be executed but not saved. If the statement corresponding to the problematic transaction is in a state of abnormal statement performance, the problematic transaction will be rolled back; if the statement corresponding to the problematic transaction is in a state of executed but not saved, the problematic transaction will be saved.

2. The method according to claim 1, characterized in that, The step of comparing the execution flow of statements in the target transaction with the baseline flow, and determining the problematic transaction causing the lock wait state based on the comparison result, includes: Obtain the set number of transactions executed prior to the lock-holding transaction; In response to a text processing command, redundant statements in the set number of transactions are removed; The set number of transactions, after removing redundant statements, are determined as the target transactions; The execution flow of the statements in the target transaction is compared with the baseline flow, and the problematic transaction that caused the lock wait state is determined based on the comparison result.

3. The method according to claim 2, characterized in that, The text processing commands include: The database system contains built-in text processing commands, including text filters, stream editors, and report generators.

4. The method according to claim 1, characterized in that, Determine the holding transaction, including: The transaction in the target lock waiting state is determined from the first system table in the database; wherein, the first system table is used to record the thread number of the transaction in the lock waiting state; The thread number of the transaction in the target lock waiting state is determined from the second system table in the database; wherein, the second system table is used to record the connection number of the transaction; The transaction with the smallest thread number that is in a lock-waiting state is identified as the lock-holding transaction.

5. The method according to claim 1, characterized in that, Transactions that determine the target lock wait state include: Identify the transactions in a lock-waiting state from the first system table; Transactions in the lock-waiting state whose waiting time exceeds a second set time threshold are identified as transactions in the target lock-waiting state.

6. A lock-waiting transaction processing device in a database, characterized in that, include: The acquisition module is used to identify lock-holding transactions in the database that are in a lock-waiting state. The extraction module is used to determine the target transaction executed before the lock-holding transaction; The determination module is used to compare the execution flow of the statements in the target transaction with the baseline flow, and determine the problematic transaction that caused the lock waiting state based on the comparison result; The processing module is used to perform operations corresponding to the statement state of the problematic transaction to complete the processing of lock waiting transactions in the database. Before performing the operation corresponding to the statement state of the problematic transaction based on the statement state of the problematic transaction, the method further includes: determining the statement state corresponding to the problematic transaction, wherein: If the number of modified rows of the statement corresponding to the problematic transaction is greater than a first set row number threshold and the execution time of the statement corresponding to the problematic transaction is greater than a first set time threshold, the state of the statement corresponding to the problematic transaction is determined to be a statement performance abnormality. If the number of modified rows of the statement corresponding to the problematic transaction is less than the second set row number threshold and the execution time of the statement corresponding to the problematic transaction is greater than the first set time threshold, the state of the statement corresponding to the problematic transaction is determined to be executed but not saved. If the statement corresponding to the problematic transaction is in a state of abnormal statement performance, the problematic transaction will be rolled back; if the statement corresponding to the problematic transaction is in a state of executed but not saved, the problematic transaction will be saved.

7. A non-volatile storage medium, characterized in that, The non-volatile storage medium includes a stored program, wherein, when the program is executed, it controls the device where the non-volatile storage medium is located to execute the lock-wait transaction processing method in the database according to any one of claims 1 to 5.

8. An electronic device, characterized in that, include: Processor and memory; The processor is used to run a program, wherein the program executes the database lock wait transaction processing method according to any one of claims 1 to 5.

Images