Method and device for detecting XA transaction of distributed database

Abstract

The invention discloses a method and device for detecting XA transactions of a distributed database. The method is applied to XA detection involving a plurality of business systems, and comprises the following steps: firstly, acquiring a serial number of a to-be-detected business, and searching structural information of middleware proxy SQL in a database proxy server according to the serial number; and according to the structure information, extracting full-quantity SQL statements of the to-be-detected service, and analyzing according to the full-quantity SQL statements to obtain corresponding addresses and port numbers when each SQL statement is executed, so as to form a set. When the addresses in the set are not completely identical and / or the port numbers are not completely identical, that is to say, the application logic relates to operations for updating a plurality of service systems, the operations are often reasons causing the occurrence of the XA transactions, and at the moment, the XA transactions are judged to occur. The XA transactions are accurately identified and positioned through detection, so that the occurrence of the XA transactions is reduced as much as possible, and the problems of data locking and blocking caused by the XA transactions are reduced.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method and device for detecting XA transactions in a distributed database. Background Art

[0002] Distributed transactions can reduce the coupling between modules in the core business system, make the system capacity more scalable, and promote rapid business development. After the core business system is split, the business completed by a single system needs to be completed by multiple systems. In order to ensure the consistency of these cross-system businesses, the XA (eXtended Architecture) specification is proposed. The XA specification is a distributed transaction processing standard defined by the X / Open organization, which describes the interface between the global transaction manager and the local resource manager. Its purpose is to allow multiple resources (such as databases, application servers, message queues, etc.) to be accessed in the same transaction, thereby ensuring that distributed transactions are correct and reliable and cross-system businesses are effective.

[0003] In the prior art, when processing distributed transactions through the XA specification, data will be locked before the entire transaction processing process is completed, and branch transactions will enter the blocking stage. Therefore, how to reduce data locking and blocking caused by XA transactions on the basis of processing distributed transactions has become an urgent problem to be solved. Summary of the invention

[0004] Based on the above problems, the present application provides a method and apparatus for detecting XA transactions in a distributed database, so as to reduce data locking and blocking caused by XA transactions on the basis of processing distributed transactions.

[0005] The present application discloses a method for detecting XA transactions in a distributed database, the method comprising:

[0006] Obtain the number of the service to be detected, and search for the structure information of the middleware proxy SQL in the database proxy server according to the number;

[0007] Extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the full SQL statement analysis to form a set;

[0008] When the addresses in the set are not completely the same and / or the port numbers are not completely the same, it is determined that an XA transaction occurs.

[0009] Optionally, obtaining the number of the service to be detected includes:

[0010] Receive a request for the service to be detected, and run the code corresponding to the request to generate an SQL statement;

[0011] The number is obtained according to the serial number of the service to be detected.

[0012] Optionally, the address and port number corresponding to the execution of each SQL statement are obtained according to the analysis of the full amount of SQL statements, and the set is formed, including:

[0013] Analyze the full amount of SQL statements to obtain the SQL type of each SQL statement, the access database information including the database address and the user name, the business code corresponding to the business to be detected, the address and the port number, and form SQL information;

[0014] The SQL information is recorded in the database;

[0015] The business code, the address and the port number in the storage record result are extracted to form a set.

[0016] Optionally, when the addresses in the set are not completely the same and / or the port numbers are not completely the same, before determining that an XA transaction occurs, the method further includes:

[0017] When the XA transaction whitelist includes the data in the set, the service to be detected is regarded as having no XA transaction.

[0018] Optionally, after determining that an XA transaction occurs, the method further includes:

[0019] Different addresses and / or port numbers appearing in the set are recorded to locate the SQL statement in which the XA transaction occurs.

[0020] Based on the above method for detecting XA transactions in a distributed database, the present application also discloses a device for detecting XA transactions in a distributed database, including: a search unit, an extraction unit, and a judgment unit;

[0021] The search unit is used to obtain the number of the service to be detected, and search for the structure information of the middleware proxy SQL in the database proxy server according to the number;

[0022] The extraction unit is used to extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the full SQL statement analysis to form a set;

[0023] The judging unit is used to judge that an XA transaction occurs when the addresses in the set are not completely the same and / or the port numbers are not completely the same.

[0024] Optionally, the searching unit includes:

[0025] A receiving subunit, used for receiving a request for the service to be detected, and running a code corresponding to the request to generate an SQL statement;

[0026] The acquisition subunit is used to acquire the serial number according to the serial number of the service to be detected.

[0027] Optionally, the extraction unit includes:

[0028] The analysis subunit is used to analyze the full amount of SQL statements, obtain the SQL type of each SQL statement, the access database information including the database address and the user name, the business code corresponding to the business to be detected, the address and the port number, and form SQL information;

[0029] The storage subunit is used to store and record the SQL information;

[0030] The composition subunit is used to extract the business code, the address and the port number in the storage record result to form a set.

[0031] Optionally, the device further comprises:

[0032] The whitelist unit is used to regard the service to be detected as not having an XA transaction when the XA transaction whitelist contains the data in the set.

[0033] Optionally, the device further comprises:

[0034] The positioning unit is used to record different addresses and / or port numbers appearing in the set to locate the SQL statement where the XA transaction appears.

[0035] The present application discloses a method and device for detecting XA transactions in a distributed database. It is applied to XA detection involving multiple business systems, i.e., a database set. First, the number of the business to be detected is obtained, and the structural information of the middleware proxy SQL is searched in the database proxy server according to the number. According to the structural information, the full SQL statements of the business to be detected are extracted, and the corresponding address and port number when each SQL statement is executed are obtained according to the analysis of the full SQL statements to form a set. When the addresses in the set are not completely the same and / or the port numbers are not completely the same, that is, the operations involving updating multiple business systems in the application logic are often the cause of the occurrence of XA transactions, and it is judged that an XA transaction occurs at this time. Accurately identifying and locating XA transactions through detection is intended to reduce the occurrence of XA transactions as much as possible, thereby reducing data locking and blocking problems caused by XA transactions. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0037] Figure 1 A flowchart of a method for detecting XA transactions in a distributed database disclosed in an embodiment of the present application;

[0038] Figure 2 A flowchart of another method for detecting XA transactions in a distributed database disclosed in an embodiment of the present application;

[0039] Figure 3 A schematic diagram of the structure of a device for detecting XA transactions in a distributed database disclosed in an embodiment of the present application. DETAILED DESCRIPTION

[0040] Before elaborating on the contents of this application, we first briefly introduce the professional terms and existing common solutions involved therein to ensure consistency and accuracy of understanding.

[0041] XA transactions usually consist of one or more resource managers (RMs), a transaction manager (TM), and an application program. Each RM can be a different database or business system, which is used to provide methods for accessing transaction resources. The TM coordinates the transactions involved in the global transaction and needs to communicate with all RMs involved in the global transaction. The application program defines the boundaries of the transaction.

[0042] A set is a collection, a container for storing data. An RM can contain multiple sets, each of which can store data independently, similar to multiple tables. Each set has its own name to distinguish different sets. Different types of data can be stored in different sets, such as integers, strings, lists, hashes, etc.

[0043] XA transactions use a two-phase commit protocol to coordinate the commit or rollback of changes across multiple resource managers, which specifically includes: All participants prepare to execute the transaction and lock the required resources. When a participant is ready, it reports to the TM that it is ready. When the TM confirms that all participants are ready, it sends a COMM IT command to all participants to tell them to commit. If all branches indicate that they can commit when they are ready, all branches will be told to commit. If any branch indicates that it cannot commit when it is prepared, all branches will be sent a ROLLBACK command to tell them to roll back.

[0044] XA transactions ensure the consistency of transactions across multiple RMs, that is, all changes made by the transaction are either committed on all RMs or rolled back on all RMs. Therefore, there are problems of data locking and protocol blocking. Data locking means that before the end of the entire transaction processing process, the relevant data will be locked to prevent other transactions from modifying or reading it (depending on the definition of the isolation level). This locking mechanism ensures data consistency, but leads to long-term occupation of resources. Protocol blocking means that after the branch is Ready, the branch transaction will enter a blocked state until it receives a COMM IT or ROLLBACK command, resulting in a waste of system resources and reduced transaction processing efficiency.

[0045] In summary, the performance of XA transactions is relatively poor and cannot meet the needs of high-concurrency scenarios. Its performance issues are mainly reflected in two aspects. First, since additional steps are required to ensure the consistency of transactions, the response time of a single transaction is increased during the transaction coordination process. Second, since multiple transactions may compete for the same data resources at the same time, data lock conflicts between concurrent transactions will reduce the throughput of the system. At the same time, XA transactions have a large overhead due to multiple network communications and disk input / output operations. If a crash occurs during the branch preparation phase, the transaction may be retained and need to be processed manually.

[0046] Therefore, for XA transactions involving multiple RMs, they need to be detected, and manual detection requires a lot of human resources and time. In response to the above problems, the method described in this application will help to efficiently detect XA transactions and locate the source of problems, optimize the transaction processing process, and further improve the overall performance and stability of the distributed database system.

[0047] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0048] Embodiment 1: This application discloses a method for detecting XA transactions in a distributed database.

[0049] For details, please refer to Figure 1 The method for detecting XA transactions in a distributed database disclosed in this embodiment includes the following steps:

[0050] Step 101: Obtain the ID of the service to be detected, and search for the structure information of the middleware proxy SQL in the database proxy server according to the ID.

[0051] In the method described in this embodiment, when a service request to be detected is received, the request will first be parsed to determine the specific business logic that needs to be executed. As an optional method, this step usually involves checking the request parameters, matching the business rules, and determining the corresponding processing logic. When the request is parsed, the device described in this application will execute the corresponding code according to the parsing result. During the code execution process, all operations that interact with the database will be recorded. These operations appear in the form of SQL statements, which are mainly responsible for reading, writing, updating and deleting data. In order to ensure that all operations related to the business to be detected can be obtained, the SQL statement itself and information such as execution time and results will be recorded to obtain the full amount of SQL statements.

[0052] In the method described in this embodiment, regardless of whether the execution result of the code is successful or failed, a full SQL statement will be generated. The execution result is irrelevant to the problem that this embodiment wants to solve. Therefore, no specific limitation or description is made on the content or success of the execution result, which does not affect the implementation of the method described in this embodiment.

[0053] In the method described in this embodiment, the number (Query ID) of the service to be detected can be obtained according to the preset serial number of the service to be detected. The serial number corresponds to a service and can be obtained from the service log or in the database. The content and acquisition method of the serial number are not specifically limited here. It is sufficient to obtain the number of the service to be detected. The structural information of the middleware proxy SQL can be found in the database proxy server through the serial number.

[0054] Step 102: extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the analysis of the full SQL statements to form a set.

[0055] In the method described in this embodiment, when analyzing the full amount of SQL statements, the type of each SQL statement will be identified, such as query (SELECT), insert (I NSERT), update (UPDATE), delete (DELETE), etc. The database information accessed by each SQL statement will also be extracted, including the database address (i.e., the IP address or domain name of the database server), the user name, and the name of the database that may be involved. In order to associate the SQL statement with the logic of the business to be detected, the business code (code) will also be analyzed and extracted. The business code is a unique identifier corresponding to the business to be detected, which is used to distinguish different business operations or requests. When analyzing the full amount of SQL statements, the address (IP) and port number (port) corresponding to each SQL statement during execution will also be recorded, which is helpful to determine the initiating set and the set of operations of the SQL statement.

[0056] In the method described in this embodiment, as an optional method, after completing the analysis of all SQL statements, the above analysis results will be composed into SQL information, which will be recorded as important business information and stored in a special log library or data warehouse. When recording, the system will follow certain data formats and storage specifications to ensure the accuracy and readability of the data. At the same time, in order to facilitate subsequent queries and analysis, the system will also establish appropriate indexes and labels for these records. When performing XA transaction detection, first access the log library of the above-mentioned recorded records to extract the SQL information related to the business to be detected. This extraction process can be based on key fields such as business code, database name, and user name. Subsequently, the business code, address, and port number in the SQL information are extracted to form a set. As an optional method, the set can be represented as {code, (IP1, port1), (IP2, port2),… (IP n ,port n )}.

[0057] Step 103: When the addresses in the set are not completely the same and / or the port numbers are not completely the same, it is determined that an XA transaction occurs.

[0058] In the method described in this embodiment, the address is not completely the same, which means that there is an address in the set that is different from other addresses, for example, in the set {code, (IP1, port1), (IP2, port2), (IP3, port3)}, IP1 is different from IP2 and IP3. The port number is not completely the same, which means that there is a port number in the set that is different from other port numbers, for example, in the set {code, (IP1, port1), (IP2, port2), (IP3, port3)}, port1 is different from port2 and port3.

[0059] In the method described in this embodiment, the XA transaction whitelist can be a predefined rule set, which contains relevant information about XA transactions that are allowed to exist. The relevant information may include key fields such as specific business codes, addresses, port numbers, etc., and may also contain more complex rule logic, such as business types or business amounts within a specific time period. When formulating the XA transaction whitelist, it is necessary to fully consider business characteristics, performance requirements, and security requirements. The specific content or predefined method of the XA transaction whitelist is not specifically limited here. It is sufficient that it can contain relevant information about XA transactions that are allowed to exist and does not affect the subsequent judgment steps of this embodiment.

[0060] As an optional method, before detecting the address and port number in the set, a preliminary judgment can be made on the business to be detected based on the XA transaction whitelist. Specifically, when the XA transaction whitelist contains data in the set corresponding to the business to be detected, the business to be detected is regarded as having no XA transaction. For example, if the XA transaction whitelist contains the code 000, then when the code in the set corresponding to the business to be detected is 000, the business to be detected will be regarded as having no XA transaction, and no further address and port number detection is required. The whitelist processing strategy can improve detection efficiency and reduce unnecessary resource consumption and performance loss. The whitelist rules can be reviewed and updated regularly to ensure that they always meet the business characteristics and security requirements of the system.

[0061] In the method described in this embodiment, as an optional method, when it is determined that an XA transaction occurs, an XA transaction warning is thrown in time so that the operation and maintenance personnel can respond quickly and take corresponding processing measures. The warning information may include detailed information of the XA transaction, such as the location of the SQL statement, the current state of the transaction, the business system involved, etc. The level of the warning may also be determined according to the severity and potential impact of the XA transaction, such as emergency, important, general, etc. Different levels of warnings may correspond to different processing procedures and response times. This does not specifically limit the specific content of the warning information, and it is sufficient to enable the operation and maintenance personnel to quickly understand the situation of the XA transaction.

[0062] In the method described in this embodiment, different addresses and / or port numbers appearing in the set are recorded to locate the SQL statement in which the XA transaction occurs.

[0063] The method described in this embodiment is applied to XA detection involving multiple business systems. First, the number of the business to be detected is obtained, and the structural information of the middleware proxy SQL is searched in the database proxy server according to the number. According to the structural information, the full SQL statement of the business to be detected is extracted, and the address and port number corresponding to the execution of each SQL statement are obtained according to the analysis of the full SQL statement to form a set. When the addresses in the set are not exactly the same and / or the port numbers are not exactly the same, that is, the operation of updating multiple business systems is involved in the application logic, these operations are often the cause of the occurrence of XA transactions, and it is judged that an XA transaction occurs at this time. Accurately identifying and locating XA transactions through detection is intended to minimize the occurrence of XA transactions, thereby reducing data locking and blocking problems caused by XA transactions. At the same time, the present application can also provide early warning for XA transactions, which helps to further reduce the performance loss caused by XA transactions.

[0064] Embodiment 2: This application discloses another method for detecting XA transactions in a distributed database. Figure 2 The method described in this embodiment introduces the entire process of XA transaction detection.

[0065] Step 201: Receive a request for a service to be detected.

[0066] Step 202: Execute the code of the service to be detected according to the request to obtain an SQL statement.

[0067] Step 203: Search the serial number of the service to be detected according to the serial number of the service to be detected.

[0068] Step 204: Search for the structure information of the proxy SQL according to the serial number.

[0069] Step 205: extract the full SQL statements of the business to be detected according to the structural information.

[0070] Step 206: Analyze all SQL statements to obtain the address and port number corresponding to the execution of each SQL statement, and record them in the database.

[0071] Step 207: Obtain the address and port number corresponding to the execution of each SQL statement of the service to be detected in the storage record to form a set.

[0072] Step 208: Determine whether the data in the set exists in the XA transaction whitelist. If yes, proceed to step 209. If no, proceed to step 210.

[0073] Step 209: Determine that the service to be detected does not have an XA transaction.

[0074] Step 210: Determine whether the addresses and / or port numbers in the set are not completely the same. If yes, proceed to step 211. If no, proceed to step 209.

[0075] Step 211: Determine that the business to be detected is an XA transaction, and issue an XA transaction warning message.

[0076] Based on the method for detecting XA transactions in a distributed database disclosed in the above embodiment, this embodiment correspondingly discloses a device for detecting XA transactions in a distributed database. Figure 3 , the device for detecting XA transactions of a distributed database includes: a search unit 301, an extraction unit 302 and a judgment unit 303;

[0077] The search unit 301 is used to obtain the number of the service to be detected, and search for the structure information of the middleware proxy SQL in the database proxy server according to the number;

[0078] The extraction unit 302 is used to extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the full SQL statement analysis to form a set;

[0079] The judging unit 303 is configured to judge that an XA transaction occurs when the addresses in the set are not completely the same and / or the port numbers are not completely the same.

[0080] Optionally, the searching unit 301 includes:

[0081] A receiving subunit, used for receiving a request for the service to be detected, and running a code corresponding to the request to generate an SQL statement;

[0082] The acquisition subunit is used to acquire the serial number according to the serial number of the service to be detected.

[0083] Optionally, the extraction unit 302 includes:

[0084] The analysis subunit is used to analyze the full amount of SQL statements, obtain the SQL type of each SQL statement, the access database information including the database address and the user name, the business code corresponding to the business to be detected, the address and the port number, and form SQL information;

[0085] The storage subunit is used to store and record the SQL information;

[0086] The composition subunit is used to extract the business code, the address and the port number in the storage record result to form a set.

[0087] Optionally, the device further comprises:

[0088] The whitelist unit is used to regard the service to be detected as not having an XA transaction when the XA transaction whitelist contains the data in the set.

[0089] Optionally, the device further comprises:

[0090] The positioning unit is used to record different addresses and / or port numbers appearing in the set to locate the SQL statement where the XA transaction appears.

[0091] The embodiments in this specification are described in a progressive manner. As for the devices disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple, and the relevant parts can be referred to the description of the methods.

[0092] It should also be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0093] The steps of the method or algorithm described in conjunction with the embodiments disclosed herein may be implemented directly using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0094] The features described in the embodiments of this specification can be replaced or combined with each other, so that professional and technical personnel in this field can implement or use the present application.

[0095] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for detecting XA transactions in a distributed database, characterized in that: include: Obtain the number of the service to be detected, and search for the structure information of the middleware proxySQL in the database proxy server according to the number; Extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the full SQL statement analysis to form a set; When the addresses in the set are not completely the same and / or the port numbers are not completely the same, it is determined that an XA transaction occurs.

2. The method according to claim 1, characterized in that: The obtaining of the number of the service to be detected includes: Receive a request for the service to be detected, and run the code corresponding to the request to generate an SQL statement; The number is obtained according to the serial number of the service to be detected.

3. The method according to claim 1, characterized in that: The address and port number corresponding to each SQL statement when it is executed are obtained according to the analysis of the full amount of SQL statements, and a set is formed, including: Analyze the full amount of SQL statements to obtain the SQL type of each SQL statement, the access database information including the database address and the user name, the business code corresponding to the business to be detected, the address and the port number, and form SQL information; The SQL information is recorded in the database; The business code, the address and the port number in the storage record result are extracted to form a set.

4. The method according to claim 1, characterized in that When the addresses in the set are not completely the same and / or the port numbers are not completely the same, before determining that an XA transaction occurs, the method further includes: When the XA transaction whitelist includes the data in the set, the service to be detected is regarded as having no XA transaction.

5. The method according to claim 1, characterized in that: After determining that an XA transaction occurs, the method further includes: Different addresses and / or port numbers appearing in the set are recorded to locate the SQL statement in which the XA transaction occurs.

6. A device for detecting XA transactions in a distributed database, characterized in that: include: Search unit, extraction unit and judgment unit; The search unit is used to obtain the number of the service to be detected, and search for the structure information of the middleware proxy SQL in the database proxy server according to the number; The extraction unit is used to extract the full SQL statements of the service to be detected according to the structural information, and obtain the address and port number corresponding to the execution of each SQL statement according to the full SQL statement analysis to form a set; The judging unit is used to judge that an XA transaction occurs when the addresses in the set are not completely the same and / or the port numbers are not completely the same.

7. The device according to claim 6, characterized in that The searching unit comprises: A receiving subunit, used for receiving a request for the service to be detected, and running a code corresponding to the request to generate an SQL statement; The acquisition subunit is used to acquire the serial number according to the serial number of the service to be detected.

8. The device according to claim 6, characterized in that The extraction unit comprises: The analysis subunit is used to analyze the full amount of SQL statements, obtain the SQL type of each SQL statement, the access database information including the database address and the user name, the business code corresponding to the business to be detected, the address and the port number, and form SQL information; The storage subunit is used to store and record the SQL information; The composition subunit is used to extract the business code, the address and the port number in the storage record result to form a set.

9. The device according to claim 6, characterized in that The device also includes: The whitelist unit is used to regard the service to be detected as not having an XA transaction when the XA transaction whitelist contains the data in the set.

10. The device according to claim 6, characterized in that The device also includes: The positioning unit is used to record different addresses and / or port numbers appearing in the set to locate the SQL statement where the XA transaction appears.

Images