Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-2.x)/ Development and Design Proposal/ Database Design Specifications/ Transaction
Updated on 2025-02-27 GMT+08:00
View PDF
Share

Transaction

  • [Specification] In GTM-free mode, cross-node transactions are not allowed.

    In GTM-free mode, if an executed SQL statement contains a cross-node transaction, the error message "Unsupport DML two phase commit under gtm free mode." is displayed.

    In this case, if a statement must temporarily start a cross-node transaction, run the following command in the transaction to start the cross-node transaction:

    SET enable_twophase_commit = on;

    Run the following command before the transaction ends:

    RESET enable_twophase_commit;

    Or:

    SET enable_twophase_commit = off;
  • [Specification] Large object operations do not support transactions.

    Large object operations include CREATE/DELETE DATABASE, ANALYZE, and VACUUM.

  • [Rule] When accessing the database through the JDBC client, disable the autocommit parameter and explicitly execute the transaction COMMIT.
    • On the one hand, enabling the autocommit parameter will cause some parameters (such as fetchsize) to become invalid.
    • On the other hand, the service should clarify the service logic and reduce the dependence on the database.
  • [Rule] Do not combine multiple SQL statements into one statement when accessing the database through JDBC.

    When multiple statements are combined into one statement that contains object operations, if the intermediate object operation fails, a new transaction is started to execute subsequent statements.

    Example:
    • The following statement does not meet specifications: 
      Connection conn = ....
try {
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE t1 (a int); DROP TABLE t1");
    } finally {
        stmt.close();
    }
    conn.commit();
} catch(Exception e) {
   conn.rollback();
} finally {
   conn.close();
}
      In the preceding statement, if 
      CREATE TABLE t1;

      The creation fails, and a new transaction will be started.

      DROP TABLE t1;

      The execution fails.

    • You are advised to split the statement into two statements and send them separately: 
      Connection conn = ....
try {
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.executeUpdate("CREATE TABLE t1 (a int)");
        stmt.executeUpdate("DROP TABLE t1");
    } finally {
        stmt.close();
    }
    conn.commit();
} catch(Exception e) {
   conn.rollback();
} finally {
   conn.close();
}