Hologres:SQL transaction capabilities

Supported scenarios

This section describes the use scenarios of transactions in Hologres.

• Execution of multiple DDL statements in a transaction

  You can create a transaction in which multiple table creation statements are executed. The transaction is rolled back upon an error. Sample code:

  begin;
  drop table if exists  ddl_test;
  create table ddl_test(
            uid   text not null,
            name  text not null);
  commit;

• Execution of multiple DML statements in a transaction (beta)

  In Hologres V2.0 and later, multiple DML statements can be executed in a transaction. This ensures the atomicity and consistency of multiple DML statements in an SQL operation. Transactions ensure data consistency when you use a DML statement to write data. This makes Hologres suitable for lightweight transaction processing. However, the distributed capability of Hologres cannot be fully used if a transaction contains multiple DML statements. As a result, the requirements for high queries per second (QPS) cannot be met.

  • Scenario: You can execute multiple DML statements in a transaction to ensure data consistency in INSERT, UPDATE, and DELETE statements.

  • Precautions:

    • Transactions that contain multiple DML statements can be used only in scenarios with low QPS and is not suitable for scenarios with high concurrency. You can run tests to obtain the maximum QPS that is supported by a transaction in which multiple DML statements are executed.

    • If a transaction involves both read and write operations, the transaction does not improve the query performance but causes the query performance to deteriorate. The performance loss varies in different scenarios.

    • When you read, write, delete, or update data on a table in a transaction, the table is locked. Transactions on the same table are executed in sequence, as shown in the following figure.

    • A known issue exists if you execute multiple DML statements in a transaction. If a rollback operation is performed after you cancel DML statements, the transaction is not deleted, and the table-level lock acquired by the transaction is not released. Sample statements:

      set hg_experimental_enable_transaction = on;
      begin;
      insert into dml_test select i from generate_series(1,100000) t(i);
      -- Cancel the INSERT INTO statement.
      rollback;
      -- The table dml_test is locked.

  • Usage notes:

    By default, Hologres does not support the execution of multiple DML statements in a transaction. You can enable the support by using the following command:

    set hg_experimental_enable_transaction = on;

    After you set the hg_experimental_enable_transaction parameter to on, Hologres supports the execution of multiple DML statements in a transaction.

    • If one DML statement in a transaction fails, the system automatically rolls back all DML statements in the transaction, and the other DML statements also fail.

    • If you want to roll back a DML statement that is being executed normally, you can roll back the transaction in which the DML statement is executed. This way, all DML statements in the transaction are rolled back.

      set hg_experimental_enable_transaction = on;
      begin ;
      delete from dml_test;
      insert into dml_test values (1,'sss');
      rollback;

• Hologres does not support a transaction that contains both DDL statements and DML statements.

  If DDL statements and DML statements are executed in the same transaction, the following error message is returned: ERROR: INSERT in ddl transaction is not supported now. Error example:

  begin;
  drop table if exists dml_test;
  create table dml_test
  (
            uid   text not null,
            name  text not null);
  insert into dml_test values('1','tom');
  commit;