Database lock waiting processing method and device

By judging and releasing lock waiting transactions in MySQL database, the problem of unsatisfactory processing speed of database lock waiting issues is solved, and the reliability and processing efficiency of database operation are improved. It is suitable for high-concurrency MySQL database clusters.

CN112100192BActive Publication Date: 2024-11-05CHINA CONSTRUCTION BANK
View PDF 6 Cites 0 Cited by

Patent Information

Application Number
CN202011031242.9
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2020-09-27
Publication Date
2024-11-05
Estimated Expiration
2040-09-27

AI Technical Summary

Technical Problem

In the case of high concurrency, when both SELECT and UPDATE operations are included in the MySQL database cluster, lock waiting may occur, resulting in unsatisfactory problem processing speed and it is difficult for the existing technology to quickly locate the root cause of the problem.

Method used

By determining whether there is a lock waiting transaction record in the lock waiting relationship table in the database, release the corresponding source transaction thread, and determine and release the lock waiting transaction through the comparison of the lock waiting relationship table, the current transaction table and the lock table, and improve the database lock waiting lock waiting Efficiency and accuracy of processing.

Benefits of technology

It realizes rapid positioning and deals with database lock waiting issues, improves the reliability of database operation, reduces operation and maintenance costs and development costs, and is suitable for MySQL database clusters, with good scalability and user-friendliness.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN112100192B_ABST
    Figure CN112100192B_ABST
Patent Text Reader

Abstract

The present application provides a method and device for processing a database lock wait, wherein the method comprises: determining whether a lock wait relationship table in a target database has a first lock wait transaction record, and if so, determining that the target database has a lock wait transaction; based on a source transaction identifier in the first lock wait transaction record, determining a source transaction corresponding to the lock wait transaction and releasing the thread of the source transaction, wherein the lock wait relationship table comprises: a corresponding relationship between a source transaction and a lock wait transaction. The present application can improve the efficiency and accuracy of database lock wait processing, and thus can improve the reliability of database operation.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the technical field of data processing, and in particular to a method and device for processing database lock waiting. Background Art

[0002] Usually, in the case of instantaneous high concurrency, or when multiple application services operate on the same database, if the application system uses a MYSQL database cluster as the application database DB, then when the same transaction contains both SELECT and UPDATE operations, and the operation data is the same table, lock waiting may occur. When encountering some problems during the operation of the application system, it is usually not immediately determined that it is caused by the database lock waiting mechanism, but the system application log will be checked first to determine whether it is a code operation problem or a database data or lock problem. The troubleshooting process requires the intervention of the developers of the corresponding transaction.

[0003] Therefore, this troubleshooting mechanism often has difficulty in timely identifying the root cause of the problem when dealing with lock waiting issues, resulting in unsatisfactory problem handling speed. Summary of the invention

[0004] In view of the problems in the prior art, the present application proposes a method and device for processing database lock waiting, which can improve the efficiency and accuracy of database lock waiting processing, and further improve the reliability of database operation.

[0005] In order to solve the above technical problems, this application provides the following technical solutions:

[0006] In a first aspect, the present application provides a database lock waiting processing method, comprising:

[0007] Determine whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determine that there is a lock waiting transaction in the target database;

[0008] Based on the source transaction identifier in the first lock waiting transaction record, the source transaction corresponding to the lock waiting transaction is determined and the thread of the source transaction is released, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0009] Furthermore, the database lock waiting processing method further includes:

[0010] Determine whether there is a second lock waiting transaction record in the current transaction table in the target database; if so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, then determine that there is a lock waiting transaction in the target database;

[0011] According to the source transaction identifier, the source transaction corresponding to the lock waiting transaction is determined and the thread of the source transaction is released.

[0012] Furthermore, the database lock waiting processing method further includes:

[0013] According to the lock waiting transaction identifier in the first lock waiting transaction record, it is determined whether there is a third lock waiting transaction record corresponding to the lock waiting transaction identifier in the current lock table in the target database; if so, and the source transaction identifiers in the first lock waiting transaction record and the third lock waiting transaction record are the same, it is determined that there is a lock waiting transaction in the target database;

[0014] Based on the source transaction identifier, the source transaction corresponding to the lock waiting transaction is determined and the thread of the source transaction is released.

[0015] Furthermore, the database lock waiting processing method further includes:

[0016] If the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are different or the source transaction identifiers are different, the exception information corresponding to the lock waiting transaction is output.

[0017] Further, the determining whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determining that there is a lock waiting transaction in the target database, includes:

[0018] It is determined whether the number of rows in the lock waiting relationship table in the target database is greater than zero. If so, it is determined that there is a lock waiting transaction in the target database.

[0019] Furthermore, the database lock waiting processing method further includes:

[0020] Obtaining transaction log information of the source transaction;

[0021] The transaction log information is applied to determine the database DML operation statement corresponding to the source transaction.

[0022] In a second aspect, the present application provides a database lock waiting processing device, comprising:

[0023] A first judgment module is used to judge whether there is a first lock waiting transaction record in the lock waiting relationship table of the target database, and if so, determine that there is a lock waiting transaction in the target database;

[0024] The first determination module is used to determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction based on the source transaction identifier in the first lock waiting transaction record, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0025] Furthermore, the database lock waiting processing device further includes:

[0026] A second judgment module is used to judge whether there is a second lock waiting transaction record in the current transaction table in the target database. If so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, then it is determined that there is a lock waiting transaction in the target database;

[0027] The second determining module is used to determine the source transaction corresponding to the lock waiting transaction according to the source transaction identifier and release the thread of the source transaction.

[0028] In a third aspect, the present application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the database lock waiting processing method when executing the program.

[0029] In a fourth aspect, the present application provides a computer-readable storage medium having computer instructions stored thereon, which implement the database lock waiting processing method when the instructions are executed.

[0030] It can be seen from the above technical solution that the present application provides a database lock waiting processing method and device. The method comprises: judging whether there is a first lock waiting transaction record in a lock waiting relationship table in a target database, and if so, determining that there is a lock waiting transaction in the target database; determining the source transaction corresponding to the lock waiting transaction based on the source transaction identifier in the first lock waiting transaction record and releasing the thread of the source transaction, wherein the lock waiting relationship table comprises: the corresponding relationship between the source transaction and the lock waiting transaction; being able to improve the efficiency and accuracy of database lock waiting processing, thereby being able to improve the reliability of database operation; being able to adapt to application services using a MySQL database set, being able to process the lock waiting problem occurring in the database in real time and giving an early warning for the database lock waiting problem, and further optimizing the related services by optimizing the corresponding DML operation statements, thereby reducing the cost of application development and expansion, specifically, 1. being able to improve the automation degree of database lock waiting processing, reducing the time cost of manual intervention in the operation and maintenance process, and at the same time, being able to avoid the time cost of suspending services of certain services due to manual processing caused by lock waiting problems in the production operation process; 2. being able to be quickly and flexibly applied to application services using MySQL, and having good scalability; 3. being able to provide users with a friendly processing mechanism, being simple, convenient and easy to use, and being able to improve user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] 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 some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0032] Figure 1 It is a flowchart of a database lock waiting processing method in an embodiment of the present application;

[0033] Figure 2 It is a flowchart of step 201 and step 202 of the database lock waiting processing method in an embodiment of the present application;

[0034] Figure 3 It is a flowchart of step 301 and step 302 of the database lock waiting processing method in an embodiment of the present application;

[0035] Figure 4 It is a first flow chart of the database lock waiting processing method in a specific application example of the present application;

[0036] Figure 5 It is a second flow chart of the database lock waiting processing method in a specific application example of the present application;

[0037] Figure 6 It is a structural schematic diagram of a database lock waiting processing device in an embodiment of the present application;

[0038] Figure 7 A schematic block diagram of the system structure of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0039] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0040] In order to facilitate understanding of the technical solution provided by the present application, the lock types involved in different operations in the MYSQL database are first explained below.

[0041] Shared lock (S lock): The lock type added to the query row during the SELECT statement is a shared lock. The characteristics of a shared lock are: other transactions are not allowed to add exclusive locks to the record, but shared locks are allowed. Retention time: In the repeatability level, the shared lock will be retained until the end of the transaction.

[0042] Exclusive lock (X lock): In the default isolation level (repeatability) of MYSQL, the lock type added to the operation statement by UPDATE, INSERT and DELETE statements is an exclusive lock. The characteristics of the exclusive lock are: other transactions are not allowed to add shared locks and exclusive locks to the record. Retention time: It will be maintained until the end of the transaction.

[0043] Update lock (U lock): The UPDATE statement needs to apply an update lock to the entire table before updating. After finding the record, an exclusive lock is applied to the record to be operated and the update lock is released. The update lock has the following characteristics: it is used to pre-set an exclusive lock on this page. It allows other transactions to apply shared locks, but does not allow update locks or exclusive locks to be applied. Retention time: It will be maintained until the end of the transaction.

[0044] Assume that two transactions A and B are currently executed in parallel.

[0045] 1. When A starts to execute the SELECT statement in the transaction, a shared lock is added to the record. In this way, transaction B cannot add an exclusive lock to the record and needs to wait for transaction A to commit.

[0046] 2. When A starts to execute the UPDATE statement in the transaction, it will first add an update lock to the page record. In this way, transaction B cannot add an exclusive lock to the page record and needs to wait for transaction A to commit. At the same time, after locating the record, it will add an exclusive lock to the record. In this way, transaction B cannot add a shared lock or an exclusive lock to the record and needs to wait for transaction A to commit.

[0047] 3. Because transaction A takes too long to execute, and other services are called and other tables are queried during the entire transaction, the transaction commit takes too long, and transaction B keeps waiting, resulting in a long wait time for transaction B to lock.

[0048] Based on this, the present application proposes a database lock wait processing method and device, which can monitor the relevant information of the early warning lock wait and process it immediately. At the same time, when the lock wait occurs, the relevant database DML statements and corresponding database transactions can be quickly located to solve the problems of the production environment in time.

[0049] In order to improve the efficiency and accuracy of database lock waiting processing, and thus improve the reliability of database operation, the embodiment of the present application provides a database lock waiting processing device, which can be a server or a client device, and the client device can include a smart phone, a tablet electronic device, a network set-top box, a portable computer, a desktop computer, a personal digital assistant (PDA), a vehicle-mounted device, and a smart wearable device, etc. Among them, the smart wearable device can include smart glasses, smart watches, and smart bracelets, etc.

[0050] In practical applications, the part of the database lock waiting process can be executed on the server side as described above, or all operations can be completed in the client device. The specific selection can be made according to the processing capability of the client device and the limitations of the user's usage scenario. This application does not limit this. If all operations are completed in the client device, the client device may also include a processor.

[0051] The client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side, and other implementation scenarios may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, or a server cluster consisting of multiple servers, or a server structure of a distributed device.

[0052] The server and the client device may communicate using any suitable network protocol, including network protocols that have not yet been developed on the date of filing this application. The network protocol may include, for example, TCP / IP protocol, UDP / IP protocol, HTTP protocol, HTTPS protocol, etc. Of course, the network protocol may also include, for example, RPC protocol (Remote Procedure Call Protocol) and REST protocol (Representational State Transfer) used on top of the above protocols.

[0053] The details are described through the following embodiments.

[0054] In order to improve the efficiency and accuracy of database lock waiting processing, and thus improve the reliability of database operation, this embodiment provides a database lock waiting processing method in which the execution subject is a database lock waiting processing device, and the database lock waiting processing device includes but is not limited to a server, such as Figure 1 As shown, the method specifically includes the following contents:

[0055] Step 101: determine whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determine that there is a lock waiting transaction in the target database.

[0056] Specifically, it can be determined periodically or in real time whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database; the target database can be a MYSQL database, the lock waiting relationship table can be the innodb_lock_waits table in the MYSQL database, and can include one or more rows for each blocked transaction, indicating the locks it has requested and any locks that block the request; the first lock waiting transaction record can be a transaction record that is currently in a lock waiting state in the lock waiting relationship table, and can include a lock waiting transaction identifier and a corresponding source transaction identifier.

[0057] Step 102: Based on the source transaction identifier in the first lock waiting transaction record, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0058] Specifically, the source transaction identifier may be a unique identifier of the source transaction, and the lock waiting transaction identifier may be a unique identifier of the lock waiting transaction, which is used to distinguish different transactions, such as transaction numbers; the source transaction may refer to a transaction that causes the lock waiting, and the thread of the source transaction may be a transaction thread that blocks other thread instances; the lock waiting transaction may be a blocked transaction thread; and the first lock waiting transaction record may include the source transaction identifier and the lock waiting transaction identifier.

[0059] In order to ensure the efficiency of database lock waiting processing and further improve the reliability of database lock waiting processing, the database lock waiting situation is monitored in real time. Figure 2 In one embodiment of the present application, the database lock waiting processing method further includes:

[0060] Step 201: Determine whether there is a second lock waiting transaction record in the current transaction table in the target database. If so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, then it is determined that there is a lock waiting transaction in the target database.

[0061] Specifically, the current transaction table may be an innodb_trx table in a MYSQL database, which is used to provide information about each transaction currently being executed internally; the second lock waiting transaction record may be a transaction record in the current transaction table with a status of "LOCK_WAIT"; by further determining whether the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, it is possible to monitor whether an abnormality occurs in the current database operation, thereby improving the reliability of the lock waiting transaction processing. The second lock waiting transaction record may include: a lock waiting transaction identifier and a source transaction identifier.

[0062] Among them, this embodiment does not limit the order of determining whether there is a second lock waiting transaction record in the current transaction table in the target database, and determining whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database; after simultaneous determination, it can be compared whether the lock waiting transaction identifiers in the two records are the same, and whether the source transaction identifiers are the same. If so, it is determined that there is a lock waiting transaction in the target database.

[0063] Step 202: According to the source transaction identifier, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction.

[0064] In order to further improve the reliability of database lock waiting processing while ensuring the efficiency of database lock waiting processing, see Figure 3 In one embodiment of the present application, the database lock waiting processing method further includes:

[0065] Step 301: Based on the lock waiting transaction identifier in the first lock waiting transaction record, determine whether there is a third lock waiting transaction record corresponding to the lock waiting transaction identifier in the current lock table in the target database; if so, and the source transaction identifiers in the first lock waiting transaction record and the third lock waiting transaction record are the same, it is determined that there is a lock waiting transaction in the target database.

[0066] Specifically, the third lock waiting transaction record may be a transaction record currently in a lock waiting state in a current lock table, and may include a lock waiting transaction identifier and a source transaction identifier. The current lock table may be an innodb_waits table in a MYSQL database.

[0067] Step 302: Based on the source transaction identifier, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction.

[0068] Furthermore, it is also possible to determine whether there is a third lock waiting transaction record in the current lock table in the target database. If so, and the lock waiting transaction identifiers in the first lock waiting transaction record, the second lock waiting transaction record and the third lock waiting transaction record are the same, the source transaction identifiers are the same, and the source transaction identifiers are the same, then it is determined that there is a lock waiting transaction in the target database, so as to further improve the reliability and automation of lock waiting processing.

[0069] In order to further improve the efficiency of database lock waiting processing, in one embodiment of the present application, the database lock waiting processing method further includes:

[0070] Step 401: If the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are different or the source transaction identifiers are different, then the exception information corresponding to the lock waiting transaction is output.

[0071] Specifically, a text message or email can be sent to the developer to promptly remind the current database operation abnormality. If there are multiple first lock waiting transaction records in the lock waiting relationship table, it is determined whether the lock waiting transaction identifier of each first lock waiting transaction record is in the current transaction table and the corresponding source transaction identifier is the same. If not, the abnormal information corresponding to the lock waiting transaction is output.

[0072] In order to further improve the efficiency of lock waiting transaction processing, in one embodiment of the present application, step 101 includes:

[0073] Step 001: Determine whether the number of rows in the lock waiting relationship table in the target database is greater than zero. If so, determine that there is a lock waiting transaction in the target database.

[0074] In order to improve the accuracy of lock waiting processing and facilitate the optimization of subsequent database DML operation statements, in one embodiment of the present application, the database lock waiting processing method further includes:

[0075] Step 501: Obtain transaction log information of the source transaction.

[0076] Step 502: Apply the transaction log information to determine the database DML operation statement corresponding to the source transaction.

[0077] When a lock waiting problem occurs in a production environment, it is necessary to deal with the problem in a short time to ensure the normal operation of the production environment. Based on this, the present application provides a specific application example of a database lock waiting processing method. Figure 4 In this specific application example, the method includes:

[0078] S11: Check the MySQL lock wait relationship innodb_lock_waits table.

[0079] Specifically, if the lock waiting record is not 0, the corresponding processing flow is triggered.

[0080] S12: Use the transaction number in innodb_lock_waits to query the corresponding lock status in the current lock innodb_waits table.

[0081] S13: Query the transactions in the current transaction innodb_trx table whose status is "LOCK_WAIT".

[0082] After executing any of steps S11, S12 and S13, if the corresponding thread instance can be located, the thread instance is released to solve the current lock waiting problem; further, based on the recorded relevant log information, the database DML operation statement that causes the blockage can be monitored for further optimization. For details, see Figure 5 , you can use any of the above lock wait relationship tables, current lock table, and current transaction table to implement the following:

[0083] S21: Query the lock waiting status and mark the number of rows currently waiting for locks.

[0084] S22: Determine whether the number of marked rows is greater than 0. If yes, execute step S23; otherwise, execute step S24.

[0085] S23: Query the blocked transaction thread.

[0086] S24: Determine that the lock-free waiting problem needs to be addressed.

[0087] S25: Query the source transaction that caused the lock wait.

[0088] S26: Query the thread ID of the source transaction.

[0089] S27: Obtain a specific DML statement according to the thread ID, and perform relevant optimization on the DML statement.

[0090] S28: Release the thread.

[0091] In this specific application example, some codes of the database lock waiting processing method are shown in Table 1 below:

[0092] Table 1

[0093]

[0094] From the above description, it can be seen that the database lock waiting processing provided by this specific application instance can locate the specific transaction thread that will block other thread instances, that is, the related SQL thread that causes the lock waiting, and release it by checking the current lock waiting status of the database, so as to realize the fast and automatic processing of the lock waiting problem in the production environment.

[0095] From the software level, in order to improve the efficiency and accuracy of database lock waiting processing, and thus improve the reliability of database operation, the present application provides an embodiment of a database lock waiting processing device for implementing all or part of the contents of the database lock waiting processing method, see Figure 6 The database lock waiting processing device specifically includes the following contents:

[0096] The first judgment module 10 is used to judge whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, it is determined that there is a lock waiting transaction in the target database.

[0097] The first determination module 20 is used to determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction based on the source transaction identifier in the first lock waiting transaction record, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0098] In one embodiment of the present application, the database lock waiting processing device further includes:

[0099] The second judgment module is used to judge whether there is a second lock waiting transaction record in the current transaction table in the target database. If so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, it is determined that there is a lock waiting transaction in the target database.

[0100] The second determining module is used to determine the source transaction corresponding to the lock waiting transaction according to the source transaction identifier and release the thread of the source transaction.

[0101] The embodiment of the database lock waiting processing device provided in this specification can be specifically used to execute the processing flow of the embodiment of the above-mentioned database lock waiting processing method. Its functions are not repeated here, and reference can be made to the detailed description of the embodiment of the above-mentioned database lock waiting processing method.

[0102] As can be seen from the above description, the database lock waiting processing method and device provided by the present application can improve the efficiency and accuracy of database lock waiting processing, thereby improving the reliability of database operation; can adapt to application services using MySQL database sets, can handle lock waiting problems in the database in real time and issue early warnings for database lock waiting problems, and in addition, by optimizing corresponding DML operation statements, can further optimize related services, thereby reducing the cost of application development and scalability. Specifically, 1. It can improve the degree of automation of database lock waiting processing and reduce the time cost of manual intervention in the operation and maintenance process. At the same time, it can also avoid the time cost of suspending certain services due to manual processing due to lock waiting problems during production and operation; 2. It can be quickly and flexibly applied to application services using MySQL, and has good scalability; 3. It can provide users with a friendly processing mechanism that is simple, convenient and easy to use, and can improve user experience.

[0103] From the hardware level, in order to improve the efficiency and accuracy of database lock waiting processing, and thus improve the reliability of database operation, the present application provides an embodiment of an electronic device for implementing all or part of the contents of the database lock waiting processing method. The electronic device specifically includes the following contents:

[0104] A processor, a memory, a communications interface and a bus; wherein the processor, the memory and the communications interface communicate with each other through the bus; the communications interface is used to realize information transmission between the database lock waiting processing device and related devices such as user terminals; the electronic device can be a desktop computer, a tablet computer and a mobile terminal, etc., but the present embodiment is not limited thereto. In the present embodiment, the electronic device can be implemented with reference to the embodiments for implementing the database lock waiting processing method and the embodiments for implementing the database lock waiting processing device, and the contents thereof are incorporated herein, and the repeated parts are not repeated.

[0105] Figure 7 FIG. 9 is a schematic block diagram of the system structure of the electronic device 9600 according to an embodiment of the present application. Figure 7 As shown, the electronic device 9600 may include a central processor 9100 and a memory 9140; the memory 9140 is coupled to the central processor 9100. It is worth noting that Figure 7 is exemplary; other types of structures may also be used to supplement or replace this structure to implement telecommunication functions or other functions.

[0106] In one or more embodiments of the present application, the database lock waiting processing function may be integrated into the central processing unit 9100. The central processing unit 9100 may be configured to perform the following control:

[0107] Step 101: determine whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determine that there is a lock waiting transaction in the target database.

[0108] Step 102: Based on the source transaction identifier in the first lock waiting transaction record, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0109] It can be seen from the above description that the electronic device provided by the embodiment of the present application can improve the efficiency and accuracy of database lock waiting processing, thereby improving the reliability of database operation.

[0110] In another embodiment, the database lock waiting processing device can be configured separately from the central processor 9100. For example, the database lock waiting processing device can be configured as a chip connected to the central processor 9100, and the database lock waiting processing function is implemented under the control of the central processor.

[0111] like Figure 7As shown, the electronic device 9600 may also include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily have to include Figure 7 In addition, the electronic device 9600 may also include Figure 7 For components not shown, reference may be made to the prior art.

[0112] like Figure 7 As shown, the central processing unit 9100 is sometimes also referred to as a controller or an operation control, and may include a microprocessor or other processor device and / or logic device. The central processing unit 9100 receives input and controls the operation of various components of the electronic device 9600.

[0113] The memory 9140 may be, for example, one or more of a cache, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices. The above-mentioned information related to the failure may be stored, and a program for executing the relevant information may also be stored. The CPU 9100 may execute the program stored in the memory 9140 to implement information storage or processing, etc.

[0114] The input unit 9120 provides input to the central processing unit 9100. The input unit 9120 is, for example, a key or a touch input device. The power supply 9170 is used to provide power to the electronic device 9600. The display 9160 is used to display display objects such as images and texts. The display may be, for example, an LCD display, but is not limited thereto.

[0115] The memory 9140 may be a solid-state memory, such as a read-only memory (ROM), a random access memory (RAM), a SIM card, etc. It may also be a memory that saves information even when the power is off, can be selectively erased, and is provided with more data, examples of which are sometimes referred to as EPROMs, etc. The memory 9140 may also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142, which is used to store application programs and function programs or processes for executing the operation of the electronic device 9600 through the central processor 9100.

[0116] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for communication functions of the electronic device and / or for executing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0117] The communication module 9110 is a transmitter / receiver 9110 that sends and receives signals via an antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processor 9100 to provide input signals and receive output signals, which may be the same as the case of a conventional mobile communication terminal.

[0118] Based on different communication technologies, multiple communication modules 9110 may be provided in the same electronic device, such as a cellular network module, a Bluetooth module and / or a wireless LAN module, etc. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide an audio output via the speaker 9131 and receive an audio input from the microphone 9132, thereby realizing a common telecommunication function. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor 9130 is also coupled to the central processor 9100, so that recording can be performed on the local machine through the microphone 9132, and the sound stored on the local machine can be played through the speaker 9131.

[0119] It can be seen from the above description that the electronic device provided by the embodiment of the present application can improve the efficiency and accuracy of database lock waiting processing, thereby improving the reliability of database operation.

[0120] The embodiment of the present application also provides a computer-readable storage medium capable of implementing all the steps in the database lock waiting processing method in the above embodiment. The computer-readable storage medium stores a computer program. When the computer program is executed by a processor, all the steps in the database lock waiting processing method in the above embodiment are implemented. For example, when the processor executes the computer program, the following steps are implemented:

[0121] Step 101: determine whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determine that there is a lock waiting transaction in the target database.

[0122] Step 102: Based on the source transaction identifier in the first lock waiting transaction record, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction, wherein the lock waiting relationship table includes: the corresponding relationship between the source transaction and the lock waiting transaction.

[0123] It can be seen from the above description that the computer-readable storage medium provided in the embodiment of the present application can improve the efficiency and accuracy of database lock waiting processing, thereby improving the reliability of database operation.

[0124] In this application, each embodiment of the above method is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. For relevant parts, refer to the partial description of the method embodiment.

[0125] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0126] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0127] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0128] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1The steps for the functions specified in one or more boxes.

[0129] Specific embodiments are used in this application to illustrate the principles and implementation methods of this application. The description of the above embodiments is only used to help understand the method and core idea of ​​this application. At the same time, for those skilled in the art, according to the idea of ​​this application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on this application.

Claims

1. A database lock waiting processing method, characterized in that: include: Determine whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determine that there is a lock waiting transaction in the target database, wherein the first lock waiting transaction record includes: a lock waiting transaction identifier and a corresponding source transaction identifier; Based on the source transaction identifier in the first lock waiting transaction record, determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction, wherein the lock waiting relationship table includes: a corresponding relationship between source transactions and lock waiting transactions, the source transaction is a transaction causing the lock waiting, and the lock waiting transaction is a blocked transaction thread; The database lock waiting processing method further includes: Determine whether there is a second lock waiting transaction record in the current transaction table in the target database; if so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, then determine that there is a lock waiting transaction in the target database, and the current transaction table is used to provide information about each transaction currently being executed internally; According to the source transaction identifier, the source transaction corresponding to the lock waiting transaction is determined and the thread of the source transaction is released.

2. The database lock waiting processing method according to claim 1, characterized in that: Also includes: According to the lock waiting transaction identifier in the first lock waiting transaction record, it is determined whether there is a third lock waiting transaction record corresponding to the lock waiting transaction identifier in the current lock table in the target database; if so, and the source transaction identifiers in the first lock waiting transaction record and the third lock waiting transaction record are the same, it is determined that there is a lock waiting transaction in the target database; Based on the source transaction identifier, the source transaction corresponding to the lock waiting transaction is determined and the thread of the source transaction is released.

3. The database lock waiting processing method according to claim 1, characterized in that: Also includes: If the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are different or the source transaction identifiers are different, the exception information corresponding to the lock waiting transaction is output.

4. The database lock waiting processing method according to claim 1, characterized in that: The determining whether there is a first lock waiting transaction record in the lock waiting relationship table in the target database, and if so, determining that there is a lock waiting transaction in the target database, includes: It is determined whether the number of rows in the lock waiting relationship table in the target database is greater than zero. If so, it is determined that there is a lock waiting transaction in the target database.

5. The database lock waiting processing method according to claim 1, characterized in that: Also includes: Obtaining transaction log information of the source transaction; The transaction log information is applied to determine the database DML operation statement corresponding to the source transaction.

6. A database lock waiting processing device, characterized in that: include: A first judgment module is used to judge whether there is a first lock waiting transaction record in the lock waiting relationship table of the target database, and if so, it is determined that there is a lock waiting transaction in the target database, and the first lock waiting transaction record includes: a lock waiting transaction identifier and a corresponding source transaction identifier; A first determination module is used to determine the source transaction corresponding to the lock waiting transaction and release the thread of the source transaction based on the source transaction identifier in the first lock waiting transaction record, wherein the lock waiting relationship table includes: a corresponding relationship between the source transaction and the lock waiting transaction, the source transaction is the transaction causing the lock waiting, and the lock waiting transaction is the blocked transaction thread; The database lock waiting processing device further includes: A second judgment module is used to judge whether there is a second lock waiting transaction record in the current transaction table in the target database. If so, and the lock waiting transaction identifiers in the first lock waiting transaction record and the second lock waiting transaction record are the same, and the source transaction identifiers are the same, it is determined that there is a lock waiting transaction in the target database, and the current transaction table is used to provide information about each transaction currently being executed internally; The second determining module is used to determine the source transaction corresponding to the lock waiting transaction according to the source transaction identifier and release the thread of the source transaction.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the database lock waiting processing method according to any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium having computer instructions stored thereon, characterized in that: When the instruction is executed, the database lock waiting processing method according to any one of claims 1 to 5 is implemented.

Citation Information

Patent Citations

  • Deadlock detection method and device of database transaction lock mechanism

    CN101576830A

  • Processing method and device of database deadlock and database system

    CN108205464A

  • Database system lock operation analysis method and device

    CN110399378A

  • Method and device for tracking database transaction time and analyzing database lock

    CN111221869A

  • Thread execution method and device based on distributed lock, and storage medium

    CN111259030A