How to use group commit for hot rows and best practices

In scenarios such as flash sales, fund deductions from hot accounts, inventory reductions, and high-concurrency counters, a large number of concurrent requests update the same row of data within an extremely short period. To ensure transaction consistency, relational databases follow a process of "lock acquisition → execution → log writing and commit → lock release" for updating the same row. This process is serialized at the row level: while one transaction holds the lock, other transactions must wait in a queue, and each transaction incurs the fixed overhead of SQL execution and transaction commit independently. As concurrency increases, row lock contention and repeated overhead become throughput bottlenecks.

Group commit for hot rows addresses this bottleneck: using an SQL hint, the database kernel automatically aggregates multiple concurrent UPDATE operations on the same hot row into one execution and one commit, thereby improving write throughput for hot rows. The aggregation is performed automatically by the kernel and is transparent to applications. Applications only need to declare the hint and comply with the usage rules below.

Prerequisites

Enable tenant-level hot row group commit

Hot row group commit is controlled by the tenant-level parameter _enable_hotspot_group_commit. It is disabled by default. To enable it, set this parameter to true.

ALTER SYSTEM SET _enable_hotspot_group_commit = true tenant = xxx;

You can specify a particular tenant for tenant, or set it to all to apply the setting to all tenants.

Enable server-side prepared statements on the client

Hot row group commit takes effect only when the server uses the prepared statement (PS) protocol. To enable server-side PS, add useServerPrepStmts to your JDBC connection string:

jdbc:oceanbase://host:2881/test?useServerPrepStmts=true

On the application side, use PreparedStatement to execute statements with hints. Bind parameters using placeholders ?:

PreparedStatement ps = conn.prepareStatement(
    "UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id = ?");
ps.setLong(1, 100);
ps.setLong(2, 1);
ps.executeUpdate();

Disable free routing

The transaction containing the statement with hot row group commit must have free routing disabled. If free routing is not disabled, statements with hints will fail with error code -4007 and the message txn free route is enabled, need to disable it by hint or configuration. You can disable free routing in the following two ways:

• Tenant-level configuration: Free routing provides significant performance improvements in most scenarios. If not necessary, we recommend you use the second method.

  ALTER SYSTEM SET _enable_transaction_internal_routing = false tenant = xxx;
  

• Hint: Add TRANS_PARAM('DISABLE_TX_FREE_ROUTE' 'true') to the DML statement immediately before the hot UPDATE. This takes effect from the next statement and disables free routing for the current transaction:

  INSERT /*+ TRANS_PARAM('DISABLE_TX_FREE_ROUTE' 'true') */ INTO trade_log VALUES (?, ?, ?);
  

Plan cache

The plan cache must be enabled. The aggregate key information for hot row group commit is written to the PS cache during SQL precompilation. If the plan cache is disabled, the aggregate key information cannot be obtained for each execution, but no error is reported. Instead, the operation silently degrades to a normal UPDATE executed row by row. The plan cache is controlled by the tenant-level system variable ob_enable_plan_cache, which is enabled by default. To manually enable it, execute the following statement:

SET GLOBAL ob_enable_plan_cache = ON;

Execute the UPDATE statement as the last DML statement in the transaction

This constraint is a prerequisite for correct transaction semantics during aggregate request execution. For more information, see How hot row performance optimization works.

Hint configuration

Hot row group commit is enabled using an SQL hint, with the syntax /*+ GROUP_COMMIT(bool rollback_on_no_affect_row) */. This hint must follow the UPDATE keyword.

Semantically, this hint contains three levels of meaning:

• GROUP —— Aggregate execution

  Declares this UPDATE statement to participate in hot row aggregation. The database kernel merges multiple concurrent UPDATE statements that use the same SQL template and act on the same hot row into a single batch, sharing a single batch execution path. The aggregate key is automatically inferred by the database from the WHERE clause (either the primary key or a unique key), and does not need to be explicitly specified.

• COMMIT —— Unified Commit

  All requests within an aggregated batch are committed together after execution completes. To ensure transaction semantics are correct, an UPDATE statement with this hint must be the last DML statement executed within a transaction.

• rollback_on_no_affect_row —— Request-level affected-row count check

  This parameter applies to each sub-request within an aggregated batch, rather than the entire batch. That is, it performs an additional check on the affected_rows (number of rows affected by the execution) result for each sub-request:

  • TRUE: The database checks the affected_rows of each sub-request after execution. If a sub-request's affected_rows = 0, then only that sub-request is rolled back, and an error code -6290 is returned to that request. Other sub-requests within the same batch are not affected and are committed as usual.

  • FALSE: No additional check is performed on the affected_rows of the execution results for each request within the aggregated batch. A sub-request is considered successfully committed upon completion (commit_on_success). When affected_rows = 0, it returns 0 and is committed normally.

  Therefore, TRUE adds a sub-request-level affected-row count check on top of the default "commit upon successful execution" logic, while FALSE leaves the default behavior unchanged.

  The following example is based on the table structure below:

  CREATE TABLE account (
      id           BIGINT       NOT NULL,
      user_id      BIGINT       NOT NULL,
      biz_id       BIGINT       NOT NULL,
      balance      BIGINT       NOT NULL,
      gmt_modified TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      UNIQUE KEY uk_user_biz (user_id, biz_id)
  );
  

  The choice between TRUE and FALSE depends on whether the UPDATE statement contains a WHERE condition that could result in affected_rows = 0, and whether an explicit error needs to be returned to the client if this occurs.

  • If the WHERE clause contains a conditional expression (such as balance >= ?) and the condition is not met, resulting in affected_rows = 0, the update should be rolled back and an error reported.

    UPDATE /*+ GROUP_COMMIT(TRUE) */ account
        SET balance = balance - ?
        WHERE id = ? AND balance >= ?;
    

  • If the WHERE clause contains only equality conditions, affected_rows = 0 is an acceptable execution result and the update is committed normally.

    UPDATE /*+ GROUP_COMMIT(FALSE) */ account
        SET balance = balance + ?
        WHERE id = ?;
    

Rules and constraints

When hot row group commit is enabled and the hint is executed via the PS protocol, statements must comply with the following rules. Violations will result in execution failure or ineffective functionality: In case of failure, the error code is -4007 (OB_NOT_SUPPORTED). When the feature is ineffective, it is executed as a regular UPDATE. Specific behavior follows the descriptions in each rule. If the PS protocol is not used or the feature is disabled, the hint does not take effect and is executed as a regular UPDATE without returning an error.

Protocol and statements

• Only the standard two-phase PS protocol is supported. Text-based and combined PS protocols are not supported. Specific behaviors are described below.

• Only UPDATE statements are supported. The GROUP_COMMIT hint is ignored and does not take effect when used with INSERT, DELETE, or SELECT.

• It is not supported for use in PL (stored procedures). Execution will fail with error code -4007.

• Executing an UPDATE statement with a hint using the text-based protocol: Does not take effect and is executed as a regular UPDATE.

• Executing an UPDATE statement with a hint using the combined PS protocol: Execution fails with error code -4007.

WHERE and aggregate key

Hot row group commit groups and aggregates concurrent update requests for the same row by identifying the primary key or unique key in the WHERE clause. A statement must meet all of the following rules; otherwise, execution fails and error code -4007 is returned.

• Supported statement types

  • Key values must use ? placeholders. Literal constants cannot be used; otherwise, execution fails with error code -4007 and the message key matched in where condition but with value.

    UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id = ?;
    

  • Only = and <=> are recognized. >, <, >=, <=, !=, and BETWEEN do not participate in key derivation. Additional non-equality conditions do not affect aggregation as long as the key is fully covered.

    UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id = ? AND gmt_modified > ?;
    

  • Single-element IN (?) is supported. The optimizer rewrites it to = ?, which is then recognized by hot row group commit. This rewriting depends on _inlist_rewrite_threshold (default 1000). Only when this parameter is set to 1 does the rewriting not occur, in which case hot row group commit does not take effect.

    UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id IN (?);
    

  • Primary keys take precedence over unique keys. When both the primary key and unique key are covered in the WHERE clause, aggregation is performed based on the primary key.

    -- Both id (primary key) and uk_user_biz (unique key) are covered. Aggregation is performed by primary key id.
    UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id = ? AND user_id = ? AND biz_id = ?;
    

  • Multiple unique keys cannot be fully matched simultaneously. If no primary key is matched and two or more unique keys are fully covered, execution fails with error code -4007 and the message multiple unique key matched in where condition.

  • Partitioned tables can use global unique indexes as aggregate keys. However, the unique key columns must still be fully covered and bound with ?.

    CREATE TABLE t_part (
        id   BIGINT NOT NULL,
        g_id BIGINT NOT NULL,
        val  BIGINT NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY uk_g_id (g_id) GLOBAL
    ) PARTITION BY HASH(id) PARTITIONS 16;
    
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t_part SET val = val - ? WHERE g_id = ?;
    

• SET clause constraints

  • Hit key columns cannot appear in the SET clause. Execution fails with error code -4007 and the message updating key columns in group commit. Only non-key columns are updated:

    UPDATE /*+ GROUP_COMMIT(TRUE) */ account SET balance = balance - ? WHERE id = ?;
    

  • Subqueries cannot be nested within the SET clause, for example, SET val = (SELECT ...).

• Scope of application

  • Only single-table UPDATE is supported. Multi-table updates (JOIN UPDATE) will fail with error code -4007 and the message multiple table update or join update.

• Limitations in Oracle-compatible mode

  • WHERE rowid = ? cannot be used as an aggregate key. rowid is a pseudo-column that represents the physical address of each row in a table in Oracle-compatible mode. Since it is not a primary key or unique key defined on the table, it cannot be used in aggregate key derivation.

• Unsupported syntax

  • Unable to locate an aggregate key: The WHERE clause does not fully cover all columns of the primary key or unique key. Execution fails and returns -4007.

    -- Table: t(id INT PRIMARY KEY, val INT)
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET val = val - ? WHERE val > ?;
    

  • Unable to locate an aggregate key: Literal constant values are used for key columns. Execution fails and returns -4007.

    -- Table: t(id INT PRIMARY KEY, val INT)
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET val = val - ? WHERE id = 1;
    

  • Unable to locate an aggregate key: Non-equal operators are used for key columns. Execution fails and returns -4007.

    -- Table: t(id INT PRIMARY KEY, val INT)
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET val = val - ? WHERE id > ?;
    

  • Unable to locate an aggregate key: Multiple unique keys are matched without matching the primary key. Execution fails and returns -4007.

    -- Table: t(id INT, uk1 INT, uk2 INT, val INT, UNIQUE KEY(uk1), UNIQUE KEY(uk2))
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET val = val - ? WHERE uk1 = ? AND uk2 = ?;
    

  • Unable to locate an aggregate key: The SET clause modifies the matched key columns. Execution fails and returns -4007.

    -- Table: t(id INT PRIMARY KEY, val INT)
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET id = ? WHERE id = ?;
    

  • The SET clause contains a subquery. Execution fails and returns -4007.

    -- Table: t(id INT PRIMARY KEY, val INT)
    UPDATE /*+ GROUP_COMMIT(TRUE) */ t SET val = (SELECT MAX(val) FROM t2) WHERE id = ?;
    

  • Involves multiple tables. Execution fails and returns -4007.

    UPDATE /*+ GROUP_COMMIT(TRUE) */ t1
    JOIN t2 ON t1.id = t2.t1_id
    SET t1.val = t1.val - ?
    WHERE t1.id = ?;
    

Table limitations

Hot row group commit does not take effect and the statement is executed as a regular UPDATE in the following cases:

• A table has a UDF, Trigger, or foreign key defined on it.

• The statement updates a global index column.

• The table has neither a primary key nor a unique key (no available aggregate key).

Hot row group commit can be used for partitioned tables, but note the following:

• You can locate a single row based on the primary key, unique key, or globally unique index.

• Updating columns of the partitioning key, primary key, unique key, or global index is not supported. If an attempt to update a partitioning key column fails, -4007 is returned.

Transaction requirements

Hot row group commit can be used for single-statement transactions or explicit transactions that already contain preceding DML. The transaction must meet the following requirements:

• The transaction has disabled free routing. Free routing can be disabled through tenant-level configuration. If disabling it per transaction, add the TRANS_PARAM('DISABLE_TX_FREE_ROUTE' 'true') hint to the DML before the hot UPDATE. This hint takes effect from the next statement and cannot be re-enabled within the current transaction after being disabled.

• If preceding DML exists, it involves only one log stream. If the preceding DML involves multiple tables, ensure these writes fall within the same log stream. This can be achieved by placing the related tables in the same table group.

• If preceding DML exists, it does not use parallel DML (PDML). If earlier DML executed within the transaction already uses PDML, this optimization does not take effect.

Best practices

We recommend that you organize hot row group commits by transaction type: prefer single-statement transactions; use multi-statement transactions only when upfront DML is required. For a complete end-to-end experience, see Experience the hot row update capability of OceanBase Database.

Single-statement transaction

In auto-commit mode, execute a fixed parameterized UPDATE statement using PreparedStatement. Set the JDBC connection string to useServerPrepStmts=true and reuse the precompiled statement handle.

This section uses the following table structure as an example:

CREATE TABLE t_hot (
    id  BIGINT PRIMARY KEY,
    val BIGINT NOT NULL
);

Choose TRUE or FALSE based on how you want to handle the case where 0 rows are affected:

• To roll back and return an error when 0 rows are affected, use GROUP_COMMIT(TRUE).

• To commit normally and return affected_rows = 0 when 0 rows are affected, use GROUP_COMMIT(FALSE).

UPDATE /*+ GROUP_COMMIT(TRUE) */ t_hot
    SET val = val - ?
    WHERE id = ? AND val >= ?;

UPDATE /*+ GROUP_COMMIT(FALSE) */ t_hot
    SET val = val + ?
    WHERE id = ?;

Multi-statement transaction

When other DML operations precede the hot UPDATE, place the hint to disable free routing directly before the preceding DML, then execute the UPDATE statement with the GROUP_COMMIT hint.

Additionally, after executing the UPDATE statement with the GROUP_COMMIT hint, you should explicitly call the COMMIT or ROLLBACK statement: on one hand, although group commit has automatic transaction commit semantics, re-executing COMMIT incurs minimal overhead; on the other hand, if the tenant-level configuration disables the group commit feature, transactions will lack proper commit and rollback operations, affecting the integrity of business logic execution.

The t_hot table is defined as in the single-statement transaction section, and a new t_log table is added:

CREATE TABLE t_log (
    id     BIGINT PRIMARY KEY,
    ref_id BIGINT NOT NULL,
    amount BIGINT NOT NULL
);

BEGIN;
INSERT /*+ TRANS_PARAM('DISABLE_TX_FREE_ROUTE' 'true') */
    INTO t_log(id, ref_id, amount)
    VALUES (?, ?, ?);
UPDATE /*+ GROUP_COMMIT(TRUE) */ t_hot
    SET val = val - ?
    WHERE id = ? AND val >= ?;
COMMIT;

Keep the preceding DML fixed and minimal; when involving multiple tables, place related tables into the same table group.

CREATE TABLE t_log (
    id     BIGINT PRIMARY KEY,
    ref_id BIGINT NOT NULL,
    amount BIGINT NOT NULL
) TABLEGROUP = tg_hot;

CREATE TABLE t_hot (
    id  BIGINT PRIMARY KEY,
    val BIGINT NOT NULL
) TABLEGROUP = tg_hot;

Scenarios unsuitable for hot row group commit

• Low-concurrency scenarios. The number of concurrent requests is insufficient to promptly fill the aggregation batch, leading to increased latency and limited throughput gains. In this case, using ELR alone can meet the requirements.

• Non-hot row scenarios. Updates are scattered across multiple rows, each forming its own queue, and the number of requests in a single queue is insufficient to trigger effective aggregation.

• Transactions containing a large amount of upfront writes. Excessive upfront DML can easily trigger transaction log writing before the hot UPDATE is executed (controlled by the tenant-level parameter _private_buffer_size; logs accumulated from upfront DML will be flushed to disk once this threshold is exceeded), causing aggregation to fail. If significant upfront writes are necessary for your business, you can appropriately increase _private_buffer_size to ensure that the accumulated logs from upfront DML do not flush to disk before the hot UPDATE is executed, or split the hot UPDATE into an independent transaction.

• The UPDATE statement for hot row update cannot be placed at the end of statements within the transaction. This constraint ensures correct transaction semantics during the execution of aggregate requests. For details, see How hot row performance optimization works.

Error codes