Updated on 2025-03-13 GMT+08:00

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;

    Before the transaction ends, run the following command:

    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 jobs.

  • [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.

    If multiple statements are combined into one, as long as one of them fails to be executed, the whole statement fails and a failure message is returned. This is inconvenient for fault locating. You are advised to split the statement.

    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();
      }
    • 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();
      }