HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v5.0

Documentation:v5.0

Supported Versions:
Current(v5.0)

Other Versions:
v3.1/
v3.0/
v2.1/
v2.0/
v1.1

SELECT Auto-Commit Transactions

Overview

There are differences in transaction status control between Oracle and MogDB. Oracle does not initiate transactions for read-only commands and implicitly starts transactions for write commands, which are ended explicitly. MogDB controls transaction status by implicitly starting and ending transactions, and explicitly starting and ending them for any read or write commands that require transaction semantics.

Currently, the driver sends an explicit Begin to the kernel in non-auto-commit mode, and transactions require an explicit Commit from the user to end. This can lead to a problem: if a table has been accessed by a read-only connection, that connection will maintain an open transaction, holding resources that are not released, thereby blocking the execution of truncate operations.

This feature implements a mechanism compatible with Oracle's transaction status, where the driver does not send Begin in non-auto-commit mode. For read-only commands, the kernel automatically commits, and for write commands, users need to commit explicitly.

Usage Instructions

The GUC parameter behavior_compat_options has a new configuration item compat_oracle_txn_control.

  • When the driver is in non-auto-commit mode (autocommit = off) and the kernel is configured with this parameter: behavior_compat_options = 'compat_oracle_txn_control';, the auto-commit transaction feature for SELECT is enabled, and the compat_oracle_txn_control option is not allowed to be modified via set behavior_compat_options after JDBC 5.0.0.8, Psycopg2 5.0.0.5.

  • When the driver is in auto-commit mode (autocommit = on), even if the kernel is configured with this parameter: behavior_compat_options = 'compat_oracle_txn_control';, the auto-commit transaction feature for SELECT will not be enabled.

    Note: In auto-commit mode (autocommit = on), when the driver version is JDBC 5.0.0.6/5.0.0.7, Psycopg2 5.0.0.4, ODBC 5.0.0.2, enabling this feature will cause the driver's auto-commit mode to be ineffective.

  • After using JDBC setSavepoint, SELECT does not auto-commit.

Usage Limitations

  1. This feature is only applicable in A compatibility mode.
  2. For the driver side issuing multiple SQL statements in one command, and if the first statement is not start transaction/begin, all SQL statements will be committed together automatically. For example, set xxx; update xxxx; if set and update are issued together, the update will be automatically committed after execution, rather than being explicitly committed as expected. Therefore, when using, please write single statements to avoid statements that need explicit submission from being auto-committed.
  3. For functions/storage procedures involving cursor OUT parameters or returning cursor types, after executing the function/storage procedure, auto-commit will occur, and subsequent fetch operations will result in errors as the portal is not found.

Auto-commit Transaction Behavior in Different Scenarios

  1. Individual read commands auto-commit (excluding select for update/share/key share/no key update)
  2. Read commands within a transaction block do not auto-commit and require explicit submission
  3. Read commands within functions/storage procedures do not auto-commit; if the function contains only read commands, it auto-commits upon completion
  4. Individual write commands require explicit submission (insert/update/delete/merge)
  5. Write commands within a transaction block do not auto-commit and require explicit submission
  6. Write commands within functions/storage procedures do not auto-commit and require explicit submission
  7. Individual DDL auto-commits
  8. DDL commands within a transaction block do not auto-commit and require explicit submission
  9. DDL within functions/storage procedures do not auto-commit; if the DDL does not contain write behavior, it auto-commits upon completion of the function
  10. DDL within functions/storage procedures does not auto-commit; if the DDL contains write behavior, explicit submission is required after the function is completed
  11. If functions/storage procedures contain sub-transactions, regardless of the sub-transaction's commit or rollback, it depends on whether the main transaction has write behavior; if there is write behavior, explicit submission is required, if not, it auto-commits
  12. For special DDL commands like explain, anonymous blocks, execute, if there is write behavior inside, explicit submission is required
  13. For single lock commands, lock commands within functions/storage procedures, the kernel will explicitly execute them in a transaction block, requiring explicit submission
  14. For single declare cursor cursor definition operations, the kernel will explicitly execute them in a transaction block, requiring explicit submission. Cursor definition operations within functions/storage procedures are not actively executed in a transaction block, and the function auto-commits upon completion.
  15. select into statements require creating a table first and then inserting data, so essentially, they are DDL statements and will auto-commit (after version 5.0.8).
  16. To not affect database initialization, this Oracle transaction compatibility feature is turned off by default; during the database startup process, the feature can be enabled without affecting normal startup.

Behavior Description of Various SQL in Different Scenarios

Database Object Object Operation Single Statement Statements in Transaction Block Statements Inside Functions/Stored Procedures
TABLE SELECT Auto-commit Explicit commit If the function contains only select read commands, it auto-commits upon completion
SELECT FOR UPDATE Explicit commit Explicit commit Explicit commit
SELECT FOR SHARE Explicit commit Explicit commit Explicit commit
SELECT FOR KEY SHARE Explicit commit Explicit commit Explicit commit
SELECT FOR NO KEY UPDATE Explicit commit Explicit commit Explicit commit
INSERT Explicit commit Explicit commit Explicit commit
UPDATE Explicit commit Explicit commit Explicit commit
DELETE Explicit commit Explicit commit Explicit commit
MERGE INTO Explicit commit Explicit commit Explicit commit
DECLARE CURSOR DECLARE CURSOR Explicit commit Explicit commit If the function contains only cursor operations without write commands, it auto-commits upon completion
CLOSE CURSOR CLOSE CURSOR Explicit commit Explicit commit If the function contains only cursor operations without write commands, it auto-commits upon completion
MOVE/FETCH MOVE/FETCH Explicit commit Explicit commit If the function contains only cursor operations without write commands, it auto-commits upon completion
TABLESPACE CREATE TABLESPACE Auto-commit Not supported in transaction blocks Not supported in functions/stored procedures
ALTER TABLESPACE Auto-commit Explicit commit Explicit commit
DROP TABLESPACE Auto-commit Not supported in transaction blocks Not supported in functions/stored procedures
DATABASE CREATE DATABASE Auto-commit Not supported in transaction blocks Not supported in functions/stored procedures
ALTER DATABASE Auto-commit Explicit commit Explicit commit
DROP DATABASE Auto-commit Not supported in transaction blocks Not supported in functions/stored procedures
TABLE CREATE TABLE Auto-commit Explicit commit Explicit commit
ALTER TABLE Auto-commit Explicit commit Explicit commit
TRUNCATE TABLE Auto-commit Explicit commit Explicit commit
DROP TABLE Auto-commit Explicit commit Explicit commit
SCHEMA CREATE SCHEMA Auto-commit Explicit commit Explicit commit
ALTER SCHEMA Auto-commit Explicit commit Explicit commit
DROP SCHEMA Auto-commit Explicit commit Explicit commit
VIEW CREATE VIEW Auto-commit Explicit commit Explicit commit
ALTER VIEW Auto-commit Explicit commit Explicit commit
DROP VIEW Auto-commit Explicit commit Explicit commit
PACKAGE CREATE PACKAGE Auto-commit Not supported Not supported
CREATE PACKAGE BODY Auto-commit Not supported Not supported
ALTER PACKAGE Auto-commit Explicit commit Explicit commit
DROP PACKAGE Auto-commit Explicit commit Explicit commit
TRIGGER CREATE TRIGGER Auto-commit Explicit commit Explicit commit
ALTER TRIGGER Auto-commit Explicit commit Explicit commit
DROP TRIGGER Auto-commit Explicit commit Explicit commit
FUNCTION CREATE FUNCTION Auto-commit Not supported Not supported
ALTER FUNCTION Auto-commit Explicit commit Explicit commit
DROP FUNCTION Auto-commit Explicit commit Explicit commit
ROLE CREATE ROLE Auto-commit Explicit commit Explicit commit
ALTER ROLE Auto-commit Explicit commit Explicit commit
DROP ROLE Auto-commit Explicit commit Explicit commit
INDEX CREATE INDEX Auto-commit Explicit commit Explicit commit
ALTER INDEX Auto-commit Explicit commit Explicit commit
REINDEX Auto-commit Explicit commit Explicit commit
DROP INDEX Auto-commit Explicit commit Explicit commit
SEQUENCE CREATE SEQUENCE Auto-commit Explicit commit Not supported in functions/stored procedures
ALTER SEQUENCE Auto-commit Explicit commit Not supported in functions/stored procedures
DROP SEQUENCE Auto-commit Explicit commit Explicit commit
USER CREATE USER Auto-commit Explicit commit Explicit commit
ALTER USER Auto-commit Explicit commit Explicit commit
DROP USER Auto-commit Explicit commit Explicit commit
COMMENT COMMENT Auto-commit Explicit commit Explicit commit
VACUUM VACUUM Auto-commit Cannot be placed inside a transaction block Cannot be placed inside a function
EXPLAIN EXPLAIN If the EXPLAIN statement has write behavior, e.g., EXPLAIN ANALYZE INSERT INTO tb1 VALUES(1); requires explicit commit, otherwise auto-commit Explicit commit If the EXPLAIN statement has write behavior, requires explicit commit, otherwise auto-commit upon function completion
SHOW SHOW Auto-commit Explicit commit Auto-commit upon function completion
ALTER SYSTEM SET ALTER SYSTEM SET Auto-commit Statements not supported in transaction blocks Statements not supported in functions/stored procedures
SET SET Auto-commit Explicit commit Auto-commit upon function completion
SHUTDOWN SHUTDOWN Auto-commit Will be executed before commit, after which the current session will close Will be executed before commit, after which the current session will close
ANONYMOUS BLOCK EXECUTE ANONYMOUS BLOCK EXECUTE If the anonymous block has write behavior, e.g., begin insert into tb1 values(1); end; / requires explicit commit, otherwise auto-commit Explicit commit If the anonymous block has write behavior, requires explicit commit, otherwise auto-commit upon function completion
LOCK TABLE LOCK TABLE Explicit commit Explicit commit Explicit commit
SET CONSTRAINTS SET CONSTRAINTS Auto-commit Explicit commit Auto-commit upon function completion
CHECKPOINT CHECKPOINT Auto-commit Explicit commit Auto-commit upon function completion
SHRINK SHRINK Auto-commit Explicit commit Explicit commit
CREATE RULE CREATE RULE Auto-commit Explicit commit Explicit commit
TYPE CREATE TYPE Auto-commit Explicit commit Explicit commit
ALTER TYPE Auto-commit Explicit commit Explicit commit
EVENT TRIGGER CREATE EVENT TRIGGER Auto-commit Explicit commit Explicit commit
ALTER EVENT TRIGGER Auto-commit Explicit commit Explicit commit
DROP EVENT TRIGGER Auto-commit Explicit commit Explicit commit
PURGE PURGE TABLE Auto-commit Explicit commit Explicit commit
PURGE INDEX Auto-commit Explicit commit Explicit commit
PURGE RECYCLEBIN Auto-commit Explicit commit Explicit commit
TimeCapsule Table TimeCapsule Table Auto-commit Explicit commit Explicit commit
PREPARE PREPARE Auto-commit Explicit commit Auto-commit upon function completion
EXECUTE EXECUTE If the EXECUTE statement has write behavior, e.g., EXECUTE (INSERT INTO tb1 VALUES(1)); requires explicit commit, otherwise auto-commit Explicit commit If the EXECUTE statement has write behavior, requires explicit commit, otherwise auto-commit upon function completion
COPY COPY Auto-commit Explicit commit Explicit commit
LOAD LOAD Auto-commit Explicit commit Explicit commit
DEALLOCATE DEALLOCATE Auto-commit Explicit commit Auto-commit upon function completion
GRANT Auto-commit Explicit commit Explicit commit
REVOKE Auto-commit Explicit commit Explicit commit
GRANT ROLE GRANT ROLE Auto-commit Explicit commit Explicit commit
REVOKE ROLE REVOKE ROLE Auto-commit Explicit commit Explicit commit
EVENT CREATE EVENT Auto-commit Explicit commit Explicit commit
ALTER EVENT Auto-commit Explicit commit Explicit commit
DROP EVENT Auto-commit Explicit commit Explicit commit
OWNED DROP OWNED Auto-commit Explicit commit Explicit commit
DEFAULT PRIVILEGES ALTER DEFAULT PRIVILEGES Auto-commit Explicit commit Explicit commit
MATERIALIZED VIEW REFRESH MATERIALIZED VIEW Auto-commit Explicit commit Explicit commit
HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.