Long Locking Time During Update

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: update 上锁耗时长

| username: 像风一样的男子

A simple update statement takes 6 seconds. I checked the execution time and it seems to be stuck on locking. What could be the reason for this?

| username: ajin0514 | Original post link

Let’s look at the execution plan.

| username: Fly-bird | Original post link

The table is locked. Due to lock conflicts or errors, the plan may fail and retry execution multiple times. This time does not include the natural time of the preceding executions before the last execution (Note: The time in the execution plan does not include this preceding time).

| username: 像风一样的男子 | Original post link

The execution plan is very simple

| username: tidb菜鸟一只 | Original post link

Check the locks in the CLUSTER_TIDB_TRX table.

| username: 像风一样的男子 | Original post link

This table only records the current moment’s state, once it’s gone, it’s gone.

| username: tidb菜鸟一只 | Original post link

Has this SQL been executing continuously, or does it only execute at certain times?

| username: 像风一样的男子 | Original post link

This will only be triggered when certain conditions are met, not always.

| username: tidb菜鸟一只 | Original post link

Next time you execute it, check it out. There should be a lock.

| username: 大飞哥online | Original post link

Check if there is any information related to locks recorded in the logs.

| username: zhanggame1 | Original post link

Check for lock conflicts to see if there are concurrent transactions.

| username: ajin0514 | Original post link

Take a look at the lock information in the system table.

| username: 路在何chu | Original post link

Adjust the isolation level to see if it is the RC isolation level, and check if the where condition has an index.

| username: 像风一样的男子 | Original post link

This locking issue is difficult to troubleshoot, and it mainly needs to be investigated from the business perspective.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.