Structured query statement blocking detection method and device, equipment, readable storage medium and program product

Abstract

The invention relates to a blocking detection method and device for a structured query statement, equipment, a readable storage medium and a program product. A first thread identifier and a second thread identifier are determined according to an active service table of a target database, a first service identifier of a first service requesting a lock and a second service identifier of a second service holding the lock, and a first structured query statement and a second structured query statement corresponding to the first thread identifier and the second thread identifier respectively are used for querying the first structured query statement and the second structured query statement. And structuring the time corresponding relationship between the query statements to obtain a blocking detection result. Compared with a traditional method of searching according to manual experience, the method has the advantages that the first thread identifier and the second thread identifier are determined by using the first service identifier of the request lock and the second service identifier of the holding lock, each structured query statement is determined by using each thread identifier, and the blocking detection result is determined by combining the corresponding relationship between the query statements; and the blocking detection efficiency of the structured query statement is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a blocking detection method and device of structured query language, computer equipment, computer readable storage medium and computer program product. BACKGROUND

[0002] With the development of computer technology, database technology appears. Business data can be stored in a database, and when business data in the database is operated (such as adding, deleting, inquiring or modifying), a structured query language (SQL) is used to perform corresponding operations. When structured query languages of multiple businesses are concurrently executed, a blocking structured query language is generated, which causes subsequent transactions to be in a waiting state and unable to continue to execute. In order to ensure the smooth execution of the structured query language, the blocking structured query language needs to be detected in time. At present, the detection of the blocking structured query language is usually performed by manual analysis and search according to experience. However, the manual analysis and search according to experience take a long time, and it is inconvenient to quickly locate and solve the blocking of the structured query language.

[0003] Therefore, the current detection method of the blocking structured query language has the defect of low detection efficiency. SUMMARY

[0004] Therefore, it is necessary to provide a blocking detection method, device, computer equipment, computer readable storage medium and computer program product of a structured query language, which can improve the detection efficiency.

[0005] In a first aspect, the present application provides a blocking detection method of a structured query language, comprising:

[0006] When it is detected that a blocking detection condition is met, a first business identifier of a first business requesting a lock and a second business identifier of a second business holding the lock in a target database are acquired;

[0007] According to an active business table of the target database, the first business identifier and the second business identifier, a first thread identifier corresponding to the first business and a second thread identifier corresponding to the second business are determined; the active business table comprises thread identifiers of each business in an active state;

[0008] According to the first thread identifier and the second thread identifier, a first structured query language corresponding to the first business and a second structured query language corresponding to the second business are respectively determined;

[0009] According to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and a time correspondence relationship between the first structured query statement and the second structured query statement, a structured query statement blocking detection result is obtained.

[0010] In a second aspect, the present application further provides a structured query statement blocking detection device, comprising:

[0011] The acquisition module is configured to, when it is detected that the blocking detection condition is met, acquire a first service identifier of a first service requesting a lock and a second service identifier of a second service holding the lock in a target database.

[0012] The first determination module is configured to determine a first thread identifier corresponding to the first service and a second thread identifier corresponding to the second service according to an active service table of the target database, the first service identifier, and the second service identifier; the active service table comprises thread identifiers of each service in an active state.

[0013] The second determination module is configured to determine a first structured query statement corresponding to the first service and a second structured query statement corresponding to the second service according to the first thread identifier and the second thread identifier, respectively.

[0014] The detection module is configured to obtain a structured query statement blocking detection result according to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and a time correspondence relationship between the first structured query statement and the second structured query statement.

[0015] In a third aspect, the present application further provides a computer device, comprising a memory and a processor, the memory stores a computer program, and the processor implements the steps of the above method when executing the computer program.

[0016] In a fourth aspect, the present application further provides a computer readable storage medium, which stores a computer program, and the computer program implements the steps of the above method when executed by a processor.

[0017] In a fifth aspect, the present application further provides a computer program product, comprising a computer program, and the computer program implements the steps of the above method when executed by a processor.

[0018] The blocking detection method, device, computer device, computer readable storage medium and computer program product of the structured query statement provided by the embodiments of the present application can determine the first thread identifier corresponding to the first service and the second thread identifier of the second service according to the active service table of the target database, the first service identifier of the first service requesting a lock and the second service identifier of the second service holding a lock when it is detected that the blocking detection condition is met, and obtain the blocking detection result based on the first structured query statement and the second structured query statement corresponding to the first thread identifier and the second thread identifier respectively and the time corresponding relationship between the structured query statements. Compared with the traditional manual experience searching, the first thread identifier and the second thread identifier are determined by using the first service identifier of the first service requesting a lock and the second service identifier of the second service holding a lock, the first structured query statement and the second structured query statement are determined by using each thread identifier respectively, the blocking detection result is determined in combination with the corresponding relationship between the query statements, and the efficiency of the blocking detection of the structured query statement is improved. BRIEF DESCRIPTION OF DRAWINGS

[0019] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the related art, the drawings needed to be used in the embodiments of the present application or the related art description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other related drawings can be obtained by those skilled in the art without creative labor.

[0020] Figure 1 The flowchart of the blocking detection method of the structured query statement in an embodiment;

[0021] Figure 2 The flowchart of the blocking detection method of the structured query statement in another embodiment;

[0022] Figure 3 The interface diagram of the blocking detection result in an embodiment;

[0023] Figure 4 The structural block diagram of the blocking detection device of the structured query statement in an embodiment;

[0024] Figure 5 The internal structure diagram of the computer device in an embodiment. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical solutions and advantages of the present application more clear, the present application will be further described in detail below with reference to the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and not to limit the present application.

[0026] It should be noted that the terms "first", "second", etc. used in the present application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "include" and "have" and any variations thereof used in the present application are intended to cover non-exclusive inclusion. The term "a plurality of" used in the present application means two or more. The term "and / or" used in the present application means one of the options or any combination of the options.

[0027] In the related art, business data can be stored in a database. When operating the business data in the database (such as adding, deleting, querying, and modifying), a structured query statement needs to be used to perform the corresponding operation. When multiple structured query statements of business are executed concurrently, a blocked structured query statement can be generated, causing the subsequent business to be in a waiting state and unable to continue execution. To ensure the smooth execution of the structured query statement, the blocked structured query statement needs to be detected in time.

[0028] The troubleshooting and monitoring of the database structured query statement blocking mainly uses the following scheme: using a custom script to connect to the database for querying at regular intervals and using manual analysis; through the connected link, the state table of the database system is queried by executing the structured query statement to find the currently blocked structured query statement; through an alarm component or other components, alarm information is sent to the corresponding processing personnel. However, this scheme has the following problems: the data information is relatively scattered, too dependent on the experience of the operation and maintenance personnel for processing, and the source of the blocked structured query statement cannot be visually viewed, the analysis and troubleshooting time is long, and it is not convenient for quickly positioning and solving the structured query statement blocking fault problem; the existing data cannot be converted into time series monitoring data, and the collected data cannot be stored in the time series database; since the historical blocking link data is not persistently stored, the historical system blocking situation cannot be traced back, and it is not convenient for fault review.

[0029] Based on this, the present application realizes the regular collection and monitoring of the blocked structured query statement in the database system through a monitoring component; by converting the obtained monitoring information into time series monitoring data, the real-time monitoring and alarm efficiency can be improved; by displaying the corresponding logical blocking relationship of the time series monitoring data on the monitoring interface, a relationship topology graph of the blocked structured query statement is constructed, which helps the operation and maintenance personnel to quickly locate the blocking source in an intuitive way, and the efficiency is much higher than that of the traditional troubleshooting and analysis method.

[0030] In one embodiment, as Figure 1As shown, a blocking detection method of structured query statement is provided, and the embodiment is exemplified by the method applied to a server. It can be understood that the method can also be applied to a terminal, and can also be applied to a system including a terminal and a server, and is implemented through the interaction of the terminal and the server, including the following steps S202 to S208. Among them:

[0031] In step S202, when it is detected that the blocking detection condition is met, the first service identifier of the first service requesting a lock and the second service identifier of the second service holding the lock in the target database are obtained.

[0032] Among them, the server can be deployed with a database management system, for example, a target database. In the database management system, there are cases of blocking structured query statements, which means that in the database management system, the access of one service to data resources hinders the access of other services to the same resources, causing the latter services to be in a waiting state and unable to continue execution until the first processed service is executed. The blocking of the structured query statement of the database usually occurs in a multi-concurrent environment where multiple services compete for the same resource, and part of the services are blocked, affecting the overall performance and data consistency of the database. In order to quickly identify and locate the link and source of the blocked structured query statement, continuously track the performance indicators of the database, and identify the source structured query statement that may cause performance degradation or failure, the server can perform blocking detection on the structured query statement.

[0033] The server can detect whether there is a blocking structured query statement in the target database when a blocking detection condition is met. The blocking condition includes but is not limited to timed detection, instruction-based detection, and detection when a blocking alarm exists. For example, the server can scan the system state table of the target database at a fixed time, or scan the system state table based on an externally triggered instruction, or scan the system state table when a blocking alarm is detected, so that the server can obtain the first business identifier of the first business requesting a lock and the second business identifier of the second business holding a lock in the target database. The first business requesting a lock can be a business that needs to operate on a certain data table and thus needs to request a lock for the data table to implement the operation on the data table. The second business holding a lock can be a business that is currently operating on a certain data table with a lock. The first business and the second business can each have one or more. The server can be provided with a monitoring component, which can be a component for collecting different system and service monitoring data, and the monitoring component has monitoring logic for blocking structured query statements. The server can collect information about the blocking database structured query statement through the monitoring component and convert it into time-series data with time characteristics, and also output it to the Metric index for analysis at a fixed time. The Metric index refers to a standard or measurement method for quantitatively evaluating a specific object or process.

[0034] The target database includes a system table, which includes various parameters of the target database running at the time. The server can obtain the various identifiers from the target database system table. For example, the server can obtain the first business identifier of the first business requesting a lock and the second business identifier of the second business holding a lock in the target database from the lock waiting association table of the target database. The lock waiting association table stores the correspondence between the business identifier of the business requesting a lock and the business identifier holding a lock.

[0035] Step S204, according to the active business table of the target database, the first business identifier and the second business identifier, determine the first thread identifier corresponding to the first business and the second thread identifier corresponding to the second business; the active business table includes the thread identifier of each business in an active state.

[0036] The system table of the target database further includes an active business table. The active business table includes thread identifiers of each business in an active state. The first business identifier and the second business identifier can be a business identifier (Identity document, ID) of the first business and a business identifier of the second business, respectively. The server can determine a first thread identifier corresponding to the first business and a second thread identifier corresponding to the second business according to the active business table of the target database, the first business identifier, and the second business identifier. For example, the server finds the first thread identifier associated with the first business identifier from the active business table based on the first business identifier, and finds the second thread identifier associated with the second business identifier from the active business table based on the second business identifier.

[0037] In step S206, a first structured query statement corresponding to the first business and a second structured query statement corresponding to the second business are determined according to the first thread identifier and the second thread identifier, respectively.

[0038] The structured query statement being executed by the target database can be recorded in the business table. The server can determine the first structured query statement corresponding to the first business based on the first thread identifier, and determine the second structured query statement corresponding to the second business based on the second thread identifier. For example, the server finds the first structured query statement corresponding to the first business identifier from the business table based on the first thread identifier, and finds the second structured query statement corresponding to the second business identifier from the business table based on the second thread identifier.

[0039] In step S208, a blocking detection result of the structured query statement is obtained according to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and a time corresponding relationship between the first structured query statement and the second structured query statement.

[0040] The first service and the second service can each have one or more. The request time or holding time of each service to the lock can be different. To analyze the source of the structured query statement causing the blocking, the server can construct a time correspondence relationship between the structured query statements of each service, such as a time correspondence relationship between the first structured query statement and the second structured query statement. The time correspondence relationship can be obtained based on the service start time of each structured query statement. The server can obtain a blocking detection result of the structured query statement according to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence relationship between the first structured query statement and the second structured query statement. For example, the server determines the order in which each service starts according to the time correspondence relationship, and determines the blocking relationship between each service according to whether each service belongs to a lock request or a lock holding, and sorts the blocking relationship in time order, so that the server can obtain the blocking detection result of the structured query statement. The server can determine the structured query statement causing the blocking based on the blocking detection result.

[0041] In the blocking detection method of the structured query statement, when the blocking detection condition is detected, the first thread identifier corresponding to the first service and the second thread identifier of the second service are determined according to the active service table of the target database, the first service identifier of the first service requesting the lock in the target database, and the second service identifier of the second service holding the lock. The blocking detection result is obtained based on the first structured query statement and the second structured query statement corresponding to the first thread identifier and the second thread identifier respectively, and the time correspondence relationship between the structured query statements. Compared with the traditional manual experience searching, the first thread identifier and the second thread identifier are determined by using the first service identifier requesting the lock and the second service identifier holding the lock, the first structured query statement and the second structured query statement are determined by using each thread identifier, the blocking detection result is determined by combining the correspondence relationship between the query statements, and the efficiency of the blocking detection of the structured query statement is improved.

[0042] In one embodiment, according to the active service table of the target database, the first service identifier, and the second service identifier, the first thread identifier corresponding to the first service and the second thread identifier corresponding to the second service are determined, including: querying the active service table according to the first service identifier to obtain the first thread identifier corresponding to the first service identifier; and querying the active service table according to the second service identifier to obtain the second thread identifier corresponding to the second service identifier.

[0043] In this embodiment, the server can obtain the thread identifiers corresponding to the respective services based on the active service table. For example, the server can query the active service table according to the first service identifier to obtain the first thread identifier corresponding to the first service identifier. The server can query the active service table according to the second service identifier to obtain the second thread identifier corresponding to the second service identifier. The server can pre-associate the identifier of the service requesting a lock in the active service table with the blocked service. The blocked service indicates a service that wants to take a lock but fails to take the lock and is in a waiting state. The server can also pre-associate the identifier of the service holding a lock in the active service table with the blocker service. The blocker service indicates a service that holds a lock and causes other services to wait. After determining the first service identifier of the first service and the second service identifier of the second service, the server can determine the first thread identifier corresponding to the first service and the second thread identifier corresponding to the second service from the active service table.

[0044] Through this embodiment, the server can determine the thread identifiers corresponding to the first service and the second service respectively based on the active service table, the first service identifier, and the second service identifier. Thus, the server can use the respective thread identifiers to analyze the blocked structured query language statement, thereby improving the efficiency of the analysis of the blocked structured query language statement.

[0045] In one embodiment, determining the first structured query language statement corresponding to the first service and the second structured query language statement corresponding to the second service according to the first thread identifier and the second thread identifier includes: extracting the currently executed structured query language statement corresponding to the first service from the active service table according to the first thread identifier to obtain the first structured query language statement; and determining the second structured query language statement corresponding to the second service from the performance mode table of the target database according to the process identifier corresponding to the second thread identifier. The performance mode table includes respective currently executed structured query language statements.

[0046] In this embodiment, the active service table also records the structured query language statement corresponding to the first service. Thus, the server can extract the currently executed structured query language statement corresponding to the first service from the active service table according to the first thread identifier to obtain the first structured query language statement.

[0047] The target database further includes a performance mode table. The performance mode table includes various structured query statements that are currently being executed, and the various structured query statements correspond to different processes. The second thread identifier can be a thread identifier of the second service that holds the lock. That is, the second service is currently being executed. The server can determine a process identifier corresponding to the second service based on the second thread identifier. Thus, the server determines the second structured query statement corresponding to the second service from the performance mode table of the target database based on the process identifier corresponding to the second thread identifier. The server can previously associate the thread, the process, and the table in which the corresponding currently executed structured query statement of the service are located.

[0048] Through this embodiment, the server can determine the first structured query statement in combination with the active service table, and determine the second structured query statement in combination with the process identifier and the performance mode table. Thus, the server can more accurately extract the structured query statements corresponding to the service requesting the lock and the service holding the lock, respectively, and improve the accuracy of the blocking detection of the structured query statement.

[0049] In one embodiment, the step of obtaining the time correspondence relationship includes: determining the time correspondence relationship between each of the first structured query statements and each of the second structured query statements based on the request time corresponding to the first structured query statement and the request time corresponding to the second structured query statement.

[0050] In this embodiment, the time correspondence relationship represents the order relationship of the start times of the respective services corresponding to the respective structured query statements. The server can obtain the request time corresponding to the first structured query statement, and obtain the request time corresponding to the second structured query statement. Each of the request times represents the time at which the execution of the structured query statement is requested. Thus, the server can determine the time correspondence relationship between each of the first structured query statements and each of the second structured query statements based on the request time corresponding to the first structured query statement and the request time corresponding to the second structured query statement. For example, the server sorts each of the first structured query statements and the second structured query statements according to the order of the request times, and further determines the blocking relationship between the respective structured query statements in combination with the data tables to which the first structured query statement and the second structured query statement are directed.

[0051] Through this embodiment, the server can determine the time correspondence relationship between the respective structured query statements in combination with the request times of the respective structured query statements. Thus, the server detects the blocking of the structured query statement in combination with the time correspondence relationship, and improves the efficiency of the blocking detection.

[0052] In an embodiment, the server obtains the blocking detection result of the structured query statement according to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first structured query statement and the second structured query statement. The method comprises: determining the request time order of each first structured query statement and each second structured query statement according to the time correspondence; and generating blocking time sequence data according to the request time order. The blocking time sequence data comprises each first structured query statement and the corresponding first thread identifier, and each second structured query statement corresponding to each first structured query statement and the second thread identifier, which are sorted according to the request time order.

[0053] In the embodiment, the server can generate the blocking time sequence data of the structured query statement in combination with the thread identifier, the structured query statement, and the time order of each service. The blocking time sequence data represents the logical chain that causes the structured query statement to block, for example, the execution of the structured query statement requested later is blocked by the execution of the structured query statement requested earlier.

[0054] In the embodiment, the server can determine the request time order of each first structured query statement and each second structured query statement according to the time correspondence, so that the server can generate the blocking time sequence data according to the request time order. The blocking time sequence data comprises each first structured query statement and the corresponding first thread identifier, and each second structured query statement corresponding to each first structured query statement and the second thread identifier, which are sorted according to the request time order. The correspondence between each first structured query statement and each second structured query statement can be the correspondence between the operations on the same data table. For example, the first structured query statement corresponds to the first service request for applying a lock to a data table, the second structured query statement is operating on the data table and holds the lock of the data table, and the first structured query statement and the second structured query statement have a blocking correspondence.

[0055] Through the embodiment, the server can combine the blocking relationship between the first structured query statement and the second structured query statement into time sequence data in combination with the request time order, thereby improving the efficiency of blocking detection of the structured query statement.

[0056] In one embodiment, after obtaining the blocking detection result of the structured query statement based on the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first and second structured query statements, the method further includes: converting the blocking detection result into topology data using a data visualization tool; the topology data includes each first node corresponding to each of the first structured query statements and the first thread identifier, each second node corresponding to each of the second structured query statements and the second thread identifier, and each association relationship between the nodes; each association relationship is determined based on the time correspondence.

[0057] In this embodiment, the server can also visualize the obtained blocking detection results. This visualization can be based on a topology. The server can be equipped with a data visualization tool. The server can use this tool to convert the blocking detection results into topology data. This topology data includes multiple nodes, each node containing a structured query statement and its corresponding thread identifier. The topology data can include first nodes corresponding to each of the first structured query statements and their corresponding thread identifiers, second nodes corresponding to each of the second structured query statements and their corresponding thread identifiers, and the relationships between these nodes. These relationships represent the blocking logic between structured query statements, and can be determined based on the time correspondence. For example, a structured query statement with a earlier start time may cause a structured query statement with a later start time to be blocked.

[0058] In this embodiment, the server can combine data visualization tools to convert the congestion detection results of time-series data into topology data, thereby improving the intuitiveness of the congestion detection results.

[0059] In one exemplary embodiment, such as Figure 2 As shown, Figure 2 This is a flowchart illustrating a blocking detection method for structured query statements in another embodiment. In this embodiment, the server includes a data acquisition and time-series data conversion module, a time-series data storage module, and a visualization blocking information relationship graph conversion module.

[0060] The server finds out the blocked SQL and the source of the blocked SQL by periodically scanning the database system state table through the data collection and time series data conversion module. The time and blocking information are associated through a time series conversion algorithm, converted into time series data (blocking detection results), and output. The time series data is system state data collected in chronological order, used to track performance, identify problems, and predict trends. The data is recorded with a timestamp.

[0061] The server stores the time series data output from the periodic monitoring and time series data conversion module in the module through the time series data storage module, facilitating subsequent queries. The server can also convert the blocking information relationship diagram through the visual blocking information relationship diagram conversion module, combine the information in the time series data storage module, and visually display the blocking information in a certain time period to form a topological relationship diagram (topological data).

[0062] For example, the target database described above can be a distributed database cluster system based on a database system combined with self-developed functional components. The server collects the blocked SQL data of the target database in real time through the Exporter component and stores it in Prometheus (the first time series database) and influxdb (the second time series database). Finally, it is visually displayed through node graphs and Grafana data visualization tools. The Exporter is a tool that collects performance and state data of the target database. It can collect various indicators of the target database (such as query delay, connection number, buffer pool usage, etc.), and store these indicators in time series databases to monitor the database. Node graph is a chart type tool for data visualization, used to represent the relationship between elements (nodes) in a complex system. Each node represents an object in the system, and the connection (edge) between nodes represents their relationship or interaction. This chart is widely used in multiple fields, such as network topology, dependency mapping, data flow diagram, etc.

[0063] The server adds monitoring logic for blocked SQL to the Exporter monitoring component and outputs it to the corresponding Metric indicators periodically. For example, the server adds logic to the above monitoring component to collect relevant information about blocked SQL and convert it into time series data with time characteristics.

[0064] The detection logic of the server for structured query language can be represented as: SELECT b.trx_mysql_thread_id AS 'blocked_thread_id',

[0065] b.trx_query AS 'blocked_sql_text',

[0066] c.trx_mysql_thread_id AS blocker_thread_id,

[0067] (SELECT a.sql_text;

[0068] FROM performance_schema.events_statements_current a;

[0069] join performance_schema.threads b ON a.thread_id = b.thread_id;

[0070] join information_schema.processlist c ON b.processlist_id = c.id;

[0071] join information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id;

[0072] where c.id=blocker_thread_id ORDER BY d.trx_started) AS blocker_sql_text,

[0073] (Unix_timestamp()-Unix_timestamp(c.trx_started)) AS blocked_time;

[0074] FROM information_schema.innodb_lock_waits a;

[0075] INNER JOIN information_schema.innodb_trx b ON a.requesting_trx_id=b.trx_id INNER JOIN information_schema.innodb_trx c ON a.blocking_trx_id=c.trx_id.

[0076] The server can first determine the lock association waiting table, that is, execute FROM information_schema.innodb_lock_waits a. This indicates reading the lock waiting association table (innodb_lock_waits) built in the target database and giving the table an alias a. This table stores the correspondence between the business identity requesting the lock and the business identity holding the lock.

[0077] The server can also associate the blocked business table. For example, the server executes INNER JOIN information_schema.innodb_trx b ON a.requesting_trx_id = b.trx_id, which indicates inner joining the active business table (innodb_trx) and giving the table an alias b. b represents the blocked business, that is, the business that wants to obtain the lock but fails to obtain it and is in a waiting state; the association condition a.requesting_trx_id = b.trx_id: binds the first business identity requesting the lock (a.requesting_trx_id) and the primary key of the business table (b.trx_id) to obtain the detailed information (such as thread identity, execution structured query statement, business start time, etc.) of the blocked business from the business table, so that the server can obtain a temporary result set containing the lock waiting relationship, blocked business information, and blocker business information.

[0078] The server can extract the blocked thread identity (first thread identity). For example, the server executes b.trx_mysql_thread_id AS 'blocked_thread_id'. Among them, the server extracts the thread identity (trx_mysql_thread_id) of the target database corresponding to the business from the blocked business table (b) and gives it an alias blocked_thread_id. The final result is displayed as the blocked first thread identity.

[0079] The server can also extract the blocked structured query statement (first structured query statement). For example, the server executes b.trx_query AS 'blocked_sql_text'. This indicates extracting the structured query statement (trx_query) currently being executed by the business from the blocked business table (b) and giving it an alias blocked_structured query statement_text. The final result is displayed as the blocked structured query statement (first structured query statement).

[0080] The server can extract the second thread identification of the blocker. For example, the server executes c.trx_mysql_thread_id AS 'blocker_thread_id'. This indicates that the server word extracts the thread identification (trx_mysql_thread_id) in the target database corresponding to the business of the blocker (c) from the associated blocker business table (c), and is named blocker_thread_id. The final result is shown as the second thread identification of the blocker.

[0081] The server can further obtain the second structured query statement of the blocker. For example, the server can query by blocker_thread_id. The server can first determine the table of currently executing structured query statements. The server can execute FROM performance_schema.events_statements_current a. This indicates obtaining the table of currently executing structured query statements (events_statements_current) of the performance schema, and aliasing it as a. This table stores the structured query statement text that each thread in the target database is currently executing. The server can associate the structured query statement with the thread. For example, the server can execute join performance_schema.threads b ON a.thread_id=b.thread_id. This indicates inner joining the table of threads (threads) of the performance schema, and aliasing it as b. The association condition is a.thread_id=b.thread_id: binding by thread identification (thread_id), because events_statements_current (a) only records the thread identification to which the structured query statement belongs, and threads (b) records detailed information of the thread, including the corresponding process identification. Thus, the server can associate the process. For example, the server can execute join information_schema.processlist c ON b.processlist_id=c.id. This indicates inner joining the system process list (processlist), and aliasing it as c; the association condition is b.processlist_id=c.id: the processlist_id field of the thread table corresponds to the process identification, and the identification field of the processlist table is the process identification of the target database connection, thereby connecting the thread information of the performance schema and the system process identification. The server can further associate the business table. For example, the server can execute join information_schema.innodb_trx d ON c.id=d.trx_mysql_thread_id. This indicates inner joining the active business table (innodb_trx), and aliasing it as d; the association condition is c.id=d.trx_mysql_thread_id: the identification (process identification) of the processlist corresponds to the thread identification (trx_mysql_thread_id) associated with the business in the business table, thereby ensuring that the server finds the structured query statement belonging to the business-related structured query statement, rather than the structured query statement of an irrelevant thread. The server can further filter to retain only the second structured query statement of the blocker. For example, the server can execute where c.id=blocker_thread_id.The result set of the filter subquery is represented, only keeping the records whose process identification is equal to the blocker thread identification (blocker_thread_id, i.e., the extracted blocker thread identification), ensuring that the query only returns the structured query statements corresponding to the blocker thread, but not the structured query statements of other threads.

[0082] The server can sort the second structured query statement obtained by querying. For example, the server executes ORDER BY d.trx_started. This represents sorting the filtered results by the business start time (d.trx_started) in ascending order by default, thereby obtaining the respective structured query statements corresponding to the blocker's business.

[0083] The server can also set the name of the extracted second structured query statement. For example, the server executes a.sql_text AS 'blocker_sql_text'. This represents extracting the second structured query statement text (sql_text) from the underlying table (a, events_statements_current) of the query and giving the query result the alias blocker_sql_text, and the final result is displayed as the blocker's structured query statement. The server returns the blocker's structured query statement as a field of the main query.

[0084] The server can also calculate the blocking duration. For example, the server executes (Unix_timestamp()-Unix_timestamp(c.trx_started)) AS 'blocked_time'. Here, Unix_timestamp() represents obtaining the Unix timestamp of the current system time in seconds, and Unix_timestamp(c.trx_started) represents converting the start time of the blocker's business (c.trx_started) into a Unix timestamp. Thus, the server subtracts the Unix timestamp of the current system time from the start time of the blocker's business to obtain the total duration of being blocked, which is in seconds. The name is blocked_time, and the final result is displayed as the blocking duration.

[0085] The server can also convert time-series data into topology data using a visualization-based congestion information graph conversion module, for use by the front-end module. For example, ... Figure 3 As shown, Figure 3 This is a schematic diagram of the interface for blocking detection results in one embodiment. In the node graph and Grafana display module, the server periodically requests topology data of blocked structured queries to visualize the topology relationships, including the blocked structured query (first structured query), the thread identifier of the blocked structured query (first thread identifier), the structured query that caused the blocking (second structured query), the thread identifier of the structured query that caused the blocking (second thread identifier), and the blocking time. Each node in the topology data identifies the thread identifier of the structured query, and the lines connecting the nodes represent the blocking relationships between them. For example... Figure 3 In the node graph, the structured query statement with thread ID 1478 causes the structured query statements with thread IDs 1488 and 1501 to be blocked. The structured query statement with thread ID 1488 causes the structured query statements with thread IDs 1510 and 1501 to be blocked. The structured query statement with thread ID 1501 causes the structured query statement with thread ID 1510 to be blocked.

[0086] Through the above embodiments, the server determines the first thread identifier and the second thread identifier by utilizing the first business identifier of the request lock and the second business identifier of the lock holder. It then uses each thread identifier to determine the first and second structured query statements, respectively. Combining the correspondence between the query statements, the server determines the blocking detection result, improving the efficiency of blocking detection for structured query statements. Furthermore, by tracing the historical blocking structured query statements and blocking chains of the database system, faster fault location and resolution are achieved. Visual querying and displaying the source of blocking structured query statements and blocking chains can also be implemented, greatly reducing the difficulty of troubleshooting blocking problems and providing strong support for rapid problem location and resolution.

[0087] It should be understood that, although each step in the flowchart involved in the embodiments described above is shown in sequence according to the arrow, these steps are not necessarily executed in the order indicated by the arrow. Unless otherwise specified herein, the execution of these steps is not strictly limited in sequence, and these steps can be executed in other orders. Moreover, at least some of the steps in the flowchart involved in the embodiments described above can include multiple steps or multiple stages, which are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily sequential, but can be alternately or alternately executed with at least part of other steps or steps or stages in other steps. It can be understood that the steps in different embodiments can be freely combined as needed, and various non-contradictory schemes formed by the combination are within the scope of protection of the present application.

[0088] Based on the same inventive concept, the embodiments of the present application also provide a structured query statement blocking detection device for implementing the above-mentioned structured query statement blocking detection method. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme described in the above method, so the specific limitations in one or more structured query statement blocking detection device embodiments provided below can refer to the limitations of the structured query statement blocking detection method in the above text, which will not be repeated here.

[0089] In one exemplary embodiment, as shown in Figure 4 A structured query statement blocking detection device is provided, comprising: an acquisition module 500, a first determination module 502, a second determination module 504, and a detection module 506, wherein:

[0090] The acquisition module 500 is configured to, when it is detected that a blocking detection condition is met, acquire a first service identifier of a first service requesting a lock and a second service identifier of a second service holding a lock in a target database.

[0091] The first determination module 502 is configured to determine a first thread identifier corresponding to the first service and a second thread identifier corresponding to the second service according to the active service table of the target database, the first service identifier, and the second service identifier; and the active service table comprises thread identifiers of services in an active state.

[0092] The second determination module 504 is configured to determine a first structured query statement corresponding to the first service and a second structured query statement corresponding to the second service according to the first thread identifier and the second thread identifier, respectively.

[0093] The detection module 506 is configured to obtain a blocking detection result of the structured query statement according to the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and a time correspondence relationship between the first structured query statement and the second structured query statement.

[0094] In an embodiment, the first determination module 502 is configured to query the active service table according to the first service identifier to obtain a first thread identifier corresponding to the first service identifier, and query the active service table according to the second service identifier to obtain a second thread identifier corresponding to the second service identifier.

[0095] In an embodiment, the second determination module 504 is configured to extract, from the active service table, a first structured query statement corresponding to the first service according to the first thread identifier, and determine a second structured query statement corresponding to the second service from a performance mode table of the target database according to a process identifier corresponding to the second thread identifier, wherein the performance mode table includes each currently executed structured query statement.

[0096] In an embodiment, the detection module 506 is configured to determine a time correspondence relationship between each first structured query statement and each second structured query statement according to a request time corresponding to the first structured query statement and a request time corresponding to the second structured query statement.

[0097] In an embodiment, the detection module 506 is configured to determine a request time sequence of each first structured query statement and each second structured query statement according to the time correspondence relationship, and generate blocking time sequence data according to the request time sequence, wherein the blocking time sequence data includes each first structured query statement and a corresponding first thread identifier, and each second structured query statement corresponding to each first structured query statement and a second thread identifier.

[0098] In an embodiment, the apparatus further includes a display module configured to convert the blocking detection result into topology data through a data visualization tool, wherein the topology data includes each first node corresponding to each first structured query statement and the first thread identifier, each second node corresponding to each second structured query statement and the second thread identifier, and each association relationship between each node, and each association relationship is determined based on the time correspondence relationship.

[0099] Each of the modules in the blocking detection apparatus of the structured query statement can be implemented by software, hardware, and combinations thereof, in whole or in part. The modules can be embedded in or independent of a processor in a computer device in hardware form, or stored in a memory in the computer device in software form, so as to be invoked by the processor to perform operations corresponding to the modules.

[0100] In an example embodiment, a computer device, which can be a server, is provided, and an internal structure diagram of the computer device can be as shown in Figure 5 The computer device includes a processor, a memory, an input / output interface, and a communication interface. The processor, the memory, and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for running the operating system and the computer program in the non-volatile storage medium. The database of the computer device is configured to store service data. The input / output interface of the computer device is configured to exchange information between the processor and external devices. The communication interface of the computer device is configured to communicate with terminals outside through a network connection. The computer program is executed by the processor to implement a blocking detection method for structured query statements.

[0101] Those skilled in the art can understand that Figure 5 The structure shown in the figure is only a block diagram of part of the structure related to the scheme of the present application, and does not constitute a limitation on the computer device to which the scheme of the present application is applied. The specific computer device can include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0102] In an example embodiment, a computer device is provided, which includes a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the blocking detection method for structured query statements.

[0103] In an embodiment, a computer readable storage medium is provided, which stores a computer program. The computer program is executed by a processor to implement the blocking detection method for structured query statements.

[0104] In an embodiment, a computer program product is provided, which includes a computer program. The computer program is executed by a processor to implement the blocking detection method for structured query statements.

[0105] It should be noted that the user information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in the present application are all information and data authorized by the user or authorized by all parties, and the collection, use and processing of related data need to comply with relevant regulations.

[0106] It can be understood by those skilled in the art that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing related hardware through a computer program. The computer program can be stored in a non-volatile computer readable storage medium. When the computer program is executed, it can include the processes of the above-mentioned embodiments of each method. In the embodiments provided in the present application, any reference to memory, database or other medium can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical storage, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration but not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc. The database involved in the embodiments provided in the present application can include at least one of a relational database and a non-relational database. The non-relational database can include a distributed database based on a block chain, etc., without being limited thereto. The processor involved in the embodiments provided in the present application can be a general-purpose processor, a central processing unit, a graphics processing unit, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., without being limited thereto.

[0107] Any technical features in the above embodiments can be combined, and for the sake of brevity, not all possible combinations are described above, however, any combination of these technical features is deemed to be within the scope of the present application.

[0108] The above embodiments only express several implementation manners of the present application, and the description is relatively specific and detailed, but it should not be understood as a limitation on the patent scope of the present application. It should be pointed out that, for ordinary skilled persons in the art, several modifications and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the appended claims.

Claims

1. A method for detecting blocking in structured query statements, characterized in that, The method includes: When the blocking detection condition is met, obtain the first service identifier of the first service requesting the lock and the second service identifier of the second service holding the lock from the target database; Based on the active business table of the target database, the first business identifier, and the second business identifier, the first thread identifier corresponding to the first business and the second thread identifier corresponding to the second business are determined; the active business table includes the thread identifiers of each business that is in an active state; Based on the first thread identifier and the second thread identifier, determine the first structured query statement corresponding to the first service and the second structured query statement corresponding to the second service, respectively. Based on the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first structured query statement and the second structured query statement, the blocking detection result of the structured query statement is obtained.

2. The method according to claim 1, characterized in that, The step of determining the first thread identifier corresponding to the first service and the second thread identifier corresponding to the second service based on the active service table of the target database, the first service identifier, and the second service identifier includes: Query the active business table based on the first business identifier to obtain the first thread identifier corresponding to the first business identifier; The active business table is queried based on the second business identifier to obtain the second thread identifier corresponding to the second business identifier.

3. The method according to claim 1, characterized in that, The step of determining the first structured query statement corresponding to the first service and the second structured query statement corresponding to the second service based on the first thread identifier and the second thread identifier respectively includes: Based on the first thread identifier, extract the currently executing structured query statement corresponding to the first business from the active business table to obtain the first structured query statement; Based on the process identifier corresponding to the second thread identifier, the second structured query statement corresponding to the second service is determined from the performance mode table of the target database; the performance mode table includes each structured query statement currently being executed.

4. The method according to claim 1, characterized in that, The steps for obtaining the time correspondence include: Based on the request time corresponding to the first structured query statement and the request time corresponding to the second structured query statement, determine the time correspondence between each first structured query statement and each second structured query statement.

5. The method according to claim 1, characterized in that, The step of obtaining the blocking detection result of the structured query statement based on the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first structured query statement and the second structured query statement includes: Based on the time correspondence, the request time order of each first structured query statement and each second structured query statement is determined; Based on the request time sequence, generate blocking time sequence data; The blocking timing data includes each of the first structured query statements and their corresponding first thread identifiers, ordered according to the request time sequence, as well as each of the second structured query statements and their corresponding second thread identifiers.

6. The method according to any one of claims 1 to 5, characterized in that, After obtaining the blocking detection result of the structured query statement based on the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first structured query statement and the second structured query statement, the method further includes: The blockage detection results are converted into topology data using data visualization tools; The topology data includes each first node corresponding to each of the first structured query statements and the first thread identifier, each second node corresponding to each of the second structured query statements and the second thread identifier, and each association relationship between the nodes; each association relationship is determined based on the time correspondence relationship.

7. A blocking detection device for structured query statements, characterized in that, The device includes: The acquisition module is used to acquire the first service identifier of the first service requesting the lock and the second service identifier of the second service holding the lock from the target database when the blocking detection condition is met. The first determining module is used to determine the first thread identifier corresponding to the first service and the second thread identifier corresponding to the second service based on the active service table of the target database, the first service identifier, and the second service identifier; the active service table includes the thread identifiers of each service that is in an active state; The second determining module is used to determine the first structured query statement corresponding to the first service and the second structured query statement corresponding to the second service based on the first thread identifier and the second thread identifier, respectively. The detection module is used to obtain the blocking detection result of the structured query statement based on the first thread identifier, the first structured query statement, the second thread identifier, the second structured query statement, and the time correspondence between the first structured query statement and the second structured query statement.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images