Use SQL transactions for DDL and mixed DML statements - Hologres

Hologres supports full Data Definition Language (DDL) transactions and partial Data Manipulation Language (DML) transactions. By default, each SQL statement runs as its own transaction. This topic covers DDL transactions, mixed DML transactions (Beta), locking behavior, and scenarios where transactions are not supported.

Transaction control statements

The following statements control transaction boundaries in Hologres:

Statement	Description
`BEGIN`	Start a transaction block.
`COMMIT`	Commit all statements in the current transaction.
`ROLLBACK`	Abort the current transaction and discard all changes.

DDL transactions

Wrap multiple DDL statements in a single transaction to ensure atomicity. If any statement fails, roll back the entire transaction to restore the previous schema state.

Example: create and roll back tables in one transaction

BEGIN;
DROP TABLE IF EXISTS ddl_test;
CREATE TABLE ddl_test (
    uid  TEXT NOT NULL,
    name TEXT NOT NULL
);
COMMIT;

Mixed DML transactions (Beta)

Available in Hologres V2.0 and later, mixed DML transactions let you combine INSERT, UPDATE, and DELETE statements in a single transaction. This ensures atomicity and consistency for data writes, making Hologres suitable for light transactional processing (TP) scenarios.

Important

Mixed DML transactions cannot fully leverage Hologres's distributed capabilities. They support only low Queries Per Second (QPS) workloads and are not suitable for high-concurrency scenarios.

Enable mixed DML transactions

Mixed DML transactions are disabled by default. Enable the feature with the following Grand Unified Configuration (GUC) parameter:

SET hg_experimental_enable_transaction = on;

Rollback behavior

After enabling the feature, all DML statements in a transaction are treated as an atomic unit:

If any DML statement fails, all other statements in the transaction are automatically rolled back.
To manually discard a transaction in progress, issue ROLLBACK. All DML statements in the transaction are rolled back.

Example: manual rollback

SET hg_experimental_enable_transaction = on;
BEGIN;
DELETE FROM dml_test;
INSERT INTO dml_test VALUES (1, 'sss');
ROLLBACK;

Locking behavior

In a mixed DML transaction, all operations — reads, writes, deletes, and updates — acquire table-level write locks. This applies to tables that are only being read, not just tables being written to. As a result, multiple transactions that touch the same table execute sequentially, not concurrently.

Under high lock contention, SQL execution time increases significantly and may exceed timeout limits.

Example: concurrent transactions competing for the same lock

Consider two concurrent transactions that both read base_tbl:

User A runs:

SET hg_experimental_enable_transaction = on;
BEGIN;
DELETE FROM dml_test;
INSERT INTO dml_test SELECT * FROM base_tbl;
COMMIT;

User B runs:

SET hg_experimental_enable_transaction = on;
BEGIN;
DELETE FROM dml_test_2;
INSERT INTO dml_test_2 SELECT * FROM base_tbl;
COMMIT;

Both transactions read base_tbl, so both acquire a write lock on it. User B's transaction waits until User A's transaction completes.

Usage notes

Mixed DML transactions support only low-QPS workloads and are not suitable for high-concurrency scenarios. Run load tests to determine the QPS ceiling for your specific workload.
If a transaction includes both write and query operations, query performance is lower than without transactions. Actual performance degradation varies by scenario.

Limitations

Transactions that contain both DDL and DML statements are not supported. Mixing DDL and DML in the same transaction causes the following error:

ERROR: INSERT in ddl transaction is not supported now

Example that triggers this error:

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;