Methods and equipment for SQL lock conflict pre-detection and intervention in GaussDBDWS database

Abstract

This invention discloses a method and device for pre-detection and intervention of SQL lock conflicts in GaussDB DWS database, relating to the field of distributed database operation and maintenance technology. The method includes: batch collecting SQL statements to be executed, sequentially completing data cleaning, syntax parsing, and risk level classification to generate a standardized queue of SQL statements to be detected; performing differentiated lock conflict pre-detection based on the risk level of each SQL statement and the locking mechanism of the GaussDB DWS cluster, and filtering out SQL statements that pass the pre-detection; executing the pre-detected SQL statements in the GaussDB DWS cluster, capturing execution anomalies in real time and identifying their types, and initiating an intervention process when a lock conflict anomaly is determined; using a three-level progressive hierarchical backoff strategy to handle lock conflicts, and performing secondary verification through PID association retrieval after handling, and completing the handling process after confirming that the lock conflict has been eliminated.

Description

Technical Field

[0001] This invention relates to the field of distributed database operation and maintenance technology, and in particular to a method and device for pre-detection and intervention of SQL lock conflicts in GaussDB DWS database. Background Technology

[0002] In batch SQL execution scenarios within the GaussDB DWS distributed database, lock conflicts caused by concurrent transactions are a common technical challenge leading to SQL execution blocking and business service interruptions. Existing solutions primarily rely on the concurrency control and lock management mechanisms provided by the database management system itself for passive handling, or require manual intervention by the database administrator to resolve lock conflicts. These solutions generally suffer from the following shortcomings: First, the intervention timing is delayed. Existing solutions typically only address the issue passively after lock conflicts have actually occurred and caused business blockages, failing to conduct targeted pre-detection before SQL execution. This results in conflicting SQL queries entering the execution queue, causing business requests to queue and execution timeouts. Second, the intervention methods are simplistic and lack finesse. They often employ a one-step operation of directly terminating the session without setting tiered handling strategies based on the severity of the conflict, easily leading to unnecessary business interruptions. Third, the processing is not real-time. Some log analysis-based solutions cannot achieve real-time detection and immediate intervention during the execution phase, making it difficult to meet the rapid response requirements of high-concurrency scenarios. Fourth, an automated closed loop is not formed. After the intervention operation is executed, there is a lack of a secondary verification mechanism for the intervention effect, making it impossible to promptly detect intervention failures caused by special circumstances such as process failure to respond, posing a risk of residual lock conflicts that continue to block subsequent business operations.

[0003] The root cause of the above shortcomings is that the existing technical solutions focus on the passive resolution of lock conflicts after the fact, and do not cover the entire closed loop of "pre-detection - in-process hierarchical intervention - post-verification". Furthermore, they do not fully adapt to the role characteristics and fine-grained scheduling requirements of CN and DN nodes in the GaussDB DWS distributed cluster, and are unable to meet the security and reliability requirements of batch SQL execution. Summary of the Invention

[0004] This invention provides a method and device for pre-detection and intervention of SQL lock conflicts in GaussDB DWS database, which solves the technical problems of lock conflict handling relying on passive post-event response, single intervention methods, and lack of closed-loop verification mechanism in batch SQL execution scenarios of GaussDB DWS distributed database.

[0005] On the one hand, this invention provides a method for pre-detection and intervention of SQL lock conflicts in GaussDB DWS database. The method includes the following steps: Step S1: Collect SQL statements to be executed in batches, and sequentially complete data cleaning, syntax parsing, and risk level classification to generate a standardized queue of SQL statements to be detected; Step S2: Perform differentiated lock conflict pre-detection based on the risk level of each SQL statement and the locking mechanism of the GaussDB DWS cluster, and filter out the SQL statements that pass the pre-detection; Step S3: Execute the SQL statements that pass the pre-detection in the GaussDB DWS cluster, capture execution anomalies in real time and perform type identification, and start the intervention process when it is determined to be a lock conflict anomaly; Step S4: Use a three-level progressive hierarchical backoff strategy to handle lock conflicts, and perform secondary verification through PID association retrieval after handling, and complete the handling process after confirming that the lock conflict has been eliminated.

[0006] In one implementation of the present invention, step S1 specifically includes: traversing a batch of SQL source files under a preset path, filtering blank lines, comment statements, and invalid statements with incomplete syntax through character matching logic, and sorting them into a queue of valid SQL statements; extracting keywords from each valid SQL statement, identifying the SQL operation type, and extracting the corresponding target table name and WHERE condition information; and classifying the statements into three levels: high risk, medium risk, and low risk, according to the lock conflict risk level corresponding to the SQL operation type.

[0007] In one implementation of the present invention, in step S2, if the SQL statement is of type SELECT or INSERT and is determined to be of low risk, it is directly marked as passing the pre-detection and no lock conflict detection is performed.

[0008] In one implementation of the present invention, in step S2, if the SQL statement is of type UPDATE or DELETE and is determined to be of medium risk, then a row lock pre-detection is performed: a tentative detection statement is dynamically constructed based on the target table name and WHERE condition, and sent to the cluster for execution; if the tentative statement is executed successfully, the pre-detection is determined to be passed, and the statement is immediately committed or rolled back to release the tentative lock; if an execution error occurs, it is determined that there is a row lock conflict, and the pre-detection fails.

[0009] In one implementation of the present invention, in step S2, if the SQL statement is of DDL type and is determined to be high risk, then a table lock pre-detection is performed: query the lock conflict system view of GaussDB DWS, filter records whose locked object name matches the target table name and whose granted lock status field has a value of t; if a matching record exists, the pre-detection is determined to have failed, and the pre-detection is retried after a preset time interval; if the retry still fails, the execution of the SQL statement is skipped, and the failure status is written to the error file.

[0010] In one implementation of the present invention, step S3 specifically includes: the CN node distributes the SQL statement to the corresponding DN node for execution, and captures the status information returned by the database in real time; when the execution fails, the error code and error description in the exception information are extracted; when the error code is deadlock detection, lock timeout, or the error description contains lock conflict related keywords, it is determined to be a lock conflict exception and the intervention process is started; other exceptions are determined to be non-lock conflict exceptions, the error information is directly recorded and the current statement execution is skipped.

[0011] In one implementation of the present invention, the three-level progressive hierarchical backoff strategy in step S4 is arranged in order of increasing intervention intensity as follows: Level 1 soft interrupt cancellation, Level 2 soft interrupt retry, and Level 3 hard interrupt forced termination; after the previous level of intervention fails, it automatically escalates to the next level of intervention.

[0012] In one implementation of the present invention, both the first-level soft interrupt cancellation and the second-level soft interrupt retry call the pg_cancel_backend function, which terminates the current SQL execution by sending a SIGINT signal to the backend process corresponding to the target PID, while retaining the session connection without destroying it; the second-level soft interrupt retry waits for a preset interval after the first-level soft interrupt cancellation before executing; the third-level hard interrupt forced termination calls the hard interrupt function pg_terminate_backend to send a SIGTERM termination signal to the target process.

[0013] In one implementation of the present invention, the PID association secondary verification in step S4 specifically involves: retrieving the lock conflict record corresponding to the PID of the current intervention target, and assigning the result to a preset verification variable; if the verification variable returns an empty value, it is determined that the lock conflict has been eliminated, the intervention result is output, and subsequent SQL statements are executed; if the return value is not empty, it is determined that the intervention has not been fully effective, the manual inspection process is triggered, and the exception is recorded.

[0014] On the other hand, the present invention also provides an SQL lock conflict pre-detection and intervention device for GaussDB DWS database, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to complete the aforementioned SQL lock conflict pre-detection and intervention method for GaussDB DWS database.

[0015] The method and device for pre-detection and intervention of SQL lock conflicts in GaussDB DWS database provided by this invention have the following beneficial effects: 1. This invention introduces a differentiated lock conflict pre-detection mechanism based on SQL type before execution. It identifies the risk level according to the statement operation type and performs targeted exploratory pre-detection on different risk statements. It proactively discovers potential lock conflicts without occupying actual lock resources, reducing business queuing and execution timeouts caused by lock waiting. This pre-emptive prevention capability is not available in existing reactive post-event handling solutions.

[0016] 2. This invention constructs a three-level progressive layered backoff strategy of "soft interrupt cancellation - soft interrupt retry - hard interrupt forced termination" in the intervention stage. Combined with the database's built-in function call logic to adapt to the fine-grained scheduling of cluster nodes, the intervention intensity is matched with the severity of the conflict. This eliminates lock conflicts while avoiding the impact on normal business sessions and improves the overall intervention success rate. Existing technologies do not provide such a layered, progressive, and software-hardware combined automated intervention method.

[0017] 3. This invention adds a secondary verification mechanism. By retrieving PID information and verifying variable assignment, it identifies whether there are still unresolved lock conflicts. When an unresolved conflict is detected, manual verification is triggered, reducing the impact of repeated conflict triggers on database stability. The above three stages—pre-detection, tiered intervention, and secondary verification—are organically linked to form a fully automated closed loop covering "pre-detection—tiered intervention during the process—post-verification," which helps shorten lock conflict handling time, reduce reliance on manual intervention, and improve the automation level of database operation and maintenance and the efficiency of batch SQL execution. Attached Figure Description

[0018] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this invention, illustrate exemplary embodiments of the invention and are used to explain the invention, but do not constitute an undue limitation of the invention. In the drawings: Figure 1 This is a flowchart of the SQL lock conflict pre-detection and intervention method for the GaussDB DWS database provided in an embodiment of the present invention; Figure 2 This is a schematic diagram of a pre-detection and intervention device for SQL lock conflicts in the GaussDB DWS database provided in an embodiment of the present invention. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0020] This invention addresses the shortcomings of GaussDB DWS distributed database's batch SQL execution scenarios, where lock conflict handling relies on passive post-event responses, employs limited intervention methods, and lacks a closed-loop verification mechanism. It adopts a full-process technical solution of "pre-event detection—in-event hierarchical intervention—post-event verification," utilizing differentiated pre-event detection, three-level hierarchical backoff intervention, and PID association verification, combined with the characteristics of GaussDB DWS cluster CN and DN nodes, to achieve precise and efficient handling of lock conflicts. The core technical means of this solution include: SQL statement parsing and type identification, differentiated lock conflict pre-event detection, hierarchical lock conflict backoff intervention, and post-intervention PID secondary verification, forming a complete technical closed loop. The specific technical methods are detailed below, fully adapting to the architectural characteristics of the GaussDB DWS distributed database, balancing technical feasibility and operational practicality. Specifically, this invention provides a method and device for pre-detecting and intervening in SQL lock conflicts in GaussDB DWS databases. The technical solution proposed in this invention is described in detail below with reference to the accompanying drawings.

[0021] Figure 1 This is a flowchart illustrating the SQL lock conflict pre-detection and intervention method for the GaussDB DWS database provided in an embodiment of the present invention. Figure 1 As shown, the method mainly includes the following steps: Step S1: Collect SQL statements to be executed in batches, and complete data cleaning, syntax parsing and risk level classification in sequence to generate a standardized queue of SQL statements to be tested; Step S2: Based on the risk level of each SQL statement, and in conjunction with the locking mechanism of the GaussDB DWS cluster, perform differentiated lock conflict pre-detection to filter out the SQL statements that pass the pre-detection. Step S3: Execute the pre-detected SQL statement in the GaussDB DWS cluster, capture execution exceptions in real time and identify their types. When a lock conflict exception is identified, the intervention process is initiated. Step S4: Use a three-level progressive hierarchical backoff strategy to handle lock conflicts. After handling, perform a secondary verification by PID association retrieval. Once the lock conflict is confirmed to be eliminated, the handling process is completed.

[0022] Furthermore, in executing the SQL lock conflict pre-detection and intervention method for the GaussDB DWS database, this embodiment of the invention specifically implements the following process: First, SQL statement parsing and type identification are performed. Precise SQL parsing and type identification technology is used to complete data cleaning, syntax feature extraction, and risk classification for batch SQL statements. This provides standardized input for subsequent differentiated lock conflict pre-detection and tiered intervention, adapting to the lightweight execution requirements of distributed database operation and maintenance. The specific implementation logic is as follows: 1. Batch preprocessing and cleaning of source files: Traverse batches of SQL source files under preset paths, reading and cleaning the file content line by line. Through character matching and filtering logic, blank lines, comment statements, and invalid statements with incomplete syntax are removed. Valid SQL statements that meet the requirements are organized into an ordered queue to ensure the accuracy of subsequent parsing and detection processes.

[0023] 2. SQL Statement Syntax Parsing: Based on string matching and keyword extraction techniques, this function simulates SQL syntax parsing logic and extracts core features from each valid SQL statement. It distinguishes between five major SQL types—SELECT, INSERT, UPDATE, DELETE, and DDL (CREATE, ALTER, DROP, etc.)—by identifying the first keyword of the statement; and extracts key information such as the target table name and WHERE conditions through keyword location.

[0024] 3. SQL Statement Risk Classification: Based on the parsed and extracted SQL type and operation characteristics, each SQL statement is automatically classified into three levels: high risk, medium risk, and low risk. Specifically, DDL statements such as DROP and ALTER are marked as high risk (prone to table locks); statements such as UPDATE and DELETE are marked as medium risk (prone to row locks); and SELECT and INSERT statements are marked as low risk (involving only a small number of tuples, with low risk of lock conflicts).

[0025] Furthermore, a differentiated lock conflict pre-detection technology is adopted. For SQL statements with different risk levels, combined with the locking mechanism of GaussDB DWS, targeted exploratory detection is implemented to identify and avoid lock conflict risks in advance. The specific implementation steps are as follows: 1. Pre-detection environment initialization: Load the environment variables of the GaussDB DWS database, configure the database cluster connection information, including the database connection address, database username, database name, etc., and establish a connection with the CN node through the JDBC driver. Simultaneously, initialize pre-detection parameters, including the tentative pre-detection interval, etc.

[0026] 2. Implementation of Differentiated Pre-detection Strategy: Different pre-detection methods are adopted according to the risk level of the SQL statement. The specific control logic is as follows: (1) If the SQL type is SELECT or INSERT, it is determined to be a low-risk statement, and the lock conflict detection is skipped and it is directly marked as passing the pre-detection. The technical basis is that: under the default isolation level, the SELECT statement only requests a shared lock, and the INSERT statement operates on new data rows. The two usually do not cause lock conflicts with other statements.

[0027] (2) If the SQL type is UPDATE or DELETE, it is determined to be a medium-risk statement, and row lock detection is performed. The specific detection method is as follows: Based on the extracted target table name and WHERE condition, a tentative detection statement is dynamically constructed, and its syntax is "SELECT * FROM target table name WHERE condition FOR UPDATE NOWAIT"; the detection statement is sent to the database cluster for execution; where the FOR UPDATE clause is used to request an exclusive row lock on the query result row, and the NOWAIT keyword indicates that the database will not enter the waiting queue and will directly return an error if it cannot immediately acquire the lock. If the detection statement is executed successfully, it means that the target data row is not currently held by other sessions with incompatible locks, the pre-detection is passed, and the lock generated during the probing process is immediately committed or rolled back; if the execution reports an error, it means that the target data row has been locked by other sessions, there is a row lock conflict, and the pre-detection fails.

[0028] (3) If the SQL type is a DDL statement such as ALTER, CREATE TABLE, DROP TABLE, TRUNCATE, VACUUM, etc., it is determined to be a high-risk statement and a table lock detection is performed. The specific detection method is as follows: query the lock conflict system view of the database cluster, and set the query condition to the locked object name matching the target table name and the value of the granted field of the lock holding status being t; if the query returns at least one record, it means that there is currently a lock granted on the target table and the pre-detection fails; if the query result is empty, the pre-detection passes.

[0029] 3. Pre-detection result processing: SQL statements that pass the pre-detection are awaited for subsequent execution; SQL statements that fail the pre-detection are re-executed after a preset 30-second time interval. If the re-pre-detection results in a pass, the pre-detection result file is output, and the process proceeds to the next execution step; if the re-pre-detection still fails, the execution of the SQL statement is skipped, and the pre-detection failure status is written to an error file. This avoids the entire batch process being blocked due to the pre-detection failure of a single statement, overcoming the shortcomings of existing technologies that lack a retry mechanism.

[0030] Furthermore, through anomaly classification and handling technology, lock conflict anomalies and non-lock conflict anomalies are accurately identified, and differentiated handling is implemented. The specific implementation steps are as follows: 1. Target SQL Execution: SQL statements that pass the pre-detection are distributed by the CN node to the corresponding DN node. After the CN node establishes a connection with the DN, it sends a request. After receiving the SQL statement, the DN node parses, optimizes, and executes the SQL statement according to local rules. During the execution process, the status information returned by the database is captured in real time.

[0031] 2. Execution result judgment: If there are no exceptions in the execution process and the result is returned successfully, the execution status is written to the execution result file and the processing flow of the next SQL statement is entered; if an exception message returned by the database is captured during the execution process, it is determined that the execution has failed and the exception classification and handling process is immediately triggered.

[0032] 3. Exception Information Parsing and Classification: Extract the error codes and error descriptions from the exception information, and determine the exception type according to preset exception classification rules. The specific classification logic is as follows: If the error code is "40P01" (deadlock detection) or "55P03" (lock timeout), or the error description contains keywords such as "lock conflict" or "lock wait timeout", it is determined to be a lock conflict exception; if it is another error code (such as syntax error, insufficient permissions, data not found, etc.), it is determined to be a non-lock conflict exception.

[0033] 4. Differentiated handling of exceptions: Different handling strategies are implemented for different types of exceptions: (1) Non-lock conflict exceptions: There is no need to start the lock conflict intervention process. The SQL statement, exception type and error description are directly written to the error file, the subsequent execution of this statement is skipped, and the next SQL statement is executed; (2) Lock conflict exceptions: The subsequent lock conflict hierarchical backoff intervention process is started immediately.

[0034] Furthermore, a three-tiered backoff intervention strategy is adopted, combined with GaussDB DWS built-in functions, to adapt to the fine-grained scheduling of CN and DN nodes, so as to match the intervention intensity with the severity of the conflict. The specific implementation steps are as follows: 1. Lock Conflict Information Collection and Location: After initiating the intervention process, the database environment variables are first loaded. A connection to the database is established using the specified database user, data connection address, and database name. The lock conflict system view is queried, and the query results are output to a temporary file, including the target node name (CN or DN), query statement ID, query statement, PID, and GRANTED status (t for acquired, f for waiting). At the same time, the target SQL statement and its associated statements that have lock conflicts are clearly marked, accurately locating the source and scope of the lock conflict.

[0035] 2. Target PID Extraction and Verification: Based on the retrieved lock conflict information, filter out PIDs with a GRANTED state of f (waiting for lock), i.e., the PIDs corresponding to SQL statements that do not hold locks. Then, verify that this PID matches the PID identifier of the target SQL statement to improve the security of the intervention operation. After successful verification, extract the node name parameter corresponding to the PID to determine the target node for the intervention operation.

[0036] 3. Implementation of a three-tiered, graded avoidance intervention: (1) Level 1 Intervention (Soft Interrupt Cancellation): After receiving the target PID and node name parameters, the CN node looks up the IP address and port information of the target node through the system table, establishes a connection with the target node using the internal connection pool, sends an SQL command to terminate the target PID, and first calls the soft interrupt function pg_cancel_backend(PID). This function uses the operating system's native system call kill(pid, SIGINT) at the underlying level. The kernel locates the target database backend process based on the passed PID and sends a SIGINT interrupt signal to the target process. After the target backend process captures the signal, it triggers the preset signal handling process, terminates the currently executing SQL query, immediately stops resource occupation, rolls back the current uncommitted transaction context, releases the held lock, and the process itself does not exit, destroy, or disconnect the connection. It only cancels the current task and keeps the session available. If the pg_cancel_backend function returns t (true), it means that the Level 1 intervention is successful and enters the subsequent secondary verification process; if the return value is f (false), it means that the target process is blocked or cannot respond and enters the Level 2 intervention stage.

[0037] (2) Second-level intervention (soft interrupt retry): After the first-level intervention fails, wait for a 10-second time interval (to avoid invalid retry due to signal blocking), and call the pg_cancel_backend(PID) function again to repeat the execution logic of the first-level intervention. If the return value is t this time, it means that the second-level intervention is successful and enters the second verification process; if the return value is still f, it is determined that the soft interrupt intervention is invalid, the intervention level is upgraded, and the third-level intervention stage is entered.

[0038] (3) Level 3 Intervention (Hard Interrupt Forced Termination): The hard interrupt function pg_terminate_backend(PID) is called. This function triggers the operating system system call kill(pid, SIGTERM) at the underlying level. The kernel locates the target backend service process based on the PID and sends a SIGTERM termination signal to the target process. After receiving the signal, the target backend process forcibly terminates the execution of the current target SQL, immediately rolls back the uncommitted complete transaction, releases all held lock resources, closes the current database session connection, and exits the backend process. After the process completes resource cleanup, it completely exits and releases the PID. If the return value is t, it means that the level 3 intervention is successful and enters the secondary verification process; if the return value is f, it means that the forced termination has failed, an alarm message is generated and recorded in the error file, and a manual intervention process is triggered to form a fallback mechanism.

[0039] Finally, a secondary lock conflict verification mechanism is added. Through PID association retrieval and variable assignment verification, the lock conflict is completely eliminated. The specific implementation steps are as follows: 1. PID Association Retrieval: Execute the target PID query statement from the previous step to retrieve the PID information of lock conflicts associated with this intervention. The target PID is the PID of the target SQL statement for this intervention. Assign the query results to a preset validation variable. If the query result is empty, the variable returns an empty value; if the query result contains records, the variable returns a list of the corresponding PIDs.

[0040] 2. Verification Result Judgment and Handling: Based on the return value of the verification variable, determine whether the lock conflict has been completely eliminated: (1) If the variable returns an empty value, it means that the secondary verification has passed, and there are no lock conflict statements related to the execution of the SQL in the current database cluster. Output the intervention result file, and the subsequent SQL statements can continue to be executed; (2) If the variable returns a non-empty value, it means that the lock conflict has not been completely eliminated, and there are still related lock conflict execution statements. At this time, the abnormal detection result is output to the error file, triggering the manual inspection process for further handling. At the same time, the error file is output and the target SQL execution is skipped to avoid repeated triggering of lock conflicts and ensure the stability of the database system. This secondary verification mechanism can avoid business blocking problems caused by incomplete lock conflict intervention and improve the overall security and operational reliability of batch SQL statement execution.

[0041] The above describes the method for pre-detecting and intervening in SQL lock conflicts of the GaussDB DWS database provided by embodiments of the present invention. Based on the same inventive concept, embodiments of the present invention also provide a device for pre-detecting and intervening in SQL lock conflicts of the GaussDB DWS database. Figure 2 This is a schematic diagram of an SQL lock conflict pre-detection and intervention device for a GaussDB DWS database provided in an embodiment of the present invention, as shown below. Figure 2 As shown, the device mainly includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor; wherein the memory 202 stores instructions that can be executed by the at least one processor 201, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to complete the aforementioned method for pre-detection and intervention of SQL lock conflicts in the GaussDB DWS database.

[0042] The various embodiments in this invention are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0043] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0044] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A method for SQL lock conflict pre-detection and intervention of GaussDB DWS database, characterized in that, The method includes the following steps: Step S1: Collect SQL statements to be executed in batches, and complete data cleaning, syntax parsing and risk level classification in sequence to generate a standardized queue of SQL statements to be tested; Step S2: Based on the risk level of each SQL statement, and in conjunction with the locking mechanism of the GaussDBDWS cluster, perform differentiated lock conflict pre-detection to filter out SQL statements that pass the pre-detection. If the SQL statement is of type SELECT or INSERT and is determined to be low-risk, it is directly marked as passing the pre-detection, and no lock conflict detection is performed. If the SQL statement is of type UPDATE or DELETE and is determined to be medium-risk, row lock pre-detection is performed: a tentative detection statement is dynamically constructed based on the target table name and WHERE condition and sent to the cluster for execution. If the tentative statement executes successfully, the pre-detection is determined to be passing, and the statement is immediately committed or rolled back to release the tentative lock. If an error occurs, a row lock conflict is determined to exist, and the pre-detection fails. If the SQL statement is of type DDL and is determined to be high-risk, table lock pre-detection is performed: a query is performed on the GaussDB database. The DWS lock conflict system view filters records where the locked object name matches the target table name and the value of the granted field of the lock holding status is 't'. If a matching record exists, the pre-detection is deemed to have failed, and the pre-detection is retried after a preset time interval. If the retry still fails, the execution of the SQL statement is skipped, and the failure status is written to the error file. Step S3: Execute the pre-detected SQL statement in the GaussDB DWS cluster, capture execution exceptions in real time and identify their types. When a lock conflict exception is identified, the intervention process is initiated. Step S4: A three-level progressive backoff strategy is adopted to handle lock conflicts. After handling, a secondary verification is performed through PID association retrieval. Once the lock conflict is confirmed to be eliminated, the handling process is completed. The three-level progressive backoff strategy is as follows, from low to high intervention intensity: Level 1 soft interrupt cancellation, Level 2 soft interrupt retry, and Level 3 hard interrupt forced termination. If the previous level of intervention fails, it will automatically escalate to the next level of intervention. The lock conflict record corresponding to the PID of the current intervention target is retrieved, and the result is assigned to a preset verification variable. If the verification variable returns an empty value, it is determined that the lock conflict has been eliminated, the intervention result is output, and subsequent SQL statements are executed. If the return value is not empty, it is determined that the intervention has not been fully effective, the manual verification process is triggered, and the exception is recorded.

2. The SQL lock conflict pre-detection and intervention method of GaussDB DWS database according to claim 1, characterized in that, Step S1 specifically includes: Iterate through a batch of SQL source files under a preset path, filter out blank lines, comment statements and invalid statements with incomplete syntax through character matching logic, and organize them into a queue of valid SQL statements. Extract keywords from each valid SQL statement, identify the SQL operation type, and extract the corresponding target table name and WHERE condition information; Based on the lock conflict risk level corresponding to the SQL operation type, statements are divided into three levels: high risk, medium risk, and low risk.

3. The SQL lock conflict pre-detection and intervention method of GaussDB DWS database according to claim 1, characterized in that, Step S3 specifically includes: The CN node distributes SQL statements to the corresponding DN node for execution, and captures the status information returned by the database in real time. When execution fails, the error code and error description are extracted from the exception information. When the error code is deadlock detection, lock timeout, or the error description contains lock conflict related keywords, it is determined to be a lock conflict exception and the intervention process is initiated; other exceptions are determined to be non-lock conflict exceptions, the error information is directly recorded and the current statement execution is skipped.

4. The SQL lock conflict pre-detection and intervention method of GaussDB DWS database according to claim 1, characterized in that, Both the first-level soft interrupt cancellation and the second-level soft interrupt retry call the pg_cancel_backend function, which terminates the current SQL execution by sending a SIGINT signal to the backend process corresponding to the target PID, while preserving the session connection without destroying it; the second-level soft interrupt retry waits for a preset interval after the first-level soft interrupt cancellation before executing; the third-level hard interrupt forced termination calls the hard interrupt function pg_terminate_backend to send a SIGTERM termination signal to the target process.

5. A device for pre-detection and intervention of SQL lock conflicts of a GaussDB DWS database, characterized in that, The device includes: at least one processor; and, A memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform the SQL lock conflict pre-detection and intervention method for the GaussDB DWS database as described in any one of claims 1-4.

Images