A database concurrency control method, device and medium

Abstract

The application discloses a database concurrent control method and device and a medium, and relates to the technical field of electric digital data processing. The method comprises the following steps: for a plurality of transactions currently running in a database, determining a transaction priority corresponding to the transaction according to a transaction characteristic of the transaction; in the case that a first transaction initiates a lock request to the database and the requested lock resource is held by a second transaction, judging whether the first transaction and the second transaction satisfy a preset lock scheduling trigger condition according to the transaction priority; if yes, calling a scheduler to send a lock release instruction to the second transaction, so that the second transaction releases the lock resource in response to the lock release instruction, and pre-rolling back a deadlock loop corresponding to the first transaction to pre-release a lock resource in conflict with the first transaction; updating a lock queue state based on the lock release operation and the pre-rollback operation, so that the first transaction acquires the lock resource through the lock queue state.

Description

Technical Field

[0001] This invention relates to the field of electronic digital data processing technology, specifically to a database concurrency control method, device, and medium. Background Technology

[0002] PostgreSQL databases employ a multi-version concurrency control (MVCC) mechanism to manage concurrent transactions. Transactions must acquire appropriate locks, such as shared locks and exclusive locks, when accessing data. Lock information is stored in a lock manager within shared memory. PostgreSQL databases suffer from severe lock contention issues in high-concurrency scenarios, especially row-level locks, table-level locks, and lightweight locks, which can easily lead to transaction blocking, deadlocks, or long-tail latency during updates to hot data.

[0003] When multiple transactions compete for the same resource, they rely on the transaction commit order and the default lock waiting queue. Subsequent transactions need to enter the waiting queue and can only proceed when the preceding transaction releases the lock. This indiscriminate waiting mechanism lacks the ability to perceive and schedule transaction behavior, which leads to high-priority transactions being blocked by low-priority transactions, short transactions timeouts due to waiting for long transactions, and deadlock detection requires traversing the wait graph, resulting in high CPU overhead and affecting the overall throughput of the database. Summary of the Invention

[0004] To address the above problems, this invention proposes a database concurrency control method, comprising:

[0005] For several transactions currently running in the database, determine the transaction priority of each transaction based on its transaction characteristics;

[0006] If the first transaction initiates a lock request to the database and the requested lock resource is held by the second transaction, it is determined whether the first transaction and the second transaction meet the preset lock scheduling triggering conditions based on the transaction priority.

[0007] If so, the scheduler is invoked to send a lock release instruction to the second transaction, so that the second transaction releases the lock resource in response to the lock release instruction, and the deadlock loop corresponding to the first transaction is pre-rolled back in order to release the lock resource that conflicts with the first transaction in advance;

[0008] Based on the lock resources released by the lock operation and the pre-rollback operation, the lock queue state is updated so that the first transaction can acquire the lock resources through the lock queue state.

[0009] In one implementation of the present invention, determining whether the first transaction and the second transaction meet the preset lock scheduling triggering conditions based on the transaction priority specifically includes:

[0010] Based on the relationship between the transaction priority and the preset threshold, the transaction types corresponding to the first transaction and the second transaction are determined respectively; wherein, the transaction types include high-priority transactions and low-priority transactions, and the transaction priority of the high-priority transactions is higher than that of the low-priority transactions;

[0011] If the first transaction is the high-priority transaction and the second transaction is the low-priority transaction, it is determined that the first transaction is blocked by the second transaction, and a preset scheduler is invoked to trigger the lock scheduling strategy for the first transaction and the second transaction.

[0012] In one implementation of the present invention, the scheduler is invoked to send a lock-yielding instruction to the second transaction, so that the second transaction releases the lock resource in response to the lock-yielding instruction, specifically including:

[0013] The scheduler is invoked to send a lock-yielding instruction to the second transaction, and it is determined whether the first transaction is in the commit critical section.

[0014] If not, control the second transaction to respond to the lock release instruction and release the lock resource held by the second transaction;

[0015] If so, accelerate the transaction process of the first transaction to raise the transaction priority of the first transaction to the highest transaction priority;

[0016] For other transactions that conflict with the first transaction, a queue-jumping strategy is triggered for the first transaction based on the highest transaction priority, so that the first transaction can preferentially acquire the lock resources released by the second transaction in response to the lock instruction.

[0017] In one implementation of the present invention, a pre-rollback is performed on the deadlock loop corresponding to the first transaction to pre-release lock resources that conflict with the first transaction, specifically including:

[0018] Deadlock pre-detection is performed on the lock wait graph corresponding to the database to determine the existence of a deadlock loop if it is detected that some lock resources held by the first transaction form a circular wait trend with other transactions.

[0019] A pre-rollback is performed on the designated transaction with the lowest priority in the deadlock loop, in order to eliminate the deadlock loop by pre-releasing the lock resources that conflict with the first transaction.

[0020] In one implementation of the present invention, determining the transaction priority corresponding to the transaction based on the transaction characteristics specifically includes:

[0021] The database execution statement corresponding to the transaction is matched with a preset SQL template to determine the transaction type corresponding to the transaction, and the transaction type factor corresponding to the transaction is determined based on the transaction type.

[0022] The execution time of the transaction is obtained, and a time penalty factor corresponding to the transaction is calculated based on the execution time; wherein the time penalty factor is negatively correlated with the execution time.

[0023] Obtain the estimated number of rows affected by the transaction in the database, and determine the impact benefit factor corresponding to the transaction based on the estimated number of rows affected and the importance of the database object operated by the transaction;

[0024] The transaction priority corresponding to the transaction is obtained by weighted summing of the transaction type factor, the duration penalty factor, and the impact on revenue factor.

[0025] In one implementation of the present invention, after determining whether the first transaction and the second transaction satisfy a preset lock scheduling trigger condition, the method further includes:

[0026] If the first transaction is a low-priority transaction, or if both the first and second transactions are low-priority transactions, it is determined that the first and second transactions do not meet the preset lock scheduling triggering conditions, and the first transaction is added to the waiting queue of the lock resource, so that the first transaction waits to acquire the lock resource according to the queue order in the waiting queue.

[0027] In one implementation of the present invention, after determining the transaction priority corresponding to the transaction, the method further includes:

[0028] Based on the source application tag corresponding to the transaction, identify multiple sub-transactions belonging to the same business link as the transaction, and determine the overall priority corresponding to the business link;

[0029] If any sub-transaction in the business chain is blocked, the overall priority of the business chain is assigned to the blocked sub-transaction to update the transaction priority corresponding to the sub-transaction.

[0030] In one implementation of the present invention, after waiting to acquire the lock resource according to the queue order of the waiting queue, the method further includes:

[0031] Based on the transaction priority of the first transaction, a corresponding timeout threshold is determined; wherein, the timeout threshold and the transaction priority are positively correlated.

[0032] If the waiting time of the first transaction exceeds the timeout threshold, the first transaction will be timed out.

[0033] This invention provides a database concurrency control device, the device comprising:

[0034] At least one processor;

[0035] And, a memory communicatively connected to the at least one processor;

[0036] The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a database concurrency control method as described in any of the preceding claims.

[0037] This invention provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0038] A database concurrency control method as described in any of the preceding items.

[0039] The database concurrency control method proposed in this invention can bring the following beneficial effects:

[0040] Based on transaction characteristics, transaction priorities are evaluated to identify the priorities of different transaction types. When lock conflicts occur, a lock-yielding mechanism is triggered, allowing lower-priority transactions to release resources and enabling higher-priority transactions to acquire lock resources first. This effectively solves the problems of high-priority transactions being blocked and short transactions timeouts due to waiting for long transactions. Simultaneously, deadlock pre-detection and pre-rollback eliminate potential risks before deadlocks form, avoiding business interruptions after a full deadlock and significantly reducing the high-frequency CPU overhead of traditional deadlock detection mechanisms. Through the synergy of lock yielding and pre-rollback, lock resources are reallocated, significantly reducing the average transaction wait time and long-tail latency, effectively improving database throughput and response efficiency. Attached Figure Description

[0041] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this invention, illustrate exemplary embodiments of the invention and are used to explain the invention, but do not constitute an undue limitation of the invention. In the drawings:

[0042] Figure 1 A flowchart illustrating a database concurrency control method provided in an embodiment of the present invention;

[0043] Figure 2 This is a schematic diagram of the architecture of an intelligent transaction scheduler provided in an embodiment of the present invention;

[0044] Figure 3 This is a schematic diagram of the structure of a database concurrency control device provided in an embodiment of the present invention. Detailed Implementation

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

[0046] The technical solutions provided by the various embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0047] like Figure 1 As shown, an embodiment of the present invention provides a database concurrency control method, comprising:

[0048] S101: For several transactions currently running in the database, determine the transaction priority of each transaction based on its transaction characteristics.

[0049] In high-concurrency database application scenarios, when multiple workloads run in combination, the traditional first-come, first-served lock scheduling mechanism may not meet business priority requirements. Critical short business transactions, such as user payments, may be blocked by long-running transactions such as report queries, leading to a decrease in the throughput of core system business and a degraded user experience. Based on this, such as Figure 2 As shown in the figure, this embodiment of the invention provides an intelligent transaction scheduler, which includes a transaction feature extraction module, a priority decision engine, and a smart lock scheduler. The transaction feature extraction module is responsible for collecting transaction features from the database system view. These features include transaction type, execution type, and estimated number of rows affected. The module then passes these features to the priority decision engine, which evaluates the priority score of each transaction based on these features, classifies the transactions, and issues scheduling instructions to the smart lock scheduler. The scheduler manages the lock queue status of the database kernel through specific strategies, and its execution results are fed back to the feature extraction module, forming a continuously optimizing closed loop. By analyzing transaction behavior in real time, dynamically calculating priorities, and intelligently intervening in lock management, the overall concurrency performance and resource utilization of the database can be effectively optimized. This architecture is integrated into the PostgreSQL database, serving as a core enhancement module for its concurrency control process or enhanced through the creation of an extension.

[0050] The transaction feature extraction module, as the perception layer, is responsible for collecting transaction features corresponding to several currently running transactions in the database through database system tables (pg_stat_activity, pg_locks, etc.) and the Hook mechanism provided by PostgreSQL during critical stages of transaction execution, such as statement parsing and lock requests. Based on these transaction features, a transaction behavior profile is built for each active transaction in the database, providing a data foundation for subsequent intelligent decision-making. Transaction characteristics include transaction type, execution duration, estimated number of rows affected, resource consumption, and source application tag. Transaction type includes core transactions, general business transactions, etc., with different types of transactions having varying importance in the business. Execution duration reflects the time elapsed from the start of the transaction to the current moment, reflecting the transaction's execution progress and efficiency, and is used to assess whether the current transaction has been consuming resources for an extended period. Estimated number of rows affected refers to the number of data rows that the SQL statement will affect, or the exact number of rows affected after actual execution. It can be estimated using the database's EXPLAIN command. The estimated number of rows affected indicates how many rows of data the transaction is expected to impact in the database, reflecting the transaction's scale and influence to some extent. Resource consumption refers to the consumption of various database resources during transaction execution, such as CPU and memory. The source application tag identifies which application initiated the transaction, helping to identify the business chain to which the transaction belongs.

[0051] The priority decision engine evaluates the priority of each transaction based on the transaction characteristics collected by the transaction feature extraction module. The initial priority evaluation primarily considers three feature indicators: transaction type, execution duration, and estimated number of rows affected. Specifically, the database execution statement corresponding to the transaction is matched with a preset SQL template to determine the transaction type. For example, `UPDATE account SET balance=balance-?WHERE user_id=?` and `INSERT INTO payment_orders` belong to the core payment category; `UPDATE product_inventory SET stock=stock-?WHERE product_id=? AND stock>=?` belongs to the inventory deduction category; and `SELECT...FROM payment_orders` belongs to the read-only query category.

[0052] Different transaction types have different processing priorities. A corresponding transaction type factor is determined based on the transaction type. Core transactions have relatively high transaction type factors, while auxiliary transactions have relatively low ones. Generally, core payment transactions directly involve fund changes and must be processed first, so they are assigned the highest transaction type factor, usually set to 1. Inventory deduction transactions will directly lead to order failure if blocked, therefore their corresponding transaction type factor also needs to be relatively high. Read-only query transactions do not involve critical business logic and can tolerate a certain delay, so their corresponding transaction type factor is relatively low.

[0053] After determining the transaction type factor, it is also necessary to obtain the transaction's execution duration. Then, a duration penalty factor is calculated based on the execution duration. The duration penalty factor is determined according to a preset duration penalty function, which can be a linear function, exponential function, etc. The specific function form can be customized according to the database's processing performance; this invention does not impose any limitations on this. The duration penalty function is a function that decreases as the execution time increases, aiming to prevent long transactions from unreasonably blocking system resources. Therefore, the duration penalty factor is negatively correlated with the execution duration; the longer the execution time, the smaller the duration penalty factor, thereby avoiding long transactions from occupying resources for an extended period. Finally, the estimated number of rows affected by the transaction in the database is obtained. Based on the estimated number of rows affected and the importance of the database objects operated on by the transaction, the corresponding impact benefit factor for the transaction is determined. For example, transactions that operate on core business tables and have a large estimated number of rows affected have a relatively high impact benefit factor; while transactions that operate on non-core business tables and have a small estimated number of rows affected have a relatively low impact benefit factor. Based on preset weighting coefficients, the transaction type factor, duration penalty factor, and impact benefit factor are weighted and summed to obtain the transaction priority corresponding to the transaction. The weighting coefficient for each factor can be adjusted according to actual business strategies. It should be noted that the transaction priority involved in this application embodiment is not a level, but a specific priority score. In this way, the priority of each transaction can be evaluated more scientifically and reasonably, avoiding the one-sidedness and irrationality of determining priority based on only a single factor.

[0054] In one embodiment, the above process achieves real-time dynamic scoring of each transaction by analyzing transaction behavior in real time. However, in actual database concurrency scenarios, transactions are not isolated; multiple transactions often form complex business chain relationships. Therefore, it is also necessary to consider the overall priority of the business chain to which the transaction belongs. The application_name connection parameter built into the PostgreSQL database can be used to identify the source application tag of a transaction, such as "web-payment-service" or "bi-reporting-tool". Each source application tag corresponds to a business system. Through the above source application tags or custom tags, it is possible to identify multiple sub-transactions belonging to the same business chain. These transactions together constitute a database business chain. For example, the payment chain includes multiple transactions such as creating an order, deducting inventory, making payment, and updating the order status. The overall priority of the business chain can be determined based on the source application tag. For example, the overall priority of the core payment business chain can be set higher because the payment business is directly related to fund transactions and user experience, and its smooth execution needs to be prioritized; while for some auxiliary business chains, such as the data report generation chain, the overall priority can be relatively lower.

[0055] The overall priority considers the importance and business requirements of all sub-transactions within the business chain. If any sub-transaction in the business chain is blocked, to ensure the smooth execution of the business chain and prevent the entire business process from being affected by the blocking of a single sub-transaction, the blocked sub-transaction needs to inherit the overall priority of its business chain. By assigning the overall priority of the business chain to the blocked sub-transaction, the transaction priority corresponding to that sub-transaction is updated. In this way, when the blocked sub-transaction subsequently initiates a lock request, it will be prioritized and allowed to acquire the lock resource first, thereby ensuring the smooth execution of the entire business chain.

[0056] S102: If the first transaction initiates a lock request to the database and the requested lock resource is held by the second transaction, determine whether the first transaction and the second transaction meet the preset lock scheduling triggering conditions according to the transaction priority.

[0057] The smart lock scheduler, acting as the executor, issues scheduling instructions based on the transaction priorities generated by the priority decision engine. The entire smart lock scheduling process begins with the first transaction initiating a lock request to the database. Unlike traditional passive waiting mechanisms, this embodiment of the invention uses the transaction priority of each transaction calculated earlier to make decisions on the lock scheduling process, ensuring that high-priority transactions can quickly obtain the required resources, thereby optimizing the overall concurrency performance of the database system. When the first transaction attempts to acquire a lock resource, such as a row lock or table lock, the scheduler first checks the availability of the lock resource requested by the first transaction, i.e., whether it is held by other transactions. If the lock resource is not held by any transaction, it means that the current lock resource is available, and the first transaction can directly acquire the lock resource and continue to execute the corresponding business operations. Therefore, in scenarios without contention, the performance of the database will not be affected in any way.

[0058] If the lock resource requested by the first transaction is held by the second transaction, it means that a lock conflict has occurred. At this time, the scheduler will not put the first transaction into the first-in first-out (FIFO) waiting queue as in the traditional scheduling mechanism, but will enter the intelligent scheduling stage of lock resources. This stage requires making decisions based on the transaction priority of each transaction.

[0059] First, based on the relationship between transaction priorities and a preset threshold, the transaction types corresponding to the first and second transactions are determined. Transaction types are categorized into high-priority transactions and low-priority transactions based on whether their priorities exceed the preset threshold. Transactions with priorities greater than the preset threshold are classified as high-priority transactions, enjoying priority in scheduling lock resources. Transactions with priorities less than the preset threshold are marked as low-priority transactions. Since the priority of low-priority transactions is significantly lower than that of high-priority transactions, low-priority transactions must yield to high-priority transactions during lock scheduling.

[0060] Next, based on the transaction priority of the requesting transaction (i.e. the first transaction), it is determined whether the current first transaction and the second transaction need the scheduler to perform lock scheduling, that is, whether the first transaction and the second transaction meet the preset lock scheduling triggering conditions.

[0061] If the first transaction is a low-priority transaction, it means that the transaction currently requesting the lock resource has a low priority. For high-priority transactions, special lock scheduling intervention from the scheduler is usually not required. In this case, the traditional scheduling mechanism can be used, placing the first transaction at the tail of the waiting queue. The first transaction will then wait to acquire the lock resource according to the queue order, and will acquire the lock and execute in order after the second transaction releases the lock resource. Since low-priority transactions have low priority and low urgent demand for system resources, following the traditional scheduling mechanism can maintain the basic operating order of the system and avoid the additional overhead caused by excessive intervention. If the first transaction is a high-priority transaction, and the current lock holder, i.e., the second transaction, is also a high-priority transaction, for fairness and to avoid livelock, the first transaction will also be added to the tail of the waiting queue for that lock resource, ensuring relatively fair competition for lock resources among high-priority transactions.

[0062] Neither of the above two scenarios satisfies the preset lock scheduling triggering conditions. The first transaction needs to be added to the waiting queue, waiting for the lock resource to be released before acquiring the lock. Traditional lock timeout mechanisms limit transaction waiting time by setting the `lock_timeout` parameter, and actively exit after timeout. Since `lock_timeout` is a fixed parameter, regardless of transaction priority, all transactions exit the waiting queue after timeout. While this method avoids indefinite waiting, it lacks flexibility. High-priority transactions may exit prematurely, causing business losses, while low-priority transactions may wait too long, affecting system resource utilization. In this embodiment of the invention, when determining whether the first transaction has timed out, it needs to determine its corresponding timeout threshold based on the transaction priority. The timeout threshold is positively correlated with the transaction priority; that is, the higher the transaction priority, the larger the corresponding timeout threshold. Generally, high-priority transactions have a high urgency and importance for system resources. Giving them a larger timeout threshold ensures they have sufficient time to wait for lock resources, reducing business losses caused by premature exit from the waiting queue. Conversely, low-priority transactions have a lower urgency and importance for system resources. Giving them a smaller timeout threshold can improve system resource utilization and prevent them from occupying the waiting queue for extended periods. When the first transaction's waiting time exceeds its corresponding timeout threshold, the scheduler will implement a corresponding timeout handling mechanism. First, the first transaction is removed from the waiting queue to prevent it from occupying the queue for too long and improve system resource utilization. Then, after removing the first transaction, the scheduler selects an appropriate method to handle it based on specific business needs and strategies, such as recommitting the transaction or logging it for later analysis.

[0063] S103: If so, send a lock release instruction to the second transaction so that the second transaction releases the lock resource in response to the lock release instruction, and perform a pre-rollback on the deadlock loop corresponding to the first transaction so as to release the lock resource that conflicts with the first transaction in advance.

[0064] When the first transaction is a high-priority transaction, while the second transaction holding the lock is a low-priority transaction, it means the first transaction is blocked by the second transaction. Since the first transaction has a significantly higher priority than the second transaction, the preset lock scheduling trigger condition is met. At this point, the scheduler needs to be invoked to trigger the lock scheduling strategy for the first and second transactions. There are two lock scheduling strategies. One is that the scheduler sends a lock release instruction to the second transaction, requiring it to pause its current operation and release the lock resource it holds. For the second transaction, being a low-priority transaction, upon receiving the lock release request, in order to ensure the smooth execution of the high-priority transaction, it will release the lock resource as required by the scheduler. By pausing the low-priority business operation, the high-priority first transaction can acquire the lock resource as soon as possible, ensuring the smooth execution of the business chain. The other approach is to pre-rollback the deadlock loop corresponding to the first transaction. In database concurrent operations, a deadlock loop refers to a situation where multiple transactions are waiting for each other to release lock resources, forming a circular wait, causing the transactions to be unable to continue execution. By pre-rolling back the deadlock loop corresponding to the first transaction, lock resources that conflict with the first transaction can be released in advance, breaking the deadlock loop and creating conditions for the first transaction to acquire lock resources.

[0065] Specifically, the scheduler is invoked to send a lock-yielding instruction to the second transaction. This instruction requires the second transaction to stop its current operations related to the lock resource and release the lock. At this point, it's necessary to further determine whether the first transaction is in a commit critical section, i.e., whether it's at the end of its lifecycle. For example, a payment transaction that has completed balance deduction, inventory deduction, and order insertion steps and is attempting to acquire the lock to commit is in a commit critical section. If it is, it means the first transaction has completed all core operations and is in the critical stage before final commit. Blocking the first transaction by the second transaction at this point would lead to significant rollback costs and business risks. Therefore, the scheduler will accelerate the first transaction's process by raising its priority to the highest system-defined priority and triggering a targeted queue-jumping strategy based on this highest priority.

[0066] The core purpose of the queue-jumping strategy is to resolve the uncertainty of resource contention that may arise after a lock release operation. When the second transaction releases the lock, the resource instantly enters a waiting state. At this time, there may be other transactions waiting in the queue besides the first transaction. If relying solely on the default first-in-first-out queue rule, the first transaction may not be able to acquire the lock immediately after the second transaction releases it; instead, it may be preempted by other transactions higher up in the queue, leading to scheduling failure. To solve this problem, the queue-jumping strategy reorganizes the original waiting queue. If the first transaction has a lock conflict with other transactions, the scheduler raises the first transaction's priority to the highest priority, making it take precedence over all other conflicting transactions and allowing it to acquire the lock released by the second transaction first, thus ensuring that the critical transaction can be committed instantly. If the first transaction is not in the commit critical section, the scheduler directly controls the second transaction to respond to the lock release instruction, immediately releasing the lock it holds, allowing the first transaction to successfully acquire the required lock and continue executing its business operations.

[0067] This invention enables the active transfer of lock resources from low-priority transactions to high-priority transactions by using lock instructions, effectively solving the priority inversion problem. It prioritizes and schedules transactions in the commit critical section, not only clearing blocked transactions but also eliminating all potential competitors. This ensures that transactions at the edge of business completion can acquire released lock resources first, achieving instant transaction commit and avoiding timeouts, rollbacks, and cascading business failures caused by delays.

[0068] The scheduler sends a lock-yielding instruction to the second transaction to release its lock resources. However, in a complex concurrent environment, the first transaction may still hold other lock resources that are being waited for by other transactions, which in turn may hold another lock needed by the first transaction. This multi-faceted circular wait relationship is forming a potential deadlock loop. If only the lock is yielded without addressing the potential deadlock loop, the first transaction, after acquiring the current lock resource, is likely to immediately fall into another wait relationship, or even directly trigger a deadlock. Therefore, while sending the lock-yielding instruction, the scheduler also checks the lock wait graph corresponding to the current database and performs deadlock pre-detection on the lock wait graph. Deadlock pre-detection does not wait for a formed deadlock loop to form, but rather predicts whether a deadlock loop involving the first transaction will form in the future based on whether the lock resources already held by the first transaction form a circular wait trend with other transactions. For example, if accounts, coupons, and product_inventory are marked as key financial resources, when two high-priority payment transactions are detected to simultaneously hold locks on some key financial resources and begin waiting for the other to hold another key financial resource, a high-level alert will be immediately triggered and a pre-rollback operation will be initiated, even if the deadlock loop has not yet fully formed.

[0069] The pre-rollback target is not randomly selected, but rather a designated transaction with the lowest priority in the deadlock loop is selected for pre-rollback. By prioritizing the termination of the transaction with the least overall performance impact and the lowest business importance, the optimal conflict resolution is achieved, effectively ensuring the smooth operation of high-priority core business processes. By proactively releasing lock resources held by the designated transaction that may conflict with the first transaction, the deadlock loop is eliminated before it actually occurs, greatly improving system stability. Furthermore, the pre-rollback operation effectively avoids the CPU overhead caused by full graph inspection and rollback after a deadlock occurs, significantly reducing the overall resource consumption of the system and improving the throughput and response efficiency of the database in high-concurrency scenarios.

[0070] S104: Update the lock queue state based on the lock resources released by the lock operation and the pre-rollback operation, so that the first transaction can acquire the lock resource through the lock queue state.

[0071] After the lock operations and pre-rollback operations released the occupied lock resources, the lock queue status managing lock allocation in the database needs to be updated synchronously to accurately reflect the latest resource availability. Only after this update can the first waiting transaction detect that the lock resource has become available by querying the latest lock queue status, successfully acquire the lock resource, and continue its blocked business operations.

[0072] The above are embodiments of the method proposed in this invention. Based on the same idea, some embodiments of this invention also provide devices and non-volatile computer storage media corresponding to the above methods.

[0073] Figure 3 This is a schematic diagram of a database concurrency control device provided in an embodiment of the present invention. Figure 3 As shown, it includes:

[0074] At least one processor; and,

[0075] At least one processor-communication-connected memory; wherein,

[0076] The memory stores instructions that can be executed by at least one processor to enable the at least one processor to perform a database concurrency control method as described in any of the preceding items.

[0077] This invention provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0078] A database concurrency control method as described in any of the preceding items.

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

[0080] The devices, media, and methods provided in the embodiments of the present invention are one-to-one correspondences. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0081] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0082] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0083] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0084] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0085] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0086] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0087] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

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

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

Claims

1. A method of database concurrency control, characterized by, The method comprises: For a plurality of transactions currently running in a database, determining a transaction priority corresponding to each transaction according to transaction characteristics of the transaction; In a case where a first transaction initiates a lock request to the database and a requested lock resource is held by a second transaction, determining whether the first transaction and the second transaction satisfy a preset lock scheduling trigger condition according to the transaction priority; If yes, calling a scheduler to send a yield lock instruction to the second transaction, so that the second transaction releases the lock resource in response to the yield lock instruction, and pre-rolling back a deadlock loop corresponding to the first transaction to pre-release a lock resource in conflict with the first transaction; Updating a lock queue state based on the lock resource released by the yield lock operation and the pre-rollback operation, so that the first transaction acquires the lock resource through the lock queue state; Calling a scheduler to send a yield lock instruction to the second transaction, so that the second transaction releases the lock resource in response to the yield lock instruction, specifically comprising: Calling a scheduler to send a yield lock instruction to the second transaction, and determining whether the first transaction is at the end of a life cycle; If no, controlling the second transaction to release the lock resource held by the second transaction in response to the yield lock instruction; If yes, accelerating a transaction process of the first transaction to raise the transaction priority corresponding to the first transaction to a highest transaction priority; For other transactions in conflict with the first transaction, triggering a queue-jumping strategy for the first transaction according to the highest transaction priority, so that the first transaction acquires the lock resource released by the second transaction in response to the yield lock instruction in priority; Pre-rolling back the deadlock loop corresponding to the first transaction to pre-release a lock resource in conflict with the first transaction, specifically comprising: Performing a deadlock pre-detection on a lock waiting graph corresponding to the database to determine that there is a deadlock loop in a case where part of the lock resource held by the first transaction forms a cyclic waiting trend with other transactions; Pre-rolling back a specified transaction with the lowest transaction priority in the deadlock loop to eliminate the deadlock loop by pre-releasing the lock resource in conflict with the first transaction; After determining the transaction priority corresponding to the transaction, the method further comprises: According to the source application label corresponding to the transaction, identifying a plurality of sub-transactions belonging to the same business link as the transaction, and determining an overall priority corresponding to the business link; In a case where any sub-transaction in the business link is blocked, assigning the overall priority of the business link to the blocked sub-transaction to update the transaction priority corresponding to the sub-transaction; According to the transaction characteristics of the transaction, determining the transaction priority corresponding to the transaction, specifically comprising: Matching a database execution statement corresponding to the transaction with a preset SQL template to determine a transaction type corresponding to the transaction, and determining a transaction type factor corresponding to the transaction according to the transaction type; acquire an execution duration of the transaction, and calculate a duration penalty factor corresponding to the transaction according to the execution duration, wherein the duration penalty factor is in a negative correlation with the execution duration; acquire an estimated influence row number corresponding to the transaction in the database, and determine an influence benefit factor corresponding to the transaction according to the estimated influence row number and importance of a database object operated by the transaction; perform weighted summation on the transaction type factor, the duration penalty factor and the influence benefit factor to obtain a transaction priority corresponding to the transaction.

2. The method of claim 1, wherein, determine whether the first transaction and the second transaction satisfy a preset lock scheduling trigger condition according to the transaction priority, specifically including: determine transaction types corresponding to the first transaction and the second transaction according to a size relationship between the transaction priority and a preset threshold value, wherein the transaction types include a high-priority transaction and a low-priority transaction, and the transaction priority of the high-priority transaction is higher than that of the low-priority transaction; in a case where the first transaction is the high-priority transaction and the second transaction is the low-priority transaction, determine that the first transaction is blocked by the second transaction, and call a preset scheduler to trigger a lock scheduling strategy for the first transaction and the second transaction.

3. The method of claim 2, wherein, after determining whether the first transaction and the second transaction satisfy the preset lock scheduling trigger condition, the method further includes: in a case where the first transaction is the low-priority transaction or the first transaction and the second transaction are both the low-priority transactions, determine that the first transaction and the second transaction do not satisfy the preset lock scheduling trigger condition, and add the first transaction to a waiting queue of the lock resource, so that the first transaction waits to acquire the lock resource in a queue order in the waiting queue.

4. The method of claim 3, wherein, after waiting to acquire the lock resource in the queue order of the waiting queue, the method further includes: determine a timeout threshold value corresponding to the transaction priority of the first transaction according to the transaction priority of the first transaction, wherein the timeout threshold value is in a positive correlation with the transaction priority; if a waiting duration of the first transaction exceeds the timeout threshold value, perform timeout processing on the first transaction.

5. A database concurrency control device characterized by comprising: The device includes: at least one processor; and a memory connected in communication with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform a database concurrency control method according to any one of claims 1-4.

6. A non-transitory computer storage medium storing computer-executable instructions that, when executed, cause a computer to perform: The computer executable instructions are configured to: perform a database concurrency control method according to any one of claims 1-4.

Images