Transaction control system and transaction control method for database

Abstract

The invention discloses a transaction control system and method for a database. As an openGauss kernel extension module, the system is composed of a session transaction context manager, an SQL statement classification interceptor, a DML transaction state machine engine, a DDL transaction coordinator and a PL / pgSQL transaction boundary analyzer. The system supports session-level automatic submission mode configuration, a DML transaction block is automatically started through a state machine in a non-automatic submission mode, and multi-statement atomicity can be achieved without depending on BEGIN statements; the DDL operation is independently and automatically submitted according to a strategy or is decoupled from a DML transaction for execution; and the PL / pgSQL storage process analyzes a forced explicit transaction boundary through a static code. According to the method, transaction management codes are simplified, atomic damage caused by omission of BEGIN is avoided, unified arrangement of DDL and DML is supported, the robustness of the storage process is improved, and the method is transparent and compatible with standard application and has good expandability.

Description

Technical Field

[0001] This application belongs to the field of database transaction management technology, and specifically relates to a database transaction control system and transaction control method. Background Technology

[0002] I. Limitations of openGauss's native transaction mechanism

[0003] As an open-source relational database, openGauss supports transaction control commands including start (BEGIN / STARTTRANSACTION), set, commit (COMMIT), and rollback (ROLLBACK). Users must explicitly start a transaction before subsequent DML operations (INSERT / UPDATE / DELETE) can be included in the same transaction unit; if no transaction is explicitly started, the default is automatic commit mode, where each DML statement is an independent transaction and committed immediately. This mechanism has the following limitations:

[0004] (1) Rigid DML transaction boundary management: Developers must strictly follow the transaction control statement specifications. If BEGIN is omitted, the DML sequence that should be executed atomically may be split into multiple independent transactions, which will destroy the atomicity of business logic (such as the separation of deduction and crediting operations in bank transfer scenarios, which will lead to financial risks).

[0005] (2) Lack of DDL transaction control: DDL operations (CREATE / ALTER / DROP TABLE, etc.) do not support transaction rollback in openGauss, or trigger implicit commit immediately after execution in a certain mode, which cannot be orchestrated in the same transaction as DML operations, increasing the difficulty and risk of managing complex data migration or architecture changes.

[0006] (3) Ambiguous transaction management in PL / pSQL: In PL / pSQL, transactions that are not explicitly committed / rolled back will be delayed until the stored procedure ends, which may lead to data inconsistency risks.

[0007] II. OpenGauss explicit transactions lack compatibility with auto-commit and non-auto-commit modes.

[0008] The openGauss explicit transaction mechanism does not support flexible switching between auto-commit and non-auto-commit modes. Users must rely on statements such as BEGIN / COMMIT to manually manage transaction boundaries, resulting in high development complexity and susceptibility to errors due to human oversight. Furthermore, the lack of a mandatory constraint mechanism for explicit transaction control in PL / pSQL further exacerbates the difficulty of ensuring data consistency. Summary of the Invention

[0009] To overcome the aforementioned deficiencies in the existing technology, this invention proposes a novel transaction control system and transaction control method based on the openGauss database.

[0010] This invention aims to construct a high-performance transaction control system for complex business scenarios, improving the flexibility and security of transaction management. Specific objectives include:

[0011] (1) Extend the openGauss transaction mechanism to support dual-mode configuration capability of automatic commit (AUTOCOMMIT) and non-automatic commit (NON-AUTOCOMMIT);

[0012] (2) Implement explicit transaction control for DML operations in non-auto-commit mode to ensure the atomicity of multi-statement operations;

[0013] (3) Optimize the transaction processing mechanism of DDL operations so that it has the feature of automatic commit and does not need to rely on traditional transaction control statements;

[0014] (4) Strengthen transaction management in PL / pgSQL stored procedures, improve the robustness of transaction boundary control, avoid data inconsistency risks, and enhance the reliability and stability of the system.

[0015] To achieve the above objectives, the present invention employs the following technical strategies:

[0016] (1) DML explicit transaction control: The transaction state management based on the state machine engine is adopted to realize the non-autocommit mode, so that DML operations can be included in explicit transaction control without relying on START TRANSACTION, thereby enhancing the flexibility and reliability of transaction control.

[0017] (2) DDL auto-commit optimization: Implement an isolation processing mechanism for DDL operations to enable them to have the feature of auto-commit and decouple them from DML transaction state, thereby reducing development complexity and the risk of misoperation.

[0018] (3) PL / pgSQL transaction enhancement: Force explicit transaction termination (commit or rollback) during stored procedure execution, eliminate data consistency risks caused by implicit commit, and improve the determinism and stability of stored procedure execution.

[0019] Specifically, this application provides the following technical solutions:

[0020] The first aspect of this application provides a transaction control system for a database, said system being an extension module of the openGauss kernel, such as... Figure 4 As shown, this system includes:

[0021] A session transaction context manager is used to maintain a transaction state context for each database session. The context includes at least an auto-commit mode flag, an active transaction block identifier, and a DDL processing strategy.

[0022] SQL statement classification and interceptor, used to identify the type of the current SQL statement during the query parsing phase, the type including at least DML statement, DDL statement and transaction control statement;

[0023] The DML transaction state machine engine is used to drive the transaction state transition of DML statements based on the session transaction context and SQL statement type. When in non-auto-commit mode and there is no active transaction block, it automatically calls the kernel function to start a new transaction block to include subsequent DML operations.

[0024] The DDL transaction coordinator is used to independently process DDL statements according to a preset strategy, enabling them to be automatically committed or executed decoupled from the current DML transaction block.

[0025] The PL / pgSQL Transaction Boundary Analyzer is used to perform static code analysis during the stored procedure compilation phase, detect the existence of transaction control points, and enforce explicit transaction boundaries.

[0026] Furthermore, in this application's system, the session transaction context manager configures the auto-commit mode using the following SQL commands:

[0027] SET AUTOCOMMIT TO {ON | OFF};

[0028] ON indicates automatic commit mode, OFF indicates non-automatic commit mode, and the SQL command takes effect on a session-by-session basis.

[0029] Furthermore, in the system of this application, when the DML transaction state machine engine executes the first DML statement in non-auto-commit mode, it automatically changes the transaction state from TBLOCK_DEFAULT to TBLOCK_BEGIN and creates an active transaction block; subsequent DML statements are executed directly when an active transaction block is detected, until a COMMIT or ROLLBACK command is received, after which the entire transaction block is committed or rolled back and the state is restored to TBLOCK_DEFAULT.

[0030] Furthermore, in this application's system, the DDL transaction coordinator configures the processing strategy using the following SQL commands:

[0031] SET DDL_IN_TRANSACTION TO {ON | OFF};

[0032] When the policy is ON, DDL statements are executed within the currently active transaction block of the DML transaction state machine engine and participate in the final commit; when the policy is OFF, DDL statements trigger implicit commits and are executed independently.

[0033] Furthermore, in the system of this application, the PL / pgSQL transaction boundary analyzer is configured with different strict levels. When it is at the highest strict level, if it detects that the stored procedure contains DML or DDL statements but does not have an explicit COMMIT, ROLLBACK, or BEGIN...EXCEPTION...END structure, a compilation error is triggered and execution is refused.

[0034] A second aspect of this application provides a database transaction control method, which is applied to the aforementioned system. This method is implemented based on an openGauss kernel extension, such as... Figure 5 As shown, this method includes the following steps:

[0035] S1. Configure the auto-commit mode flag at the session level, and switch the mode in response to the SET AUTOCOMMIT TO {ON | OFF} command;

[0036] S2. Intercept and identify the type of the current SQL statement during the query parsing phase;

[0037] S3. When a DML statement is identified and is in non-auto-commit mode, the DML transaction state machine engine checks the active transaction block identifier. If there is no active transaction block, the kernel function is automatically called to create a new transaction block and mark its status as active. The DML statement is executed in the active transaction block, and subsequent DML statements are included in the same active transaction block until a COMMIT or ROLLBACK command is received.

[0038] S4. When a DDL statement is identified, its execution method is determined according to the preset DDL_IN_TRANSACTION strategy. The strategy includes executing it within the current active transaction block and participating in the commit, or executing it independently and triggering an implicit commit.

[0039] S5. When identified as a PL / pgSQL stored procedure, static code analysis is performed during the compilation phase, requiring the inclusion of explicit transaction boundary control structures; otherwise, compilation is refused.

[0040] Furthermore, in the method of this application, the management of the active transaction block in step S3 is driven by a state machine, specifically including:

[0041] The first DML statement triggers the state transition from TBLOCK_DEFAULT to TBLOCK_BEGIN;

[0042] Subsequent DML statements will maintain the TBLOCK_BEGIN state.

[0043] Upon receiving the COMMIT command, the CommitTransactionCommand() function is called to commit the transaction and restore the TBLOCK_DEFAULT state.

[0044] Furthermore, in the method of this application, the execution strategy of the DDL statement in step S4 is configured by the SET DDL_IN_TRANSACTION TO {ON | OFF} instruction. When the strategy is OFF, the current DML transaction block is automatically committed before the DDL is executed, a new transaction is started to execute the DDL, and the current transaction block is committed after the DDL is executed, so that the DDL is executed in a separate transaction.

[0045] Furthermore, in the method of this application, the static code analysis of the PL / pgSQL stored procedure in step S5 is achieved by configuring the plpgsql.txn_strict_mode parameter. When set to the error level, a compilation error is reported for the stored procedure that does not define a clear transaction boundary and a manual verification is requested.

[0046] Furthermore, in the method of this application, step S5 also includes: configuring the strict level of PL / pSQL transaction boundaries by adding a new SQL command (such as SETplpgsql.txn_strict_mode) to force developers to define clear transaction boundaries.

[0047] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0048] Memory: Used to store computer programs;

[0049] Processor: Used to execute the computer program to implement the aforementioned database transaction control method.

[0050] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned database transaction control method.

[0051] In summary, compared with the prior art, the present invention has the following advantages:

[0052] (1) Improve development efficiency and reduce transaction management error rate: Through the non-autocommit explicit transaction mode, developers only need to configure transaction attributes once at the session level, without having to repeatedly write BEGIN statements, thereby simplifying code logic and fundamentally avoiding the risk of atomicity destruction caused by missing transaction start statements.

[0053] (2) Enhance the flexibility of transaction control and data consistency: Through the DDL transaction control mechanism, database structure changes are incorporated into a unified transaction management framework, supporting atomic execution in coordination with DML operations, and meeting the dual guarantee requirements of data and structure consistency in complex business scenarios (such as versioned data migration).

[0054] (3) Improve code quality and maintainability: By enforcing the transaction boundaries within PL / pgSQL stored procedures, the execution logic boundaries and side effect range of stored procedures are clearly defined, significantly improving the reliability and maintainability of database server code.

[0055] (4) Good compatibility and scalability: This system adopts a kernel extension mechanism. New SQL commands (such as SET SESSION_AUTOCOMMIT and SET DDL_IN_TRANSACTION) take effect at the session level, and are completely transparently compatible with existing applications that follow standard transaction syntax. This system is based on a state machine architecture, has good scalability, and can integrate more complex transaction optimization strategies. Attached Figure Description

[0056] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0057] Figure 1 This is a system architecture diagram of the system of the present invention in the openGauss kernel.

[0058] Figure 2 This is a flowchart illustrating the transformation process of the "DML transaction state machine engine in non-automatic commit mode" and the working logic of the "DDL transaction coordinator" in the embodiments of this application.

[0059] Figure 3 This is a timing diagram of the financial transfer execution in the embodiment of this application.

[0060] Figure 4 This is a structural diagram of the transaction control system of the database in this application.

[0061] Figure 5 This is a flowchart illustrating the overall implementation of the transaction control method for the database in this application.

[0062] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0063] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0064] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0065] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0066] Example: A transaction control system and transaction control method for a database.

[0067] This invention constructs a layered, modular database kernel extension system. The core design involves an "intelligent transaction state machine" intervening during the SQL statement parsing and execution phase to dynamically manage the lifecycle of transactions, rather than relying solely on fixed syntax keywords.

[0068] I. System Architecture Design

[0069] Figure 1 This is a system architecture diagram of the system of the present invention in the openGauss kernel. As an extension module of the openGauss kernel, this system mainly includes the following core components:

[0070] (1) Session Transaction Context Manager: Maintains a core transaction state context for each database session, including the current "autocommit mode" flag (AUTOCOMMIT_MODE), active transaction block identifier, DDL processing strategy, etc.

[0071] (2) SQL Statement Classifier & Interceptor: During the query parsing phase, accurately identify the type of SQL statement to be executed (whether it is DML, DDL, or transaction control statement such as COMMIT).

[0072] (3) Transaction State Machine Engine: As the core of this system, it drives the transition of transaction state based on the "session transaction context" and "SQL statement type". For example, when AUTOCOMMIT_MODE=OFF and a DML statement is received, the state machine checks whether there is an active transaction block. If not, it automatically calls the internal function BeginTransactionBlock() to create a new transaction block and sets the state to TBLOCK_BEGIN before executing the DML statement, thus simulating the effect of the user writing BEGIN.

[0073] (4) DDL Transaction Coordinator: This is dedicated to handling DDL statements. Based on a preset strategy (which can be configured by the user through new SQL commands, such as SET DDL_IN_TRANSACTION = ON / OFF), it decides whether to execute the DDL statement within the currently active transaction block (participating in the transaction's commit / rollback) or to execute it independently and trigger a commit operation.

[0074] (5) PL / pSQL Transaction Boundary Analyzer: During the compilation phase of a stored procedure / function, it performs static code analysis to detect whether it contains explicit transaction control points (COMMIT / ROLLBACK / SAVEPOINT). It can be set to different levels of strictness: from issuing warnings to compilation failure, thereby forcing developers to write code with clear transaction boundaries.

[0075] II. Implementation of Core Functions

[0076] Users can interact with the system using the following new SQL commands:

[0077] SET AUTOCOMMIT TO {ON | OFF}; -- Sets the auto-commit mode for the current session.

[0078] Figure 2The diagram shows the transition process of the "DML transaction state machine engine in non-auto-commit mode" and the working logic of the "DDL transaction coordinator," demonstrating how DDL statements are guided into the current transaction or trigger independent commits according to the DDL_IN_TRANSACTION strategy.

[0079] Scenario 1: Explicit Transaction Control with Non-Auto-Commit in DML (Core Process)

[0080] 1. User executes: SET AUTOCOMMIT TO OFF.

[0081] 2. The user executes a DML statement: INSERT INTO t1 VALUES (1).

[0082] 3. The SQL interceptor recognized that this was a DML statement.

[0083] 4. The session context manager returns the current AUTOCOMMIT_MODE=OFF.

[0084] 5. The transaction state machine engine starts working: a. Check the context: there is currently no active transaction block (current_tblock == TBLOCK_DEFAULT); b. Automatically call the kernel function BeginTransactionBlock() to set the current_tblock state to TBLOCK_BEGIN (this step replaces the user-written START TRANSACTION); c. Execute the INSERT statement, at this time the data modification is not persisted.

[0085] 6. The user continues to execute `UPDATE t1 SET col=2 WHERE id=1`. Steps 3-5b are repeated, but this time the state machine detects `current_tblock = TBLOCK_BEGIN`, so step 5b is skipped, and the UPDATE is executed directly. Both DML operations belong to the same implicitly started explicit transaction.

[0086] 7. The user executes COMMIT. The state machine receives this command, calls CommitTransactionCommand(), commits the entire transaction block, and restores the state to TBLOCK_DEFAULT.

[0087] 8. If the user executes ROLLBACK after step 6, the state machine will roll back the entire transaction block.

[0088] Scenario 2: DDL Transaction Coordination and Control

[0089] In the same session of Scenario 1, users can include DDL under transaction control by using the SET DDL_IN_TRANSACTION TO ON; command.

[0090] 1. Following step 6 of scenario 1, the current state is TBLOCK_BEGIN.

[0091] 2. User executes: CREATE TABLE t2 (id int).

[0092] 3. The SQL interceptor identifies it as DDL.

[0093] 4. The DDL transaction coordinator check policy is ON.

[0094] 5. The coordinator notifies the transaction state machine engine that the DDL will be executed within the currently active transaction block (TBLOCK_BEGIN). The engine ensures that metadata changes resulting from the DDL operation are recorded in the transaction log.

[0095] 6. When the user finally issues a COMMIT, the data modification on t1 and the table creation operation on t2 are committed as an atomic unit.

[0096] 7. If the policy is OFF (default), the DDL coordinator will trigger an implicit commit before the DDL is executed: that is, first commit the current TBLOCK_BEGIN transaction block, then execute the DDL (the DDL itself is committed immediately as an independent transaction), and then automatically (or while waiting for the next DML) start a new transaction block. This makes the DDL operation finality, but interrupts the atomicity of the previous DML transaction.

[0097] Scenario 3: PL / pSQL Transaction Boundary Enhancement

[0098] The system provides a new compilation option plpgsql.txn_strict_mode.

[0099] When set to error, the transaction boundary analyzer scans the stored procedure body during the compilation phase.

[0100] If any DML / DDL statements are found within the procedure body (according to the policy), but there is no corresponding explicit transaction boundary control structure such as COMMIT, ROLLBACK, or BEGIN...EXCEPTION...END, a compilation error will be reported: "Procedure [procedure name] contains data modification operations but no explicit transaction boundary is defined. Please check."

[0101] The above design forces developers to carefully consider the division of transactions when writing PL / pSQL, so as to avoid accidental commits or leaving incomplete transactions at the end of the process.

[0102] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0103] Example 1: Financial Transaction Scenario

[0104] Requirement: Ensure the atomicity of transfer operations (deduction and receipt) to prevent financial losses. This requirement is very common in financial transactions because any data inconsistency can lead to financial losses.

[0105] Figure 3 This is a timing diagram of the financial transfer execution in this embodiment, which compares and shows the differences in code writing and transaction control between the solution of this invention and the traditional method.

[0106] Transfer business logic code (pseudocode):

[0107] / / No need to write: connection.execute(“BEGIN”);

[0108] connection.execute(“UPDATE accounts SET balance = balance - :amountWHERE id = :fromId”);

[0109] connection.execute(“UPDATE accounts SET balance = balance + :amountWHERE id = :toId”);

[0110] connection.execute(“INSERT INTO transfer_log (from_id, to_id, amount,time) VALUES (?, ?, ?, NOW())”);

[0111] / / Business logic check...

[0112] if (everything is normal) {

[0113] connection.execute(“COMMIT”); / / Commit the three DML statements as an atomic transaction.

[0114] } else {

[0115] connection.execute(“ROLLBACK”); / / Undo all three DML statements.

[0116] }

[0117] Effect: Throughout the entire transfer service, developers don't need to worry about the BEGIN statement; they can focus solely on the business DML and the final COMMIT / ROLLBACK. The system automatically guarantees the atomicity of all operations from the first DML statement to the final command, significantly reducing coding complexity and the possibility of errors.

[0118] Example 2: Billing System Scenario

[0119] Requirement: Handle high-concurrency billing requests and avoid data inconsistencies caused by a mix of DDL and DML statements.

[0120] accomplish:

[0121] -- DDL operations: Auto-commit, independent of any transaction statements.

[0122] CREATE TABLE billing_records (id INT PRIMARY KEY, amount DECIMAL);

[0123] -- Switch to non-auto-commit mode

[0124] SET AUTOCOMMIT OFF;

[0125] -- DML operations: Automatically enter explicit transactions

[0126] INSERT INTO billing_records VALUES (1, 100.00);

[0127] INSERT INTO billing_records VALUES (2, 200.00);

[0128] -- Explicit commit

[0129] COMMIT;

[0130] Results: DDL table creation operations are completed independently and instantly, without affecting the explicit control of subsequent DML transactions. The system logic is clear, and data consistency is guaranteed.

[0131] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0132] like Figure 6 As shown in the illustration, an embodiment of this application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned database transaction control method.

[0133] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0134] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0135] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0136] Conventional procedural programming languages, such as "C" or similar programming languages.

[0137] The execution methods of program code include, but are not limited to:

[0138] It runs entirely on the user's computer;

[0139] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0140] Execute as a standalone software package;

[0141] It is executed entirely on a remote computer or server.

[0142] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0143] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the transaction control method for the database disclosed in this application.

[0144] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0145] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0146] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0147] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0148] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0149] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0150] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0151] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0152] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0153] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0154] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0155] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0156] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the transaction control system and transaction control method of the database disclosed in this application. When the computer program is executed by a processing device, it can achieve the functions defined in the embodiments of this application.

[0157] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0158] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A transaction control system for a database, characterized in that, The system, as an extension module of the openGauss kernel, includes: A session transaction context manager is used to maintain a transaction state context for each database session. The context includes at least an auto-commit mode flag, an active transaction block identifier, and a DDL processing strategy. SQL statement classification and interceptor, used to identify the type of the current SQL statement during the query parsing phase, the type including at least DML statement, DDL statement and transaction control statement; The DML transaction state machine engine is used to drive the transaction state transition of DML statements based on the session transaction context and SQL statement type. When in non-auto-commit mode and there is no active transaction block, it automatically calls the kernel function to start a new transaction block to include subsequent DML operations. The DDL transaction coordinator is used to independently process DDL statements according to a preset strategy, enabling them to be automatically committed or executed decoupled from the current DML transaction block. The PL / pgSQL Transaction Boundary Analyzer is used to perform static code analysis during the stored procedure compilation phase, detect the existence of transaction control points, and enforce explicit transaction boundaries.

2. The system according to claim 1, characterized in that, The session transaction context manager configures auto-commit mode using the following SQL commands: SET AUTOCOMMIT TO {ON | OFF}; ON indicates automatic commit mode, OFF indicates non-automatic commit mode, and the SQL command takes effect on a session-by-session basis.

3. The system according to claim 1, characterized in that, When the DML transaction state machine engine executes the first DML statement in non-auto-commit mode, it automatically changes the transaction state from TBLOCK_DEFAULT to TBLOCK_BEGIN and creates an active transaction block. Subsequent DML statements are executed directly when an active transaction block is detected, until a COMMIT or ROLLBACK command is received, at which point the entire transaction block is committed or rolled back and the state is restored to TBLOCK_DEFAULT.

4. The system according to claim 1, characterized in that, The DDL transaction coordinator configures the processing strategy using the following SQL commands: SET DDL_IN_TRANSACTION TO {ON | OFF}; When the policy is ON, DDL statements are executed within the currently active transaction block of the DML transaction state machine engine and participate in the final commit; When the policy is OFF, DDL statements trigger implicit commits to be executed independently.

5. The system according to claim 1, characterized in that, The PL / pgSQL transaction boundary analyzer is configured with different strict levels. When it is at the highest strict level, if it detects that a stored procedure contains DML or DDL statements but does not have an explicit COMMIT, ROLLBACK, or BEGIN...EXCEPTION...END structure, a compilation error is triggered and execution is refused.

6. A database transaction control method, characterized in that, The method is applied to the system as described in any one of claims 1-5, and the method is implemented based on the openGauss kernel extension, comprising the following steps: S1. Configure the auto-commit mode flag at the session level, and switch the mode in response to the SET AUTOCOMMIT TO {ON | OFF} command; S2. Intercept and identify the type of the current SQL statement during the query parsing phase; S3. When a DML statement is identified and is in non-auto-commit mode, the DML transaction state machine engine checks the active transaction block identifier. If there is no active transaction block, the kernel function is automatically called to create a new transaction block and mark its status as active. The DML statement is executed in the active transaction block, and subsequent DML statements are included in the same active transaction block until a COMMIT or ROLLBACK command is received. S4. When a DDL statement is identified, its execution method is determined according to the preset DDL_IN_TRANSACTION strategy. The strategy includes executing it within the current active transaction block and participating in the commit, or executing it independently and triggering an implicit commit. S5. When identified as a PL / pgSQL stored procedure, static code analysis is performed during the compilation phase, requiring the inclusion of explicit transaction boundary control structures; otherwise, compilation is refused.

7. The method according to claim 6, characterized in that, The management of the active transaction block in step S3 is driven by a state machine, specifically including: The first DML statement triggers the state transition from TBLOCK_DEFAULT to TBLOCK_BEGIN; Subsequent DML statements will maintain the TBLOCK_BEGIN state. Upon receiving the COMMIT command, the CommitTransactionCommand() function is called to commit the transaction and restore the TBLOCK_DEFAULT state.

8. The method according to claim 6, characterized in that, The execution strategy of the DDL statement in step S4 is configured by the SETDDL_IN_TRANSACTION TO {ON | OFF} instruction. When the strategy is OFF, the current DML transaction block is automatically committed before the DDL is executed, a new transaction is started to execute the DDL, and the current transaction block is committed after the DDL is executed, so that the DDL is executed in a separate transaction.

9. The method according to claim 6, characterized in that, The static code analysis of the PL / pgSQL stored procedure described in step S5 is achieved by configuring the plpgsql.txn_strict_mode parameter. When set to the error level, a compilation error is reported for stored procedures that do not define explicit transaction boundaries, and manual verification is requested.

10. The method according to claim 6, characterized in that, Step S5 also includes: configuring the strictness level of PL / pSQL transaction boundaries by adding new SQL commands, forcing developers to define clear transaction boundaries.

Images