tencent cloud

TDSQL Boundless

문서TDSQL BoundlessTroubleshootingTransactionLarge Transaction

Large Transaction

Download
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-07-17 16:33:33

Issue Description

During the operation of a TDSQL Boundless instance, the business side may encounter errors related to oversized transactions, which can be accompanied by the following phenomena:
An error message indicating that the transaction write size has exceeded the limit appears in the application logs.
An error message indicating that the number of transaction participants has exceeded the limit appears in the application logs.
The metric for the number of failed SQL queries in instance monitoring is increasing.
During the execution of a large transaction, memory utilization increases and the response times for other business requests become slower.
During the execution of a large transaction, lock waits or deadlocks may be triggered.
You can view metrics such as the number of failed SQL queries, memory utilization, and transactions processed per second on the Instance Details > Monitoring and Alarms > Monitoring page in the TDSQL Boundless console.

Common Causes

Oversized transactions are typically caused by excessive data writes within a single transaction, an excessive number of participants, or batch operations not being performed in batches. The possible causes are as follows:
No.
Possible cause
1
A single transaction writes an excessive amount of data, exceeding the tdstore_max_txn_size limit, which causes the transaction to be rejected.
2
The number of participants (RGs) involved in a single transaction exceeds the tdsql_transaction_max_participants limit.
3
Batch import operations (such as LOAD DATA and INSERT ... SELECT) are not executed in batches, resulting in an excessive amount of data per single operation.
4
A long-running transaction executes for an excessive duration, holds locks and memory resources for too long, and affects other workloads.
5
Operations such as ALTER TABLE on large tables generate a large amount of data copying, resulting in an oversized transaction.

Solutions

For different causes, the troubleshooting approaches are as follows:
Possible cause
Problem-solving Ideas
Excessive data volume in a single transaction
Split large transactions into multiple smaller ones and adjust the tdstore_max_txn_size parameter via the console.
Excessive number of participants
Reduce the number of tables and partitions involved in a single transaction, and execute cross-table operations in batches.
Batch import not executed in batches
Split a large batch import into multiple smaller batches, with each batch controlled within a reasonable data volume.
Long-running transaction holding locks for an excessive duration
Optimize the execution efficiency of SQL statements within transactions, reduce the transaction scope, and set a reasonable timeout period.
Excessive data copying during DDL operations
Verify that the tdsql_split_trans_during_alter_copy parameter has been enabled, so that DDL automatically splits transactions.

Operation Steps

Step 1: Viewing Monitoring Metrics

1. Log in to the TDSQL Boundless console. In the Instance List, click the target Instance ID to go to the Instance Details page.
2. Choose the Monitoring and Alarms > Monitoring tab to view the trend of the following metrics:
Monitoring Metric
Description
Focus
Number of Failed SQL Queries
Number of failed SQL statements per second
The number of failures surges when transactions exceed the limit.
Memory utilization
Instance memory utilization
Whether memory usage surges during large transaction execution.
Transactions per second
Transactions committed per second
Whether the number of transactions abnormally decreases.
Number of slow queries
Number of slow queries per second
Whether large transactions cause other queries to slow down.
Number of Active Threads
Current number of active threads
Whether any threads are blocked by large transactions for a long time.
Focus on the time points when the number of failed SQL queries spikes and memory utilization surges, and check whether they coincide with the execution times of batch tasks or large transactions.

Step 2: Viewing Large Transaction Information

Check system views to determine whether any large transactions currently exist and to view their resource usage.
1. Connect to the database instance and execute the following SQL to view information about current large transactions.
SELECT
    replication_group_id,
    transaction_id,
    disk_usage_bytes,
    memory_usage_bytes,
    rollback,
    destroyed
FROM information_schema.TDSTORE_LARGE_TXN
WHERE destroyed = 0
ORDER BY disk_usage_bytes DESC
LIMIT 20;
This view displays information about currently active large transactions. The field descriptions are as follows:
replication_group_id: The ID of the RG.
transaction_id: The ID of the transaction.
disk_usage_bytes: Disk usage (in bytes). It reflects the size of transaction data in SST files.
memory_usage_bytes: Memory usage (in bytes). It reflects the size of transactions in the MemTable.
rollback: Whether it has been rolled back.
destroyed: Whether it has been terminated.
2. If a transaction has a high disk_usage_bytes or memory_usage_bytes value, record its transaction_id for subsequent troubleshooting.
3. Execute the following SQL to view currently active sessions and locate the SQL statements corresponding to large transactions.
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 20;
Focus on sessions with a high TIME value, as they may be executing long-running or large transactions.

Step 3: Terminating Large Transactions (Optional)

If a large transaction is severely impacting normal business operations, you can terminate the corresponding session to release resources.
1. Based on the large transaction information identified in Step 2 and the sessions with high TIME values from the PROCESSLIST query result in Step 2.3, comprehensively locate the session ID corresponding to the large transaction using information such as execution time and SQL text.
2. Execute the following SQL to terminate the session.
KILL <session_id>;
Warning:
The KILL operation interrupts the currently executing transaction and triggers a rollback, which may continue to consume resources during the rollback process. Confirm the target session before executing this command to avoid terminating normal business sessions by mistake.

Step 4: Adjusting the Single Transaction Memory Limit

If your business genuinely requires executing large transactions, you can adjust the tdstore_max_txn_size parameter via the console. For detailed operations, see Set Instance Parameters.
The default value is 1 GB. If the data volume of a single transaction exceeds this limit, you can appropriately increase it.
After increasing the value, monitor the instance memory utilization to avoid memory insufficiency caused by the concurrent execution of multiple large transactions.
Attention:
Increasing the tdstore_max_txn_size value raises the memory that a single transaction can occupy, which may lead to increased memory pressure in high-concurrency scenarios.
We recommend resolving the issue by splitting transactions as the first priority. Adjust this parameter only when your business genuinely requires large transactions.

Step 5: Optimizing Batch Import Operations

Batch import is a common cause of oversized transactions. We recommend optimizing batch operations at the business level.
1. Batch Import: Split large volumes of data into multiple batches, and commit each batch using an independent transaction.
For example, split the INSERT INTO ... SELECT ... statement into multiple subqueries with LIMIT.
It is recommended to keep the data volume of each batch within the tdstore_max_txn_size limit.
2. Adjust Batch Import Parameters: If you use batch import methods such as LOAD DATA, you can adjust the following parameters via the console. For detailed operations, see Set Instance Parameters.
tdstore_bulk_load_total_merge_buffer_size: The total size of the merge buffer for batch imports, which controls memory usage during batch import operations.
tdstore_bulk_load_merge_chunk_size: The size of a single merge chunk for batch imports, which controls the data volume for each merge sorting operation.

Step 6: Optimizing Long Transactions

Long transactions hold locks and memory resources for extended periods, which increases the risk of oversized transactions.
1. Reduce Transaction Scope:
Keep only essential database operations within a transaction, and move non-database operations (such as remote calls or file I/O) outside the transaction.
Commit transactions as early as possible to avoid having them remain uncommitted for extended periods.
2. Set a Reasonable Timeout: Adjust the following parameters via the console to prevent long transactions from occupying resources indefinitely. For detailed operations, see Set Instance Parameters.
max_execution_time: The maximum execution time (in milliseconds) for SELECT statements. Setting it to a reasonable value prevents slow queries from running for extended periods.
wait_timeout: The idle timeout for connections (in seconds). Connections that remain inactive beyond this duration are automatically closed.

Step 7: Confirming DDL Transaction Splitting

ALTER TABLE and other large-table DDL operations involve data copying, which can lead to oversized transactions. TDSQL Boundless supports automatically splitting transactions during DDL to prevent individual transactions from becoming too large. Adjust the following parameters based on your business needs. For detailed operations, see Set Instance Parameters.
tdsql_split_trans_during_alter_copy parameter: Confirm that its value is ON. If it is OFF, change it to ON to enable automatic splitting and committing of small transactions during DDL data copying.
tdsql_bulk_commit_records parameter: Controls the number of records committed per batch during DDL copying. Its default value is 3000, which can be adjusted appropriately based on table data volume.

Step 8: Adjusting the Transaction Keepalive Time

If your business involves long-running legitimate large transactions (such as high-volume ETL), ensure that the transaction keep-alive time is sufficiently long to prevent transactions from being automatically released due to timeout.
Adjust the tdsql_txn_keep_alive_lease_sec parameter. This parameter controls how long TDStore waits without receiving a transaction heartbeat before releasing the transaction context. Its default value is 3600 seconds (1 hour). If a large transaction's execution time exceeds this value, you can appropriately increase it. For detailed operations, see Set Instance Parameters.
Attention:
Increasing the transaction keep-alive time extends the duration for which transaction contexts occupy memory. If a large number of long transactions coexist in an instance, memory pressure may increase. Configure this parameter appropriately based on your actual business needs.

Precautions

To prevent the issue of oversized transactions from recurring, the following preventive measures are recommended:
Configure Alarm Policies: Configure alarms for SQL failure counts and memory utilization in the console to promptly detect oversized transaction issues.
Standardize Transaction Design: Establish transaction development standards to control the data volume and execution time of individual transactions, thereby avoiding large and long-running transactions.
Process Batch Tasks in Batches: Bulk data import and update operations must be executed in batches. Keep the data volume of each batch within a reasonable range.
Monitor Transaction Metrics: Regularly monitor metrics such as transactions processed per second and SQL failure counts to promptly detect transaction exceptions.
DDL Operation Standards: Before DDL operations are performed on large tables, confirm that tdsql_split_trans_during_alter_copy has been enabled to prevent DDL from generating oversized transactions.
이전 주제: High Number of Slow Queries다음 주제: Application Deadlock

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백