Intelligent operation and maintenance management method and platform for multi-node database

Abstract

The invention relates to the technical field of database operation and maintenance, and discloses an intelligent operation and maintenance management method and platform for a multi-node database, and the method comprises the steps: collecting historical transaction execution logs from the multi-node database to construct a transaction feature vector set, constructing a transaction conflict prediction model based on the transaction feature vector set, and predicting the conflict condition of real-time transactions; then forming a real-time transaction priority queue according to the conflict predicted value, dynamically adjusting the lock granularity of the real-time transaction, and determining the execution mode of the real-time transaction by combining the transaction priority and the lock granularity; meanwhile, the execution of real-time transactions is optimized by dynamically balancing node loads; according to the method, transaction conflicts in a multi-node database environment can be reduced, and the overall throughput of the database is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database operation and maintenance, and more specifically, to an intelligent operation and maintenance management method and platform for multi-node databases. Background Art

[0002] With the rapid development of information technology, distributed database systems have become an indispensable part of modern computing architectures. Multi-node databases can provide high availability and horizontal scalability, but when performing cross-node transaction processing between multiple nodes, they still face many challenges, especially transaction conflicts, resource competition, and lock management issues that occur during concurrent execution. These problems can not only lead to transaction rollbacks and performance degradation, but may also affect the overall efficiency of the database. Therefore, how to efficiently solve cross-node transaction conflicts in multi-node databases and optimize transaction scheduling and execution has become an important topic in the development of database technology.

[0003] Currently, there are some operation and maintenance and database optimization methods that attempt to solve these problems. For example, the patent application with the publication number CN109471720A proposes an online operation and maintenance system, in which a load monitoring center and a service factory work together to dynamically adjust the scale of service instances according to node load and service instance load, thereby improving the system's response ability and adaptability. However, this system mainly focuses on the automatic expansion and recycling of service instances and does not provide an effective solution for the intelligent prediction and optimization of cross-node transaction conflicts. Specifically, although this technical solution can optimize the use efficiency of system resources, it fails to predict and schedule the complexity of cross-node transaction concurrency, thus failing to effectively reduce the occurrence of transaction conflicts.

[0004] At the same time, the Chinese patent application with the authorization announcement number CN108536447B proposes an operation and maintenance management method that manages the relationship between operation and maintenance objects and task files through a preset hierarchical structure, thereby reducing the complexity of task file management during batch deployment. Although this solution simplifies the deployment of task files, its essence is still optimized through static task path management and does not consider dynamic conflict prediction and optimization scheduling during cross-node transaction execution. Therefore, this solution does not provide an effective solution for the conflict problems caused by transaction concurrency in multi-node databases. It still appears to be insufficiently flexible and intelligent when faced with the need to highly dynamically adjust the transaction execution order.

[0005] Although the prior art has made progress in some aspects, there are still obvious deficiencies in cross-node transaction management in multi-node databases, especially in transaction conflict prediction and scheduling optimization. Existing solutions mostly rely on static configuration and resource scheduling, and it is difficult to adapt to situations such as high concurrency, complex transaction types, and dynamic loads. Traditional lock management mechanisms often lack intelligent prediction, resulting in a high probability of resource waste and conflicts. Summary of the Invention

[0006] To overcome the above-mentioned defects of the prior art, the present invention provides an intelligent operation and maintenance management method and platform for multi-node databases. By analyzing historical transaction execution logs through a deep learning model, it predicts the conflict possibility of cross-node transactions, and dynamically adjusts the priority and lock granularity of transactions based on this prediction. This application can accurately identify high-conflict transactions, optimize transaction scheduling, reduce unnecessary resource contention, thereby greatly improving the execution efficiency of cross-node transactions and reducing transaction conflicts and rollback rates.

[0007] To achieve the above object, the present invention provides the following technical solutions:

[0008] An intelligent operation and maintenance management method for a multi-node database, including:

[0009] Collect historical transaction execution logs of each node from the multi-node database to construct a set of historical transaction feature vectors; construct a transaction conflict prediction model according to the set of historical transaction feature vectors; obtain the conflict prediction value of the real-time transaction based on the transaction conflict prediction model;

[0010] Form a real-time transaction priority queue based on the conflict prediction value, and dynamically adjust the lock granularity of the real-time transaction; determine the execution method of the real-time transaction in combination with the real-time transaction priority queue and the lock granularity of the real-time transaction;

[0011] Dynamically balance the node load, execute the real-time transaction according to the real-time transaction priority queue, the lock granularity of the real-time transaction and the execution method, and monitor the execution status of the real-time transaction.

[0012] An intelligent operation and maintenance management platform for a multi-node database, which is used to implement the above-mentioned intelligent operation and maintenance management method for a multi-node database. The platform includes: a conflict prediction module: used to collect the historical transaction execution logs of each node from the multi-node database, construct a set of historical transaction feature vectors; construct a transaction conflict prediction model according to the set of historical transaction feature vectors; obtain the conflict prediction value of a real-time transaction based on the transaction conflict prediction model; an execution policy generation module: based on the conflict prediction value, form a real-time transaction priority queue, and dynamically adjust the lock granularity of the real-time transaction; combine the real-time transaction priority queue and the lock granularity of the real-time transaction to determine the execution method of the real-time transaction; a load adjustment module: used to dynamically balance the node load; an execution monitoring module: execute the real-time transaction according to the real-time transaction priority queue, the lock granularity of the real-time transaction and the execution method, and monitor the execution status of the real-time transaction.

[0013] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0014] By predicting the conflict situation of real-time transactions and sorting the real-time transactions based on the conflict prediction value, the present invention can effectively avoid the impact of the execution of high-conflict transactions on system performance and reduce the probability of transaction conflicts. Dynamically adjust the lock granularity according to the characteristics of real-time transactions, using no lock or row-level lock for transactions involving a small amount of data, and using table-level lock for transactions involving a large amount of data, which can reduce the lock management overhead while ensuring concurrency. Combine different concurrency control strategies based on transaction priority and lock granularity, using optimistic concurrency control for high-priority transactions and pessimistic concurrency control for low-priority transactions, to further optimize the execution efficiency of transactions. By dynamically balancing the load of each node and migrating the hot data of high-load nodes to low-load nodes, the cluster resources can be fully utilized to avoid performance bottlenecks caused by uneven load. The whole method integrates multiple strategies such as analysis of historical transaction logs, prediction and optimization of real-time transactions, dynamic lock granularity adjustment, differential concurrency control, and load balancing, forming a complete set of intelligent operation and maintenance management solutions, which can optimize the operation efficiency of multi-node databases in all aspects. The method provided by the present invention can improve the transaction processing ability of multi-node databases, reduce the probability of transaction conflicts, give full play to the cluster performance, and thus greatly improve the overall throughput and response speed of the database system, with high practical value. Description of the Drawings

[0015] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0016] Figure 1 This is the principle flowchart of the intelligent operation and maintenance management method for multi-node databases in the present invention;

[0017] Figure 2 This is the method flowchart for constructing a historical transaction feature vector set in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0018] Figure 3 This is the method flowchart for obtaining the conflict prediction value of real-time transactions in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0019] Figure 4 This is the method flowchart for dividing the priority levels of real-time transactions in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0020] Figure 5 This is the method flowchart for dynamically adjusting the lock granularity of real-time transactions in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0021] Figure 6 This is the method flowchart for dividing real-time transactions into a high-priority transaction set and a low-priority transaction set in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0022] Figure 7 This is the method flowchart for dynamically balancing node loads in the intelligent operation and maintenance management method for multi-node databases of the present invention;

[0023] Figure 8 This is the functional module diagram of the intelligent operation and maintenance management platform for multi-node databases in the present invention. Detailed implementation manners

[0024] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0025] Embodiment 1

[0026] Please refer to Figure 1 As shown, this embodiment provides an intelligent operation and maintenance management method for multi-node databases, including:

[0027] Step S1000, collect the historical transaction execution logs of each node from a multi-node database, construct a set of historical transaction feature vectors; construct a transaction conflict prediction model based on the set of historical transaction feature vectors; obtain the conflict prediction value of a real-time transaction based on the transaction conflict prediction model;

[0028] Further, step S1000 includes:

[0029] Step S1100, collect the historical transaction execution logs of each node from a multi-node database, construct a set of historical transaction feature vectors;

[0030] Further, as Figure 2 shown, step S1100 includes:

[0031] Step S1110, collect the historical transaction execution logs of each node from a multi-node database;

[0032] Step S1120, parse the historical transaction execution logs, extract the historical transaction feature information related to transaction conflicts, construct historical transaction feature vectors, and form a set of historical transaction feature vectors.

[0033] Specifically, the historical transaction execution log refers to a log file that records the detailed information of the transactions that have been executed in the database. By analyzing these logs, key information such as the commit time of each historical transaction, which data tables have been accessed, which data rows in the tables are involved, the load conditions of each node when the transaction was executed, and the size of the transaction can be understood.

[0034] After collecting the historical transaction logs, it is necessary to parse the log content and extract the key features related to transaction conflict prediction. These features include:

[0035] Transaction type: Is it a query transaction, an insert transaction, an update transaction, or a delete transaction; for different types of transactions, the conflict possibilities are different. Data distribution: The data involved in the transaction is distributed on which nodes; cross-node transactions are more likely to conflict than transactions within a single node. Node load: When the transaction is executed, the usage of resources such as CPU and I / O of the node where it is located; transactions on nodes with high load are more likely to be blocked. Transaction commit time: Transactions with close commit times are more likely to compete for shared resources. Proportion of data involved: The proportion of the number of data rows accessed by the transaction to the total number of rows in the table where it is located; the larger the data proportion, the higher the probability of encountering lock conflicts.

[0036] Exemplarily, a certain historical transaction A is an update transaction (UPDATE). It is committed at time t1, and the data it operates on is distributed across node 1 and node 2. Among them, it involves 10% of the data rows in the customer table on node 1 and 20% of the data rows in the order table on node 2. When the transaction is committed, the CPU utilization rates of node 1 and node 2 are 70% and 65% respectively, and this transaction updates a total of 1000 data rows. Then the feature vector corresponding to transaction A can be expressed as: [t1, UPDATE, 10%, 20%, 70%, 65%, 1000]; Organizing the feature vectors of all these historical transactions in an orderly manner to form a feature vector set, a structured data representation reflecting the transaction conflict law is obtained, laying a data foundation for intelligent prediction. This representation digitizes and structures the originally chaotic transaction logs, enabling them to be directly input into a machine learning model for training. At the same time, through feature engineering, the key features that can best reflect transaction conflicts are selected, removing a large amount of redundant information and ensuring the relevance and effectiveness of the training data. Collecting a sufficient number of historical transaction sample data is crucial for training a high-quality transaction conflict prediction model. Therefore, the above steps need to continuously collect the latest transaction logs from each node, continuously expand the historical transaction feature set, and form a dynamically growing knowledge base. As the sample data becomes increasingly rich, the generalization performance of the transaction conflict prediction model will also continuously improve to adapt to complex and changing actual business scenarios.

[0037] Step S1200, construct a transaction conflict prediction model according to the historical transaction feature vector set;

[0038] Specifically, the transaction conflict prediction model adopts a three-layer LSTM (Long Short-Term Memory) structure. The input layer receives the historical transaction feature vector sequence, the middle layer extracts the temporal correlation features of the transaction sequence, and the output layer gives the conflict probability prediction value of the current transaction. LSTM is a special recurrent neural network, which is very suitable for processing and predicting time series data. It can automatically learn the long-distance dependence relationship in the historical transaction feature sequence and discover the implicit conflict patterns between transactions through the memory unit introduced with the gating mechanism. The reason for choosing LSTM as the core model of this method is based on the following considerations:

[0039] 1. Transaction execution has an obvious temporal correlation. The execution of the current transaction depends not only on its own characteristics but also on historical transactions in a previous period. Taking the historical transaction feature sequence as the model input can mine the temporal dependence relationship between transactions, which helps to improve the accuracy of conflict prediction. 2. The influence between transaction executions has a long-term memory effect. For example, a large transaction submitted a long time ago may hold the exclusive lock of some hot data for a long time, thus blocking multiple subsequent transactions. This kind of transaction influence with a large span is very crucial for predicting the conflict probability of the current transaction. The memory unit of LSTM is specifically used to capture such long-distance dependencies and is very suitable for this scenario. 3. The transaction conflict pattern may change dynamically with the development of the business. By regularly using the newly generated transaction log data to incrementally train the model, the model can continuously learn and adapt to business changes, and continuously improve the timeliness of conflict prediction. The LSTM structure is flexible and easy to support incremental learning.

[0040] The construction of training samples is also a key step. From the historical transaction sequence, a subsequence of a certain length is intercepted in a sliding window manner as the input sample, and each sample corresponds to a target transaction. If the target transaction conflicts (such as being blocked or rolled back) within a period of time after submission, the label of this sample is 1, otherwise it is 0.

[0041] Suppose there are a total of 10 transactions submitted in sequence during the time period from t1 to t10, denoted as T1 - T10, and each transaction is represented as a feature vector. Then the following training samples can be constructed:

[0042] Input: [T1, T2, T3], Label: Whether T4 conflicts; Input: [T2, T3, T4], Label: Whether T5 conflicts; ……; Input: [T7, T8, T9], Label: Whether T10 conflicts;

[0043] In this way, the original transaction sequence data is converted into a large number of supervised learning samples. Next, these training samples are used to train the LSTM network. The loss function uses the cross-entropy function to measure the difference between the model's predicted conflict probability and the actual conflict situation. Through the backpropagation algorithm, the gradient of the loss function with respect to the network parameters is backpropagated to each layer, and the gradient descent method is used to update the parameters, so that the predicted value of the model continuously approaches the true label. The training process usually requires multiple rounds of iteration, and the model is updated once with the complete training sample set in each round of iteration. When the decrease of the loss function tends to be flat in multiple consecutive rounds of iteration, it can be considered that the model has basically learned the internal law of transaction conflicts, and the training process ends to obtain the final transaction conflict prediction model.

[0044] Exemplarily, following the historical transaction feature vector construction rule exemplified in step S1100, the input transaction sequence is as follows:

[0045] {[t1, SELECT, 5%, 0%, 20%, 15%, 200];

[0046] [t2, UPDATE, 15%, 10%, 30%, 25%, 800];

[0047] [t3, INSERT, 8%, 0%, 70%, 65%, 500]};

[0048] If the conflict probability given by the model for the above transaction sequence is 80%, it indicates that the model predicts that after the first 3 transactions, the next transaction is very likely to conflict. The DBA (Database Administrator) can adjust the execution mode of the transaction in a timely manner based on this warning to avoid conflicts. Among them, SELECT represents the data query operation, and INSERT represents the data insertion operation.

[0049] Introducing machine learning methods for transaction conflict prediction, compared with traditional heuristic rules, the greatest advantage lies in its powerful learning and generalization ability. By automatically extracting conflict patterns from a large amount of historical transaction data, the machine learning model can discover implicit rules that are difficult to summarize manually. At the same time, with regular incremental training, the model can continuously adapt to changes in database operations and always maintain a high prediction accuracy. High-quality conflict prediction can guide the DBA to choose a more appropriate scheduling strategy, while ensuring data consistency, maximizing the concurrency performance of the system.

[0050] Step S1300, based on the transaction conflict prediction model, obtain the conflict prediction value of the real-time transaction.

[0051] Furthermore, as Figure 3 shown, step S1300 includes:

[0052] Step S1310, obtain the request parameters of the real-time transactions of each node, extract the real-time transaction features from the request parameters of the real-time transactions, and construct a real-time transaction feature vector; the real-time transaction features include the real-time transaction submission time and the proportion of data involved in the real-time transaction;

[0053] Step S1320, input the real-time transaction feature vector into the transaction conflict prediction model to obtain the conflict prediction value of the real-time transaction.

[0054] Specifically, when a real-time transaction arrives at the database, the key features of the transaction are first extracted to construct a real-time transaction feature vector. The request parameters of a real-time transaction usually contain the main feature information of the transaction, such as the transaction type (query, insert, modify, or delete), the target data range, the commit time, etc. These information are crucial for judging the conflict possibility of the transaction. Among them, the transaction type determines the type of operation of the transaction on the data, and thus affects its way of locking the data. For example, a query transaction usually only needs a shared lock, while a modify transaction needs an exclusive lock. The data distribution reflects which nodes' data the transaction will access, and determines which other transactions on which nodes may have conflicts with it. The data ratio quantifies the scope of the transaction in the entire dataset, and directly affects the size of its conflict probability. The transaction commit time affects which transactions overlap with this transaction in the time window, and thus may compete for shared resources. These parameters jointly characterize the key features of a transaction.

[0055] After receiving a real-time transaction request, these feature information need to be automatically extracted from the request parameters and transformed into a structured transaction feature vector. The format of this feature vector should be consistent with the historical transaction feature vector used when training the conflict prediction model, which is convenient for inputting into the model for prediction. At the same time, to ensure the timeliness of feature extraction, the entire process needs to be automated to avoid manual intervention.

[0056] It should be noted that some information in the request parameters may have a certain degree of uncertainty. For example, a transaction declares that it will access 100 pieces of data, but it may actually only access 80 pieces during the actual execution. To cope with this situation, the system needs to continuously track the data access trajectory of the transaction during its execution, and dynamically calibrate the corresponding transaction features, especially those related to the data ratio. At the same time, the intermediate results of the transaction execution may also provide richer information for feature extraction, which should be captured and fully utilized by the system.

[0057] The timely and accurate construction of the real-time transaction feature vector is the basis of conflict prediction. Through high-quality feature representation, the transaction conflict prediction model can fully explore the implicit conflict patterns between transactions and make reliable judgments. Therefore, the design of the feature extraction process should strive to be comprehensive and detailed, covering all factors that have the greatest impact on transaction conflicts, and be prepared to handle various abnormal situations.

[0058] Input the real-time transaction feature vector into the established transaction conflict prediction model in the same format as the training data, and the model outputs a value between 0 and 1, representing the conflict prediction value of this transaction. The larger the conflict prediction value, the higher the probability that this real-time transaction will have conflicts such as lock contention and deadlock with other transactions. Therefore, the conflict prediction value can be used as an important reference basis for scheduling this real-time transaction.

[0059] The introduction of the transaction conflict prediction model enables the multi-node database system to perceive and avoid potential transaction conflicts in advance, minimize transaction blocking while ensuring data consistency, and thus greatly improve the concurrent processing ability of the system. Compared with the traditional post-detection and passive processing mechanism, the transaction conflict prediction model can detect problems earlier, reduce recovery and rollback operations, and is more conducive to supporting complex online transaction services.

[0060] Step S2000: Based on the conflict prediction value, form a real-time transaction priority queue and dynamically adjust the lock granularity of real-time transactions; combine the real-time transaction priority queue and the lock granularity of real-time transactions to determine the execution mode of real-time transactions.

[0061] Furthermore, step S2000 includes:

[0062] Step S2100: Based on the conflict prediction value, form a real-time transaction priority queue.

[0063] Furthermore, step S2100 includes:

[0064] Step S2110: Set a priority level threshold, and divide the priority levels of real-time transactions according to the conflict prediction values of real-time transactions.

[0065] Furthermore, as Figure 4 shown, step S2110 includes:

[0066] Step S2111: Set a high-priority level threshold g1, a medium-priority level threshold g2, and a low-priority level threshold g3, where 0 < g1 < g2 < g3 < 1.

[0067] Step S2112: Set the conflict prediction value of the real-time transaction as p. If p < g1, set the priority level of the real-time transaction as high priority; if g1 ≤ p < g2, set the priority level of the real-time transaction as medium priority; if g2 ≤ p < g3, set the priority level of the real-time transaction as low priority; if p ≥ g3, set the priority level of the real-time transaction as extremely low priority.

[0068] Specifically, according to the conflict prediction value given by the transaction conflict prediction model, the priority levels of real-time transactions can be automatically divided. The so-called priority level refers to the priority order in which different transactions obtain the right to use system resources when multiple concurrent transactions compete for system resources. The priority level directly determines the scheduling order of transactions and the possibility of obtaining resources. Usually, the lower the conflict prediction value of a transaction, the greater the possibility of obtaining a high priority. This is because a low conflict prediction value means that the probability of this transaction conflicting with other transactions is small. Therefore, preferentially scheduling this transaction helps to improve the overall concurrency of the multi-node system. On the contrary, transactions with high conflict prediction values are more likely to cause conflicts. If blindly scheduled first, it is very likely to block a large number of other transactions and reduce the overall processing efficiency. Therefore, the priority of such transactions should be minimized to minimize the chain blocking caused by them.

[0069] In order to quantitatively divide the priorities of transactions according to the conflict prediction value, several priority level thresholds need to be preset. For example, a high priority threshold g1, a medium priority threshold g2, and a low priority threshold g3 can be set, etc. Here, 0 < g1 < g2 < g3 < 1. For the conflict prediction value p, if p < g1, the corresponding real-time transaction priority is high; if g1 ≤ p < g2, the priority is medium; if g2 ≤ p < g3, the priority is low; if p ≥ g3, the priority is extremely low. The specific values of these priority thresholds can be configured and adjusted by the system administrator according to the business characteristics and system operation conditions to achieve the best scheduling effect.

[0070] For example, the following priority thresholds are preset: g1 = 0.3, g2 = 0.6, g3 = 0.8. If the conflict prediction value p of a real-time transaction is 0.2, its priority level is determined to be high. This means that in the scheduling competition with other transactions, this transaction is very likely to be preferentially selected for execution, so as to complete and release the occupied resources as soon as possible. For another transaction with a conflict prediction value p = 0.9, its priority level is extremely low. The system will try to delay scheduling this transaction as much as possible to avoid it occupying resources for a long time and blocking other high-priority transactions. Only when the high-priority transactions are executed, this transaction will have the opportunity to be scheduled.

[0071] It should be noted that the priority of real-time transactions is not fixed. As the execution of a transaction progresses, characteristics such as its data access scope and conflict probability may change, leading to an increase or decrease in the conflict prediction value. Once the conflict prediction value changes to the boundary of the priority threshold, the priority level of the transaction should be adjusted dynamically accordingly. For example, if a medium-priority transaction accesses much less data than expected during execution, resulting in the conflict prediction value dropping to 0.2, its priority level should immediately be adjusted from "medium" to "high" to accelerate the execution progress of the transaction. This dynamic priority adjustment mechanism ensures the real-time synchronization of the transaction priority with its conflict risk, avoiding the situation where a transaction waits for scheduling for a long time due to changes in its conflict characteristics, and further improving the overall performance of the multi-node database.

[0072] The division of transaction priorities maps the conflict probability quantitatively to an intuitive priority level by setting a priority threshold, and then guides the scheduling decision. On the one hand, it enables the scheduler to make scheduling choices directly based on simple priority labels, leaving aside the specific transaction characteristics, greatly reducing the scheduling complexity. On the other hand, through the dynamic association between the priority level and the conflict prediction value, it subtly feeds back the dynamic characteristic changes of the transaction to the scheduling process in real time, achieving the dynamic synchronization of the scheduling decision and the transaction running state, and further enhancing the flexibility and timeliness of the scheduling.

[0073] Step S2120: Assign a priority timestamp to each real-time transaction, where the priority timestamp is jointly determined by the submission time of the real-time transaction and the priority level of the real-time transaction.

[0074] The method for jointly determining the priority timestamp by the submission time of the real-time transaction and the priority level of the real-time transaction includes: according to the priority level of the real-time transaction, different timestamp adjustment coefficients are assigned, and the priority timestamp of the real-time transaction is equal to the product of its submission time and the timestamp adjustment coefficient.

[0075] Step S2130: Sort the real-time transactions in ascending order according to the dynamic priority timestamp to form a real-time transaction priority queue.

[0076] Specifically, compared with the original conflict prediction value, the priority level more intuitively represents the preferential right of different transactions to occupy resources. However, it is still difficult to precisely depict the urgency of a transaction based solely on the priority level, especially for transactions within the same priority. Therefore, it is also necessary to assign a priority timestamp to each real-time transaction to further distinguish the execution order of transactions. The priority timestamp is jointly determined by the submission time of the real-time transaction and the priority level of the real-time transaction. Among them, the submission time reflects the order in which transaction requests enter the system. And the priority level is adjusted based on the submission time, enabling high-priority transactions to take precedence over transactions with a later submission time but a lower priority.

[0077] To generate globally unique and comparable priority timestamps, a logical clock is introduced. This clock is represented by a monotonically increasing integer sequence. Whenever a real-time transaction request is received, the current clock value is used as the commit time of the transaction, and the clock value is incremented by 1 to always maintain an increasing trend. Based on the commit time, different timestamp adjustment coefficients are assigned according to the priority level of the transaction. For example, it can be stipulated that the adjustment coefficient for high-priority transactions is 1, for medium-priority transactions is 10, for low-priority transactions is 100, and for extremely low-priority transactions is 1000. The final priority timestamp of a transaction is equal to the product of its commit time and the adjustment coefficient. Through this calculation method, the timestamp of a high-priority transaction must be less than that of a low-priority transaction with a similar commit time, thus ensuring the priority scheduling right of high-priority transactions.

[0078] For example, assume the current logical clock value is 1000, and transaction A is submitted with high priority, then its priority timestamp is 1000×1 = 1000. Next, transaction B is submitted with medium priority, and the logical clock advances to 1001, then the priority timestamp of B is 1001×10 = 10010. Subsequently, transaction C is submitted with high priority, and the logical clock advances to 1002, then the priority timestamp of C is 1002×1 = 1002. By comparing the priority timestamps, the execution order of the three transactions can be determined as A, C, B in sequence. It can be seen that even though the commit time of C is later than that of B, due to its higher priority, it gets a scheduling opportunity prior to B. This timestamp allocation mechanism effectively balances the influence of transaction priority and commit time, making the scheduling decision more comprehensive and accurate.

[0079] Finally, all real-time transactions are sorted in ascending order of priority timestamps to form a real-time transaction priority queue. The transaction at the head of the queue has the smallest priority timestamp, so it will be scheduled first. As new real-time transactions are continuously submitted, the priority queue is updated in real time, always ensuring that high-priority transactions are at the head of the queue. It should be noted that since the conflict prediction value is dynamically changing, the priority level of a transaction and its position in the priority queue may also change accordingly. Therefore, it is necessary to continuously monitor the conflict prediction value of each transaction. Once a significant change occurs, recalculate its priority timestamp and adjust its position in the priority queue accordingly. Through the dynamic update of priorities, the system can quickly adapt to the real-time changes in transaction conflict situations and minimize the probability of conflicts occurring.

[0080] In summary, the process of establishing a real-time transaction priority queue is essentially a process of globally and uniformly encoding transactions by comprehensively considering the conflict prediction value, commit time, and priority level of the transactions. This encoding method intuitively reflects the system's judgment on the importance and urgency of different transactions and determines the final scheduling order of the transactions. High-quality priority division and encoding help to increase the concurrency while reducing the conflict rate, achieving the best balance between system performance and data consistency. The real-time transaction priority queue construction method in the example is simple, efficient, and easy to implement in engineering, which can significantly improve the real-time processing ability of a multi-node database system for a large number of concurrent transactions and has high practical value.

[0081] Step S2200: Dynamically adjust the lock granularity of the real-time transaction according to the data proportion involved in the real-time transaction;

[0082] Furthermore, as Figure 5 shown, step S2200 includes:

[0083] Step S2210: If the data proportion L 2 <θ 1 , the lock granularity of the real-time transaction is lock-free concurrency, where θ 1 is a preset row lock threshold;

[0084] Step S2220: If θ 1 ≤L 2 <θ 2 , the lock granularity of the real-time transaction is row-level lock, where θ 2 is a preset table lock threshold;

[0085] Step S2230: If the data proportion L 2 ≥θ 2 , the lock granularity of the real-time transaction is table-level lock.

[0086] Specifically, the lock granularity of a real-time transaction refers to the scope of locking data items during the execution of the transaction to ensure the atomicity and isolation of the transaction. The lock granularity can be divided into row-level locks, table-level locks, etc. from small to large. Different lock granularities have significant differences in concurrency control capabilities and system overheads. Therefore, it is necessary to dynamically select the optimal lock granularity according to the data access characteristics of real-time transactions. Among them, the most direct influencing factor is the proportion of data involved in the real-time transaction, that is, the proportion of the number of data operations in the transaction to the total amount of data in the data table where it is located. Generally speaking, the larger the proportion of data involved in the transaction, the more obvious the advantage of using coarse-grained locks. This is because a large proportion of data involved means that the transaction is likely to modify most of the data in the table. In this case, using row-level locks will introduce a large number of lock operations and conflict detection overheads, while directly locking the entire table can significantly reduce the system burden. On the contrary, if the proportion of data involved is small, using fine-grained locks is more conducive to improving concurrency, because this type of lock will only block a small part of row data and will not interfere with the access of other transactions to most of the data in the table.

[0087] To quantitatively determine which lock granularity is suitable for real-time transactions, two data proportion thresholds are preset, namely the row lock threshold θ 1 and the table lock threshold θ 2 , where 0 < θ 1 < θ 2 < 1. For real-time transactions with a proportion of data involved L 2 less than θ 1 , the range of their data operations is very limited. Using row-level locks may bring unnecessary overheads. Therefore, an unlock concurrency control method is adopted for such transactions, allowing them to execute concurrently with other transactions, and detecting and resolving possible conflicts through mechanisms such as optimistic locks or multi-version concurrency control to maximize the parallel processing ability of the system. For θ 1 ≤ L 2 < θ 2 of real-time transactions, the range of their data operations is moderate. Using row-level locks can better balance concurrency and lock overheads. On the one hand, by locking the actually accessed data rows, other transactions can be prevented from concurrently modifying these data, avoiding data consistency problems such as dirty reads and non-repeatable reads. On the other hand, the influence range of row-level locks is limited to single-row data and will not block the entire table, thus retaining the possibility for other transactions to access the remaining data in the table. For L 2 ≥ θ 2For real-time transactions, since the proportion of data involved is already very high, using row-level locks will introduce excessive lock requests and conflict detections, which will seriously damage the system performance. At this time, it is better to directly add a coarse-grained table-level lock to the entire table. Although this will temporarily block all access to the table by other transactions, considering that such transactions themselves tend to monopolize most of the data in the table, the loss of concurrency caused by using table-level locks is actually very limited. At the same time, table-level locks can greatly simplify the complexity of lock management, avoid a large number of trivial row lock requests, and improve the overall system performance.

[0088] It should be emphasized that the above method of dividing lock granularity is only a static initial decision. During the actual execution of transactions, the system also needs to dynamically monitor the data access trajectory of each transaction to determine whether it is necessary to dynamically adjust the lock granularity. For example, a transaction initially marked as using row locks may have its data access scope far exceed expectations during execution, resulting in a sharp increase in lock overhead, and the system may face the risk of deadlocks. In this case, the system should actively upgrade the lock granularity to table locks and immediately release the acquired row locks to mitigate the negative impact. On the contrary, for a transaction originally planned to use table locks, if the actual amount of data accessed is relatively small, the system should also consider downgrading the lock granularity to allow more concurrency.

[0089] The dynamic adjustment of lock granularity requires the system to monitor the data access trajectory of each transaction in real time. Once the data access percentage exceeds the preset threshold, the upgrade or downgrade of lock granularity should be immediately triggered to adapt to changes in transaction behavior in a timely manner. By precisely controlling the lock granularity, the system can effectively reduce lock conflicts while ensuring data consistency, thus achieving the best balance between concurrency and performance.

[0090] In summary, dynamically selecting the transaction lock granularity based on the data access percentage is an important optimization strategy in real-time transaction scheduling. By matching the most suitable lock type according to transaction characteristics, the system can adaptively control the conflict rate and maximize the overall throughput. The thresholds for row locks and table locks provide a quantitative basis for granularity switching, while the dynamic monitoring and adjustment mechanism ensure that the decision can be fine-tuned according to the real-time transaction progress. This flexible and adaptable method significantly improves the intelligence level of concurrency control in distributed databases, enabling it to cope with the challenges of high concurrency and massive data in the big data era.

[0091] Step S2300, determine the execution mode of the real-time transaction by combining the real-time transaction priority queue and the lock granularity of the real-time transaction.

[0092] Furthermore, step S2300 includes:

[0093] Step S2310: Divide the real-time transactions into a high-priority transaction set and a low-priority transaction set according to the priority level, priority timestamp, and lock granularity of the real-time transactions in the real-time transaction priority queue;

[0094] Further, as Figure 6 shown, step S2310 includes:

[0095] Step S2311: Traverse each real-time transaction in the real-time transaction priority queue, and extract the priority level E, priority timestamp T, and lock granularity L of the real-time transaction;

[0096] Step S2312: If E is a high priority or medium priority, and L is a row-level lock or lock-free concurrency, then divide the real-time transaction into the high-priority transaction set;

[0097] Step S2313: If E is a low priority or extremely low priority, or L is a table-level lock, then divide the real-time transaction into the low-priority transaction set.

[0098] Specifically, step S2311 traverses the real-time transaction priority queue. For each transaction in the queue, it reads the corresponding three attribute values, namely the priority level E, priority timestamp T, and lock granularity L. E reflects the urgency and importance of the transaction, T reflects the arrival order of the transaction, and L reflects the concurrent access granularity of the transaction. These three attributes describe the characteristics of the transaction from different perspectives and are the key basis for dividing the transaction priority set. The extraction process of these attributes needs to be fully automated without manual intervention, thus greatly improving the timeliness of scheduling decisions.

[0099] For each real-time transaction in the priority queue, determine whether its priority level E belongs to high or medium, and at the same time whether the lock granularity L is a row-level lock. If both conditions are met, then divide the transaction into the high-priority transaction set. High priority or medium priority means that the conflict prediction value of this transaction is relatively low, and the probability of lock contention with other transactions is small. A row-level lock means that the transaction only locks the actual accessed data rows, with a small locking range and is suitable for high-concurrency execution. Therefore, transactions that meet the above two conditions can release the concurrent control permission in a multi-node environment and make full use of distributed resources, thus significantly improving the transaction throughput of the system. At the same time, introducing lock-free concurrency control can further reduce lock-related overhead and amplify the concurrency advantage. This fine-grained dynamic control is particularly applicable to short transactions that frequently occur in OLTP (Online Transaction Processing) scenarios, which can control the transaction waiting time and improve the user experience.

[0100] For each real-time transaction in the priority queue, determine whether its priority level E belongs to the low or extremely low level, or whether the lock granularity L is a table-level lock. If either condition is met, the transaction is classified into the set of low-priority transactions. A low or extremely low priority means that the transaction has a high conflict prediction value and a high probability of lock contention with other transactions. A table-level lock indicates that the transaction needs to lock the entire data table, with a large locking range and low concurrency. Therefore, a transaction that meets either of the above conditions is likely to block a large number of other transactions in a concurrent environment, reducing the overall system performance. Classifying such transactions into the set of low-priority transactions and adopting a pessimistic concurrency control strategy to control their concurrency and delay scheduling execution can reduce the probability of deadlocks and avoid the risk of cascading blockages. Although this scheduling strategy sacrifices the response speed of individual transactions, it can ensure the continuous and stable execution of overall transactions, avoid serious blockage situations in a distributed environment, and is more beneficial to the long-term average performance of the system.

[0101] Transactions in the high-priority transaction set have both a small conflict prediction value and a fine lock granularity, indicating that they are likely to achieve conflict-free concurrency with other transactions on multiple nodes and should be preferentially scheduled for execution to increase the overall transaction throughput; while transactions in the low-priority transaction set either have a large conflict prediction value or a coarse lock granularity, indicating that they are likely to conflict with other transactions on multiple nodes and should be executed sequentially on a single node to avoid distributed deadlocks.

[0102] Since the state of a real-time transaction may change during execution. For example, its lock granularity may have to be upgraded to a table-level lock due to the amount of data accessed exceeding expectations. Also, its conflict probability may decrease as the number of committed transactions increases, resulting in an increase in the priority level. To dynamically adapt to these changes, the system needs to monitor the execution status of each real-time transaction in real time and adjust its priority classification in a timely manner. Once it is detected that the priority level E or lock granularity L of a transaction has changed, the system will immediately remove it from the current priority set and re-evaluate its priority based on the latest E and L attributes, and classify it into the corresponding set. Through this real-time dynamic priority adjustment, it can be ensured that each real-time transaction always receives the optimal scheduling strategy that matches its current state, avoiding misclassification of priorities due to changes in transaction states and affecting system performance. At the same time, this efficient priority adjustment can further reduce the probability of deadlocks while improving the scheduling accuracy, and is an important measure to ensure the correctness and efficiency of distributed transactions.

[0103] Step S2310 can quickly divide transactions into high-priority and low-priority sets through the combined judgment of the priority level E and the lock granularity L. This classification method clarifies the execution priority and concurrency control strategy of each transaction, reducing the complexity and decision-making time in the scheduling process. Transactions in the high-priority transaction set have a low conflict risk and high concurrency. The system can give priority to processing this part of the transactions, thus significantly improving the transaction throughput. Transactions in the low-priority transaction set occupy more system resources due to a higher conflict risk or a coarser lock granularity. By delaying scheduling or restricting concurrency, it is possible to prevent these transactions from blocking the execution of other transactions and reduce the occurrence probability of system deadlocks and blocks. For transactions in the high-priority transaction set, using a finer lock granularity (such as row-level locks or lock-free concurrency) can maximize concurrency and make full use of the resources of the multi-node database. For transactions in the low-priority transaction set, by restricting their concurrency (such as using table-level locks), the overhead of lock contention and conflict detection can be reduced, optimizing the system resource allocation. By reasonably dividing the transaction set and selecting an appropriate scheduling strategy, the rapid processing of high-priority transactions reduces the average response time of the system, while the delayed scheduling of low-priority transactions reduces the conflict rate and deadlock risk of the system, thus improving the throughput and performance of the system as a whole.

[0104] Step S2320 adopts an optimistic concurrency control strategy for real-time transactions in the high-priority transaction set;

[0105] Specifically, step S2320 adopts an optimistic concurrency control (OCC) strategy for real-time transactions in the high-priority transaction set. The so-called optimistic concurrency control is a concurrency control method that assumes that conflicts between transactions do not occur frequently. Different from pessimistic locks, optimistic locks believe that the situation of conflicts between transactions during execution is less, so there is no need to exclusively lock data resources from the beginning. Instead, optimistic locks allow multiple transactions to read the same data resource simultaneously and defer the locking operation until the transaction commit stage. Although this loose concurrency control strategy has certain conflict detection and rollback overheads, it greatly improves the overall concurrency of the system and reduces the blocking time of transactions due to lock waiting, especially suitable for OLTP (online transaction processing) business scenarios with more reads than writes.

[0106] Applying the OCC strategy to the set of high-priority transactions means allowing multiple high-priority transactions to be started and executed simultaneously on different nodes. During the execution of transactions, transactions on different nodes can each read the required data without waiting for the permission of the global lock. Only when a transaction needs to commit an update does the central coordination node perform conflict detection. If a write-write conflict or read-write conflict is detected between transactions, some transactions are selected to be rolled back according to preset rules (usually first-come, first-served); if no conflict is detected, the updates of all transactions can be committed simultaneously. By adopting this strategy, the parallel processing ability of the distributed system can be maximally exerted, and the average response speed of high-priority transactions can be significantly improved. At the same time, since the probability of lock conflicts for high-priority transactions is relatively low, the overall overhead of conflict detection and rollback is controllable and will not significantly affect the system performance. Therefore, applying optimistic locks to high-priority transactions is a key measure to improve the overall throughput of the system.

[0107] Step S2330: Apply the pessimistic concurrency control strategy to the real-time transactions in the set of low-priority transactions.

[0108] Specifically, applying the pessimistic concurrency control strategy to the real-time transactions in the set of low-priority transactions, they are executed sequentially within a single node strictly according to the priority timestamp order to reduce distributed deadlocks. The so-called pessimistic concurrency control strategy is a conservative database transaction scheduling mechanism. It defaults that data conflicts often occur, so it exclusively locks the resources required by a transaction before the transaction is executed. The main advantage of this strategy is that it can effectively avoid transaction conflicts and data inconsistencies, and the disadvantage is that the concurrency degree is low and it is easy to cause blocking. In this step, since all involved are low-priority transactions and their conflict prediction values are relatively high, indicating that there is indeed an easy contention for lock resources among them. Therefore, using the pessimistic concurrency control can exactly avoid the unnecessary conflicts and repeated blockings of these transactions. To further reduce the risk of deadlocks, this step requires that the low-priority transactions be executed serially according to the priority timestamp order.

[0109] Step S3000: Dynamically balance the node loads, execute the real-time transactions according to the real-time transaction priority queue, the lock granularity of the real-time transactions, and the execution mode, and monitor the execution status of the real-time transactions.

[0110] Furthermore, step S3000 includes:

[0111] Step S3100: Dynamically balance the node loads;

[0112] Furthermore, step S3100 includes:

[0113] Step S3110: Statistically analyze the resource usage of each node, identify high-load nodes and low-load nodes;

[0114] Furthermore, as Figure 7As shown, step S3110 includes:

[0115] Step S3111, collect the load metrics of each node and normalize the load metrics; the load metrics include CPU usage, memory usage, I / O usage, and network bandwidth usage;

[0116] Step S3112, calculate the comprehensive load index R of each node according to the load metrics of each node j ; R j represents the comprehensive load index of the j-th node;

[0117] The calculation of the comprehensive load index R of each node j includes:

[0118]

[0119] where:

[0120] R j represents the comprehensive load index of the j-th node, and its value range is [0, +∞). The larger the value, the higher the node load. CPU j represents the CPU usage of the j-th node, and its value range is [0, 1]. ω is a parameter that adjusts the influence intensity of CPU usage. The larger ω is, the greater the influence of CPU usage on the comprehensive load index. MEM j represents the memory usage of the j-th node, MEM total the total memory of all nodes. Taking the square root is to mitigate the influence of memory usage. IO j represents the I / O usage of the j-th node, and its value range is [0, 1]. ∈ is a very small positive number to avoid the denominator being zero. The fractional form is to reflect the saturation effect of I / O usage. When the I / O usage is very high, its contribution to the comprehensive load index tends to 1. NET j represents the network bandwidth usage of the j-th node, and its value range is [0, 1]. The hyperbolic tangent function tanh(·) can nonlinearly amplify the influence of network bandwidth usage and can more sensitively perceive changes in network load. α is the weight of CPU usage, β is the weight of memory usage, γ is the weight of I / O usage, and λ is the weight of network bandwidth usage, satisfying the condition: α + β + γ + λ = 1; as CPU j increases, increases, R j increases; the larger ω is, the more obvious the influence of CPU usage on the comprehensive load index. As MEM j increases, when MEM total remains unchanged, increases, R j increases. As IO jWith the increase of Increases and approaches 1, R j Increases. As the NET j Increases, tanh(NET j ) increases and approaches 1, R j Increases.

[0121] This formula comprehensively considers four key load indicators of nodes and comprehensively evaluates the load level of nodes. Different mathematical transformations are adopted for different load indicators, and non-linear functions are introduced to enable it to adapt to the changing characteristics of load indicators. For example, the logarithmic transformation of CPU utilization rate, the fractional transformation of I / O utilization rate, etc. can adapt to the sensitive interval of load indicators. The adjustment factors ω and ∈ are introduced to increase the flexibility of the formula and enable parameter tuning for different systems. This formula can be used as an important basis for the intelligent operation and maintenance management of multi-node databases and plays a positive role in reasonably scheduling real-time transactions and balancing the loads of each node. The comprehensive load index R j For nodes with a higher R j , tasks assigned to them should be minimized; while for nodes with a lower R j , task assignment to them can be given priority. The routing of real-time transactions can be dynamically adjusted according to the immediate changes of R j of each node, so as to make full use of the computing resources of each node and avoid over-concentration of load. At the same time, this index can also be used to evaluate the health status of nodes. If the R j of a certain node is at a very high level for a long time, it may indicate that it is necessary to expand and upgrade it, or to balance the load of the system more effectively.

[0122] Step S3113, if R j > RW 2 , then the j-th node is a high-load node; if R j < RW 1 , then the j-th node is a low-load node; where RW 1 is the preset low-load threshold, RW 2 is the preset high-load threshold, 0 < RW 1 < RW 2 < 1.

[0123] Specifically, continuously collect the key performance indicators of each node, and evaluate its current load status based on this. Common load indicators include CPU usage, memory usage, I / O usage, and network bandwidth usage, etc. Among them, CPU usage reflects the busyness of the node in processing transaction requests, and it measures the proportion of the time when the CPU executes transaction requests in the total CPU time within a certain period. CPU usage can be subdivided into user-mode CPU usage and kernel-mode CPU usage, corresponding to the computing operations of transactions and system calls, interrupt handling, etc. respectively. When transactions are intensive, a large amount of CPU time will be used for calculation and verification, resulting in a high CPU usage. If the CPU usage is in a saturated state for a long time, this node is very likely to become the performance bottleneck of the entire cluster. Memory usage reflects the tightness of the node's memory resources. It measures the proportion of the actual memory used by the node in the total available memory within a certain period. Memory usage can also be subdivided according to the memory pool type, such as buffer pool usage, shared pool usage, etc. The concurrent execution of transactions often requires a large amount of memory for various calculations such as data caching, query sorting, and connection. Once the available memory is insufficient, transactions will spend more time on data exchange, and in severe cases, it will even cause memory overflow errors.

[0124] I / O usage indicates the load of the node in I / O operations such as disk read and write. It measures the proportion of the actual number of I / O operations that occur within a certain period in the maximum IOPS (Input / Output Operations Per Second). It can be divided into read IOPS usage and write IOPS usage, reflecting the load of read operations and write operations respectively. Frequent data persistence, log writing, etc. will all occupy a large amount of I / O resources. When there is an I / O bottleneck, the execution progress of the entire transaction will be restricted by the speed of data reading and writing. Network bandwidth usage reflects the load of the node in distributed transaction communication. It measures the proportion of the actual number of bytes transmitted by the node in the maximum bandwidth within a certain period. It can be subdivided into incoming bandwidth usage and outgoing bandwidth usage, reflecting the load of data reception and sending respectively. The execution of transactions often requires cross-node data transmission and status synchronization, which will consume the network bandwidth resources of the node. If the network bandwidth becomes a scarce resource, it will extend the response time of distributed transactions.

[0125] To accurately judge the load status of each node, take a certain time interval (such as 5 seconds) as the sampling period, and continuously collect the instantaneous values of the above indicators. Then, calculate the average value of each indicator within a certain period (such as 1 minute) respectively, as the basis for evaluating the node load. For the j-th node, if its comprehensive load index R j falls within the interval (RW 2 , 1], that is, R j > RW 2, it can be determined that the node is in a high-load state. This means that all key resources of the node, such as CPU, memory, I / O, etc., have been exhausted. Continuing to receive new transaction requests may lead to a sharp drop in performance, with risks such as transaction timeouts and system crashes. Therefore, the system will reduce the task allocation to high-load nodes and try to migrate some transactions on them to other nodes until the load index of the node returns to the medium-load level. In contrast, if the comprehensive load index R of node j j falls within the interval [0, RW 1 ), that is, R j <RW 1 , it can be determined that the node is in a low-load state. This means that there are still a large amount of surplus hardware resources on the node that can be scheduled, and it is fully capable of accepting more transaction requests. Therefore, low-load nodes will be preferentially selected as the execution nodes for new transactions, and when necessary, they can also receive running transactions migrated from other high-load nodes to share their pressure. By guiding more tasks to flow to low-load nodes, local overheating can be alleviated and resource allocation can be optimized.

[0126] Between the above two states are medium-load nodes, whose load index R j falls within the interval [RW 1 , RW 2 . Medium-load nodes carry a moderate amount of transaction processing tasks, and the resource utilization rate is close to optimal. The current task allocation intensity should be maintained without major adjustments. It should be noted that the settings of RW 1 and RW 2 should be reasonably determined according to business requirements and system capacity. Selecting too low a threshold may lead to frequent task scheduling and data migration between nodes, bringing additional system overhead; while selecting too high a threshold may prevent the load imbalance problem from being alleviated in a timely manner, affecting the average response time of online transactions. Therefore, the DBA needs to continuously dynamically adjust RW 1 and RW 2 , and even set different thresholds for different types of nodes to achieve the best load control effect.

[0127] Step S3120, identify the hot data in high-load nodes and migrate the hot data in high-load nodes to low-load nodes.

[0128] Specifically, to identify the hot data in high-load nodes, it is necessary to comprehensively consider the data access characteristics in multiple dimensions. These characteristics include:

[0129] Data access frequency: That is, the number of accesses to this data within a unit of time. The higher the access frequency, the more transactions compete for this data, making it a potential hot data. Data access duration: That is, the average occupation time for each access to this data. The larger the access duration, the longer the transactions accessing this data may take to complete, resulting in other transactions waiting for a long time and exacerbating the node load. Data conflict frequency: That is, the number of times that accessing this data causes transaction lock conflicts or deadlocks within a unit of time. The higher the conflict frequency, the more likely this data is to trigger competition among transactions, making it a potential hot data. These characteristics describe the level of data access load from different perspectives. The system needs to continuously monitor these data access metrics, calculate their weighted average values, and dynamically evaluate the heat of each data partition. When the comprehensive heat of a certain partition exceeds the preset hot threshold, it is marked as hot data. The setting of the hot threshold needs to consider both the effect of load balancing and the migration overhead, and can be obtained through simulation experiments or empirical estimation methods.

[0130] After identifying the hot data, it is necessary to migrate it from high-load nodes to low-load nodes. The data migration is achieved by means of partition replication and redistribution. Specifically, first select several nodes with the lowest load as migration targets, and then copy the hot data partitions from high-load nodes to the target nodes. During the copying process, it is necessary to ensure the consistency of the partition data to avoid introducing inconsistencies due to data modification during replication. Therefore, an exclusive lock is usually added to the hot partition during the copying operation to block the write operations of other transactions until the copying is completed.

[0131] After the copying is completed, it is necessary to switch the routing of the transaction requests from the original node to the new node, that is, update the metadata information to allow subsequent transactions to access the hot partition replicas on the new node. At the same time, delete the hot partition on the original node to release the storage space. This redistribution process may cause a short-term transaction interruption, but through the rapid update of the metadata information and the request retry mechanism, the interruption time can be controlled within milliseconds.

[0132] The entire data migration process runs automatically without manual intervention. The system background triggers the load balancing process regularly, and each process performs hot data identification and migration. Since the business load is constantly changing, the trigger interval should not be too long each time, so as to promptly perceive the load change and dynamically adjust the data distribution. However, frequent migrations will also introduce additional system overhead, so the trigger interval cannot be too short either. In practice, the load balancing process can be triggered periodically according to the change of the business load, for example, once an hour.

[0133] Through hot data migration, the access pressure on high-load nodes can be effectively alleviated, and the load is transferred to relatively idle nodes, thus achieving dynamic load balancing among multiple nodes. On the one hand, this avoids excessive load on individual nodes and affects the rapid response of business requests. On the other hand, it also improves the overall utilization rate of resources, allows idle nodes to undertake more work, and gives play to the parallel processing advantages of the multi-node system. It should be noted that although hot data migration can alleviate hot issues, it cannot completely eliminate hot spots. This is because migration occurs after the hot spot is formed, rather than preventing the generation of hot spots in advance. At the same time, when multiple high-frequency transactions access a data partition simultaneously, even if it is migrated to the node with the lowest load, this partition may become a hot spot again in a short period. Therefore, data migration can only be used as a supplementary means to alleviate hot spots, rather than replacing preventive measures such as transaction priority division and lock granularity adjustment. Only by taking multiple measures simultaneously, combining dynamic balancing with preventive scheduling, can transaction conflicts be eliminated to the greatest extent and the high concurrency ability of the multi-node database be fully utilized.

[0134] In summary, through hot data migration, it is possible to alleviate the local overload caused by hot spots, avoid individual nodes becoming performance bottlenecks, and make full use of idle nodes to squeeze system resources, thereby improving the concurrent processing ability of the multi-node database as a whole. This dynamic load balancing ability is the key to building a scalable distributed data service.

[0135] Step S3200: Execute real-time transactions according to the real-time transaction priority queue, the lock granularity of real-time transactions, and the execution method, and monitor the execution status of real-time transactions.

[0136] Specifically, according to the real-time transaction priority queue, the lock granularity of real-time transactions, and the execution method, the system finally determines the specific execution plan for each real-time transaction and enters the transaction execution stage. The scheduling engine is responsible for sequentially retrieving each transaction from the real-time transaction queue and allocating it to the execution engine of the corresponding node for running. During the execution process, the system needs to monitor the running status of each transaction, promptly detect possible abnormalities, and ensure that the transaction is successfully completed as expected.

[0137] Transaction execution monitoring mainly includes the following aspects:

[0138] Progress tracking: That is, record the key steps and timestamps of transaction execution, such as the transaction start time, the time to obtain the lock, the commit time, etc. By analyzing these timestamps, the execution efficiency of the transaction can be evaluated, and possible performance bottlenecks can be found. When the key steps of the transaction do not receive a response for a long time, such as the transaction cannot obtain the lock for a long time, it may indicate serious problems such as deadlocks and requires the system to be highly vigilant.

[0139] Resource occupation: That is, record the system resources consumed during the execution of a transaction, including CPU usage, I / O volume, memory occupation, etc. When the resource consumption of a transaction is abnormally high, it may be due to problems in the transaction logic, such as infinite loops, frequent I / O, etc., and it is necessary to capture and process it in a timely manner. Resource occupation monitoring is crucial for identifying problematic transactions, preventing them from occupying system resources for a long time, and ensuring overall performance.

[0140] Lock occupation: That is, record the lock resources occupied during the execution of a transaction, including the type of lock (shared lock, exclusive lock), granularity (row-level lock, table-level lock), occupation duration, etc. By analyzing this lock occupation information, the lock competition situation between transactions can be discovered, providing a decision-making basis for optimizing the lock granularity of transactions and adjusting the transaction execution order. When the lock occupation time is abnormally long or the lock granularity is abnormally large, it often means that problems such as deadlocks or lock escalations have occurred, and the system needs to intervene in a timely manner.

[0141] The above monitoring metric data can, on the one hand, be displayed to the system administrator in real time and notify the administrator through an alarm mechanism when an abnormal situation occurs, assisting in manual problem diagnosis and processing. On the other hand, the monitoring data can also be fed back to the optimizer and scheduler for dynamically optimizing the execution method of transactions. For example, when monitoring finds that the lock granularity of a certain transaction is set too large, resulting in a large number of transactions being blocked, the scheduler can promptly increase the priorities of these blocked transactions and change their execution order; the optimizer can also promptly adjust the lock strategy of this transaction to reduce the lock granularity and relieve lock competition.

[0142] During the operation of a transaction, the system also needs to provide a rollback and fault recovery mechanism to ensure that when a transaction fails to execute, it can safely revoke the operations that have been executed and maintain data consistency. The rollback mechanism is implemented by recording rollback logs during the execution of a transaction. The rollback logs store the data state before the transaction modification. Once the transaction fails to execute, the system rolls back the data to the state before the transaction started according to the rollback logs, just as if the transaction had never been executed. This log-based rollback method only needs to reverse and redo the write operations of the transaction, avoiding full table scans and greatly improving the rollback efficiency.

[0143] The fault recovery mechanism mainly deals with sudden failures such as system crashes and node disconnections during the execution of a transaction. These failures may cause the transaction to be suddenly interrupted halfway through, with the committed part of the transaction taking effect and the uncommitted part not taking effect, resulting in inconsistent data. To address this situation, in addition to recording rollback logs during the execution of a transaction, the system also needs to record redo logs (redo logs). Contrary to rollback logs, the redo logs record the data state after the transaction execution. During fault recovery, the system compares the redo logs with the current state of the database, redoes the committed transactions that have not been flushed to the database, and rolls back the uncommitted transactions, thereby restoring to a consistent state.

[0144] Transaction execution monitoring, rollback, and fault recovery mechanisms together build a security protection network for the distributed database, eliminating data logic errors caused by abnormal transaction execution and ensuring the continuity of business processes. Combining these mechanisms with the transaction conflict prediction method proposed in this paper, multiple measures can be taken simultaneously. From the perspective of scheduling optimization, serious transaction conflicts can be avoided at the source; from the perspective of execution monitoring, after transaction conflicts occur, they can be processed in a timely manner to minimize losses, ultimately improving the reliability and concurrency performance of the distributed database in all aspects.

[0145] Example 2

[0146] Based on Example 1, this example provides an intelligent operation and maintenance management platform for a multi-node database, as Figure 8 shown, including:

[0147] Conflict prediction module: used to collect historical transaction execution logs of each node from the multi-node database, construct a set of historical transaction feature vectors; based on the set of historical transaction feature vectors, construct a transaction conflict prediction model; based on the transaction conflict prediction model, obtain the conflict prediction value of real-time transactions;

[0148] Execution policy generation module: based on the conflict prediction value, form a real-time transaction priority queue, dynamically adjust the lock granularity of real-time transactions; combine the real-time transaction priority queue and the lock granularity of real-time transactions to determine the execution method of real-time transactions;

[0149] Load adjustment module: used to dynamically balance node loads;

[0150] Execution monitoring module: execute real-time transactions according to the real-time transaction priority queue, the lock granularity of real-time transactions, and the execution method, and monitor the execution status of real-time transactions.

[0151] The methods, systems, and devices of the present application can be implemented in many ways. For example, the methods, systems, and devices of the present application can be implemented through software, hardware, firmware, or any combination of software, hardware, and firmware. The above order of steps for the method is only for illustration, and the steps of the method of the present application are not limited to the above specific described order unless otherwise specifically stated. In addition, in some embodiments, the present application can also be implemented as a program recorded in a recording medium, and these programs include machine-readable instructions for implementing the method according to the present application. Therefore, the present application also covers a recording medium storing a program for executing the method according to the present application.

[0152] In addition, parts of the above technical solutions provided in the embodiments of the present application that are consistent with the corresponding technical solutions in the prior art in terms of implementation principles are not described in detail to avoid excessive elaboration.

[0153] The specific embodiments described above further elaborate in detail on the objectives, technical solutions, and beneficial effects of the present invention. It should be understood that the above description is only the specific embodiments of the present invention and is not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included within the protection scope of the present invention.

Claims

1. An intelligent operation and maintenance management method for a multi-node database, characterized in that: The method comprises: Collect historical transaction execution logs of each node from a multi-node database to construct a historical transaction feature vector set; construct a transaction conflict prediction model based on the historical transaction feature vector set; and obtain conflict prediction values ​​of real-time transactions based on the transaction conflict prediction model; Based on the conflict prediction value, a real-time transaction priority queue is formed to dynamically adjust the lock granularity of the real-time transaction; the execution mode of the real-time transaction is determined by combining the real-time transaction priority queue and the lock granularity of the real-time transaction; Dynamically balance node loads, execute real-time transactions based on the real-time transaction priority queue, lock granularity and execution mode of real-time transactions, and monitor the execution status of real-time transactions.

2. The intelligent operation and maintenance management method for a multi-node database according to claim 1 is characterized in that: The construction of the historical transaction feature vector set includes: Parse historical transaction execution logs, extract historical transaction feature information related to transaction conflicts, construct historical transaction feature vectors, and form a historical transaction feature vector set; The conflict prediction value of the real-time transaction is obtained by: Obtaining the request parameters of the real-time transaction of each node, extracting the real-time transaction features from the request parameters of the real-time transaction, and constructing a real-time transaction feature vector; the real-time transaction features include the real-time transaction submission time and the proportion of data involved in the real-time transaction; The real-time transaction feature vector is input into the transaction conflict prediction model to obtain the conflict prediction value of the real-time transaction.

3. The intelligent operation and maintenance management method for a multi-node database according to claim 2 is characterized in that: The forming of a real-time transaction priority queue comprises: Set the priority level threshold and classify the priority level of real-time transactions according to the conflict prediction value of real-time transactions; Assigning a priority timestamp to each real-time transaction, wherein the priority timestamp is determined by the real-time transaction submission time and the priority level of the real-time transaction; Real-time transactions are sorted in ascending order according to dynamic priority timestamps to form a real-time transaction priority queue.

4. The intelligent operation and maintenance management method for a multi-node database according to claim 3 is characterized in that: The method in which the priority timestamp is determined by the real-time transaction submission time and the priority level of the real-time transaction includes: Different timestamp adjustment coefficients are assigned according to the priority level of the real-time transaction. The priority timestamp of the real-time transaction is equal to the product of its submission time and the timestamp adjustment coefficient.

5. The intelligent operation and maintenance management method for a multi-node database according to claim 3 is characterized in that: The priority levels of the real-time transactions include: Set a high priority level threshold g1, a medium priority level threshold g2 and a low priority level threshold g3, wherein 0<g1<g2<g3<1; The conflict prediction value of the real-time transaction is set to p. If p<g1, the priority level of the real-time transaction is set to high priority; if g1≤p<g2, the priority level of the real-time transaction is set to medium priority; if g2≤p<g3, the priority level of the real-time transaction is set to low priority; if p≥g3, the priority level of the real-time transaction is set to very low priority.

6. The intelligent operation and maintenance management method for a multi-node database according to claim 5 is characterized in that: The dynamic adjustment of the lock granularity of the real-time transaction includes: If the data ratio L2 involved in the real-time transaction is less than θ1, the lock granularity of the real-time transaction is lock-free concurrency, and θ1 is the preset row lock threshold; If θ1≤L2<θ2, the lock granularity of the real-time transaction is row-level lock, and θ2 is the preset table lock threshold; If the real-time transaction involves a data ratio L2 ≥ θ2, the lock granularity of the real-time transaction is a table-level lock.

7. The intelligent operation and maintenance management method for a multi-node database according to claim 6 is characterized in that: The method of determining the execution mode of the real-time transaction includes: According to the priority level, priority timestamp and lock granularity of the real-time transactions in the real-time transaction priority queue, the real-time transactions are divided into a high-priority transaction set and a low-priority transaction set; Adopt optimistic concurrency control strategy for real-time transactions in high-priority transaction set; A pessimistic concurrency control strategy is adopted for real-time transactions in the low-priority transaction set.

8. The intelligent operation and maintenance management method for a multi-node database according to claim 7 is characterized in that: The dividing of the real-time transactions into a high-priority transaction set and a low-priority transaction set comprises: Traverse each real-time transaction in the real-time transaction priority queue, extract the priority level E, priority timestamp T and lock granularity L of the real-time transaction; If E is high priority or medium priority, and L is row-level lock or lock-free concurrency, the real-time transaction is classified into the high priority transaction set; If E is a low priority or a very low priority, or L is a table-level lock, the real-time transaction is classified into a low-priority transaction set.

9. The intelligent operation and maintenance management method for a multi-node database according to claim 8, characterized in that: The dynamic balancing of node loads includes: Count the resource usage of each node and identify high-load nodes and low-load nodes; Identify hotspot data in high-load nodes and migrate them to low-load nodes; The identifying of high-load nodes and low-load nodes comprises: Collect the load indicators of each node and normalize them; the load indicators include CPU utilization, memory utilization, I / O utilization and network bandwidth utilization; According to the load index of each node, the comprehensive load index R of each node is calculated. j ; R j represents the comprehensive load index of the jth node; If R j >RW2, then the jth node is a high-load node; if R j <RW1, then the jth node is a low-load node; wherein RW1 is a preset low-load threshold, RW2 is a preset high-load threshold, and 0<RW1<RW2<1.

10. An intelligent operation and maintenance management platform for a multi-node database, which is used to implement the intelligent operation and maintenance management method for a multi-node database according to any one of claims 1 to 9, characterized in that: The platform includes: Conflict prediction module: used to collect historical transaction execution logs of each node from a multi-node database and construct a historical transaction feature vector set; construct a transaction conflict prediction model based on the historical transaction feature vector set; and obtain the conflict prediction value of the real-time transaction based on the transaction conflict prediction model; Execution strategy generation module: Based on the conflict prediction value, a real-time transaction priority queue is formed, and the lock granularity of the real-time transaction is dynamically adjusted; the execution mode of the real-time transaction is determined by combining the real-time transaction priority queue and the lock granularity of the real-time transaction; Load regulation module: used to dynamically balance node loads; Execution monitoring module: executes real-time transactions and monitors the execution status of real-time transactions according to the real-time transaction priority queue, lock granularity and execution mode of the real-time transactions.

Images