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 write and query operations, query performance is lower than when no transaction is used. The actual performance loss varies depending on the scenario.

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

      Important

      Note that a lock is applied not only to the table being written to, but also to the table being queried. For example:

      • User A executes the following SQL statement:

        set hg_experimental_enable_transaction = on;
        BEGIN ;
        delete from dml_test;
        insert into dml_test SELECT * FROM base_tbl;
        COMMIT;

      • User B executes the following SQL statement:

        set hg_experimental_enable_transaction = on;
        BEGIN ;
        delete from dml_test_2;
        insert into dml_test_2 SELECT * FROM base_tbl;
        COMMIT;

      In this case, the base_tbl table is also locked. The SQL statement submitted by User B must wait for the transaction from User A to finish before it can start. Therefore, after you enable multi-statement DML transactions, the SQL running time increases significantly if a conflict occurs. This may even cause a timeout error.

    • 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 dml_test table is locked.

  • Usage notes:

    Mixed DML transactions are disabled by default, but you can enable them using the following GUC parameter:

    set hg_experimental_enable_transaction = on;

    Enabling mixed DML transactions allows a single transaction to contain multiple DML statements:

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

  For example, if you include DDL and DML statements in the same transaction, the following error is reported: ERROR: INSERT in ddl transaction is not supported now.

  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;