Database deadlock analysis method and device, storage medium and electronic equipment
By using the waiting graph algorithm and random forest supervision learning method in the database, deadlock SQL statements are extracted and prediction models are built, the advance prediction and prevention of deadlock problems are solved, and the database performance and stability are improved.
Patent Information
- Application Number
- CN202411277531.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-12
- Publication Date
- 2024-12-13
AI Technical Summary
When multiple users access the same database at the same time, deadlock problems occur frequently, resulting in the impact of system performance and stability. The existing solutions rely on manual intervention, which is not efficient and cannot fully prevent deadlocks.
By acquiring the real-time and historical data of the database, extracting deadlock SQL statements as training data, using the waiting graph algorithm to detect deadlocks and combining the random forest supervision learning method to build a deadlock prediction model, predict and prevent deadlocks from occurring in advance, and generate deadlock analysis Report and determine the optimal dismissal strategy.
It realizes advance prediction and effectively prevents deadlocks, improves database performance, reduces manual intervention and processing time, and improves system stability.
Smart Images
Figure CN119127518A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of databases, and in particular to a database deadlock analysis method and device, a storage medium, and an electronic device. Background Art
[0002] With the surge in data volume and the widespread popularity of database applications, the concurrency control of database systems has become increasingly complex. When multiple users access the same database at the same time, deadlock problems frequently occur, seriously affecting the performance and stability of the system. Although existing database management systems have certain deadlock detection and resolution mechanisms, these mechanisms often intervene only after deadlock occurs, and the processing methods are simple and crude, which may cause abnormal transaction termination and data inconsistency.
[0003] Existing solutions include setting timeout mechanisms, transaction isolation levels, etc., but these methods often rely on manual intervention, are inefficient, or cannot fully prevent deadlocks. Therefore, how to predict and effectively prevent deadlocks in advance has become a key issue in improving database performance. Summary of the invention
[0004] In view of this, an embodiment of the present invention provides a database deadlock analysis method and device, a storage medium, and an electronic device to achieve early prediction and effective prevention of deadlock, thereby improving database performance.
[0005] To achieve the above objectives, the embodiments of the present invention provide the following technical solutions:
[0006] A first aspect of an embodiment of the present invention discloses a deadlock analysis method for a database, the method comprising:
[0007] In response to an analysis start instruction for a target database, real-time database information of the target database is acquired to obtain real-time data, and past database information of the target database is acquired to obtain historical data;
[0008] Extracting the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and extracting the SQL statements to be executed from the real-time data as data to be analyzed;
[0009] Executing a deadlock detection strategy based on the data to be analyzed, and executing a deadlock prediction strategy based on the data to be analyzed and the training data;
[0010] The execution process of the deadlock detection strategy includes:
[0011] Generate a resource waiting graph based on a waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generate a reminder message indicating that a deadlock currently exists in the target database;
[0012] The execution process of the deadlock prediction strategy includes:
[0013] Based on the random forest supervised learning method and the training data, a deadlock prediction model is obtained by training and persistence processing to obtain a corresponding model file;
[0014] Prediction processing is performed based on the model file and the data to be analyzed to obtain a prediction result; the prediction result represents each transaction in the target database that has a risk of deadlock.
[0015] Preferably, after generating a reminder message indicating that a deadlock currently exists in the target database, the method further comprises:
[0016] A lock dependency graph is generated based on the resource waiting graph, and the lock dependency graph is traversed and calculated to obtain a deadlock chain; the deadlock chain represents the transaction information of each transaction involved in the deadlock and the resource information held by it;
[0017] A deadlock analysis report is generated based on the transaction information and the resource information and displayed.
[0018] Preferably, the method further comprises:
[0019] Determining an optimal deadlock resolution strategy based on an adaptive decision algorithm, the transaction information, and the resource information;
[0020] The deadlock currently existing in the target database is released based on the deadlock release strategy.
[0021] Preferably, the deadlock prediction model is obtained by training based on the random forest supervised learning method and the training data and is persisted to obtain a corresponding model file, including:
[0022] Extracting features corresponding to each transaction from the training data to obtain training value features and training trend features;
[0023] Performing feature fusion on the training numerical features and the training trend features respectively, and constructing a training set based on the fused training numerical features and the fused training trend features;
[0024] Based on the training set and the random forest supervised learning method, a deadlock prediction model is trained and persisted to obtain a corresponding model file.
[0025] Preferably, performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result includes:
[0026] Extracting features corresponding to each transaction from the data to be analyzed, and obtaining numerical features to be analyzed and trend features to be analyzed;
[0027] Performing feature fusion on the numerical features to be analyzed and the trend features to be analyzed respectively, to obtain the fused numerical features to be analyzed and the fused trend features to be analyzed;
[0028] Comparing the fused trend to be analyzed with the fused training trend features;
[0029] For each transaction whose fused trend to be analyzed does not match the fused training trend feature, determine whether the fused numerical feature to be analyzed corresponding to the transaction matches the fused training numerical feature; if they do, generate a prediction result indicating that the transaction has a deadlock risk;
[0030] For each transaction whose merged trend to be analyzed matches the merged training trend feature, a prediction result indicating that the transaction has a deadlock risk is generated.
[0031] A second aspect of an embodiment of the present invention discloses a deadlock analysis device for a database, the device comprising:
[0032] A response unit, configured to respond to an analysis start instruction for a target database, obtain real-time database information of the target database to obtain real-time data, and obtain past database information of the target database to obtain historical data;
[0033] An extraction unit, used to extract the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and to extract the SQL statements to be executed from the real-time data as data to be analyzed;
[0034] A detection unit, configured to execute a deadlock detection strategy based on the data to be analyzed, wherein the execution process of the deadlock detection strategy includes: generating a resource waiting graph based on a waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generating a reminder message indicating that a deadlock currently exists in the target database;
[0035] A prediction unit is used to execute a deadlock prediction strategy based on the data to be analyzed and the training data; the execution process of the deadlock prediction strategy includes: training a deadlock prediction model based on a random forest supervised learning method and the training data and performing persistence processing to obtain a corresponding model file; performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result; the prediction result represents each transaction in the target database that has a risk of deadlock.
[0036] Preferably, the device further comprises:
[0037] A report generation unit is used to generate a lock dependency graph based on the resource waiting graph after generating a reminder message indicating that a deadlock currently exists in the target database, and traverse and calculate the lock dependency graph to obtain a deadlock chain; the deadlock chain represents the transaction information of each transaction involved in the deadlock and the resource information held by it; a deadlock analysis report is generated based on the transaction information and the resource information and displayed.
[0038] Preferably, the device further comprises:
[0039] The deadlock release unit is used to determine the optimal deadlock release strategy based on the adaptive decision algorithm, the transaction information and the resource information; and release the deadlock currently existing in the target database based on the deadlock release strategy.
[0040] A third aspect of an embodiment of the present invention discloses a storage medium for storing a computer program. When the computer program is executed, it is specifically used to implement the database deadlock analysis method described in any one of the first aspects of the embodiments of the present invention.
[0041] A fourth aspect of an embodiment of the present invention discloses an electronic device, including: a memory and a processor;
[0042] The memory is used to store computer programs;
[0043] The processor is used to execute the computer program, specifically to implement the database deadlock analysis method described in any one of the first aspects of the embodiments of the present invention.
[0044] Based on the above-mentioned embodiments of the present invention, a database deadlock analysis method and device, storage medium, and electronic device are provided. In response to an analysis start instruction for a target database, real-time database information of the target database is obtained to obtain real-time data, and past database information of the target database is obtained to obtain historical data; deadlock SQL statements that cause deadlock are extracted from the real-time data and the historical data as training data, and SQL statements to be executed are extracted from the real-time data as data to be analyzed; a deadlock detection strategy is executed based on the data to be analyzed, and a deadlock prediction strategy is executed based on the data to be analyzed and the training data; the execution process of the deadlock detection strategy includes: generating a resource waiting graph based on a waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generating a reminder message indicating that the target database currently has a deadlock; the execution process of the deadlock prediction strategy includes: training based on a random forest supervised learning method and the training data to obtain a deadlock prediction model and performing persistence processing to obtain a corresponding model file; performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result; the prediction result indicates that each transaction in the target database has a risk of deadlock. In this solution, on the one hand, the waiting graph algorithm is used to deeply analyze the data to be analyzed and output an analysis report. On the other hand, a model file is obtained based on the random forest supervised learning method and historical data training, and an early warning is issued based on the model file and the data to be analyzed, so as to achieve early prediction and effectively prevent the occurrence of deadlocks, thereby improving database performance. BRIEF DESCRIPTION OF THE DRAWINGS
[0045] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.
[0046] Figure 1 A flowchart of a database deadlock analysis method disclosed in an embodiment of the present invention;
[0047] Figure 2 A structural diagram of a deadlock analysis device for a database disclosed in an embodiment of the present invention;
[0048] Figure 3 The present invention is a structural diagram of an electronic device disclosed in an embodiment of the present invention. DETAILED DESCRIPTION
[0049] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0050] In this application, the terms "comprises", "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element.
[0051] As can be seen from the background technology, existing solutions include setting timeout mechanisms, transaction isolation levels, etc., but these methods often rely on manual intervention, are inefficient, or cannot fully prevent deadlocks. Therefore, how to predict and effectively prevent deadlocks in advance has become a key issue in improving database performance.
[0052] Therefore, an embodiment of the present invention discloses a deadlock analysis method and device, a storage medium, and an electronic device for a database. In this solution, on the one hand, an analysis report is output based on an in-depth analysis of the data to be analyzed based on a waiting graph algorithm; on the other hand, a model file is obtained based on a random forest supervised learning method and historical data training, and an early warning is issued based on the model file and the data to be analyzed, so as to achieve early prediction and effectively prevent the occurrence of deadlock, thereby improving the performance of the database.
[0053] like Figure 1 FIG. 1 is a flowchart of a database deadlock analysis method disclosed in an embodiment of the present invention, comprising the following steps:
[0054] Step S101: in response to an analysis start instruction for a target database, real-time database information of the target database is acquired to obtain real-time data, and past database information of the target database is acquired to obtain historical data.
[0055] In step S101, the target database includes a relational database, and the database information includes: transaction information, lock information, SQL statements, write-ahead logs, and binary logs. The transaction information includes: transaction start and end time and locked resource information.
[0056] The specific process of obtaining database information is as follows: collecting database write-ahead logs and binary logs and other files through the agent, and collecting transaction information, deadlock information, and SQL statements by calling the relational database API interface.
[0057] In one embodiment, after the real-time data and historical data are obtained, they are pre-processed respectively.
[0058] Specifically, the preprocessing process includes: parsing the write-ahead log and binary log, converting the log content into SQL, further cleaning the data, removing duplicate data, noise data, and regularizing and normalizing the data. At the same time, during the preprocessing process, SQL statements and transaction information that are known not to cause deadlocks are removed, and only transaction information and SQL statements such as DML and DDL that are prone to deadlocks are retained.
[0059] A transaction is a logical unit of database operations, which contains multiple SQL statements. SQL statements are specific operation instructions, and transactions are a collection of these operations, which are used to ensure data consistency and integrity. For example, a transaction can contain multiple SQL statements (such as insert, update, and delete), and these SQL statements are part of the transaction. Transaction information includes transaction identifiers, transaction start and end times, and locked resources.
[0060] Step S102: extracting the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and extracting the SQL statements to be executed from the real-time data as data to be analyzed.
[0061] In step S102, before starting the deadlock analysis, the SQL statements that have been executed by the database are recorded in the binary log. The binary log file will be stored for more than 7 days. The binary log can parse the SQL statements executed in the history of each transaction, which is part of the historical data. The SQL statements in the historical data that may or have caused deadlocks are identified as training data.
[0062] The database information after deadlock analysis is enabled is called real-time data. SQL statements with execution results in the real-time data are identified as training data. SQL statements being executed or to be executed are identified as data to be analyzed.
[0063] Preferably, the training data and the data to be analyzed also include transaction information of transactions corresponding to each SQL statement, so as to facilitate subsequent feature extraction.
[0064] Step S103: executing a deadlock detection strategy based on the data to be analyzed, and executing a deadlock prediction strategy based on the data to be analyzed and the training data.
[0065] In step S103, a deadlock detection strategy may be performed first and then a deadlock prediction strategy may be performed, which can detect whether there are deadlocked transactions in the current target database and predict the risk of deadlock in the future for transactions that have not yet deadlocked. In the prior art, whether the current transaction has been deadlocked is not considered, and a prediction is performed directly, resulting in an inaccurate prediction conclusion.
[0066] In the specific implementation process of step S103, the deadlock detection strategy is executed based on the data to be analyzed as follows:
[0067] Based on the waiting graph algorithm and the data to be analyzed, a resource waiting graph is generated. If there is a loop in the resource waiting graph, a reminder message is generated indicating that a deadlock currently exists in the target database.
[0068] Among them, the wait graph algorithm is a technology used to detect database deadlocks. It constructs a graph to represent the waiting relationship between transactions and resources. The nodes in the graph represent transactions or resources, and the edges represent the request or holding relationship between transactions and resources. If there is a loop in the graph, it means that there is a deadlock. The resource wait graph is a graphical representation used to describe the waiting relationship between various resources (such as locks, database rows, tables, etc.) and transactions.
[0069] It can be understood that the data to be analyzed is further analyzed by a waiting graph algorithm in the graph theory method to generate a resource waiting graph. If a loop exists in the resource waiting graph, a reminder message indicating that a deadlock currently exists in the target database is generated.
[0070] Furthermore, a lock dependency graph is generated based on the resource waiting graph, and the lock dependency graph is traversed and calculated to obtain a deadlock chain. Based on the deadlock chain, the transaction information of each transaction involved in the deadlock and the resource information held by the transaction are represented and a deadlock analysis report is generated and displayed.
[0071] The Lock Dependency Graph displays the dependencies between transactions and locks to help identify deadlock situations.
[0072] It can be understood that if there is a loop in the resource waiting graph, a lock dependency graph is generated. By deeply traversing and calculating the lock dependency graph, the deadlock chain is accurately tracked and restored, and the details of each transaction involved in the deadlock and the resources they hold are clearly pointed out, thereby generating a detailed deadlock analysis report.
[0073] Furthermore, based on the adaptive decision algorithm and the deadlock chain characterizing the transaction information of each transaction involved in the deadlock and the resource information held by it, an optimal deadlock release strategy is determined, and the deadlock of each current transaction in the target database is released based on the deadlock release strategy.
[0074] It should be noted that deadlock resolution strategies refer to various methods and techniques used to resolve deadlocks that have already occurred. These strategies are designed to break the deadlock cycle waiting relationship. The main strategies include:
[0075] Terminate transactions: Terminate one or more transactions involved in a deadlock to free up resources.
[0076] Resource deprivation: Forcefully reclaim resources held by certain transactions and allocate these resources to other transactions.
[0077] Transaction rollback: restore the transaction to its previous safe state, cancel its request and holding of resources, and restart execution.
[0078] The adaptive decision algorithm automatically selects the best deadlock solution by considering multiple factors such as transaction priority, transaction execution stage, expected impact, etc. When a deadlock occurs, the transaction with the least transaction execution or the least impact on the database may be rolled back first to quickly break the deadlock cycle while minimizing the disruption to business continuity.
[0079] In addition, in order to ensure data consistency and integrity to the greatest extent, a deadlock recovery strategy is executed after the deadlock is resolved to ensure that the affected transactions can be resubmitted according to a certain logic after the deadlock is resolved.
[0080] In the specific implementation process of step S103, the deadlock prediction strategy is executed based on the data to be analyzed and the training data, including the following steps:
[0081] Step S201: Based on the random forest supervised learning method and training data, a deadlock prediction model is trained and persisted to obtain a corresponding model file.
[0082] In the specific implementation process of step S201, the features corresponding to each transaction are extracted from the training data to obtain training numerical features and training trend features; the training numerical features and training trend features are fused respectively, and a training set is constructed based on the fused training numerical features and the fused training trend features; a deadlock prediction model is trained based on the training set and the random forest supervised learning method and is persisted to obtain a corresponding model file.
[0083] It can be understood that based on the random forest supervised learning method, through the historical deadlock cases in the training set, it is possible to learn and identify the behavior patterns that lead to deadlocks.
[0084] The numerical features may be update, insert, table, lock, transaction information corresponding to the SQL statement, etc., and the training numerical features are the numerical features extracted from the training data.
[0085] The trend feature may be the execution time of the transaction corresponding to the SQL statement, and the training trend feature is the execution time of the transaction corresponding to the SQL statement extracted from the training data.
[0086] It should be noted that, for the training data, the features corresponding to each transaction are extracted to obtain the training numerical features and the training trend features. The training numerical features are fused through feature discretization, feature construction, feature selection, dimensionality reduction technology, etc., and the training trend features are fused through time series decomposition, etc., to form the fused training numerical features and the fused training trend features.
[0087] The persistence process includes: storing in a Vector Database to obtain the corresponding model file. A Vector Database is a database used to store and retrieve high-dimensional vector data. It is widely used in machine learning and data mining. These databases support efficient similarity search and approximate nearest neighbor search.
[0088] In the above-mentioned embodiment of the present invention, recursive feature elimination is used to select the most discriminative feature subset to facilitate the effective learning and analysis of the subsequent model, and the model file obtained after processing will be stored in the data warehouse for subsequent use.
[0089] Preferably, for multiple databases, there are multiple cloud database instances under one tenant. For data security, one tenant needs to train a corresponding deadlock prediction model.
[0090] Step S202: Perform prediction processing based on the model file and the data to be analyzed to obtain a prediction result.
[0091] The prediction results represent transactions in the target database that are at risk of deadlock.
[0092] Preferably, when a prediction result is obtained that a transaction has a high probability of being deadlocked, the prediction result is displayed visually, and a warning signal is sent to the database administrator or application, suggesting optimization of the transaction execution order or structural adjustment.
[0093] In the specific implementation process of step S202, first, the features corresponding to each transaction are extracted for the data to be analyzed to obtain the numerical features to be analyzed and the trend features to be analyzed; the numerical features to be analyzed and the trend features to be analyzed are respectively fused to obtain the fused numerical features to be analyzed and the fused trend features to be analyzed.
[0094] It can be understood that the above-mentioned feature extraction and fusion means are consistent with the above-mentioned training data feature extraction and fusion means, and they can be referenced to each other.
[0095] Then, the fused trend to be analyzed is compared with the fused training trend features; for each transaction whose fused trend to be analyzed does not match the fused training trend features, it is determined whether the fused numerical features to be analyzed corresponding to the transaction match the fused training numerical features. If they match, a prediction result is generated indicating that the transaction has a deadlock risk; for each transaction whose fused trend to be analyzed matches the fused training trend features, a prediction result is generated indicating that the transaction has a deadlock risk.
[0096] In the above-mentioned embodiment of the present invention, the trend features to be analyzed are compared with the training trend features in the model file in the order of trend features first and numerical features later, and it is determined whether the trend features to be analyzed are within the time range of the training trend features. If so, an interpretation is performed between the numerical features to be analyzed and the training numerical features. If they match, a prediction result is generated indicating that the corresponding transaction has a deadlock risk.
[0097] It should be noted that if the trend feature to be analyzed is within the time range of the training trend feature, it is determined that the trend feature to be analyzed does not match the training trend feature, and the execution time of the corresponding transaction is less than the time range of the training trend feature. Therefore, the risk of deadlock is low. Subsequent numerical feature comparison is performed to further determine whether the transaction has the risk of deadlock.
[0098] If it is not within the range of the training trend feature, the time range that the trend feature to be analyzed does not match is obtained, and the fault range is determined. The fault range is determined by judging whether the SQL statement locks the table. If so, the number of rows in the locked table is determined as the fault range, and a prediction result is generated that indicates that the corresponding transaction has a deadlock risk. At the same time, the numerical feature of other time ranges of the trend feature to be analyzed is interpreted. If the numerical feature matches, a prediction result is generated that indicates that the corresponding transaction has a deadlock risk.
[0099] Based on the above-mentioned embodiment of the present invention, a database deadlock analysis method is disclosed. In this scheme, on the one hand, an analysis report is output based on an in-depth analysis of the data to be analyzed based on a waiting graph algorithm. On the other hand, a model file is obtained based on a random forest supervised learning method and historical data training, and an early warning is issued based on the model file and the data to be analyzed, so as to achieve early prediction and effectively prevent the occurrence of deadlocks, thereby improving the performance of the database.
[0100] Corresponding to the deadlock analysis method of a database disclosed in the above embodiment of the present invention, as Figure 2 , which is a structural diagram of a database deadlock analysis device disclosed in an embodiment of the present invention, includes: a response unit 201, an extraction unit 202, a detection unit 203 and a prediction unit 204.
[0101] The response unit 201 is used to respond to the analysis start instruction for the target database, obtain the real-time database information of the target database to obtain real-time data, and obtain the past database information of the target database to obtain historical data;
[0102] An extraction unit 202 is used to extract the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and to extract the SQL statements to be executed from the real-time data as data to be analyzed;
[0103] The detection unit 203 is used to execute a deadlock detection strategy based on the data to be analyzed. The execution process of the deadlock detection strategy includes: generating a resource waiting graph based on the waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generating a reminder message indicating that a deadlock currently exists in the target database;
[0104] The prediction unit 204 is used to execute the deadlock prediction strategy based on the data to be analyzed and the training data; the execution process of the deadlock prediction strategy includes: training based on the random forest supervised learning method and the training data to obtain a deadlock prediction model and persisting it to obtain a corresponding model file; performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result; the prediction result represents each transaction in the target database that has a risk of deadlock.
[0105] Among them, the prediction unit 204 for obtaining a deadlock prediction model through training based on a random forest supervised learning method and training data and performing persistence processing to obtain a corresponding model file is specifically used for:
[0106] Extract the features corresponding to each transaction from the training data to obtain training value features and training trend features;
[0107] Perform feature fusion on the training numerical features and the training trend features respectively, and construct a training set based on the fused training numerical features and the fused training trend features;
[0108] Based on the training set and the random forest supervised learning method, the deadlock prediction model is trained and persisted to obtain the corresponding model file.
[0109] Among them, prediction processing is performed based on the model file and the data to be analyzed to obtain the prediction results, which are specifically used for:
[0110] Extract the features corresponding to each transaction from the data to be analyzed, and obtain the numerical features and trend features to be analyzed;
[0111] Perform feature fusion on the numerical features to be analyzed and the trend features to be analyzed respectively, and obtain the fused numerical features to be analyzed and the fused trend features to be analyzed;
[0112] Compare the fused trend to be analyzed with the fused training trend features;
[0113] For each transaction whose fused trend to be analyzed does not match the fused training trend feature, determine whether the fused numerical feature to be analyzed corresponding to the transaction matches the fused training numerical feature. If they do, generate a prediction result indicating that the transaction has a deadlock risk.
[0114] For each transaction whose fused trend to be analyzed matches the fused training trend feature, a prediction result is generated indicating that the transaction has a deadlock risk.
[0115] In one embodiment, the deadlock analysis device further includes:
[0116] The report generation unit is used to generate a lock dependency graph based on a resource wait graph after generating a reminder message indicating that a deadlock currently exists in a target database, and to traverse and calculate the lock dependency graph to obtain a deadlock chain; the deadlock chain represents the transaction information of each transaction involved in the deadlock and the resource information held by it; a deadlock analysis report is generated based on the transaction information and resource information and displayed.
[0117] In one embodiment, the deadlock analysis device further includes:
[0118] The deadlock release unit is used to determine the optimal deadlock release strategy based on the adaptive decision algorithm, transaction information and resource information; and release the deadlock currently existing in the target database based on the deadlock release strategy.
[0119] Based on a deadlock analysis device for a database disclosed in the above-mentioned embodiment of the present invention, in this solution, on the one hand, an analysis report is outputted based on an in-depth analysis of the data to be analyzed based on a waiting graph algorithm, and on the other hand, a model file is obtained based on a random forest supervised learning method and historical data training, and an early warning is issued based on the model file and the data to be analyzed, so as to achieve early prediction and effectively prevent the occurrence of deadlocks, thereby improving the performance of the database.
[0120] The present invention also provides an electronic device, please refer to Figure 3 , the electronic device includes a memory 301 and a processor 302.
[0121] Wherein, the memory 301 is used to store computer programs;
[0122] The processor 302 is used to execute a computer program, specifically to implement the database deadlock analysis method provided in any embodiment of the present application.
[0123] The present application also provides a computer storage medium for storing a computer program. When the computer program is executed, it is specifically used to implement the database deadlock analysis method provided in any embodiment of the present application.
[0124] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can refer to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system or system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiment. The system and system embodiments described above are merely schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without creative work.
[0125] Professionals may further appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.
[0126] The above description of the disclosed embodiments enables one skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to one skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A database deadlock analysis method, characterized in that: The method comprises: In response to an analysis start instruction for a target database, real-time database information of the target database is acquired to obtain real-time data, and past database information of the target database is acquired to obtain historical data; Extracting the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and extracting the SQL statements to be executed from the real-time data as data to be analyzed; Executing a deadlock detection strategy based on the data to be analyzed, and executing a deadlock prediction strategy based on the data to be analyzed and the training data; The execution process of the deadlock detection strategy includes: Generate a resource waiting graph based on a waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generate a reminder message indicating that a deadlock currently exists in the target database; The execution process of the deadlock prediction strategy includes: Based on the random forest supervised learning method and the training data, a deadlock prediction model is obtained by training and persistence processing to obtain a corresponding model file; Prediction processing is performed based on the model file and the data to be analyzed to obtain a prediction result; the prediction result represents each transaction in the target database that has a risk of deadlock.
2. The method according to claim 1, characterized in that After generating a reminder message indicating that a deadlock currently exists in the target database, the method further includes: A lock dependency graph is generated based on the resource waiting graph, and the lock dependency graph is traversed and calculated to obtain a deadlock chain; the deadlock chain represents the transaction information of each transaction involved in the deadlock and the resource information held by it; A deadlock analysis report is generated based on the transaction information and the resource information and displayed.
3. The method according to claim 2, characterized in that The method further comprises: Determining an optimal deadlock resolution strategy based on an adaptive decision algorithm, the transaction information, and the resource information; The deadlock currently existing in the target database is released based on the deadlock release strategy.
4. The method according to claim 1, characterized in that: The deadlock prediction model is obtained by training based on the random forest supervised learning method and the training data and is persisted to obtain a corresponding model file, including: Extracting features corresponding to each transaction from the training data to obtain training value features and training trend features; Performing feature fusion on the training numerical features and the training trend features respectively, and constructing a training set based on the fused training numerical features and the fused training trend features; Based on the training set and the random forest supervised learning method, a deadlock prediction model is trained and persisted to obtain a corresponding model file.
5. The method according to claim 4, characterized in that The performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result includes: Extracting features corresponding to each transaction from the data to be analyzed, and obtaining numerical features to be analyzed and trend features to be analyzed; Performing feature fusion on the numerical features to be analyzed and the trend features to be analyzed respectively, to obtain the fused numerical features to be analyzed and the fused trend features to be analyzed; Comparing the fused trend to be analyzed with the fused training trend features; For each transaction whose fused trend to be analyzed does not match the fused training trend feature, determine whether the fused numerical feature to be analyzed corresponding to the transaction matches the fused training numerical feature; if they do, generate a prediction result indicating that the transaction has a deadlock risk; For each transaction whose merged trend to be analyzed matches the merged training trend feature, a prediction result indicating that the transaction has a deadlock risk is generated.
6. A deadlock analysis device for a database, characterized in that: The device comprises: A response unit, configured to respond to an analysis start instruction for a target database, obtain real-time database information of the target database to obtain real-time data, and obtain past database information of the target database to obtain historical data; An extraction unit, used to extract the deadlock SQL statements that cause the deadlock from the real-time data and the historical data as training data, and to extract the SQL statements to be executed from the real-time data as data to be analyzed; A detection unit, configured to execute a deadlock detection strategy based on the data to be analyzed, wherein the execution process of the deadlock detection strategy includes: generating a resource waiting graph based on a waiting graph algorithm and the data to be analyzed, and if there is a loop in the resource waiting graph, generating a reminder message indicating that a deadlock currently exists in the target database; A prediction unit is used to execute a deadlock prediction strategy based on the data to be analyzed and the training data; the execution process of the deadlock prediction strategy includes: training a deadlock prediction model based on a random forest supervised learning method and the training data and performing persistence processing to obtain a corresponding model file; performing prediction processing based on the model file and the data to be analyzed to obtain a prediction result; the prediction result represents each transaction in the target database that has a risk of deadlock.
7. The device according to claim 6, characterized in that The device also includes: A report generation unit is used to generate a lock dependency graph based on the resource waiting graph after generating a reminder message indicating that a deadlock currently exists in the target database, and traverse and calculate the lock dependency graph to obtain a deadlock chain; the deadlock chain represents the transaction information of each transaction involved in the deadlock and the resource information held by it; a deadlock analysis report is generated based on the transaction information and the resource information and displayed.
8. The device according to claim 7, characterized in that The device also includes: The deadlock release unit is used to determine the optimal deadlock release strategy based on the adaptive decision algorithm, the transaction information and the resource information; and release the deadlock currently existing in the target database based on the deadlock release strategy.
9. A storage medium, characterized in that: Used to store a computer program, which, when executed, is specifically used to implement the database deadlock analysis method as described in any one of claims 1 to 5.
10. An electronic device, characterized in that: include: Memory and processor; The memory is used to store computer programs; The processor is used to execute the computer program, specifically to implement the database deadlock analysis method as described in any one of claims 1 to 5.