Methods, devices, and media for implementing autonomous transactions based on PostgreSQL database

Abstract

This application discloses a method, device, and medium for implementing autonomous transactions based on a PostgreSQL database, relating to the field of electronic digital data processing technology. The method includes: switching the execution context of the current database session process from the main transaction context to the autonomous transaction context using the stack structure corresponding to each transaction maintained by the current database session process; during the execution context switch, traversing a pre-configured resource registry to determine the first resource requiring isolation in the main transaction and the isolation strength corresponding to the first resource; according to the isolation policy corresponding to the isolation strength, saving the resource state corresponding to the main transaction to the resource isolation stack at the corresponding resource isolation execution time, and initializing the second resource corresponding to the autonomous transaction; executing the transaction logic corresponding to the autonomous transaction based on the autonomous transaction context, and after the autonomous transaction is completed, restoring the first resource corresponding to the main transaction and switching the execution context back to the main transaction context.

Description

Technical Field

[0001] This application relates to the field of electronic digital data processing technology, specifically to a method, device, and medium for implementing autonomous transactions based on a PostgreSQL database. Background Technology

[0002] In a standard transaction processing model, a database session typically contains only one main transaction context and its nested sub-transactions. The commit or rollback of the main transaction determines the final state of all data modifications within it. However, in some advanced application scenarios, such as audit logging, operation tracing, and message queue writing, it is necessary to execute a code block within a main transaction that can be independently committed or rolled back, and the result of this code block's operation is unaffected by the final state of the main transaction. This independent transaction block is called an autonomous transaction.

[0003] Some existing commercial databases (such as Oracle) support autonomous transaction functionality, but their implementation is heavily reliant on their specific kernel architecture. In open-source database systems like PostgreSQL, because each session process is designed as a single, continuous transaction execution environment, all transaction resources are strongly bound to the session state, and it does not natively support creating and running multiple independent main transaction contexts within the same session. Existing technologies exist to simulate independent transaction environments by creating virtual process structures; however, such solutions require maintaining an additional set of virtual process management logic in the database kernel, running parallel to the physical process structure, increasing kernel complexity and the difficulty of maintaining state consistency. Summary of the Invention

[0004] To address the aforementioned issues, this application proposes an autonomous transaction implementation method based on the PostgreSQL database, including: During the execution of the main transaction in the current database session process, in response to the call to a pre-declared autonomous transaction, the execution context of the current database session process is switched from the main transaction context to the autonomous transaction context through the stack structure corresponding to each transaction maintained by the current database session process. When performing a context switch, the pre-configured resource registry is traversed to determine the first resource that needs to be isolated in the main transaction and the isolation strength corresponding to the first resource. According to the isolation strategy corresponding to the isolation strength, at the corresponding resource isolation execution time, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized; Based on the autonomous transaction context, the transaction logic corresponding to the autonomous transaction is executed, and after the autonomous transaction is completed, the first resource corresponding to the main transaction is restored, and the execution context is switched to the main transaction context.

[0005] In one implementation of this application, the stack structure includes a main transaction stack and autonomous transaction stacks independent of the main transaction stack. The execution context of the current database session process is switched from the main transaction context to the autonomous transaction context through the stack structure corresponding to each transaction maintained by the current database session process. Specifically, this includes: The execution of the main transaction is paused, and the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process; In the autonomous transaction stack maintained by the current database session process, an autonomous transaction context for executing the autonomous transaction is created, and the execution context of the current database session process is switched to the autonomous transaction context.

[0006] In one implementation of this application, the isolation strength includes mandatory isolation and on-demand isolation. According to the isolation strategy corresponding to the isolation strength, at the corresponding resource isolation execution time, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized, specifically including: If the isolation strength corresponding to the first resource is the forced isolation, according to the corresponding forced isolation strategy, when performing context switching, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized; If the isolation strength corresponding to the first resource is on-demand isolation, an isolation identifier corresponding to the first resource is generated according to the corresponding lazy isolation strategy. When the autonomous transaction accesses the second resource of the same type as the first resource for the first time, the resource state corresponding to the main transaction is saved to the resource isolation stack through the isolation identifier, and the second resource corresponding to the autonomous transaction is initialized.

[0007] In one implementation of this application, before the autonomous transaction first accesses a second resource of the same type as the first resource, saves the resource state corresponding to the main transaction to the resource isolation stack, and initializes the second resource corresponding to the autonomous transaction, the method further includes: Obtain an access trajectory summary of the autonomous transaction; wherein the access trajectory summary is generated based on the historical execution records of the autonomous transaction, including the historical access frequency and corresponding confidence level of each resource marked as on-demand isolation; Based on the historical access frequency and the confidence level, determine the predicted set of resources required for this execution of the autonomous transaction from the resources marked as on-demand isolated; Predictive resources in the predicted resource set are pre-isolated so that when the autonomous transaction first accesses the predicted resource, the predicted resource has already completed resource state saving and resource initialization.

[0008] In one implementation of this application, creating an autonomous transaction context for executing the autonomous transaction specifically includes: An autonomous transaction context is created for executing the autonomous transaction, and the transaction block state of the autonomous transaction is initialized to the starting state; wherein, the transaction block state refers to the state identifier used to characterize the current lifecycle stage of the transaction; Set the transaction block status to running state to start the autonomous transaction; If the autonomous transaction fails to start, the autonomous transaction is popped from the autonomous transaction stack, and the transaction block state is restored to the outer transaction state.

[0009] In one implementation of this application, after the autonomous transaction is completed, restoring the first resource corresponding to the main transaction specifically includes: After the autonomous transaction is completed, the autonomous transaction is committed or rolled back, the transaction block status is set to idle, and the autonomous transaction is popped from the autonomous transaction stack to restore the first resource corresponding to the main transaction.

[0010] In one implementation of this application, executing the transaction logic corresponding to the autonomous transaction specifically includes: Attempt to execute the transaction logic corresponding to the autonomous transaction; if the autonomous transaction fails to apply for the target lock resource and enters a waiting state, determine the current holder of the target lock resource. If the current holder is the main transaction and the main transaction is in a suspended state, it is determined that there is an intra-session deadlock between the autonomous transaction and the main transaction; Roll back the autonomous transaction and release the second resource occupied by the autonomous transaction so that the main transaction can resume execution after the autonomous transaction is rolled back.

[0011] In one implementation of this application, the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process, specifically including: Based on the resource consumption characteristics of the autonomous transaction during its historical execution process, the execution mode corresponding to the autonomous transaction is determined; wherein, the execution mode includes the isolation mode; When the execution mode is the isolation mode, the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process, and the resource status required by the autonomous transaction is marshalled to the external execution thread so that the transaction logic corresponding to the autonomous transaction can be executed through the external execution thread.

[0012] This application provides an autonomous transaction implementation device based on a PostgreSQL database, the device comprising: At least one processor; And, a memory communicatively connected to the at least one processor; 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 the autonomous transaction implementation method based on the PostgreSQL database as described in any of the preceding claims.

[0013] This application provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows: Execute the autonomous transaction implementation method based on the PostgreSQL database as described in any of the preceding items.

[0014] The autonomous transaction implementation method based on PostgreSQL database proposed in this application can bring the following benefits: This architecture, based on the native single-database session process of PostgreSQL, utilizes a dual-stack structure—comprising a main transaction stack and an autonomous transaction stack—maintained within the session process. This allows for switching the execution context between the main and autonomous transactions, enabling the coexistence of multiple independent transactions within a single process. During transaction switching, a resource registry identifies resources requiring isolation and their isolation strength. Based on different isolation strategies, the main transaction's resource state is saved to the resource isolation stack, and independent resources are initialized for the autonomous transactions. Fine-grained callback management replaces the overall resource copying, eliminating the need for additional abstraction layers such as virtual processes. This allows for deep integration with the database kernel, avoiding compatibility and debugging complexity issues. Attached Figure Description

[0015] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 A flowchart illustrating an autonomous transaction implementation method based on a PostgreSQL database, provided as an embodiment of this application; Figure 2This is a schematic diagram of a dual-stack management architecture provided in an embodiment of this application; Figure 3 This is a schematic diagram of the structure of an autonomous transaction implementation device based on a PostgreSQL database, provided as an embodiment of this application. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application 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 application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0017] The technical solutions provided by the various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0018] like Figure 1 As shown in the embodiment of this application, an autonomous transaction implementation method based on a PostgreSQL database is provided, including: S101: During the execution of the main transaction in the current database session process, in response to the call to the pre-declared autonomous transaction, the execution context of the current database session process is switched from the main transaction context to the autonomous transaction context through the stack structure corresponding to each transaction maintained by the current database session process.

[0019] To implement autonomous transactions in the PostgreSQL architecture, this application departs from external simulation approaches such as virtual processes. Instead, it constructs a dual-stack transaction context management model within a single database session process. This aims to achieve multiple independent transaction contexts within a single database session process, enabling the coexistence and switching between autonomous transactions and the main transaction. During the execution of the main transaction in the current database session process, it first identifies whether the target of the call is an autonomous routine. Autonomous transactions require, during the compilation phase, the declaration segment of PL / SQL or a compatible stored procedure language to declare a subroutine (including functions, stored procedures, anonymous blocks, and nested subroutines) as an autonomous routine using specific compiler directive syntax (such as PRAGMAAUTONOMOUS_TRANSACTION). The compiler records this flag in the routine's metadata structure (such as pg_proc or an internal representation) during compilation, allowing the execution engine to accurately identify and trigger the autonomous transaction processing flow at runtime. After triggering the autonomous transaction processing flow, the execution engine, in response to the call to the pre-declared autonomous transaction, switches the execution context of the current database session process from the main transaction context to the autonomous transaction context using the stack structure corresponding to each transaction maintained by the current database session process. Execution context refers to the complete state environment maintained by the database session process required for the execution of the current transaction, including but not limited to execution state information such as transaction identifier, transaction state, memory context, resource owner, lock management state, snapshot information, and program counter. In the embodiments of this application, a single database session process maintains two independent stack structures: the main transaction stack and the autonomous transaction stack. The main transaction stack maintains the original master-child transaction nesting relationship, while the autonomous transaction stack, as a new stack structure independent of the main transaction stack, is dedicated to managing the creation, nesting, and destruction of autonomous transactions. Each autonomous transaction has a complete and independent transaction context structure, and its lifecycle is managed within this stack.

[0020] In one embodiment, when the execution engine reaches an autonomous transaction, it triggers the autonomous transaction processing flow. First, the system pauses the execution of the main transaction, saving its execution state such as the program counter. Then, the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process, ensuring that the paused state of the main transaction can be accurately recorded and subsequently resumed. Next, a brand-new transaction context structure is created at the top of the autonomous transaction stack, marked as an autonomous transaction, and its nesting level is set to point to the creator transaction context. Finally, the execution context pointer of the current database session process is switched from the main transaction context to the newly created autonomous transaction context, ensuring that all subsequent database operations are executed within the independent environment of this autonomous transaction. The newly created autonomous transaction context is initialized, a new transaction ID is assigned, a snapshot is obtained, and its isolation level is set (inherited from the main transaction by default, but can also be set separately within the autonomous transaction using SET TRANSACTION).

[0021] like Figure 2 The diagram illustrates a dual-stack management architecture. When a routine declared as autonomous is executed, the autonomous process is invoked, the current main transaction is paused, and the current main transaction context is pushed onto the main-child transaction stack for storage. Then, a brand-new autonomous transaction context is created and activated at the top of the autonomous transaction stack. This autonomous transaction context possesses all the characteristics of the main transaction and can independently execute SQL, acquire locks, commit, or rollback. After the autonomous transaction is completed, the autonomous transaction context is popped from the stack, the original main transaction context is restored, and the execution of the main transaction resumes.

[0022] It's important to note that when pushing the main transaction context onto the main transaction stack, the characteristics of different autonomous transactions need to be considered, and differentiated context management strategies should be implemented. Specifically, the resource consumption characteristics of autonomous transactions during historical execution are obtained. These characteristics refer to a set of multi-dimensional performance indicators collected through continuous monitoring of each execution of the autonomous transaction, including but not limited to CPU usage time, peak memory allocation, number of I / O operations, lock holding time, temporary file generation, and network interaction frequency during the execution of the autonomous transaction. Based on these resource consumption characteristics, a resource consumption profile is constructed for the autonomous transaction, thereby determining the appropriate execution mode for that autonomous transaction. Execution modes are divided into two categories: normal execution mode and isolation mode. Normal execution mode involves directly handling context switching within a single database session process, without additional marshaling to external threads. This mode is suitable for lightweight autonomous transactions with low resource consumption, reducing additional thread scheduling overhead and ensuring execution efficiency. The isolation mode requires the resource states needed for autonomous transactions to be marshalled to an external execution thread via a state interface. This external thread then executes the corresponding transaction logic of the autonomous transaction. This mode is suitable for autonomous transactions with long execution times and high resource consumption, avoiding prolonged blocking of the main transaction execution within the current session process and further improving resource utilization. Marshalling resource states refers to serializing and passing the key execution parameters, initialized secondary resource references, and snapshot information required for transaction initiation from the autonomous transaction context to the external execution thread. This allows the external execution thread to independently reconstruct the execution environment of the autonomous transaction without accessing the main session process's internal data structures. Thus, the main transaction can enter a recoverable state without waiting for the long-running autonomous transaction to complete, improving the overall responsiveness of transaction scheduling.

[0023] Autonomous transaction operations are divided into transaction block operations and transaction operations. The upper-level operations are transaction block operations, which mainly involve the entry and exit processing of the autonomous transaction execution and changes in the transaction block state. They do not focus on the specific execution details of the transaction; the actual transaction operations are completed by the underlying functions. There are also transaction stack operations between autonomous transactions and outer transactions. The outer transaction refers to the caller of the current autonomous transaction, i.e., the transaction that is suspended and pushed onto the stack due to the start of the autonomous transaction. It can be the main transaction, a sub-transaction, or a higher-level autonomous transaction. This application embodiment uses the main transaction as an example. When the main transaction is replaced with a sub-transaction or a higher-level autonomous transaction, the autonomous transaction implementation method provided in this application embodiment is still applicable. Specific operation functions are shown in Table 1: Table 1 Operation Function Table

[0024] Autonomous affairs exist in multiple states, as shown in Table 2: Table 2 Transaction Status Table

[0025] A transaction block state is a status identifier used to represent the current lifecycle stage of a transaction. Autonomous transactions rely on transaction block states to manage their independent transaction operations, isolating them from the execution flow of outer transactions. The transaction block state transitions as operations are performed on an autonomous transaction.

[0026] Specifically, before creating an autonomous transaction, the outer transaction may be a main transaction, a sub-transaction, or an autonomous transaction, and the transaction block state is the corresponding outer transaction block state. After creating an autonomous transaction context for executing the autonomous transaction, the system enters the autonomous transaction context environment. The state change here is accomplished by calling the function `BeginInternalAutonTransaction`, which has two steps: first, it creates the autonomous transaction and initializes the transaction block state to the starting state; second, it starts the autonomous transaction and sets the transaction block state to the running state. If the autonomous transaction fails to start, `PopAutonTransaction` is called to pop the autonomous transaction from the autonomous transaction stack. At this point, the transaction block state will revert to the original outer transaction state.

[0027] An autonomous transaction is always in a running state during its execution. Upon completion, if the autonomous transaction finishes successfully, the `ReleaseCurrentAutonTransaction` function is called to commit the current autonomous transaction. If an error occurs during execution, the `RollbackAndReleaseCurrentAutonTransaction` function is called to roll back the current autonomous transaction. After committing or rolling back the autonomous transaction, the transaction block state is set to an idle state, and then the autonomous transaction is popped from the autonomous transaction stack, thus restoring the first resource corresponding to the main transaction. At this point, the transaction block state will be restored to the outer transaction state, i.e., the current state of the main transaction.

[0028] By switching autonomous transaction states, a closed loop can be formed between transaction block states and dual-stack context management in the PostgreSQL single-process session model. Transaction block states provide identifiable lifecycle identifiers, and dual stacks provide context isolation for physical containers. Together, they ensure that when autonomous transactions and main transactions switch within the same process space, there will be no state pollution due to creation failure, nor resource contention or isolation breaches due to incomplete termination processes. Thus, fault-tolerant autonomous transaction lifecycle management is achieved without relying on virtual processes.

[0029] When an autonomous transaction needs to execute a COMMIT command during its execution, before executing this commit command, the transaction block state is set to the running state, the CommitTransactionCommand function is called to commit the transaction, and the transaction block state is set to the idle state. At this point, the current autonomous transaction is completely terminated. Then, the StartTransactionCommand function is called to start a new transaction, and the transaction block state is set to the running state. If the new transaction fails during execution, the process jumps back to the command execution entry point, restores the transaction block state to the running state, and then executes the rollback autonomous transaction process. Through this state switching method, if the commit operation cannot be completed, the autonomous transaction will not remain in an uncertain intermediate state, but will instead undo the executed data modifications, release the occupied secondary resources, and exit the autonomous transaction context through a rollback operation, avoiding state pollution or resource blocking for suspended outer transactions within the session.

[0030] If an autonomous transaction needs to execute a ROLLBACK command (rollback command) during its execution, the `exec_stmt_rollback` function, which handles the rollback command, sets the transaction block state to the running state. It then calls the `AbortCurrentTransaction` function to roll back the transaction and sets the transaction block state to the idle state, at which point the autonomous transaction is terminated. Next, the `StartTransactionCommand` function is called to start a new transaction to maintain the continuity of the autonomous transaction context within the session, and sets the transaction block state to the running state. If the execution fails, it jumps back to the `exec_stmt_rollback` function, restores the transaction block state to the running state, and then executes the rollback process again. Through this method, even if an exception occurs during the execution of the rollback command itself, the overall stability when multiple transactions coexist within a single process can be guaranteed by forcibly rolling back and terminating the process.

[0031] S102: When performing a context switch, traverse the pre-configured resource registry to determine the first resource that needs to be isolated in the main transaction and the isolation strength corresponding to the first resource.

[0032] The essential purpose of context switching is to achieve complete isolation between the main transaction and autonomous transactions. The core obstacle to transaction isolation lies in the sharing of session-level global resources. In PostgreSQL's single-process session model, various transaction resources within a database session process are not inherently bound to a specific transaction context, but rather exist as a global state within the session space. If these shared resources are not identified and isolated during context switching, the main transaction and autonomous transactions will be in the same resource environment. Any operation by the autonomous transaction on resources will directly overwrite or interfere with the resource state of the main transaction, causing confusion in transaction identification, memory allocation, lock holding, snapshot visibility, etc., thus completely losing transaction independence.

[0033] In this embodiment, the system maintains a session-level global resource list that needs to be isolated, i.e., a resource registry. The resource registry includes, but is not limited to, transaction memory context, resource owners, various lock manager states, snapshots, file descriptors, and specific background process state variables. Each resource defines its corresponding standard callback function: pre_backup (pre-switch preprocessing), backup (saving the current state), init (initializing resources for the new transaction), and restore (restoring the original state). This embodiment replaces the traditional virtual process's potential overall resource copying with callback-based saving and restoring of resource states. During context switching, the pre-configured resource registry needs to be traversed to determine the first resource to be isolated in the main transaction and its corresponding isolation strength. Isolation strength refers to the isolation strategy level set for different types of resources. Its purpose is to adopt differentiated isolation timing and methods based on resource characteristics, minimizing performance overhead caused by resource copying while ensuring transaction isolation. Clearly defining the first resource to be isolated and its corresponding isolation strength allows for pre-determining which resources need immediate isolation and which can be delayed before the actual transaction context switch, thus providing a scheduling basis for subsequent resource state saving and initialization operations. It should be noted that the traversal of the resource registry and the determination of isolation strength must be completed synchronously as an integral part of the context switch, in close succession, rather than as a separate step before or after the context switch.

[0034] Isolation strength specifically includes two types: mandatory isolation and on-demand isolation. Mandatory isolation is applicable to core resources that have a decisive impact on transaction correctness and data consistency, such as transaction identifiers, current snapshots, resource owner pointers, and transaction memory contexts. If the state of these resources becomes mixed, it will directly lead to the indistinguishability between autonomous transactions and the main transaction at the transaction boundary. Therefore, state saving and new resource initialization must be performed immediately at the moment of execution context switch, without any delay. On-demand isolation is applicable to auxiliary resources that are not accessed by all autonomous transactions, such as state variables of specific background processes, temporary file handle tables, and certain cache structures. If these resources are unconditionally backed up and initialized with every context switch, it will cause a large amount of unnecessary resource copying overhead, especially in scenarios where autonomous transactions are frequently called but resource access patterns are simple, resulting in significant performance losses. Therefore, the system adopts a lazy strategy for on-demand isolated resources, triggering isolation operations only when an autonomous transaction actually accesses this type of resource for the first time, thereby achieving fine-grained resource management of loading on demand and isolating resources only when needed. The on-demand isolation mechanism avoids unnecessary copying of unused resources, significantly reduces switching overhead, and meets the needs of scenarios such as audit logs for independent transaction commits while maintaining the original simplicity of the architecture.

[0035] This application embodiment achieves on-demand state switching of core resources through a fine-grained resource isolation mechanism and a lazy isolation strategy. Compared with the virtual process solution, which may require the overall saving and restoration of the entire virtual context or maintenance of shadow resources, this method is more convenient and directly performs callback processing on real resources, greatly reducing the overhead of state switching. Its performance advantage is more obvious in high-frequency application scenarios such as audit logs.

[0036] S103: According to the isolation strategy corresponding to the isolation strength, at the corresponding resource isolation execution time, save the resource state corresponding to the main transaction to the resource isolation stack, and initialize the second resource corresponding to the autonomous transaction.

[0037] After identifying the first resources and determining their isolation strength by traversing the resource registry, differentiated isolation strategies are executed at the appropriate resource isolation execution times according to the isolation strength corresponding to each first resource. By calling the backup callback function, the resource state corresponding to the main transaction is saved to the resource isolation stack. Then, the init callback function is called to synchronously initialize the second resource corresponding to the autonomous transaction, ultimately achieving physical separation between the resource environment of the main transaction and the resource environment of the autonomous transaction. The resource isolation stack is a stack-based storage structure that works in conjunction with the main transaction stack and the autonomous transaction stack. Its function is to provide temporary state snapshot storage space for the isolated main transaction resources during each context switch. The second resource belongs to the same resource category as the first resource, but it is completely independent of the first resource in terms of memory address, state value, and lifecycle management, thus ensuring that the autonomous transaction's operations on this resource do not back-infiltrate the main transaction's resource space.

[0038] In one embodiment, when the isolation strength corresponding to the first resource is mandatory isolation, according to the mandatory isolation strategy, during context switching, a backup callback is executed to save the resource state of the main transaction to the resource isolation stack, and an init callback is executed to initialize a completely new set of second resources for the autonomous transaction to be created. The execution of mandatory isolation is strictly limited to the synchronization phase of context switching, that is, before the autonomous transaction context is activated but before any transaction logic is executed. This is because the resource types targeted by mandatory isolation, such as transaction identifiers, current snapshots, resource owners, and transaction memory contexts, are all fundamental resources for transaction execution. If there is any delay, the autonomous transaction will share the same resource state with the main transaction at the initial stage of startup, leading to blurred transaction boundaries and compromised data consistency. The mandatory isolation strategy ensures that the autonomous transaction runs in a completely independent resource environment from the beginning, fundamentally preventing the mixing of states between the main transaction and the autonomous transaction on core resources.

[0039] When the isolation strength corresponding to the first resource is on-demand isolation, according to the lazy isolation strategy, the main transaction resource state is not immediately saved and new resources are created when the autonomous transaction starts. Instead, a corresponding isolation identifier is generated only for the first resource. The isolation identifier usually exists in the form of a flag or hash entry, and its function is to record that the first resource has been identified as an on-demand isolation type, but the actual isolation operation has not yet been triggered. The isolation identifier is associated with the type information of the first resource, rather than being bound to a specific first resource instance. This means that all on-demand isolation resources of the same type share the semantics of the same isolation identifier. After generating the isolation identifier, the execution context switch continues, and the autonomous transaction begins to execute transaction logic in its context. When the autonomous transaction accesses a second resource of the same type as the first resource for the first time during execution, it identifies this as the first access scenario of an on-demand isolation resource by querying the isolation identifier corresponding to the resource type, and then triggers a delayed isolation operation. The current state of the first resource corresponding to the main transaction is saved to the resource isolation stack, and then the init callback function is called to create the second resource, and the resource access request of the autonomous transaction is redirected to the newly created second resource.

[0040] The aforementioned lazy isolation strategy ensures that the timing of isolation operations is driven entirely by the actual resource access behavior of the autonomous transaction, rather than by the fixed timing of context switching. This avoids unnecessary copying of unused resources by the autonomous transaction, significantly improving performance in scenarios with high-frequency creation of autonomous transactions. Taking temporary file handles as an example, when starting an autonomous transaction, if it does not involve temporary file operations, there is no need to back up and restore all entries of the table. The saving of the current state of the resource and the initialization of the new table are only triggered when the autonomous transaction first attempts to create the temporary file. This can significantly reduce overhead in autonomous transactions with high-frequency calls but simple resource access patterns (such as those that only log).

[0041] In one embodiment, under the lazy strategy of on-demand isolation, although unnecessary copying of unused resources by autonomous transactions is avoided, when an autonomous transaction first accesses a certain type of on-demand isolated resource, the state of the main transaction resource must be saved immediately upon the access. If this isolation operation involves complex resource structures or a large amount of state data, it will inevitably cause a response delay for the first access, affecting the smoothness of the autonomous transaction's execution. Therefore, before the autonomous transaction first accesses a second resource of the same type as the first resource and triggers lazy isolation, this embodiment of the application predicts the autonomous transaction's resource access intention in advance and uses pre-isolation to complete the state transition of resources with a high probability of being accessed in advance. This shifts the copying overhead of on-demand isolation from the access critical path to the switching preparation stage, achieving a smooth experience with zero waiting time during access.

[0042] Specifically, after determining that a certain type of resource belongs to the on-demand isolation type, an access trajectory summary corresponding to that autonomous transaction is obtained. The access trajectory summary is generated based on the historical execution records of the autonomous transaction, including the historical access frequency and corresponding confidence level of each resource marked as on-demand isolated. The confidence level refers to the measure of the reliability of the prediction that the autonomous transaction will still access a certain type of resource in this execution. This value is not fixed but dynamically adjusted according to the matching results between previous predictions and actual access results; the confidence level increases when the prediction is correct and decreases when the prediction is incorrect. Based on the access trajectory summary, the historical access frequency and confidence level of each type of on-demand isolated resource are extracted. Based on historical access frequency and confidence level, a predicted resource set required for this autonomous transaction is determined from the resources marked as on-demand isolation. Specifically, resources with a historical access frequency of non-zero are selected as candidate resources from the on-demand isolation resources. Then, the historical access frequency, confidence level, and isolation cost required to complete the isolation operation of each candidate resource are combined, and the sum of the products of the above parameters is calculated to obtain the pre-isolation index of each candidate resource. The pre-isolation index reflects the overall benefit-cost ratio of isolating the resource in advance. Candidate resources are added to the predicted resource set in descending order of pre-isolation index until the cumulative isolation cost of all predicted resources in the predicted resource set reaches the pre-isolation resource limit set by the current database session process. The pre-isolation resource limit refers to the total cost threshold of on-demand isolation resources that are allowed to be isolated in advance in the current context switch step. This threshold can be dynamically adjusted according to the current system memory load and session performance configuration.

[0043] After determining the predicted resource set, and after the context switch is completed but before the autonomous transaction begins executing its transaction logic, a mandatory pre-isolation operation is synchronously performed on these predicted resources. This pre-saves the resource state of the main transaction to the resource isolation stack and initializes the new resources. This ensures that when the autonomous transaction first accesses a predicted resource, its resource state has already been saved and initialized. The autonomous transaction can then directly use the initialized second resource without triggering any isolation process, eliminating the first-access latency under the on-demand isolation strategy. Furthermore, pre-isolation only applies to the high-confidence predicted resource set; on-demand isolated resources not included in the predicted set maintain a lazy strategy, avoiding unnecessary isolation costs.

[0044] In one embodiment, after executing the transaction logic corresponding to the autonomous transaction, the second resource actually accessed in this autonomous transaction is determined. This second resource is matched with the predicted resource set to determine the hit status of each predicted resource in the predicted resource set. The hit status refers to whether the predicted resource was actually accessed in this autonomous transaction. If the predicted resource was actually accessed, it is considered a hit; otherwise, it is considered a miss. Based on the hit status, the confidence level of the corresponding resource in the access trajectory summary is dynamically adjusted. For a hit predicted resource, its confidence level is adjusted upwards by a preset step size to increase the probability that the resource will be included in the predicted set in subsequent similar autonomous transactions. For a miss predicted resource, its confidence level is adjusted downwards by a preset step size to decrease the probability that it will be included in the predicted set in subsequent similar autonomous transactions. This dynamic adjustment mechanism ensures that the predicted resource set always matches the actual access situation of the current autonomous transaction, guaranteeing performance gains while avoiding the additional overhead caused by ineffective pre-isolation, gradually optimizing the adaptability of the pre-isolation strategy, and improving the overall execution performance of the autonomous transaction.

[0045] S104: Based on the autonomous transaction context, execute the transaction logic corresponding to the autonomous transaction, and after the autonomous transaction is completed, restore the first resource corresponding to the main transaction and switch the execution context to the main transaction context.

[0046] After completing the context switch and resource isolation, the transaction logic of the autonomous transaction is performed within its own autonomous transaction context. INSERT operations use the autonomous transaction's own transaction ID, snapshot, and locks. COMMIT operations only commit the autonomous transaction's own modifications and release the locks held by the main transaction; this commit has no impact on the main transaction, which is currently suspended. Each autonomous transaction executes its own SQL operations and data modifications independently. All resource changes and state modifications resulting from these operations only affect the isolated second resource and do not affect the first resource state saved by the main transaction, thus ensuring the operational independence of the two transactions from the outset. After all the autonomous transaction's transaction logic is completed, the restore callback function is called to pop the saved main transaction resource state from the resource isolation stack, restoring the main transaction's original resource environment. After all first resource states are restored, the transaction context of the completed autonomous transaction is popped from the autonomous transaction stack and cleaned up. Then, the main transaction's transaction context is restored from the main transaction stack and set as the current context. The execution context is switched to the main transaction context, allowing the main transaction to continue execution from its original suspended starting point.

[0047] In traditional database architectures, deadlocks typically occur between different session processes. Each session runs its own transaction independently and competes for lock resources. When multiple sessions form a circular wait chain, the system's deadlock detector can identify and break the deadlock by analyzing the lock wait graph. However, in this embodiment, the dual-stack transaction context management model simulates multiple independent transactions within a single database session process. This introduces a new deadlock scenario: an autonomous transaction may be waiting for a lock that is already held by the main transaction but cannot be released because the main transaction is paused. The essential difference between this type of deadlock occurring within the same session and traditional cross-session deadlocks is that the waiting party and the holding party are not distributed in independent process spaces, but coexist within a single session process. Traditional deadlock detection logic, because it defaults to constructing the wait relationship graph at the session or process level, cannot identify this kind of circular wait hidden within a single process. Therefore, the deadlock detection mechanism must be specifically enhanced.

[0048] Specifically, the existing deadlock detector is expanded by adding checks on whether the waiting party and the holder belong to the same session and whether the holder's transaction is currently paused when collecting information on waiting transactions. In other words, if an autonomous transaction fails to acquire a target lock and enters a waiting state while attempting to execute its transaction logic, it means that the target lock is already held by another transaction. At this point, the enhanced deadlock detection logic needs to be triggered to determine the current holder of the target lock. If the current holder is the main transaction and the main transaction is paused, the autonomous transaction cannot continue, and the main transaction is also paused. If the lock cannot be released, a circular wait will occur within the single session. After determining that a deadlock exists within the same session between the autonomous transaction and the main transaction, the autonomous transaction is rolled back. Once the autonomous transaction has completed the rollback and completely released the secondary resource it holds, the target lock resource held by the main transaction is no longer being waited for by the autonomous transaction. The main transaction can then safely resume execution after the autonomous transaction's rollback is complete, continuing its transaction logic from the previous pause point. After the rollback, the system will also return a deadlock error message to the client, indicating that the autonomous transaction call was terminated due to a deadlock within the same session. The upper-layer application can then decide whether to retry or take other compensatory measures based on business needs.

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

[0050] Figure 3 This is a schematic diagram of the structure of an autonomous transaction implementation device based on a PostgreSQL database, provided in an embodiment of this application. Figure 3 As shown, it includes: At least one processor; and, At least one processor-communication-connected memory; wherein, The memory stores instructions that can be executed by at least one processor, which enables the at least one processor to perform the autonomous transaction implementation method based on the PostgreSQL database as described in any of the preceding items.

[0051] This application provides a non-volatile computer storage medium storing computer-executable instructions, which are configured as follows: The autonomous transaction implementation method based on the PostgreSQL database as described in any of the preceding items.

[0052] The various embodiments in this application 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 basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

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

Claims

1. A method for implementing autonomous transactions based on the PostgreSQL database, characterized in that, The method includes: During the execution of the main transaction in the current database session process, in response to the call to a pre-declared autonomous transaction, the execution context of the current database session process is switched from the main transaction context to the autonomous transaction context through the stack structure corresponding to each transaction maintained by the current database session process. When performing a context switch, the pre-configured resource registry is traversed to determine the first resource that needs to be isolated in the main transaction and the isolation strength corresponding to the first resource. According to the isolation strategy corresponding to the isolation strength, at the corresponding resource isolation execution time, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized; Based on the autonomous transaction context, the transaction logic corresponding to the autonomous transaction is executed, and after the autonomous transaction is completed, the first resource corresponding to the main transaction is restored, and the execution context is switched to the main transaction context; The isolation strength includes mandatory isolation and on-demand isolation. According to the isolation strategy corresponding to the isolation strength, at the corresponding resource isolation execution time, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized, specifically including: If the isolation strength corresponding to the first resource is the forced isolation, according to the corresponding forced isolation strategy, when performing context switching, the resource state corresponding to the main transaction is saved to the resource isolation stack, and the second resource corresponding to the autonomous transaction is initialized; If the isolation strength corresponding to the first resource is on-demand isolation, an isolation identifier corresponding to the first resource is generated according to the corresponding lazy isolation strategy. When the autonomous transaction accesses the second resource of the same type as the first resource for the first time, the resource state corresponding to the main transaction is saved to the resource isolation stack through the isolation identifier, and the second resource corresponding to the autonomous transaction is initialized.

2. The autonomous transaction implementation method based on PostgreSQL database according to claim 1, characterized in that, The stack structure includes a main transaction stack and autonomous transaction stacks independent of the main transaction stack. By using the stack structure corresponding to each transaction maintained by the current database session process, the execution context of the current database session process is switched from the main transaction context to the autonomous transaction context. Specifically, this includes: The execution of the main transaction is paused, and the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process; In the autonomous transaction stack maintained by the current database session process, an autonomous transaction context for executing the autonomous transaction is created, and the execution context of the current database session process is switched to the autonomous transaction context.

3. The autonomous transaction implementation method based on PostgreSQL database according to claim 1, characterized in that, Before the autonomous transaction first accesses a second resource of the same type as the first resource, saves the resource state corresponding to the main transaction to the resource isolation stack, and initializes the second resource corresponding to the autonomous transaction, the method further includes: Obtain an access trajectory summary of the autonomous transaction; wherein the access trajectory summary is generated based on the historical execution records of the autonomous transaction, including the historical access frequency and corresponding confidence level of each resource marked as on-demand isolation; Based on the historical access frequency and the confidence level, determine the predicted set of resources required for this execution of the autonomous transaction from the resources marked as on-demand isolated; Predictive resources in the predicted resource set are pre-isolated so that when the autonomous transaction first accesses the predicted resource, the predicted resource has already completed resource state saving and resource initialization.

4. The autonomous transaction implementation method based on PostgreSQL database according to claim 2, characterized in that, Creating an autonomous transaction context for executing the autonomous transaction, specifically including: An autonomous transaction context is created for executing the autonomous transaction, and the transaction block state of the autonomous transaction is initialized to the starting state; wherein, the transaction block state refers to the state identifier used to characterize the current lifecycle stage of the transaction; Set the transaction block status to running state to start the autonomous transaction; If the autonomous transaction fails to start, the autonomous transaction is popped from the autonomous transaction stack, and the transaction block state is restored to the outer transaction state.

5. The autonomous transaction implementation method based on PostgreSQL database according to claim 4, characterized in that, After the autonomous transaction is completed, the first resource corresponding to the main transaction is restored, specifically including: After the autonomous transaction is completed, the autonomous transaction is committed or rolled back, the transaction block status is set to idle, and the autonomous transaction is popped from the autonomous transaction stack to restore the first resource corresponding to the main transaction.

6. The method for implementing autonomous transactions based on a PostgreSQL database according to claim 1, characterized in that, Executing the transaction logic corresponding to the autonomous transaction specifically includes: Attempt to execute the transaction logic corresponding to the autonomous transaction; if the autonomous transaction fails to apply for the target lock resource and enters a waiting state, determine the current holder of the target lock resource. If the current holder is the main transaction and the main transaction is in a suspended state, it is determined that there is an intra-session deadlock between the autonomous transaction and the main transaction; Roll back the autonomous transaction and release the second resource occupied by the autonomous transaction so that the main transaction can resume execution after the autonomous transaction is rolled back.

7. The autonomous transaction implementation method based on PostgreSQL database according to claim 2, characterized in that, Pushing the main transaction context corresponding to the main transaction onto the main transaction stack maintained by the current database session process specifically includes: Based on the resource consumption characteristics of the autonomous transaction during its historical execution process, the execution mode corresponding to the autonomous transaction is determined; wherein, the execution mode includes the isolation mode; When the execution mode is the isolation mode, the main transaction context corresponding to the main transaction is pushed onto the main transaction stack maintained by the current database session process, and the resource status required by the autonomous transaction is marshalled to the external execution thread so that the transaction logic corresponding to the autonomous transaction can be executed through the external execution thread.

8. An autonomous transaction implementation device based on a PostgreSQL database, characterized in that, The device includes: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which are executed by the at least one processor to enable the at least one processor to perform the autonomous transaction implementation method based on the PostgreSQL database as described in any one of claims 1-7.

9. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are set as follows: Perform the autonomous transaction implementation method based on the PostgreSQL database as described in any one of claims 1-7.

Images