Database transaction request processing method, related equipment and storage medium

Abstract

This invention discloses a database transaction request processing method, related devices, and storage media. The method includes: receiving an update transaction request for a first data row in the database; if a first hot row update wait queue for the first data row exists in the storage engine, writing the update transaction request into the first hot row update wait queue; if no first hot row update wait queue exists in the storage engine, determining the queue length of a first row lock wait queue corresponding to the first data row in the storage engine; when the queue length of the first row lock wait queue exceeds a preset length threshold, creating a corresponding first hot row update wait queue for the first data row in the storage engine, and writing the update transaction request into the first hot row update wait queue; and, in response to a preset trigger signal, adding the update transaction request in the first hot row update wait queue to the first row lock wait queue. This invention improves the transaction throughput of the database system under high concurrency conditions.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a database transaction request processing method, related equipment, and storage medium. Background Technology

[0002] In a database system, when a large number of concurrent transactions issue update operations on the same data row in the database, that data row becomes a hot data row. To ensure the transaction throughput of the database system under high concurrency, rate limiting is generally implemented for update transaction requests on hot data rows.

[0003] Taking the relational database system MySQL as an example, related technologies add the SQL hot data row update hint syntax, which allows MySQL users to provide the primary key of the hot data row when submitting SQL statements (i.e., transaction requests). After receiving the SQL statement with the hot data row hint, MySQL extracts the primary key of the hot data row and then queues the transaction requests for the hot data row primary key at the MySQL server layer, ensuring that at any given time, only one transaction is executing for each hot data row primary key until the transaction is completed.

[0004] The implementation of the aforementioned technologies requires changes to SQL syntax, increasing the workload for MySQL users. Users need to modify their existing application's SQL statements to meet the syntax requirements of the hot data rows feature. Furthermore, since MySQL users need to provide the primary keys of hot data rows, the user must handle the detection of these rows. This means user programs need to allocate additional system resources to identify which rows are hot, such as using an additional key-value caching system to store information like access frequency and primary keys. This information also needs to be updated and maintained in real-time as hot data rows change, significantly increasing maintenance costs for database users. Moreover, using locks to serialize update requests for hot data rows at the server layer reduces concurrency between update transactions for the same hot data row, failing to fully utilize idle database resources and hindering the improvement of transaction throughput under high concurrency. Summary of the Invention

[0005] To address the problems of existing technologies, embodiments of the present invention provide a database transaction request processing method, related equipment, and storage medium. The technical solution is as follows:

[0006] On the one hand, a database transaction request processing method is provided, the method comprising:

[0007] Receive an update transaction request for the first data row in the database;

[0008] If a first hot row update waiting queue corresponding to the first data row exists in the storage engine, then the update transaction request is written into the first hot row update waiting queue.

[0009] If there is no first hot row update waiting queue corresponding to the first data row in the storage engine, then determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine.

[0010] When the queue length of the first row lock waiting queue exceeds a preset length threshold, a corresponding first hot row update waiting queue is created in the storage engine for the first data row, and the update transaction request is written into the first hot row update waiting queue.

[0011] In response to a preset trigger signal, update transaction requests in the first hot row update waiting queue are added to the first row lock waiting queue.

[0012] On the other hand, a database transaction request processing apparatus is provided, the apparatus comprising:

[0013] The receiving module is used to receive update transaction requests for the first data row in the database;

[0014] The first write module is used to write the update transaction request into the first hot row update waiting queue when there is a first hot row update waiting queue corresponding to the first data row in the storage engine.

[0015] The first determining module is used to determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine when there is no first hot row update waiting queue corresponding to the first data row in the storage engine.

[0016] A creation module is used to create a corresponding first hot row update waiting queue for the first data row in the storage engine when the queue length of the first row lock waiting queue exceeds a preset length threshold, and write the update transaction request into the first hot row update waiting queue.

[0017] The second write module is used to add update transaction requests in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal.

[0018] As an optional approach, the second write module includes:

[0019] The first monitoring module is used to monitor whether there is a row lock in the first row lock waiting queue that has received the row lock of the first data row from an update transaction request;

[0020] A first signal generating module is configured to generate a first preset trigger signal when the first monitoring module detects a certain event, wherein the preset trigger signal includes the first preset trigger signal;

[0021] The first write submodule is used to add the first update transaction request located at the head of the first hot row update waiting queue to the first row lock waiting queue in response to the first preset trigger signal.

[0022] As another optional approach, the second write module includes:

[0023] The second monitoring module is used to monitor the waiting time of each update transaction request in the first hot update waiting queue;

[0024] The second signal generation module is used to generate a second preset trigger signal when the waiting time is detected to exceed a preset time. The preset trigger signal includes the second preset trigger signal.

[0025] The second write submodule is used to add the target update transaction request with a waiting time exceeding a preset time to the first row lock waiting queue in response to the second preset trigger signal.

[0026] As an alternative, the device further includes:

[0027] The deadlock detection module is used to perform deadlock detection on the first row lock waiting queue and identify the deadlock update transaction requests that have caused deadlocks in the first row lock waiting queue.

[0028] The rollback module is used to roll back the deadlock update transaction request.

[0029] As an alternative, the device further includes:

[0030] The third write module is used to write the update transaction request into the first row lock waiting queue when the queue length of the first row lock waiting queue does not exceed the preset length threshold.

[0031] As an alternative, the device further includes:

[0032] The second determining module is used to determine the total number of update transaction requests corresponding to each data row in the storage engine;

[0033] The sending module is used to send the update transaction request to the storage engine when the total number of transactions is less than a preset transaction number threshold.

[0034] As an alternative, the device further includes:

[0035] The third determining module is used to determine the target update field corresponding to each update transaction request in the first row lock waiting queue;

[0036] The fourth determination module is used to determine at least one update transaction request that has the same target update field;

[0037] The request merging module is used to merge the at least one update transaction request according to the update instructions of each update transaction request in the at least one update transaction request, so as to obtain a merged transaction request.

[0038] On the other hand, a computer device is provided, including a processor and a memory, wherein the memory stores at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the transaction request processing method of the database described above.

[0039] On the other hand, a computer-readable storage medium is provided, wherein at least one instruction or at least one program is stored therein, the at least one instruction or the at least one program being loaded and executed by a processor to implement the database transaction request processing method as described above.

[0040] On the other hand, a computer program product or computer program is provided, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the various alternative implementations described above.

[0041] In this embodiment of the invention, upon receiving an update transaction request for a first data row in the database, the storage engine is queried. If a first hot row update waiting queue corresponding to the first data row exists in the storage engine, the update transaction request is written into the first hot row update waiting queue. If no first hot row update waiting queue corresponding to the first data row exists in the storage engine, the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine is determined. When the queue length of the first row lock waiting queue exceeds a preset length threshold, a corresponding first hot row update waiting queue is created for the first data row in the storage engine, and the aforementioned update... Transaction requests are written to the first hot row update waiting queue, and in response to a preset trigger signal, update transaction requests in the first hot row update waiting queue are added to the first row lock waiting queue. The above technical solution does not require any changes to SQL syntax, nor does it require any hot row information maintenance work on the user side. Hot data rows are automatically discovered at the database storage engine layer, and the queuing operation of row lock waiting queue for hot data rows is rate-limited at the storage engine layer. This allows multiple transactions updating the same hot data row to achieve higher concurrency, utilize more system resources, and greatly improve the transaction throughput of the database system under high concurrency. Attached Figure Description

[0042] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0043] Figure 1 This is a schematic diagram of an implementation environment provided by an embodiment of the present invention;

[0044] Figure 2 This is a schematic diagram of the basic structure of MySQL provided in an embodiment of the present invention;

[0045] Figure 3 This is a flowchart illustrating the database transaction request processing method provided in an embodiment of the present invention;

[0046] Figure 4 This is a flowchart illustrating another database transaction request processing method provided in an embodiment of the present invention;

[0047] Figure 5(a) is a flowchart illustrating another database transaction request processing method provided in an embodiment of the present invention;

[0048] Figure 5(b) is a flowchart illustrating another database transaction request processing method provided in an embodiment of the present invention;

[0049] Figure 6 This is a flowchart illustrating another database transaction request processing method provided in an embodiment of the present invention;

[0050] Figure 7 This is a flowchart illustrating another database transaction request processing method provided in an embodiment of the present invention;

[0051] Figure 8(a) is a schematic diagram of the comparative experimental results in the first scenario provided by the embodiment of the present invention;

[0052] Figure 8(b) is a schematic diagram of the comparative experimental results in the second scenario provided by the embodiment of the present invention;

[0053] Figure 9 This is a schematic diagram of the structure of the database transaction request processing device provided in an embodiment of the present invention;

[0054] Figure 10 This is a hardware structure block diagram of a server provided in an embodiment of the present invention. Detailed Implementation

[0055] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0056] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or server that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or devices.

[0057] Please see Figure 1 The diagram shown is a schematic diagram of an implementation environment provided by an embodiment of the present invention. The implementation environment may include a terminal 110 and a server 120. The terminal 110 and the server 120 may be directly or indirectly connected by wired or wireless communication. This application does not impose any limitations on this connection.

[0058] Terminal 110 may have a client installed on server 120 providing services. Terminal 110 can use this client to perform functions such as data transmission and message interaction (e.g., sending update transaction requests and receiving returned response data). The terminal may be a smartphone, tablet, laptop, desktop computer, smart speaker, smartwatch, etc., but is not limited to these.

[0059] Server 120 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms.

[0060] Cloud storage is a new concept that extends and develops from the concept of cloud computing. A distributed cloud storage system (hereinafter referred to as a storage system) refers to a storage system that uses cluster applications, grid technology, and distributed storage file systems to bring together a large number of storage devices of various types (storage devices are also called storage nodes) in the network to work together through application software or application interfaces to provide data storage and business access functions to the outside world.

[0061] A database, simply put, can be viewed as an electronic filing cabinet—a place to store electronic files, where users can query and update the data (including modification, addition, and deletion). A "database" is a collection of data stored together in a certain way, capable of being shared by multiple users, with minimal redundancy, and independent of application programs.

[0062] A Database Management System (DBMS) is a computer software system designed to manage databases, generally possessing basic functions such as storage, retrieval, security, and backup. DBMSs can be classified according to the database model they support, such as relational or XML (Extensible Markup Language); or according to the type of computer they support, such as server clusters or mobile devices; or according to the query language used, such as SQL (Structured Query Language) or XQuery; or according to performance priorities, such as maximum scale or maximum operating speed; or other classification methods. Regardless of the classification method used, some DBMSs can cross categories, for example, simultaneously supporting multiple query languages.

[0063] Figure 1 The database management system in server 120 shown can be a relational database management system such as MySQL, or other database systems compatible with relational database management systems such as the distributed cloud database CynosDB, etc. This embodiment of the invention uses MySQL as an example to illustrate the database transaction request processing method provided by this embodiment.

[0064] like Figure 2 The diagram shows the basic structure of MySQL, which consists of two layers: the Server layer and the Storage Engine layer.

[0065] The MySQLServer layer interfaces with multiple storage engines used for data management. It comprises a connection layer and a SQL layer. The connection layer enables terminal applications to connect to MySQL; the SQL layer handles functions such as permission checks, query caching, SQL syntax parsing, SQL statement optimization, SQL statement execution, and the MySQL protocol.

[0066] The MySQL storage engine layer includes multiple storage engine implementations that provide functions such as data table storage, retrieval, and transactions for the MySQL Server layer. Among them, the InnoDB storage engine is the most commonly used default storage engine for MySQL, providing functions such as transaction processing, row-level locking, and data row storage and retrieval.

[0067] The smallest unit of data in the MySQL database system is a row (data row), which is the basic unit that makes up a MySQL table. All rows in a table have the same format.

[0068] To enable concurrent and safe access to different rows in a data table, MySQL's InnoDB storage engine provides row-level locking capabilities, collectively known as the row-locking system. In this system, InnoDB maintains a row lock waiting queue for each row (data row) in the table. This queue contains update transaction requests that need to acquire the corresponding row lock. Each update transaction request can only execute its update operation on that data row after acquiring the row lock. Upon completion of the update, the row lock is released and can be passed to the next update transaction request in the waiting queue.

[0069] In practical applications, when two update transactions acquire the row locks required by each other and simultaneously request to acquire the row locks of the other, these two update transactions enter a circular waiting situation, and the database system will enter a deadlock state due to this circular waiting.

[0070] It should be noted that an update transaction request is a series of update operations executed as a single logical unit of work, consisting of a series of update operation instructions, such as statements. These update operations can include modifying, adding, or deleting data records in a data row.

[0071] In practical applications, when a large number of concurrent transactions update the same row of data records in the same table of a MySQL database, that row can be called a hot data row.

[0072] In related technologies, the handling of concurrent update transaction requests when updating hot data rows not only increases the workload and maintenance costs on the database user side, but also reduces the concurrency between update transactions of the same hot data row by locking and queuing at the server layer. This makes it impossible to make full use of the idle resources of the database system and is not conducive to improving the transaction throughput of the database system under high concurrency.

[0073] Furthermore, in the process of implementing this invention, the inventors also discovered that when updating hot data rows, the queue length of the row lock waiting queue corresponding to the hot data row is closely related to the concurrency level. Deadlock detection is a necessary step for every update transaction request, and performing deadlock detection locks the entire row lock system. The execution time complexity of deadlock detection is closely related to the queue length of the row lock waiting queue. When updating hot data rows, as concurrency increases, the increased queue length of the row lock waiting queue for the hot data row leads to a rapid increase in deadlock detection costs, which in turn causes a sharp decrease in the overall transaction throughput of the system.

[0074] In summary, this invention proposes a database transaction request processing method based on storage engine layer rate limiting. This method does not change the SQL syntax, automatically identifies hot data rows at the storage engine layer, and limits the rate of enqueuing operations for row locks of hot data rows. By controlling the queue length of the row lock waiting queue corresponding to the hot data rows, the overhead of deadlock detection is controlled. While providing users with transparent hot data row detection, rate limiting protection is also implemented, achieving high system resource utilization and high transaction throughput under high concurrency. This method can be used to optimize high-frequency update workloads that follow an idempotent distribution, such as e-commerce flash sales.

[0075] In this embodiment of the invention, for storage engines such as InnoDB that maintain a row-lock system, each data row corresponds to a row-lock waiting queue. When the queue length of this row-lock waiting queue exceeds a certain threshold (e.g., a threshold of 32), the transaction throughput of the entire system will begin to decrease because the deadlock detection cost is positively correlated with the queue length. Therefore, this embodiment of the invention uses the relationship between the queue length of the row-lock waiting queue and the threshold to determine whether a data row is a hot data row. Furthermore, this embodiment of the invention creates an independent queue for the discovered hot data rows at the storage engine layer, called the hot row update waiting queue. All subsequent transaction requests attempting to lock the hot data row will first join the hot row update waiting queue after discovering that the data row has a hot row update waiting queue, and wait until the row-lock waiting queue length is lower than the threshold before actually entering the corresponding row-lock waiting queue. This ensures that the queue length of the row-lock waiting queue remains within a threshold, unaffected by the increase in concurrency, thereby controlling the overhead of deadlock detection.

[0076] The technical solution of this invention will be described in detail below from the perspective of locking transactions for updating hot data rows.

[0077] Please see Figure 3 The diagram shown is a flowchart illustrating a database transaction request processing method provided in an embodiment of the present invention. This method can be applied to... Figure 1 The server in the middle. It should be noted that this specification provides the operation steps of the method as described in the embodiments or flowcharts, but based on conventional or non-inventive labor, more or fewer operation steps may be included. The order of steps listed in the embodiments is only one of many possible execution orders and does not represent the only execution order. In actual system or product execution, the methods shown in the embodiments or drawings can be executed sequentially or in parallel (e.g., in a parallel processor or multi-threaded processing environment). Specifically, as shown in the figures... Figure 3 As shown, the method may include:

[0078] S301, Receive an update transaction request for the first data row in the database.

[0079] The first data row can be any data row from any table in the database. An update transaction request is used to request an update operation on the data records in the first data row. This update operation can include modification, addition, and deletion. The update transaction request can be generated by the user corresponding to the terminal when operating the terminal's client.

[0080] Taking e-commerce flash sales as an example, the quantity of each item participating in a flash sale is usually predetermined. For instance, if 100 units of a certain item are reserved for the flash sale, the e-commerce platform's backend server can maintain a flash sale item data table in the database. Each data row in this table records relevant information for one flash sale item, including the remaining quantity. When an end user purchases a flash sale item through a client on the e-commerce platform, the client can generate an update transaction request for that item and send it to the backend server. Upon receiving the update transaction request, the backend server can update the corresponding data row in the flash sale item data table in the database, such as modifying the remaining quantity by decrementing it by 1.

[0081] For MySQL, the connection layer contained in the MySQLServer layer can establish a connection with the client in the terminal and receive update transaction requests sent by the client.

[0082] S303, determine whether there is a first hot row update waiting queue corresponding to the first data row in the storage engine. If it exists, proceed to step S305; if it does not exist, proceed to steps S307 to S309.

[0083] S305, the update transaction request is written into the first hot row update waiting queue.

[0084] Specifically, when there is a first hot row update wait queue corresponding to the first data row in the storage engine, it indicates that the first data row is currently a hot data row. At this time, the update transaction request is written to the first hot row update wait queue instead of being written directly to the row lock wait queue corresponding to the first data row. This can effectively avoid the impact of excessively long row lock wait queues caused by high concurrency and large deadlock detection overhead on the system transaction throughput, thus ensuring that the system has a high transaction throughput.

[0085] The first hot row update waiting queue can be a sequential queue or a circular queue, with a first-in-first-out queue being preferred.

[0086] S307, determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine.

[0087] S309, determine whether the queue length of the first row lock waiting queue exceeds the preset length threshold. If it does, proceed to step S311.

[0088] The preset length threshold can be set according to the actual transaction throughput requirements of the system in the application. Generally, if the transaction throughput requirement is high, the preset length threshold can be set smaller, while if the transaction throughput requirement is not too high, the preset length threshold can be set slightly larger. As a possible implementation, the preset length threshold can be set to 32, that is, a maximum of 32 update transactions can be arranged in the row lock waiting queue.

[0089] S311, In the storage engine, a corresponding first hot row update waiting queue is created for the first data row, and the update transaction request is written into the first hot row update waiting queue.

[0090] In this embodiment of the invention, when the queue length of the first row lock waiting queue exceeds a preset length threshold, it indicates that there are a large number of concurrent requests for the first data row. At this time, the first data row becomes a hot data row. A corresponding first hot row update waiting queue can be created for it at the storage engine layer, and newly received update transaction requests can be written into the first hot row update waiting queue. This can avoid the excessive deadlock detection overhead caused by the first row lock waiting queue being too long, thereby improving the transaction throughput of the system.

[0091] As one possible implementation, such as Figure 4 As shown, if the first data row does not have a hot row update waiting queue, and the result of step S309 is that the queue length of the first row lock waiting queue corresponding to the first data row does not exceed the preset length threshold, it indicates that the first data row is not currently a hot data row. At this time, step S315 can be executed to directly write the newly received update transaction request into the first row lock waiting queue.

[0092] like Figure 4 As shown in S317 to S319, each update transaction request in the first row lock waiting queue is arranged in order. When it acquires the row lock of the first data row, it can perform update operations on the data records in the first data row based on the row lock. When the update operation is completed, the row lock is released and passed to the next update transaction request in the first row lock waiting queue.

[0093] It should be noted that, in this embodiment of the invention, update transaction requests in the row lock waiting queue are in the normal row lock acquisition process and can directly acquire row locks to update the corresponding data rows. However, update transaction requests in the hot row update waiting queue cannot directly acquire row locks and are not in the normal row lock acquisition process. Their state can be understood as a waiting-to-be-wake-up state, awaiting entry into the row lock waiting queue. Update transaction requests in the hot row update waiting queue can only enter the normal row lock acquisition process to acquire row locks after being woken up and added to the corresponding row lock waiting queue. Therefore, in this embodiment of the invention, deadlock detection is performed only on the row lock waiting queue, while deadlock detection is not performed on the hot row update waiting queue.

[0094] S313, in response to a preset trigger signal, add the update transaction request in the first hot row update waiting queue to the first row lock waiting queue.

[0095] Specifically, when adding a new transaction to the first row lock waiting queue, it can be entered according to the enqueue rules of the first row lock waiting queue. For example, the enqueue rules can be to write the newly added update transaction request to the tail of the first row lock waiting queue.

[0096] In this embodiment of the invention, update transaction requests in the first hot update waiting queue will be woken up to complete the waiting in the following two situations.

[0097] The first scenario involves an update transaction request that has acquired a row lock in the first row lock waiting queue being awakened. Generally, when an update transaction request acquires a row lock in the row lock waiting queue, completes the corresponding update operation, and needs to release the row lock, it will pass the row lock to the next update transaction request in the same row lock waiting queue. If the data row corresponding to the row lock waiting queue is a hot data row, the update transaction request at the head of the hot row update waiting queue corresponding to that data row will also be awakened and added to the row lock waiting queue corresponding to that hot data row to enter the normal row lock acquisition process.

[0098] The second scenario involves being woken up due to a timeout. When an update transaction request waits in the hot row update waiting queue for too long, it may be because the transaction content of the update transaction request contains multiple rows for updating, and a deadlock has occurred due to indefinite waiting in the hot row update waiting queue. In this embodiment of the invention, when the waiting time of an update transaction request in the hot row update waiting queue exceeds a certain threshold, it will automatically time out and wake up, directly adding the request to the corresponding row lock waiting queue. Deadlock detection will then be performed on the added row lock waiting queue to identify and resolve deadlocks.

[0099] Corresponding to the first scenario described above, the preset trigger signal may include a first preset trigger signal for waking up update transaction requests in the first hot update waiting queue. Specifically, in executing step S313, this embodiment of the invention may include the following steps as shown in FIG5(a):

[0100] S501a, monitor whether there is an update transaction request that has received the row lock of the first data row in the first row lock waiting queue.

[0101] Specifically, when the current update transaction request completes its corresponding update operation, it releases the row lock and passes the row lock to the next update transaction request in the first row lock waiting queue. After acquiring the row lock, the next update transaction request can be dequeued and execute the corresponding update operation. At this time, the queue length of the first row lock waiting queue will be less than the preset length threshold. By monitoring whether any update transaction requests in the first row lock waiting queue have received the row lock of the first data row, update transaction requests in the first hot row update waiting queue can be added to the normal row lock acquisition process in a timely manner.

[0102] If it is detected that an update transaction request has received a row lock for the first data row in the first row lock waiting queue, then step S501a can be executed.

[0103] S503a generates a first preset trigger signal.

[0104] S505a, in response to the first preset trigger signal, the first update transaction request located at the head of the first hot row update waiting queue is added to the first row lock waiting queue.

[0105] Specifically, the first update transaction request at the head of the first hot row update waiting queue can be added to the tail of the first row lock waiting queue, so that the update transaction requests in the hot row update waiting queue can enter the normal row lock acquisition process in a timely manner, ensuring the high transaction throughput of the system.

[0106] Corresponding to the second scenario described above, the preset trigger signal may include a second preset trigger signal for waking up update transaction requests in the first hot update waiting queue. Specifically, in executing step S313, this embodiment of the invention may include the following steps as shown in Figure 5(b):

[0107] S501b, monitor the waiting time of each update transaction request in the first hot row update waiting queue.

[0108] Specifically, a timer can be configured for each first hot row update waiting queue. This timer can record the enqueue duration of each update transaction request in each corresponding hot row update waiting queue, and this enqueue duration is the waiting duration.

[0109] S503b: When the waiting time is detected to exceed the preset time, a second preset trigger signal is generated.

[0110] The preset duration can be set based on experience in actual applications, and this invention does not impose specific limitations on it.

[0111] S505b, in response to the second preset trigger signal, the target update transaction request whose waiting time exceeds the preset time is added to the first row lock waiting queue.

[0112] The target update transaction request may be a single update transaction request or multiple update transaction requests.

[0113] In this embodiment of the invention, when the hot row update waiting queue is empty, it indicates that update transaction requests for the hot data rows corresponding to the hot row update waiting queue can be added to the corresponding row lock waiting queue. That is, the queue length of the row lock waiting queue is less than the preset length threshold, which means that the hot data rows corresponding to the hot row update waiting queue have been changed from hot data rows to ordinary data rows. At this time, the hot row update waiting queue corresponding to the data row can be deleted.

[0114] In practical applications, after adding target update transaction requests with waiting times exceeding the preset time to the first row lock waiting queue, the following steps as shown in Figure 5(b) may also be included:

[0115] S507b, perform deadlock detection on the first row lock waiting queue to identify the deadlock update transaction request that caused the deadlock in the first row lock waiting queue.

[0116] When target update transaction requests with a waiting time exceeding a preset duration appear in the hot row update wait queue, it is highly likely that these target update transaction requests contain multiple row updates and have caused a deadlock due to indefinite waiting in the hot row update wait queue. Therefore, after adding these target update transaction requests to the corresponding row lock wait queue, deadlock detection should be performed on the row lock wait queue in a timely manner to identify the deadlocked update transaction request that caused the deadlock.

[0117] S509b, perform a rollback operation on the deadlock update transaction request.

[0118] In practical applications, when two transaction requests each acquire the row lock needed by the other, and simultaneously attempt to acquire the same row lock, a deadlock occurs. MySQL's InnoDB storage engine provides deadlock detection capabilities, which can identify such circular wait situations by examining the row lock wait queue and proactively roll back one of the deadlocked update transaction requests, allowing the system to continue running.

[0119] The technical solution of this invention does not require any modification to SQL syntax, nor does it require any maintenance of hot data row information on the user side. It automatically discovers hot data rows at the storage engine layer of the database and limits the rate of row lock waiting queue enqueue operations for hot data rows at the storage engine layer. This enables multiple transactions updating the same hot data row to achieve higher concurrency, utilize more system resources, and greatly improve the transaction throughput of the database system under high concurrency.

[0120] To better reduce the overhead of deadlock detection and improve the system's transaction throughput under high concurrency, one possible implementation is as follows: Figure 6 As shown, after receiving an update transaction request for the first data row in the database, the server may also include the following steps:

[0121] S601, determine the total number of update transaction requests corresponding to each data row in the storage engine.

[0122] S603, when the total number of transactions is less than a preset transaction number threshold, the update transaction request is sent to the storage engine.

[0123] For MySQL, its InnoDB storage engine provides the `innodb_thread_concurrency` parameter, which controls the number of physical threads entering the InnoDB storage engine, i.e., the number of update transaction requests. By default, this parameter is usually zero, indicating no limit. This embodiment of the invention controls the number of threads entering the InnoDB storage engine by setting this parameter, thereby further limiting the queue length of the hot row lock waiting queue on top of the hot row update waiting queue, and further improving the system's transaction throughput under high concurrency.

[0124] Specifically, the total number of update transaction requests corresponding to each data row in the storage engine can be obtained by summing the number and value of update transaction requests in the hot row update waiting queue and row lock waiting queue corresponding to each data row. The preset transaction count threshold can be obtained by retrieving the storage engine's `thread_concurrency` parameter. In practice, this parameter can be set to the product of the number of CPU cores and the concurrency factor, where the concurrency factor can range from 4 to 1000, and its specific value can be selected based on the concurrency situation in the actual application. For example, if the concurrency factor is 4 and the number of CPU cores is 16, then the storage engine's `thread_concurrency` parameter can be set to 16 * 4 = 64.

[0125] When sending an update transaction request to the storage engine, the number of update transaction requests can be sent to the storage engine based on the difference between the total number of transactions and the preset transaction number threshold.

[0126] Considering that in practical applications, updates to frequently accessed data rows are mostly single-point updates, and the executed SQL statements are similar, such as incrementing or decrementing a counter field of the frequently accessed data row, in order to further improve the transaction throughput of the system under high concurrency, one possible implementation is as follows: Figure 7 As shown, after adding the update transaction request in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal, the following steps may also be included:

[0127] S701, determine the target update field corresponding to each update transaction request in the first row lock waiting queue.

[0128] S703, identify at least one update transaction request with the same target update field.

[0129] S705, according to the update instructions of each update transaction request in the at least one update transaction request, merge the at least one update transaction request to obtain a merged transaction request.

[0130] For example, if there are 10 update transaction requests in the first row lock waiting queue, all targeting the counter field and all having update instructions that increment by 1, then these 10 update transaction requests can be merged into a single merge transaction request. The update instruction for this merge transaction request is to increment the counter field by 10, which can significantly reduce the queue length of the row lock waiting queue and improve the transaction throughput of the system under high concurrency.

[0131] Considering that the runtime of deadlock detection for the row lock waiting queue is positively correlated with the queue length, disabling deadlock detection can avoid its impact on the system's concurrent transaction throughput, thus ensuring a high concurrent transaction throughput. However, disabling deadlock detection requires setting an appropriate `lock_wait_timeout` parameter value to resolve deadlock issues, allowing deadlocked transactions to return failure directly after timeout. Therefore, as another possible implementation, deadlock detection for the row lock waiting queue in the storage engine can also be disabled. Then, after adding update transaction requests from the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal, the method may further include the following steps:

[0132] 1) Determine the lock wait time for each update transaction request in the first row lock wait queue;

[0133] 2) Determine update transaction requests whose lock waiting time exceeds a preset waiting time. The preset waiting time can be set based on actual experience, for example, it can be set to 50 seconds.

[0134] 3) Identify update transaction requests whose lock waiting time exceeds a preset waiting time as deadlock update transaction requests;

[0135] 4) Remove the deadlock update transaction request from the first row lock waiting queue and return a message that the deadlock update transaction request failed to execute.

[0136] This invention provides transparent hot data row detection and rate limiting to the user side through fine-grained rate limiting at the storage engine level, ensuring efficient utilization of system resources and high transaction throughput under high concurrency. It can be used to optimize high-frequency update workloads that follow a power-law distribution, such as e-commerce flash sales.

[0137] To verify the beneficial effects of the present invention, the following two sets of comparative experiments were conducted in the embodiments of the present invention.

[0138] Group 1: Single hotspot data row scenario

[0139] In this set of experiments, the tested MySQL table contained a row of hot data, and all update transaction requests updated a specific field of this hot data row. Figure 8(a) shows the experimental results of prior art 1, the present invention, and prior art 2 in this scenario. Prior art 1 did not perform any rate limiting on the database update transaction requests, while prior art 2 adopted the method described in the background art, which modified the SQL syntax and performed rate limiting at the MySQL Server layer.

[0140] As shown in Figure 8(a), with the increase in the concurrency of update transaction requests, the transaction throughput (TPS) of the system under the prior art 1 solution begins to drop sharply. However, under the technical solution of this invention, the system still has a high transaction throughput under high concurrency, and the transaction throughput does not experience a sharp drop. Under the prior art 2 solution, although the system's transaction throughput remains stable, it is very low. This is because prior art 2 serializes all update transaction requests with locks at the server layer, resulting in each update transaction request being executed sequentially, with no possibility of concurrent execution. Therefore, its CPU utilization is very low, and the overall transaction throughput is much lower than that of prior art 1 and this invention. In this invention, the serialization of update transaction requests is only done when necessary, i.e., only during the row locking phase, while other parts of the transaction processing can be executed in parallel, thus improving CPU utilization and transaction throughput (TPS).

[0141] The second group consists of scenarios involving multiple hotspot data rows.

[0142] In this group of experiments, a similar update operation as the first group was used, except that the number of hot data rows was increased to 32, and the access probability of each hot data row was the same. Figure 8(b) shows the experimental results of prior art 1, the present invention, and prior art 2 in this scenario. Prior art 1 did not perform any rate limiting on the database update transaction requests, while prior art 2 adopted the rate limiting method described in the background art by modifying the SQL syntax and performing rate limiting at the MySQL Server layer.

[0143] As shown in Figure 8(b), compared with the first set of experimental results, the system transaction throughput (TPS) of the prior art 1 scheme has increased. This is mainly because the number of hot data rows has increased, the concurrency has not changed, and the number of concurrent update transaction requests for each hot data row has decreased, thereby reducing the deadlock detection cost. As a result, the overall TPS of the system has increased, and the gap with the TPS of the present invention has also narrowed. Compared with the first set of experimental results, the TPS of the prior art 2 scheme has also increased, mainly because there are more hot data rows. However, compared with the present invention, the TPS of the prior art 2 is always lower than that of the present invention.

[0144] Therefore, it can be seen that the present invention greatly improves the transaction throughput of update transaction requests for high-concurrency hot data rows in single / multiple hot data row scenarios, and the system transaction throughput is much higher than that of the prior art.

[0145] Corresponding to the database transaction request processing methods provided in the above embodiments, this embodiment of the invention also provides a database transaction request processing apparatus. Since the database transaction request processing apparatus provided in this embodiment corresponds to the database transaction request processing methods provided in the above embodiments, the implementation methods of the aforementioned database transaction request processing methods are also applicable to the database transaction request processing apparatus provided in this embodiment, and will not be described in detail in this embodiment.

[0146] Please see Figure 9 The diagram shows a schematic representation of a database transaction request processing device according to an embodiment of the present invention. This device has the function of implementing the database transaction request processing method described in the above method embodiment. This function can be implemented in hardware or by hardware executing corresponding software. Figure 9 As shown, the device may include:

[0147] The receiving module 910 is used to receive an update transaction request for the first data row in the database;

[0148] The first write module 920 is used to write the update transaction request into the first hot row update waiting queue when there is a first hot row update waiting queue corresponding to the first data row in the storage engine.

[0149] The first determining module 930 is used to determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine when there is no first hot row update waiting queue corresponding to the first data row in the storage engine.

[0150] The creation module 940 is used to create a corresponding first hot row update waiting queue for the first data row in the storage engine when the queue length of the first row lock waiting queue exceeds a preset length threshold, and write the update transaction request into the first hot row update waiting queue.

[0151] The second write module 950 is used to add update transaction requests in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal.

[0152] In one possible implementation, the second write module 950 may include:

[0153] The first monitoring module is used to monitor whether there is a row lock in the first row lock waiting queue that has received the row lock of the first data row from an update transaction request;

[0154] A first signal generating module is configured to generate a first preset trigger signal when the first monitoring module detects a certain event, wherein the preset trigger signal includes the first preset trigger signal;

[0155] The first write submodule is used to add the first update transaction request located at the head of the first hot row update waiting queue to the first row lock waiting queue in response to the first preset trigger signal.

[0156] In another possible implementation, the second write module 950 may include:

[0157] The second monitoring module is used to monitor the waiting time of each update transaction request in the first hot update waiting queue;

[0158] The second signal generation module is used to generate a second preset trigger signal when the waiting time is detected to exceed a preset time. The preset trigger signal includes the second preset trigger signal.

[0159] The second write submodule is used to add the target update transaction request with a waiting time exceeding a preset time to the first row lock waiting queue in response to the second preset trigger signal.

[0160] In one possible implementation, the device may further include:

[0161] The deadlock detection module is used to perform deadlock detection on the first row lock waiting queue and identify the deadlock update transaction requests that have caused deadlocks in the first row lock waiting queue.

[0162] The rollback module is used to roll back the deadlock update transaction request.

[0163] In one possible implementation, the device may further include:

[0164] The third write module is used to write the update transaction request into the first row lock waiting queue when the queue length of the first row lock waiting queue does not exceed the preset length threshold.

[0165] In one possible implementation, the device may further include:

[0166] The second determining module is used to determine the total number of update transaction requests corresponding to each data row in the storage engine;

[0167] The sending module is used to send the update transaction request to the storage engine when the total number of transactions is less than a preset transaction number threshold.

[0168] In one possible implementation, the device may further include:

[0169] The third determining module is used to determine the target update field corresponding to each update transaction request in the first row lock waiting queue;

[0170] The fourth determination module is used to determine at least one update transaction request that has the same target update field;

[0171] The request merging module is used to merge the at least one update transaction request according to the update instructions of each update transaction request in the at least one update transaction request, so as to obtain a merged transaction request.

[0172] It should be noted that the apparatus provided in the above embodiments is only illustrated by the division of the above functional modules when implementing its functions. In actual applications, the above functions can be assigned to different functional modules as needed, that is, the internal structure of the device can be divided into different functional modules to complete all or part of the functions described above. In addition, the apparatus and method embodiments provided in the above embodiments belong to the same concept, and the specific implementation process can be found in the method embodiments, which will not be repeated here.

[0173] When the database transaction request processing device of this embodiment receives an update transaction request for a first data row in the database, it queries the storage engine. If a first hot row update waiting queue corresponding to the first data row exists in the storage engine, the update transaction request is written into the first hot row update waiting queue. If no first hot row update waiting queue corresponding to the first data row exists in the storage engine, the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine is determined. When the queue length of the first row lock waiting queue exceeds a preset length threshold, a corresponding first hot row update waiting queue is created for the first data row in the storage engine. The above update transaction requests are written into the first hot row update waiting queue, and in response to a preset trigger signal, the update transaction requests in the first hot row update waiting queue are added to the first row lock waiting queue. The above technical solution does not require any modification to the SQL syntax, nor does it require any hot row information maintenance work on the user side. It automatically discovers hot data rows at the database storage engine layer and limits the rate of enqueuing operations for row lock waiting queues of hot data rows at the storage engine layer. This allows multiple transactions updating the same hot data row to achieve higher concurrency, utilize more system resources, and greatly improve the transaction throughput of the database system under high concurrency.

[0174] This invention provides a computer device including a processor and a memory. The memory stores at least one instruction or at least one program, which is loaded and executed by the processor to implement the database transaction request processing method provided in the above method embodiments.

[0175] The memory can be used to store software programs and modules. The processor executes various functional applications and database transaction request processing by running the software programs and modules stored in the memory. The memory can mainly include a stored program area and a stored data area. The stored program area can store the operating system, application programs required for functions, etc.; the stored data area can store data created according to the use of the device, etc. In addition, the memory can include high-speed random access memory, and can also include non-volatile memory, such as at least one disk storage device, flash memory device, or other volatile solid-state storage device. Accordingly, the memory can also include a memory controller to provide the processor with access to the memory.

[0176] The methods and embodiments provided in this invention can be executed on a computer terminal, server, or similar computing device. Taking running on a server as an example... Figure 10 This is a hardware structure block diagram of a server for running a database transaction request processing method provided in an embodiment of the present invention, as shown below. Figure 10As shown, the server 1000 can vary significantly due to different configurations or performance. It may include one or more central processing units (CPUs) 1010 (CPUs 1010 may include, but are not limited to, microprocessors (MCUs) or programmable logic devices (FPGAs), memory xx30 for storing data, and one or more storage media 1020 (e.g., one or more mass storage devices) for storing application programs 1023 or data 1022. The memory 1030 and storage media 1020 may be temporary or persistent storage. The program stored in the storage media 1020 may include one or more modules, each module may include a series of instruction operations on the server. Furthermore, the CPU 1010 may be configured to communicate with the storage media 1020 and execute the series of instruction operations in the storage media 1020 on the server 1000. Server 1000 may also include one or more power supplies xx60, one or more wired or wireless network interfaces xx50, one or more input / output interfaces xx40, and / or one or more operating systems xx21, such as Windows Server™, Mac OS X™, Unix™, Linux™, FreeBSD™, etc.

[0177] The input / output interface 1040 can be used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of server 1000. In one example, the input / output interface 1040 includes a network interface controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the input / output interface 1040 may be a radio frequency (RF) module for wireless communication with the Internet.

[0178] Those skilled in the art will understand that Figure 10 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, server 1000 may also include... Figure 10 The more or fewer components shown, or having the same Figure 10 The different configurations shown.

[0179] Embodiments of the present invention also provide a computer-readable storage medium, which can be disposed in a computer device to store at least one instruction or at least one program related to implementing a database transaction request processing method, wherein the at least one instruction or the at least one program is loaded and executed by the processor to implement the database transaction request processing method provided in the above method embodiments.

[0180] Embodiments of the present invention also provide a computer program product or computer program including computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the various optional implementations described above.

[0181] Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0182] It should be noted that the order of the above embodiments of the present invention is merely for descriptive purposes and does not represent the superiority or inferiority of the embodiments. Furthermore, specific embodiments have been described above. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims can be performed in a different order than that shown in the embodiments and still achieve the desired result. Additionally, the processes depicted in the drawings do not necessarily require a specific or sequential order to achieve the desired result. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

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

[0184] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by hardware or by a program instructing related hardware. The program can be stored in a computer-readable storage medium, such as a read-only memory, a disk, or an optical disk.

[0185] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for processing transaction requests in a database, characterized in that, The method includes: Receive an update transaction request for the first data row in the database; If a first hot row update waiting queue corresponding to the first data row exists in the storage engine, then the update transaction request is written into the first hot row update waiting queue. If there is no first hot row update waiting queue corresponding to the first data row in the storage engine, then determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine. When the queue length of the first row lock waiting queue exceeds a preset length threshold, a corresponding first hot row update waiting queue is created in the storage engine for the first data row, and the update transaction request is written into the first hot row update waiting queue. In response to a preset trigger signal, update transaction requests in the first hot row update waiting queue are added to the first row lock waiting queue.

2. The database transaction request processing method according to claim 1, characterized in that, The step of adding the update transaction request in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal includes: Monitor whether there is an update transaction request that has received the row lock of the first data row in the first row lock waiting queue; If it is detected, a first preset trigger signal is generated, the preset trigger signal including the first preset trigger signal; In response to the first preset trigger signal, the first update transaction request located at the head of the first hot row update waiting queue is added to the first row lock waiting queue.

3. The database transaction request processing method according to claim 1, characterized in that, The step of adding the update transaction request in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal includes: Monitor the waiting time of each update transaction request in the first hot row update waiting queue; When the waiting time is detected to exceed a preset time, a second preset trigger signal is generated, the preset trigger signal including the second preset trigger signal; In response to the second preset trigger signal, the target update transaction request whose waiting time exceeds the preset time is added to the first row lock waiting queue.

4. The database transaction request processing method according to claim 3, characterized in that, After adding the target update transaction request with a waiting time exceeding a preset time to the first row lock waiting queue in response to the second preset trigger signal, the method further includes: Perform deadlock detection on the first row lock waiting queue to identify deadlock update transaction requests that have caused deadlocks in the first row lock waiting queue. Rollback operation is performed on the deadlock update transaction request.

5. The database transaction request processing method according to claim 1, characterized in that, The method further includes: When the queue length of the first row lock waiting queue does not exceed the preset length threshold, the update transaction request is written into the first row lock waiting queue.

6. The database transaction request processing method according to claim 1, characterized in that, After receiving an update transaction request for the first data row in the database, the method further includes: Determine the total number of update transaction requests corresponding to each data row in the storage engine; When the total number of transactions is less than a preset transaction number threshold, the update transaction request is sent to the storage engine.

7. The database transaction request processing method according to claim 1, characterized in that, After adding the update transaction request in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal, the method further includes: Determine the target update field corresponding to each update transaction request in the first row lock waiting queue; Identify at least one update transaction request that has the same target update field; Based on the update instructions of each update transaction request in the at least one update transaction request, the at least one update transaction request is merged to obtain a merged transaction request.

8. A database transaction request processing apparatus, characterized in that, The device includes: The receiving module is used to receive update transaction requests for the first data row in the database; The first write module is used to write the update transaction request into the first hot row update waiting queue when there is a first hot row update waiting queue corresponding to the first data row in the storage engine. The first determining module is used to determine the queue length of the first row lock waiting queue corresponding to the first data row in the storage engine when there is no first hot row update waiting queue corresponding to the first data row in the storage engine. A creation module is used to create a corresponding first hot row update waiting queue for the first data row in the storage engine when the queue length of the first row lock waiting queue exceeds a preset length threshold, and write the update transaction request into the first hot row update waiting queue. The second write module is used to add update transaction requests in the first hot row update waiting queue to the first row lock waiting queue in response to a preset trigger signal.

9. A computer device, characterized in that, The system includes a processor and a memory, wherein the memory stores at least one instruction or at least one program, the at least one instruction or the at least one program being loaded and executed by the processor to implement the database transaction request processing method as described in any one of claims 1 to 7.

10. A computer-readable storage medium storing at least one instruction or at least one program, said at least one instruction or said at least one program being loaded and executed by a processor to implement the database transaction request processing method as described in any one of claims 1 to 7.

Images