Issue Description
During the operation of a TDSQL Boundless instance, the business side may encounter deadlock errors, which can be accompanied by the following phenomena:
A deadlock error with the error code 1213 (ER_LOCK_DEADLOCK) appears in the application logs.
A lock wait timeout error appears in the application logs, with the error code 1205 (ER_LOCK_WAIT_TIMEOUT).
The metric for the number of failed SQL queries in instance monitoring is increasing.
In high-concurrency scenarios, some transactions are automatically rolled back with the message "Deadlock found when trying to get lock".
You can view metrics such as the number of failed SQL queries and running threads on the Instance Details > Monitoring and Alarms > Monitoring page in the TDSQL Boundless console to determine the frequency and timing of deadlock occurrences.
Common Causes
Deadlocks are typically caused by multiple transactions holding locks that each other needs, forming a wait cycle. The possible causes are as follows:
|
1 | Multiple transactions access the same table or rows in different orders, forming a lock wait cycle. |
2 | A transaction executes for an excessive duration within its scope and holds locks for too long, increasing the probability of deadlock. |
3 | Concurrency surges abruptly, lock contention intensifies, and a large number of transactions compete for the same set of resources within a short period. |
4 | Deadlock detection is not enabled or the lock wait timeout is configured improperly, causing deadlocks to be undetectable and unresolvable in a timely manner. |
Solutions
For different causes, the troubleshooting approaches are as follows:
|
Inconsistent Transaction Access Order | Standardize the table access order within a transaction to ensure concurrent transactions acquire locks in the same sequence. |
Excessively Long Transaction Execution Time | Break down large transactions into smaller ones to reduce the lock-holding time per transaction; optimize the execution efficiency of SQL statements within transactions. |
Concurrency Surges Abruptly | Implement rate limiting or off-peak execution on the business side to reduce the intensity of concurrent contention. |
Deadlock Detection Not Enabled | Enable deadlock detection through the console and properly configure the lock wait timeout. |
Troubleshooting
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:
|
Number of Failed SQL Queries | Number of failed SQL statements per second | The number of failures surges when a deadlock occurs. |
Number of Active Threads | Current number of running threads | Whether concurrency surges abruptly. |
Number of slow queries | Number of slow queries per second | Whether lock wait causes queries to slow down. |
CPU utilization | Instance CPU utilization | Whether lock contention causes CPU usage to increase. |
Focus on the time points when the number of failed SQL queries spikes, and check whether they coincide with business concurrency peaks or batch task execution times.
Step 2: Viewing Deadlock Information
Check deadlock records through system views to analyze the transactions involved in the deadlock and their lock-wait relationships.
1. Connect to the database instance and execute the following SQL to view deadlock records.
SELECT
rollback_trans_id,
cycle_transactions,
occurred_at
FROM information_schema.TDSTORE_PESSIMISTIC_DEADLOCK_INFO
ORDER BY occurred_at DESC
LIMIT 20;
This view displays information about recent deadlocks. The fields are described as follows:
rollback_trans_id: The ID of the transaction that was rolled back.
cycle_transactions: A list of the transactions that form the deadlock cycle.
occurred_at: The time when the deadlock occurred.
2. To view deadlock details, execute the following SQL to view the lock-wait details of the deadlock.
SELECT
rollback_trans_id,
requesting_node,
requesting_trans_id,
blocking_trans_id,
req_lock_range,
blk_lock_range,
occurred_at,
requesting_sql
FROM information_schema.TDSTORE_PESSIMISTIC_DEADLOCK_DETAIL_INFO
ORDER BY occurred_at DESC
LIMIT 20
This view displays the lock-wait relationships at the time of a deadlock, helping you locate the specific resources and transactions involved in the conflict.
Step 3: Viewing Current Lock Wait Sessions
If a deadlock is occurring or there is a prolonged lock wait, check the lock-wait status of the current session.
1. Execute the following SQL to view currently active sessions, with a focus on sessions that have a high TIME value, as these sessions may be waiting for lock resources.
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 20
2. If you identify a session with a prolonged lock wait, you can use the KILL command to terminate that session and release the lock resources.
Warning:
The KILL operation interrupts the currently executing transaction and triggers a rollback. Confirm the target session before executing this command to avoid terminating normal business sessions by mistake.
Step 4: Enabling Deadlock Detection
If deadlock detection is not enabled, it is recommended to enable it through the console so that the system can automatically detect and resolve deadlocks.
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 Parameter Settings > Database Parameters tab and locate the following parameters:
|
tdstore_deadlock_detect
| Whether to enable deadlock detection. After it is enabled, the system automatically detects deadlock cycles and rolls back one of the transactions. | OFF |
tdstore_deadlock_victim
| The policy for selecting a victim transaction when a deadlock occurs. WRITE_LEAST preferentially rolls back the transaction that writes less data; START_LATEST preferentially rolls back the transaction that started later. | WRITE_LEAST |
3. Set the value of tdstore_deadlock_detect to ON to enable deadlock detection.
4. Set the value of tdstore_deadlock_victim based on your business scenario:
WRITE_LEAST: This policy prioritizes protecting transactions that write a large amount of data and is suitable for scenarios where data writing is the primary operation.
START_LATEST: This policy prioritizes protecting the transaction that was started first and is suitable for scenarios where the transaction execution order is well-defined.
5. In the Current Value column of the target parameter's row, click to modify the parameter value, click Save, and then click OK in the confirmation dialog.
Attention:
Enabling deadlock detection incurs a small performance overhead. However, in scenarios where deadlocks occur frequently, enabling detection can prevent transactions from being blocked for extended periods, and the overall benefit outweighs the cost.
Step 5: Adjusting the Lock Wait Timeout
If the lock wait timeout is set to an inappropriate value (either too long or too short), you can adjust the tdsql_lock_wait_timeout parameter through the console.
1. On the Parameter Settings > Database Parameters tab, locate the tdsql_lock_wait_timeout parameter.
2. Adjust the parameter value based on your business scenario:
The default value is 10 seconds. If business transactions have a long execution time, you can appropriately increase this value to prevent normal transactions from being mistakenly terminated due to lock waits.
If you want deadlocks and lock waits to be resolved quickly, you can appropriately decrease this value to reduce business blocking time.
3. In the Current Value column of the target parameter's row, click to modify the parameter value, click Save, and then click OK in the confirmation dialog.
Step 6: Optimizing Business Transactions
Optimize transaction design at the business level to fundamentally reduce the probability of deadlock occurrence.
1. Unified Access Order: Ensure that all concurrent transactions access tables and rows in the same order. For example, both Transaction A and Transaction B should update table1 first and then table2, thereby avoiding cross-waiting.
2. Reduce Transaction Scope:
Break down large transactions into multiple smaller ones to reduce the lock-holding time of individual transactions.
Avoid including time-consuming operations (such as remote calls or file I/O) within transactions, and commit transactions as soon as possible.
When using SELECT ... FOR UPDATE, try to lock only the required rows to avoid locking excessive resources.
3. Reduce Concurrency Intensity:
Throttle high-concurrency write scenarios to control the number of concurrently executed transactions.
Execute batch operations in batches to avoid locking a large amount of resources in a single batch.
Schedule write operations for non-core services during off-peak hours.
4. Use appropriate indexes:
Ensure that update and delete operations use indexes to locate rows, thereby avoiding full-table scans and locking.
Update operations without an index lock a large number of rows, significantly increasing the probability of deadlocks.
Step 7: Optimizing SQL Statements
Lock contention is often related to inefficient SQL. Optimizing SQL can reduce lock-holding time.
1. Execute EXPLAIN on SQL statements involved in lock contention to view their execution plans.
EXPLAIN UPDATE table_name SET ... WHERE ...;
2. Focus on the following information:
When the type column shows ALL, it indicates a full-table scan, which locks a large number of rows. An index needs to be added.
A large value in the rows column indicates that too many rows are being scanned, which also results in more rows being locked.
3. Optimization suggestion:
Add indexes to columns used in WHERE clauses to reduce the number of rows scanned and locked.
Avoid executing UPDATE or DELETE operations on columns without an index.
Use LIMIT to limit the number of rows affected by a single operation.
Precautions
To prevent deadlock issues from recurring, the following preventive measures are recommended:
Enable deadlock detection: Ensure the tdstore_deadlock_detect parameter is set to ON so that the system automatically detects and resolves deadlocks.
Configure Alarm Policies: Configure alarms for the SQL failure quantity metric in the console to promptly detect deadlock issues.
Standardize Transaction Design: Establish transaction development standards, unify table access order, and control transaction size and lock holding time.
Regularly Inspect Slow SQL Queries: Periodically check for Slow SQL queries through DBbrain's intelligent diagnosis and optimize inefficient queries to reduce lock holding time.
Monitor SQL Failure Quantity: Regularly monitor the SQL failure quantity metric in the console monitoring panel. If a sudden spike occurs, promptly investigate whether deadlock or lock wait issues exist.