Search

CN-122019065-A - Transaction control system and transaction control method of database

CN122019065ACN 122019065 ACN122019065 ACN 122019065ACN-122019065-A

Abstract

The application discloses a transaction control system and a transaction control method for a database. The system is used as openGauss kernel expansion module and consists of session transaction context manager, SQL sentence classification interceptor, DML transaction state machine engine, DDL transaction coordinator and PL/pgSQL transaction boundary analyzer. The system supports the configuration of a session-level automatic submitting mode, automatically opens a DML transaction block through a state machine in a non-automatic submitting mode, can realize multi-sentence atomicity without depending on BEGIN sentences, automatically submits or decouples the DDL operation from the DML transaction according to a strategy, and forces an explicit transaction boundary through static code analysis in a PL/pgSQL storage process. The application simplifies transaction management codes, avoids atomic damage caused by missing BEGIN, supports unified arrangement of DDL and DML, improves robustness of storage process, is transparent and compatible to standard application, and has good expandability.

Inventors

  • ZHAO XIAOLIN
  • Su Zhangyan

Assignees

  • 广州海量数据库技术有限公司

Dates

Publication Date
20260512
Application Date
20260205

Claims (10)

  1. 1. A transaction control system for a database, said system acting as an extension module to a openGauss core, comprising: A session transaction context manager for maintaining a transaction state context for each database session, the context comprising at least an auto-commit mode flag, an active transaction block identifier, and a DDL processing policy; the SQL sentence classification and interceptor is used for identifying the type of the current SQL sentence in the query analysis stage, wherein the type at least comprises a DML sentence, a DDL sentence and a transaction control sentence; the DML transaction state machine engine is used for driving the transaction state conversion of the DML statement according to the session transaction context and the SQL statement type, and automatically calling a kernel function to start a new transaction block to incorporate the subsequent DML operation when the DML transaction state machine engine is in a non-automatic commit mode and no active transaction block exists currently; The DDL transaction coordinator is used for independently processing DDL sentences according to a preset strategy to automatically submit or be decoupled and executed with the current DML transaction block; The PL/pgSQL transaction boundary analyzer is used for carrying out static code analysis in the compiling stage of a storage process, detecting the existence of a transaction control point and forcing the requirement of an explicit transaction boundary.
  2. 2. The system of claim 1, wherein the session transaction context manager implements an auto-commit mode configuration by SQL instructions: SET AUTOCOMMIT TO {ON | OFF}; Where ON indicates an automatic commit mode and OFF indicates a non-automatic commit mode, the SQL instructions are effective in units of sessions.
  3. 3. The system of claim 1, wherein the DML transaction state machine engine automatically transitions the transaction state from TBLOCK _default to TBLOCK _begin and creates an active transaction block when executing a first DML statement in a non-automatic COMMIT mode, and wherein subsequent DML statements execute directly upon detecting that an active transaction block already exists until the entire transaction block is committed or rolled back and state is restored to TBLOCK _default after receipt of a COMMIT or ROLLBACK command.
  4. 4. The system of claim 1, wherein the DDL transaction coordinator configures processing policies by SQL instructions to: SET DDL_IN_TRANSACTION TO {ON | OFF}; When the policy is ON, the DDL statement executes within the currently active transaction block of the DML transaction state machine engine and participates in the final commit, and when the policy is OFF, the DDL statement triggers the implicit commit to execute independently.
  5. 5. The system of claim 1, wherein the PL/pgSQL transaction boundary analyzer is configured to different levels of stringency, when at a highest level of stringency, if stored procedures are detected to contain DML or DDL statements without explicit COMMIT, ROLLBACK or begin.
  6. 6. A method of transaction control of a database, characterized in that the method is applied to a system according to any of claims 1-5, the method being implemented based on openGauss kernel extensions, comprising the steps of: s1, configuring an automatic submitting mode mark at a session level, and responding to SET AUTOCOMMIT TO { ON|OFF } instructions to perform mode switching; s2, intercepting and identifying the type of the current SQL sentence in a query analysis stage; S3, when the DML statement is identified and is in a non-automatic submitting mode, checking an identifier of an active transaction block by a DML transaction state machine engine, and if no active transaction block exists currently, automatically calling a kernel function to create a new transaction block and marking the state of the new transaction block as active; S4, when the DDL statement is identified, determining an execution mode according to a preset DDL_IN_TRANSACTION strategy, wherein the strategy comprises executing IN a current active TRANSACTION block and participating IN commit, or independently executing and triggering implicit commit; s5, when the PL/pgSQL storage process is identified, static code analysis is carried out on the PL/pgSQL storage process in the compiling stage, the forced requirement contains an explicit transaction boundary control structure, and if not, the compiling is refused.
  7. 7. The method according to claim 6, wherein the management of the active transaction block in step S3 is driven by a state machine, in particular comprising: the first DML statement triggers a transition of state from TBLOCK _default to TBLOCK _begin; Keeping TBLOCK _BEGIN state unchanged when the subsequent DML statement is executed; Call CommitTransactionCommand () COMMIT transaction and resume TBLOCK _default state after receipt of the COMMIT command.
  8. 8. The method of claim 6, wherein the execution policy of the DDL statement IN step S4 is configured by a SET ddl_in_transaction TO { on|off } instruction, and when the policy is OFF, a current DDL TRANSACTION block is automatically committed before the DDL is executed, a new TRANSACTION execution DDL is started, and the current TRANSACTION block is committed after the DDL execution, so that the DDL is executed IN a single TRANSACTION.
  9. 9. The method of claim 6, wherein the static code analysis of the PL/pgSQL store procedure in step S5 is implemented by configuring plpgsql.txn_struct_mode parameter, when set to error level, reporting the compiling error for the store procedure without defined explicit transaction boundary and applying for manual checking.
  10. 10. The method of claim 6, wherein step S5 further comprises forcing the developer to define a clear transaction boundary by adding a SQL instruction configuration PL/pSQL transaction boundary severity level.

Description

Transaction control system and transaction control method of database Technical Field The application belongs to the technical field of database transaction management, and particularly relates to a transaction control system and a transaction control method of a database. Background 1. Limitations of openGauss native transaction mechanisms OpenGauss as an open source relational database, supported transaction control commands include start (BEGIN/START TRANSACTION), set, COMMIT (COMMIT), and ROLLBACK (ROLLBACK). After the user needs to explicitly start the transaction, the party can incorporate the subsequent DML operation (INSERT/UPDATE/DELETE) into the same transaction unit, and when the transaction is not explicitly started, the automatic commit mode is adopted by default, and each DML statement independently forms the transaction and is committed in real time. This mechanism has the following limitations: (1) DML transaction boundary management is inflexible and developers need to strictly follow the transaction control statement specifications. If BEGIN is omitted, the DML sequence executed by the atomic response may be split into a plurality of independent transactions, and the logical atomicity of the business is destroyed (e.g. separation of deduction and deposit operation in bank transfer scenario will lead to fund risk). (2) DDL transaction control is missing, namely DDL operation (CREATE/ALTER/DROP TABLE and the like) does not support transaction rollback in openGauss or triggers implicit commit immediately after being executed in a specific mode, and cannot be uniformly arranged in the same transaction with the DML operation, so that the management and control difficulty and risk of complex data migration or architecture change are increased. (3) PL/pSQL transaction management ambiguities-in PL/pSQL, transactions that are not explicitly committed/rolled back may be delayed until processing at the end of the storage process, possibly posing a data inconsistency risk. 2. OpenGauss explicit transactions have insufficient compatibility with auto-commit and non-auto-commit modes OpenGauss explicit transaction mechanisms do not support flexible switching between automatic COMMIT and non-automatic COMMIT modes, and users need to manually manage transaction boundaries depending on sentences such as BEGIN/COMMIT, so that development complexity is high and errors are easily caused by artificial omission. Meanwhile, a forced constraint mechanism for explicit transaction control in PL/pSQL is lacking, so that the difficulty of guaranteeing data consistency is further increased. Disclosure of Invention In order to overcome the defects in the prior art, the invention provides a novel transaction control system and a transaction control method based on openGauss databases. The invention aims to construct a high-performance transaction control system oriented to complex service scenes, improve the flexibility and the safety of transaction management, and specifically aims to comprise: (1) Extending openGauss a transaction mechanism supporting auto-commit (AUTOCOMMIT) and NON-auto-commit (NON-AUTOCOMMIT) dual mode configuration capabilities; (2) Explicit transaction control of the DML operation in a non-automatic commit mode is realized, and atomicity of multi-sentence operation is ensured; (3) Optimizing a transaction processing mechanism of DDL operation to enable the DDL operation to have automatic commit characteristics and not to depend on a traditional transaction control statement; (4) Transaction management in the PL/pgSQL storage process is enhanced, robustness of transaction boundary control is improved, risk of data inconsistency is avoided, and reliability and stability of the system are enhanced. In order to achieve the above object, the present invention adopts the following technical strategies: (1) DML explicit transaction control, namely, adopting transaction state management based on a state machine engine to realize a NON-automatic commit (NON-AUTOCOMMIT) mode, so that DML operation can be incorporated into explicit transaction control without relying on START TRANSACTION, and the flexibility and reliability of the transaction control are enhanced. (2) DDL automatic submission optimization, namely implementing an isolation processing mechanism for DDL operation, so that the DDL operation has the characteristic of automatic submission (AUTOCOMMIT) and is decoupled from a DML transaction state, and development complexity and misoperation risk are reduced. (3) PL/pgSQL transaction strengthening, namely forcing the explicit transaction to be terminated (commit or rollback transaction) in the execution of the storage process, eliminating the hidden trouble of data consistency caused by implicit commit, and improving the certainty and stability of the execution of the storage process. Specifically, the application provides the following technical scheme: A first aspect of the present application pr