Applicability
This feature is available only in OceanBase Database Enterprise Edition.
Hot rows are the same row or a small number of rows that are repeatedly updated by a large number of concurrent requests within a short period. A typical scenario is when the application-side concurrency is already high, but the database-side throughput cannot continue to increase linearly, and lock waits are concentrated on the update chain for the same row.
Applications typically exhibit the following symptoms:
Concurrent requests continue to increase, but TPS growth slows down or stops altogether.
Average response time and P99 latency increase.
The SQL statements themselves are simple, but a large number of requests are updating the same row or a few rows.
OceanBase provides two types of optimizations for hot row updates:
Early Lock Release (ELR): Shortens the duration for which a single transaction holds a row lock.
Group commit (hot row group commit): Aggregates and executes multiple concurrent
UPDATEoperations on the same hot row, reducing the fixed overhead of repeated execution and recommitments.
These two solutions address different issues and can be used together.
Why are hot row updates slow?
A regular UPDATE operation modifies a row through a Read-Modify-Write process: it acquires a row lock when the final result is written to the MemTable, and the lock is released only after the transaction is fully committed. The transaction commit process consists of three steps:
Write the transaction's commit log to the log system cache.
The system flushes the logs to disk and synchronizes them to Followers.
After a majority of replicas have successfully written the logs, the transaction status is changed to committed, and the row lock is released.
That is:
Wait for row lock → Execute UPDATE → Commit log written to cache → Logs flushed to disk + Synchronization → Commit completed → Release row lock
When a large number of requests update the same row, the bottleneck mainly stems from the following two aspects:
Serialization of row locks.
At any given moment, the same row can only be updated by one transaction. While a transaction holds a row lock (from acquisition to release), other transactions must wait in a queue. The higher the concurrency, the longer the queue.
Repetition of fixed overhead.
Each transaction independently executes an SQL statement, generates a transaction log, and completes a commit. Even if all transactions update the same row, these fixed operations are repeated for each transaction.
Therefore, hot row optimization needs to reduce two types of overheads:
Reduce the duration for which each transaction holds a row lock.
Reduce the number of times that a large number of similar updates are repeatedly executed and committed.
ELR addresses the first type of overhead, while group commit addresses the second type.
ELR: Reduce row lock hold time
In the conventional commit process, a transaction typically waits until the commit log has been synchronized to a majority of replicas before releasing the row lock. For hot rows, subsequent transactions must always wait behind this row lock, directly amplifying the commit wait time into a row lock wait time.
The core idea of ELR is to release the row lock early after a transaction has completed local log writing and is ready to proceed with commit, allowing subsequent transactions to start execution earlier.
The benefits are straightforward: the row lock no longer covers the entire commit wait process, allowing subsequent updates to acquire the lock earlier. In hot row scenarios, even if a single transaction only reduces its lock hold time slightly, when applied to a large number of queued requests, it can significantly improve overall throughput.
ELR does not affect the correctness of a transaction's final commit or rollback. Subsequent transactions, even if they acquire the row lock earlier, are still constrained by transaction dependencies. After the preceding transactions are finally committed or rolled back, subsequent transactions continue to complete according to consistency rules.
The benefits of ELR are significant: early release of the row lock can increase single-row update throughput by several times. In higher-specification hardware and cross-region deployment scenarios, ELR moves network time and disk I/O time out of the row lock coverage range, making it particularly effective for deployment forms with high network latency between data centers.
Therefore, ELR is characterized by:
The benefits are mainly reflected in reduced lock wait for hot row updates.
ELR does not reduce the number of SQL executions or transaction commits.
Application requests are still executed according to the original transaction semantics; the optimization process is transparent to the application.
Group Commit: Reduce repeated execution and repeated commits
While ELR reduces lock hold time, each transaction still executes an UPDATE and commits separately. As concurrency continues to increase, these repetitive fixed overheads can still become a bottleneck.
The core idea of group commit is to aggregate multiple concurrent UPDATE operations on the same SQL template and the same hot row, processing them as a batch by the database.
Aggregation occurs only during the execution and commit phases. Each request remains an independent database request, holding its own independent client connection. After the batch execution is complete, the database returns the execution result (success, failure, or 0 rows affected) to the corresponding client, with no interference between requests.
After aggregation, the benefits mainly come from three aspects.
Execution path merging. Multiple requests no longer execute the SQL plan individually; instead, they share a single batch execution path.
Commit overhead merging. Multiple requests no longer complete an independent commit individually; instead, the aggregated commit path handles it uniformly.
Hot row contention convergence. Concurrent updates on the same hot row are converged into the batch processing, reducing the overhead of multiple transactions repeatedly competing for the same row.
Group commit changes the "organization of execution and commit", but each request still returns results such as success, failure, or 0 rows affected according to transaction semantics.
The aggregate key is automatically derived by the database from the primary key columns or unique index columns, and does not need to be explicitly specified. During batch execution, intermediate result records for the same row are stored in the row cache. Subsequent updates read the previous results from the cache to continue the computation. After a batch of executions completes, the final results are written to the storage layer.
In addition to the aggregated execution semantics that bring significant performance improvements, hot row group commit also provides a layer of transactional semantics called automatic commit. At the transaction commit level, the entire batch of aggregated requests is committed uniformly after execution. If there are other statements within the transaction following the aggregate statement, these statements will execute outside the original transaction context, leading to fragmented transaction semantics and potentially consistency issues such as dirty reads. Therefore, to ensure the integrity of transaction semantics, an UPDATE statement with the GROUP_COMMIT hint must be executed as the last DML statement within a transaction.
How ELR and Group Commit Work Together
ELR and group commit address different problems and operate at different stages:
Optimization |
Problem solved |
Location |
|---|---|---|
| ELR | Long row lock hold time | Early release of the row lock |
| group commit | Repeated execution and repeated commits of similar updates | Batch execution and commit |
When used together, their benefits are stacked in the following order:
ELR allows row locks to be released faster, enabling queued requests to enter the executable state sooner.
Concurrent requests on the same hot row are more likely to be aggregated within a short period.
Group commit batches and commits these requests, further diluting fixed overheads.
This can be understood as: ELR makes requests flow faster, and group commit processes these flowing requests in batches.
Their usage methods also differ:
Comparison Items |
ELR |
group commit |
|---|---|---|
| Whether to modify the SQL statement | Typically not required | You need to add GROUP_COMMIT(TRUE\|FALSE) Hint |
| Key benefits | Reduce row lock waits | Reduce redundant execution and commit overhead |
| Suitable Forms | High-concurrency hotspot updates | High-concurrency parameterization on the same hot row UPDATE |
The Impact of UPDATE Position on Lock Holding Time
The above optimizations apply during the UPDATE execution and transaction commit phases. However, before this, the position of UPDATE within a transaction already determines its basic lock holding duration.
In single-statement transactions, UPDATE acquires a lock from the point it starts reading data until the transaction is fully committed. In multi-statement transactions, if the UPDATE statement appears at the beginning of the transaction, the execution time of all subsequent SQL statements is included in its lock holding time. When the UPDATE statement is placed as the last statement to execute in a transaction, its lock holding time is essentially the same as that in a single-statement transaction.
Both ELR and group commit can reduce the time consumed during the commit phase, but they cannot shorten the lock holding time that has been extended by preceding SQL statements. Therefore, placing hot UPDATE as the last statement to execute in a transaction is key to optimizing performance.
Usage boundaries
Hot row optimization is suitable for scenarios characterized by high concurrency, short transactions, and concentrated updates on the same row or a few rows.
The optimization benefit may not be significant in the following cases:
Low concurrency, making it difficult to form effective aggregation within a short time.
Updates are scattered across a large number of different rows, not hot row updates.
SQL templates are not fixed, or different requests cannot be identified as the same type of update.
A transaction is long and contains many preceding writes, preventing hot
UPDATEstatements from consistently entering the aggregation path.A transaction contains writes across log streams or parallel DML, making group commit aggregation impossible.
An
UPDATEstatement with theGROUP_COMMIThint cannot be executed as the last statement in a transaction.