A database instance lock conflict management system and method

Abstract

This application provides a database instance lock conflict management system and method. The database instance lock conflict management system includes: an audit diagnosis module, a transaction information analysis module, a transaction intelligent analysis module, and a solution provision module; the transaction information analysis module is used to obtain the identification information of the abnormal transaction and the abnormal SQL statement execution information when an SQL statement execution exception occurs, based on the abnormal execution scenario information; and to determine the level of the impact scope of the SQL statement execution exception based on the abnormal execution scenario information; the transaction intelligent analysis module is used to determine the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction and the abnormal SQL statement execution information; the solution provision module is used to generate a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the level of the impact scope.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a database instance lock conflict management system and method. Background Technology

[0002] In database systems, to ensure data consistency, when a transaction modifies certain data, the database system locks that data to prevent other transactions from modifying it until the first transaction completes its modification and releases the lock. When multiple database transactions execute concurrently and modify the same data, lock contention is a significant problem that can occur.

[0003] While various solutions exist for database instance lock conflicts, they only provide monitoring and early warning functions and cannot quickly locate and resolve problems online. Subsequent solutions rely on technical personnel. Due to the diversity, complexity, and hidden nature of lock conflict scenarios, technical personnel need a high level of expertise. Furthermore, lock conflict issues need to be resolved promptly; otherwise, it could cause a sudden collapse of the database instance environment. Relying solely on technical personnel makes it difficult to resolve problems in a timely manner.

[0004] Therefore, how to accurately locate and resolve lock conflicts in database instances online in a timely manner is a technical problem that urgently needs to be solved. Summary of the Invention

[0005] This application provides a database instance lock conflict management system and method for accurately locating and resolving database instance lock conflicts online and in a timely manner.

[0006] One embodiment of this application provides a database instance lock conflict management system, characterized in that the system includes an audit diagnosis module, a transaction information analysis module, a transaction intelligent analysis module, and a solution providing module; the audit diagnosis module is used to obtain the status information of the SQL statement execution process of the database instance; and, based on predefined lock conflict characteristics, to obtain abnormal execution scenario information when the SQL statement execution is abnormal from the status information; the transaction information analysis module is used to obtain the identification information of the abnormal transaction and the abnormal SQL statement execution information when the SQL statement execution is abnormal based on the abnormal execution scenario information; wherein, the abnormal SQL statement execution information includes at least abnormal SQL statement execution record information, abnormal SQL statement execution time information, and thread information calling the abnormal SQL statement; and to determine the level of the impact scope of the SQL statement execution abnormality based on the abnormal execution scenario information; the transaction intelligent analysis module is used to determine the type and cause of the lock conflict that caused the SQL statement execution abnormality based on the identification information of the abnormal transaction and the abnormal SQL statement execution information; the solution providing module is used to generate a solution for the SQL statement execution abnormality based on the type and cause of the lock conflict and the level of the impact scope.

[0007] In some embodiments, the transaction intelligent analysis module is further configured to: when it is impossible to determine the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, determine the type and cause of the lock conflict that caused the SQL statement execution exception based on the status information of the historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information.

[0008] In some embodiments, determining the type and cause of lock conflicts that cause the SQL statement execution exception based on the status information of the historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information includes: obtaining historical execution scenario information from the status information of the historical SQL statement execution process that has a similarity greater than a preset threshold to the abnormal execution scenario information; and using the type and cause of lock conflicts corresponding to the historical execution scenario information as the type and cause of lock conflicts that cause the SQL statement execution exception.

[0009] In some embodiments, the predefined lock conflict characteristics include waiting row lock characteristics, which include the innodb_row_lock_waits metric. The step of obtaining abnormal execution scenario information from the status information based on the predefined lock conflict characteristics when an SQL statement execution exception occurs includes: obtaining the SQL statement execution context information of the current database instance when the value of the innodb_row_lock_waits metric is greater than a preset threshold; and using the SQL statement execution context information as the abnormal execution scenario information.

[0010] In some embodiments, the predefined lock conflict features include deadlock features, which include the keywords LOCK HOLD, RECORD LOCKS, and LOCK REQUEST. The step of obtaining abnormal execution scenario information when an SQL statement execution exception occurs from the state information based on the predefined lock conflict features includes: if the keywords LOCK HOLD, RECORD LOCKS, and LOCK REQUEST are present in the state information, obtaining the context information of the SQL statement execution for the current database instance; and using the context information of the SQL statement execution as the abnormal execution scenario information.

[0011] In some embodiments, determining the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement includes: determining the type and cause of the lock conflict as follows: if, based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, multiple transactions are updating the same table and are mutually waiting for the release of write lock permissions held by each other for the same table, the SQL statements of the multiple transactions are mutually waiting for the release of write lock permissions held by each other for the same table, resulting in a deadlock.

[0012] In some embodiments, the lock conflict is a deadlock caused by transaction 1 and transaction 2 performing update operations on the same table; the impact range level includes a first level and a second level, where the impact range corresponding to the first level is smaller than that corresponding to the second level; generating a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the impact range level includes: if the impact range level is the first level, generating the following solution: rolling back the transaction corresponding to the identifier information of the exception transaction; replaying or having other SQL statements acquire the lock held by the SQL statement in the lock waiting state first, and rewriting the locking process; if the rollback operation fails, rewriting the SQL code commit logic of the transaction and replaying the transaction execution.

[0013] In some embodiments, generating a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the level of the impact scope includes: generating the following solution when the level of the impact scope is the second level:

[0014] Switch the routing for the application server to access the database node corresponding to the database instance from the original database management system to the backup database management system; take the original database management system offline; while offline, perform the following repair operations on the original database management system:

[0015] Roll back the SQL commit state of Transaction 1 and Transaction 2 to the initialization state; rewrite the SQL code commit logic of Transaction 1 and Transaction 2; and add locking priority to the updated field content of the same table; after the repair operation is completed, replay the SQL execution statements of the original database management system; bring the original database management system back online; and back up the original database management system to obtain a new standby database management system.

[0016] One embodiment of this application provides a solution to database instance lock conflicts, applied to a database instance lock conflict management system as described above. The method includes: obtaining state information of the SQL statement execution process of the database instance; and, based on predefined lock conflict characteristics, obtaining abnormal execution scenario information when the SQL statement execution is abnormal from the state information; obtaining the identifier information of the abnormal transaction and the abnormal SQL statement execution information when the SQL statement execution is abnormal based on the abnormal execution scenario information; wherein, the abnormal SQL statement execution information includes at least abnormal SQL statement execution record information, abnormal SQL statement execution time information, and thread information that called the abnormal SQL statement; determining the level of the impact scope of the SQL statement execution abnormality based on the abnormal execution scenario information; determining the type and cause of the lock conflict that caused the SQL statement execution abnormality based on the identifier information of the abnormal transaction and the abnormal SQL statement execution information; and generating a solution for the SQL statement execution abnormality based on the type and cause of the lock conflict and the level of the impact scope.

[0017] In some embodiments, the method further includes: when it is impossible to determine the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, the following operation is performed: based on the status information of the historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information, the type and cause of the lock conflict that caused the SQL statement execution exception are determined.

[0018] This application provides an electronic device, which includes a memory and a processor. The memory stores a computer program, and the processor executes the method described above when running the program.

[0019] This application provides a storage medium for storing a computer-readable program, which, when run, performs the method described above.

[0020] The technical solutions provided in this application have at least the following advantages compared with the prior art:

[0021] In the embodiments provided in this application, the transaction information analysis module obtains the identification information of the abnormal transaction and the abnormal SQL statement execution information when the SQL statement execution is abnormal, based on the abnormal execution scenario information obtained by the audit diagnosis module; and determines the level of the impact scope of the SQL statement execution abnormality based on the abnormal execution scenario information; the transaction intelligent analysis module determines the type and cause of the lock conflict that caused the SQL statement execution abnormality based on the identification information of the abnormal transaction and the abnormal SQL statement execution information; and the solution providing module generates a solution for the SQL statement execution abnormality based on the type and cause of the lock conflict and the level of the impact scope. This allows for accurate online location and timely resolution of lock conflict issues in database instances. Attached Figure Description

[0022] This application will be further described by way of exemplary embodiments, which will be described in detail with reference to the accompanying drawings. These embodiments are not limiting; in these embodiments, the same reference numerals denote the same structures, wherein:

[0023] Figure 1 This is an exemplary schematic diagram of a database instance lock conflict management system according to some embodiments of this application;

[0024] Figure 2 This is an exemplary flowchart of a database instance lock conflict management method according to some embodiments of this application;

[0025] Figure 3 This is an exemplary structural diagram of an electronic device according to some embodiments of this application. Detailed Implementation

[0026] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are merely some examples or embodiments of this application. For those skilled in the art, these drawings can be applied to other similar scenarios without creative effort. Unless obvious from the context or otherwise specified, the same reference numerals in the drawings represent the same structures or operations.

[0027] It should be understood that the terms “system,” “device,” “unit,” and / or “module” used herein are one method of distinguishing different components, elements, parts, sections, or assemblies at different levels. However, if other words can achieve the same purpose, they may be replaced by other expressions.

[0028] As indicated in this application and claims, unless the context clearly indicates otherwise, the words "a," "an," "an," and / or "the" do not specifically refer to the singular and may also include the plural. Generally speaking, the terms "comprising" and "including" only indicate the inclusion of explicitly identified steps and elements, which do not constitute an exclusive list, and the method or apparatus may also include other steps or elements.

[0029] Flowcharts are used in this application to illustrate the operations performed by the system according to embodiments of this application. It should be understood that the preceding or following operations are not necessarily performed precisely in sequence. Instead, the steps can be processed in reverse order or simultaneously. Furthermore, other operations can be added to these processes, or one or more steps can be removed from them.

[0030] For ease of understanding, the technical solution of this application is described below with reference to the accompanying drawings and embodiments.

[0031] Figure 1 This is an exemplary schematic diagram of a database instance lock conflict management system according to some embodiments of this application.

[0032] like Figure 1 As shown, the database instance lock conflict management system connects to the database management system (DBMS) to monitor and resolve lock conflict issues within the DBMS. Application servers (e.g., servers used by various e-commerce platforms) can operate on databases 1 through 2020 through the DBMS. The DBMS can include, but is not limited to, various open-source or paid relational database management systems such as MySQL and Oracle. For illustrative purposes only, this application uses MySQL as the DBMS in the provided embodiments to illustrate the database instance lock conflict management system.

[0033] like Figure 1 As shown, the database instance lock conflict management system includes an audit diagnosis module, a transaction information analysis module, a transaction intelligent analysis module, and a solution provision module.

[0034] The audit diagnostic module is used to obtain the status information of the SQL (Structured Query Language) statement execution process of the database instance; and to obtain the abnormal execution scenario information when the SQL statement execution is abnormal from the status information based on predefined lock conflict characteristics.

[0035] A database instance is a layer of data management software located between the user and the operating system, serving as the channel for accessing the database. All operations performed by the user on the data in the database, including data definition, data query, data maintenance, and database operation control, are carried out under the database instance. Applications can only interact with the database through the database instance.

[0036] In practice, the audit diagnostic module can monitor the status information of SQL statement execution processes across all database instances, including but not limited to: SQL statements, transactions, stored procedures, and views. From the monitored status information of all SQL statement execution processes, the audit diagnostic module filters and records in detail the execution status of SQL statements with abnormal transactions or non-transactional functions.

[0037] A transaction consists of one or more basic operations, or one or more SQL statements. The most significant characteristic of a transaction is that all the SQL statements it contains are committed to the database as a whole. The entire transaction succeeds only when all the SQL statements have been executed; if any SQL statement fails, the entire transaction fails.

[0038] In some embodiments, the types of lock conflicts may include: deadlock, waiting for row locks, table locks, read-only locks, DDL / SELECT / DML waiting for locks, and SQL waiting for MDL locks. Predefined lock conflict characteristics may include the characteristics of the various lock conflict types mentioned above.

[0039] In some embodiments, predefined lock conflict characteristics include row lock waiting characteristics, which include the `innodb_row_lock_waits` metric. The `innodb_row_lock_waits` metric represents the total number of times the system has waited since startup. In specific implementations, if the value of the `innodb_row_lock_waits` metric exceeds a preset threshold, the execution context information of the SQL statement for the current database instance can be obtained; this SQL statement execution context information is used as information about abnormal execution scenarios. The SQL statement execution context information includes, but is not limited to, SQL statement information, transaction event information, and InnoDB status information. InnoDB is one of the database engines of MySQL. In specific implementations, if other database management systems are used, the status information is the status information of the database engine of those other database management systems and is not limited to the descriptions in this specification.

[0040] In some embodiments, predefined lock conflict characteristics include deadlock characteristics, which include the keywords LOCKHOLD (lock holding), RECORD LOCKS (record locks), and LOCK REQUEST (lock request). In specific implementations, if the keywords LOCKHOLD, RECORD LOCKS, or LOCK REQUEST are present in the status information, the execution context information of the SQL statement in the current database instance can be obtained; this SQL statement execution context information is then used as information about abnormal execution scenarios.

[0041] The transaction information analysis module is used to obtain the identification information of the abnormal transaction and the execution information of the abnormal SQL statement when the SQL statement execution is abnormal, based on the abnormal execution scenario information. Among them, the abnormal SQL statement execution information includes at least the abnormal SQL statement execution record information, the abnormal SQL statement execution time information, and the thread information that called the abnormal SQL statement.

[0042] As an example only, the abnormal execution scenario information obtained by the audit diagnosis module is as follows:

[0043] Transaction 1:

[0044] Host: 192.168.1.10

[0045] Thread:15790086

[0046] SQL statement: insert into t_ac values(xxx,yyy)…

[0047] Status: Normal

[0048] LockRequest:RECORD LOCKS space id 61pag on 26077n bits 576index'uq_sd_sc_bi'of…

[0049] LockHold:

[0050] Transaction 2:

[0051] Host: 192.168.1.20

[0052] Thread:16790099

[0053] SQL statement: insert into t_ac values(xxx,yyy)…

[0054] Status: Rollback

[0055] LockRequest:RECORD LOCKS space id 61pag on 26086n bits 368index'uq_sd_sc_bi'of…

[0056] LockHold:RECORD LOCKS space id 61pag on 26077n bits 576index'uq_sd_sc_bi'of…

[0057] The transaction information analysis module analyzes the above abnormal execution scenario information and can obtain: transaction identification information "Transaction 1" and "Transaction 2"; abnormal SQL statement execution information such as "insert into t_acvalues(xxx,yyy)..." and "Thread:15790086".

[0058] In the specific implementation process, the transaction information analysis module can format the above-mentioned abnormal execution scenario information to obtain a detailed analysis report. This report can record detailed information such as the server information of the instance database node where the failure occurred, the region location, host name, IP address, thread number, SQL statement, execution time, transaction execution result, current table result status, and rollback status.

[0059] In some embodiments, in order to obtain a reasonable lock conflict solution in the future, the transaction information analysis module is also used to determine the level of the impact range of the SQL statement execution exception based on the exception execution scenario information.

[0060] In some embodiments, the scope of influence is classified into a first level and a second level, wherein the scope of influence corresponding to the first level is smaller than the scope of influence corresponding to the second level.

[0061] The transaction intelligent analysis module is used to determine the type and cause of lock conflicts that cause SQL statement execution exceptions based on the identification information of abnormal transactions and the execution information of abnormal SQL statements.

[0062] As an example only, the abnormal transaction intelligent analysis module, based on the identification information of abnormal transactions and the execution information of abnormal SQL statements extracted from the abnormal execution scenario information in the above example, determines that when multiple transactions' SQL statements update the same table and are waiting for each other to release their write lock permissions on the same table, it can determine the type and cause of the lock conflict: multiple transactions' SQL statements are waiting for each other to release their write lock permissions on the same table, causing a deadlock.

[0063] In some embodiments, lock conflicts are more complex, and the abnormal transaction intelligent analysis module cannot determine the type and cause of lock conflicts that cause SQL statement execution exceptions based on the identification information of abnormal transactions and the execution information of abnormal SQL statements. In this case, the type and cause of lock conflicts that cause SQL statement execution exceptions can be determined based on the status information of the historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information.

[0064] In practical implementation, historical execution scenario information with a similarity greater than a preset threshold to the abnormal execution scenario information can be obtained from the status information of historical SQL statement execution. The type and cause of lock conflicts corresponding to the historical execution scenario information are used as the type and cause of lock conflicts that caused the SQL statement execution abnormality. For example, the status information of all SQL statement execution processes of the current database instance in the past three years can be used as the status information of historical SQL statement execution processes.

[0065] In the embodiments provided in this application, historical execution scenario information with a similarity greater than a preset threshold to abnormal execution scenario information is obtained from the status information of the historical SQL statement execution process of the database instance; the type and cause of lock conflict corresponding to the historical execution scenario information are used as the type and cause of lock conflict that causes SQL statement execution abnormality, thereby determining the type and cause of lock conflict that causes SQL statement execution abnormality without any blind spots, and effectively maintaining the stable operation of the database management system.

[0066] The solution provides modules for generating solutions to SQL statement execution exceptions based on the type and cause of lock conflicts, as well as the level of impact.

[0067] In some embodiments, the lock conflict is a deadlock, which is caused by transaction 1 and transaction 2 performing update operations on the same table.

[0068] As an example only, for the abnormal execution scenario information in the above example, after extraction by the transaction information analysis module, the identification information of the abnormal transaction and the execution information of the abnormal SQL statement at the time of the SQL statement execution error are obtained. Based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, the transaction intelligent analysis module analyzes and finds the following reasons for the lock conflict: Transaction 2's SQL statement executes an insert to update table content. The update process requires acquiring write lock permissions for the inserted fields. However, at this time, Transaction 1's SQL statement is already updating but has not yet completed, and the write lock permissions have not been released. Therefore, the execution status of Transaction 2's SQL statement is that the write request needs to wait. However, Transaction 1's insert statement still needs to request write permissions for some data in the fields it is about to update. Transaction 2's insert statement needs to release the write lock before it can successfully acquire write permissions. This mutual waiting process results in a deadlock problem.

[0069] In the specific implementation process, when the impact level is the first level, the solution providing module can generate the following solutions: roll back the transaction corresponding to the identification information of the abnormal transaction; replay or make other SQL statements acquire the lock held by the SQL statement in the lock waiting state first, and rewrite the locking process; if the rollback operation fails, rewrite the SQL code commit logic of the transaction and replay the transaction execution.

[0070] In the specific implementation process, by Figure 1 The document provides specific implementation solutions for modules in the database management system, such as the transaction commit module and the SQL execution module, and offers solutions for module generation. For example, the transaction commit module performs rollback operations on transactions, and the SQL execution module executes SQL statements based on the rewritten transaction SQL code and commit logic.

[0071] In the specific implementation process, when the impact level is level two, the following solution is generated:

[0072] Switch the routing for the application server to access the database node corresponding to the database instance from the original database management system to the backup database management system; take the original database management system offline; while offline, perform the following repair operations on the original database management system:

[0073] Roll back the SQL commit state of Transaction 1 and Transaction 2 to the initialization state; rewrite the SQL code commit logic of Transaction 1 and Transaction 2; and add locking priority to the updated field content of the same table; after the repair operation is completed, replay the SQL execution statements of the original database management system; bring the original database management system back online; and back up the original database management system to obtain a new standby database management system.

[0074] In the specific implementation process, such as Figure 1 The backup database management system shown is identical to the database management system. During the repair operation described above, the routing of the application server to the database node corresponding to the database instance is switched to the backup database management system. After the repair operation is completed, the connection between the application server and the database management system is restored.

[0075] In the embodiments provided in this application, an audit diagnosis module is used to detect various lock conflicts of a predefined database instance; a transaction information analysis module is used to extract the identification information of abnormal transactions and the execution information of abnormal SQL statements from the abnormal execution scenario information; a transaction intelligent analysis module is used to determine the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction, the execution information of the abnormal SQL statement, and the historical SQL statement execution information; and a solution provision module is used to generate a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the level of its impact. This enables comprehensive detection of various lock conflict problems in the database instance, timely and accurate resolution of lock conflict problems, and effective maintenance of the stable operation of the database management system.

[0076] Figure 2 This is an exemplary flowchart of a database instance lock conflict management method according to some embodiments of this application. This method is applied to the database instance lock conflict management system provided in the embodiments of this application, such as... Figure 2 As shown, the method includes the following steps.

[0077] In step S210, the status information of the SQL statement execution process of the database instance is obtained; and based on the predefined lock conflict characteristics, the abnormal execution scenario information when the SQL statement execution is abnormal is obtained from the status information.

[0078] In step S220, based on the abnormal execution scenario information, the identification information of the abnormal transaction and the abnormal SQL statement execution information when the SQL statement execution is abnormal are obtained; and based on the abnormal execution scenario information, the level of the impact range of the SQL statement execution abnormality is determined.

[0079] The abnormal SQL statement execution information includes at least the abnormal SQL statement execution record information, the abnormal SQL statement execution time information, and the thread information that called the abnormal SQL statement.

[0080] In step S230, the type and cause of the lock conflict that caused the SQL statement execution exception are determined based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement.

[0081] In some embodiments, if the type and cause of the lock conflict that caused the SQL statement execution exception cannot be determined based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, the following operations are performed:

[0082] Based on the status information of the historical SQL statement execution process of the database instance within a preset time period, and the abnormal execution scenario information, determine the type and cause of lock conflicts that cause SQL statement execution exceptions.

[0083] In step S240, a solution to the SQL statement execution exception is generated based on the type and cause of the lock conflict and the level of its impact.

[0084] In some embodiments, determining the type and cause of lock conflicts that cause the SQL statement execution exception based on the status information of the historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information includes: obtaining historical execution scenario information from the status information of the historical SQL statement execution process that has a similarity greater than a preset threshold to the abnormal execution scenario information; and using the type and cause of lock conflicts corresponding to the historical execution scenario information as the type and cause of lock conflicts that cause the SQL statement execution exception.

[0085] In some embodiments, the predefined lock conflict characteristics include waiting row lock characteristics, which include the innodb_row_lock_waits metric. The step of obtaining abnormal execution scenario information from the status information based on the predefined lock conflict characteristics when an SQL statement execution exception occurs includes: obtaining the SQL statement execution context information of the current database instance when the value of the innodb_row_lock_waits metric is greater than a preset threshold; and using the SQL statement execution context information as the abnormal execution scenario information.

[0086] In some embodiments, the predefined lock conflict features include deadlock features, which include the keywords LOCK HOLD, RECORD LOCKS, and LOCK REQUEST. The step of obtaining abnormal execution scenario information when an SQL statement execution exception occurs from the state information based on the predefined lock conflict features includes: if the keywords LOCK HOLD, RECORD LOCKS, and LOCK REQUEST are present in the state information, obtaining the context information of the SQL statement execution for the current database instance; and using the context information of the SQL statement execution as the abnormal execution scenario information.

[0087] In some embodiments, determining the type and cause of the lock conflict that caused the SQL statement execution exception based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement includes: determining the type and cause of the lock conflict as follows: if, based on the identification information of the abnormal transaction and the execution information of the abnormal SQL statement, multiple transactions are updating the same table and are mutually waiting for the release of write lock permissions held by each other for the same table, the SQL statements of the multiple transactions are mutually waiting for the release of write lock permissions held by each other for the same table, resulting in a deadlock.

[0088] In some embodiments, the lock conflict is a deadlock caused by transaction 1 and transaction 2 performing update operations on the same table; the impact range level includes a first level and a second level, where the impact range corresponding to the first level is smaller than that corresponding to the second level; generating a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the impact range level includes: if the impact range level is the first level, generating the following solution: rolling back the transaction corresponding to the identifier information of the exception transaction; replaying or having other SQL statements acquire the lock held by the SQL statement in the lock waiting state first, and rewriting the locking process; if the rollback operation fails, rewriting the SQL code commit logic of the transaction and replaying the transaction execution.

[0089] In some embodiments, generating a solution for the SQL statement execution exception based on the type and cause of the lock conflict and the level of the impact scope includes: generating the following solution when the level of the impact scope is the second level:

[0090] Switch the routing for the application server to access the database node corresponding to the database instance from the original database management system to the backup database management system; take the original database management system offline; while offline, perform the following repair operations on the original database management system:

[0091] Roll back the SQL commit state of Transaction 1 and Transaction 2 to the initialization state; rewrite the SQL code commit logic of Transaction 1 and Transaction 2; and add locking priority to the updated field content of the same table; after the repair operation is completed, replay the SQL execution statements of the original database management system; bring the original database management system back online; and back up the original database management system to obtain a new standby database management system.

[0092] In the embodiments of the above database instance lock conflict management method, the specific processing of each step and its resulting technical effects can be referred to the relevant descriptions in the corresponding module embodiments, which will not be repeated here.

[0093] Figure 3 This is an exemplary structural diagram of an electronic device according to some embodiments of this application.

[0094] like Figure 3 As shown, the electronic device includes: at least one processor 301, at least one communication interface 302, at least one memory 303, and at least one communication bus 304. Optionally, the communication interface 302 can be an interface for a communication module, such as the interface for a GSM module. The processor 301 may be a CPU, an Application Specific Integrated Circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention. The memory 303 may include high-speed RAM and may also include non-volatile memory, such as at least one disk storage device. The memory 303 stores a program, and the processor 301 calls the program stored in the memory 303 to execute some or all of the above-described method embodiments.

[0095] This application relates to a storage medium for storing a computer-readable program, which, when run, performs some or all of the above-described method embodiments.

[0096] Optionally, the storage medium may be a non-transitory computer-readable storage medium, such as a ROM, random access memory (RAM), CD-ROM, magnetic tape, floppy disk, and optical data storage device.

[0097] Based on the same inventive concept, this application also provides a computer program product, including a computer program that, when executed by a processor, implements some or all of the above-described method embodiments.

[0098] The basic concepts have been described above. Obviously, for those skilled in the art, the detailed disclosure above is merely illustrative and does not constitute a limitation of this application. Although not explicitly stated herein, those skilled in the art may make various modifications, improvements, and corrections to this application. Such modifications, improvements, and corrections are suggested in this application, and therefore remain within the spirit and scope of the exemplary embodiments of this application.

[0099] Furthermore, this application uses specific terms to describe its embodiments. For example, "an embodiment," "one embodiment," and / or "some embodiments" refer to a particular feature, structure, or characteristic related to at least one embodiment of this application. Therefore, it should be emphasized and noted that "an embodiment," "one embodiment," or "an alternative embodiment" mentioned twice or more in different locations in this application do not necessarily refer to the same embodiment. In addition, certain features, structures, or characteristics in one or more embodiments of this application can be appropriately combined.

[0100] Furthermore, unless expressly stated in the claims, the order of processing elements and sequences, the use of numbers and letters, or other names described in this application are not intended to limit the order of the processes and methods of this application. Although the foregoing disclosure has discussed some currently considered useful embodiments of the invention through various examples, it should be understood that such details are for illustrative purposes only, and the appended claims are not limited to the disclosed embodiments; rather, the claims are intended to cover all modifications and equivalent combinations that conform to the substance and scope of the embodiments of this application. For example, while the system components described above can be implemented using hardware devices, they can also be implemented solely through software solutions, such as installing the described system on existing servers or mobile devices.

[0101] Similarly, it should be noted that, in order to simplify the description of the present application and thus aid in the understanding of one or more embodiments of the invention, the foregoing description of the embodiments of the present application sometimes combines multiple features into a single embodiment, drawing, or description thereof. However, this disclosure method does not imply that the subject matter of the application requires more features than those mentioned in the claims. In fact, the embodiments contain fewer features than all the features of the single embodiments disclosed above.

[0102] In some embodiments, numbers describing the quantity of components and attributes are used. It should be understood that such numbers used in the description of embodiments are modified in some examples with the terms "approximately," "approximately," or "generally." Unless otherwise stated, "approximately," "approximately," or "generally" indicates that the numbers are allowed to vary by ±20%. Accordingly, in some embodiments, the numerical parameters used in the specification and claims are approximate values, which may be changed depending on the characteristics required by individual embodiments. In some embodiments, numerical parameters should take into account specified significant digits and employ a general method of digit reservation. Although the numerical ranges and parameters used to confirm their breadth of scope in some embodiments of this application are approximate values, in specific embodiments, such values ​​are set as precisely as feasible.

[0103] For each patent, patent application, patent application publication, and other material such as articles, books, specifications, publications, and documents referenced in this application, the entire contents of that patent are incorporated herein by reference. This excludes historical application documents that are inconsistent with or conflict with the content of this application, as well as documents that limit the broadest scope of the claims in this application (currently or subsequently appended to this application). It should be noted that if there are any inconsistencies or conflicts between the descriptions, definitions, and / or terminology used in the supplementary materials of this application and the content of this application, the descriptions, definitions, and / or terminology used in this application shall prevail.

[0104] Finally, it should be understood that the embodiments described in this application are merely illustrative of the principles of the embodiments of this application. Other modifications may also fall within the scope of this application. Therefore, alternative configurations of the embodiments of this application are considered as examples and not limitations, and are regarded as consistent with the teachings of this application. Accordingly, the embodiments of this application are not limited to the embodiments explicitly described and illustrated in this application.

Claims

1. A system for managing database instance lock conflicts, the system comprising: The system comprises an audit diagnosis module, a transaction information analysis module, a transaction intelligent analysis module and a solution providing module; The audit diagnosis module is configured to acquire state information of a SQL statement execution process of a database instance; and According to a predefined lock conflict feature, the state information is acquired to obtain abnormal execution scenario information when the SQL statement execution is abnormal; The transaction information analysis module is configured to acquire identification information of an abnormal transaction and abnormal SQL statement execution information when the SQL statement execution is abnormal according to the abnormal execution scenario information; wherein the abnormal SQL statement execution information at least comprises abnormal SQL statement execution record information, abnormal SQL statement execution time information and thread information of a calling abnormal SQL statement; and According to the abnormal execution scenario information, a level of an influence range of the SQL statement execution abnormality is determined; The transaction intelligent analysis module is configured to determine a type and a cause of a lock conflict causing the SQL statement execution abnormality according to the identification information of the abnormal transaction and the abnormal SQL statement execution information; The solution providing module is configured to generate a solution to the SQL statement execution abnormality according to the type and the cause of the lock conflict and the level of the influence range.

2. The system of claim 1, wherein, The transaction intelligent analysis module is further configured to: In a case where the type and the cause of the lock conflict causing the SQL statement execution abnormality cannot be determined according to the identification information of the abnormal transaction and the abnormal SQL statement execution information, the type and the cause of the lock conflict causing the SQL statement execution abnormality are determined according to state information of a historical SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information.

3. The system of claim 2, wherein, The determination of the type and the cause of the lock conflict causing the SQL statement execution abnormality according to the state information of the historical SQL statement execution process of the database instance within the preset time period and the abnormal execution scenario information comprises: From the state information of the historical SQL statement execution process, historical execution scenario information with a similarity greater than a preset threshold to the abnormal execution scenario information is acquired; The type and the cause of the lock conflict corresponding to the historical execution scenario information are taken as the type and the cause of the lock conflict causing the SQL statement execution abnormality.

4. The system of claim 1, wherein, The predefined lock conflict feature comprises a wait row lock feature, the wait row lock feature comprises an innodb_row_lock_waits index, and the acquisition of the abnormal execution scenario information when the SQL statement execution is abnormal from the state information according to the predefined lock conflict feature comprises: In a case where a value of the innodb_row_lock_waits index is greater than a preset threshold, context information of the SQL statement execution of the current database instance is acquired; The context information of the SQL statement execution is taken as the abnormal execution scenario information.

5. The system of claim 1, wherein, The predefined lock conflict feature includes a deadlock feature, the deadlock feature includes keywords LOCK HOLD, RECORD LOCKS, LOCK REQUEST, the predefined lock conflict feature is used to acquire the abnormal execution scenario information when the SQL statement execution is abnormal from the state information, including: In the case that the keywords LOCK HOLD, RECORD LOCKS, LOCK REQUEST exist in the state information, the context information of the SQL statement execution of the current database instance is acquired; The context information of the SQL statement execution is used as the abnormal execution scenario information.

6. The system of claim 1, wherein, The type and cause of the lock conflict causing the SQL statement execution abnormality are determined according to the identification information of the abnormal transaction and the abnormal SQL statement execution information, including: In the case that the SQL statements of the multiple transactions update the same table and wait for the release of the write lock of the same table held by the other party, the type and cause of the lock conflict are determined according to the identification information of the abnormal transaction and the abnormal SQL statement execution information: The multiple transaction SQL statements wait for the release of the write lock of the same table held by the other party, which causes a deadlock.

7. The system of claim 1, wherein, The type of the lock conflict is a deadlock, and the deadlock is caused by the update operation of transaction 1 and transaction 2 on the same table; The level of the influence range includes a first level and a second level, and the influence range corresponding to the first level is smaller than the influence range corresponding to the second level; The solution to the SQL statement execution abnormality is generated according to the type and cause of the lock conflict and the level of the influence range, including: In the case that the level of the influence range is the first level, the following solutions are generated: The transaction corresponding to the identification information of the abnormal transaction is rolled back; and Replay or make other SQL statements acquire the lock held by the SQL statement in the lock waiting state first, and rewrite the locking process; In the case that the rollback operation fails, the SQL code submission logic of the transaction is rewritten, and the transaction execution is replayed.

8. The system of claim 7, wherein, The solution to the SQL statement execution abnormality is generated according to the type and cause of the lock conflict and the level of the influence range, including: in the case that the level of the influence range is the second level, the following solutions are generated: Switch the routing of the application server accessing the database node corresponding to the database instance from the original database management system to the standby database management system; Offline the original database management system; In the offline state, the following repair operations are performed on the original database management system: Roll back the SQL submission state of the transaction 1 and the transaction 2 to the initialization state; Rewrite the SQL code submission logic of the transaction 1 and the transaction 2; and Lock the update field content of the same table with priority; After the repair operation is completed, the SQL execution statement of the original database management system is replayed; The original database management system is brought online again; and Re-backup the original database management system to obtain a new standby database management system.

9. A method for resolving database instance lock conflicts, the method comprising: The method is applied to the management system of database instance lock conflict as claimed in any one of claims 1-8, and comprises: obtaining state information of a SQL statement execution process of a database instance; and obtaining abnormal execution scenario information of the SQL statement execution from the state information according to a predefined lock conflict feature; obtaining identification information of an abnormal transaction and abnormal SQL statement execution information when the SQL statement execution is abnormal according to the abnormal execution scenario information; wherein the abnormal SQL statement execution information at least comprises abnormal SQL statement execution record information, abnormal SQL statement execution time information, and thread information of a calling abnormal SQL statement; and determining a level of an influence range of the SQL statement execution abnormality according to the abnormal execution scenario information; determining a type and cause of the lock conflict causing the SQL statement execution abnormality according to the identification information of the abnormal transaction and the abnormal SQL statement execution information; generating a solution to the SQL statement execution abnormality according to the type and cause of the lock conflict and the level of the influence range.

10. The method of claim 9, wherein, The method further comprises: in a case where the type and cause of the lock conflict causing the SQL statement execution abnormality cannot be determined according to the identification information of the abnormal transaction and the abnormal SQL statement execution information, performing the following operation: determining the type and cause of the lock conflict causing the SQL statement execution abnormality according to historical state information of a SQL statement execution process of the database instance within a preset time period and the abnormal execution scenario information. 11.An electronic device comprising a memory and a processor, the memory storing a computer program, and the processor executing the program to perform the method of claim 9 or 10. 12.A storage medium for storing a computer readable program, the computer readable program being executed to perform the method of claim 9 or 10.

Images