The time consumption of explicitly starting transactions with begin and commit operations continues to increase

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

Original topic: 显式开启事务begin和commit操作耗时持续增长

| username: TiDBer_qijtMMBk

[TiDB Usage Environment] Production Environment / Test / PoC
[TiDB Version]
[Reproduction Path] The client continuously executes transaction operations as follows:
begin;
select for update;
commit;
With global default pessimistic lock, the client finds that QPS becomes slower and slower after running for a period of time.
[Encountered Problem: Problem Phenomenon and Impact]
The select operation time is stable, but the begin and commit times continue to increase, from 0.1ms to 9ms, and continue to increase.
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
BEGIN PESSIMISTIC;

Time: 2023-12-22T15:20:24.519268695+08:00

Txn_start_ts: 0

User@Host: root[root] @ 10.62.1.214 [10.62.1.214]

Conn_ID: 4323361084275716885

Query_time: 0.009707726

Parse_time: 0

Compile_time: 0.000087458

Rewrite_time: 0.000002129

Optimize_time: 0.000017072

Wait_TS: 0.000240997

DB: test

Is_internal: false

Digest: 307313c7c9681e7b8be9e83d48bb6ca0b821c207c7e7981b828d115515c911bc

Num_cop_tasks: 0

Mem_max: 4096

Prepared: true

Plan_from_cache: false

Plan_from_binding: false

Has_more_results: false

KV_total: 0

PD_total: 0.000229767

Backoff_total: 0

Write_sql_response_total: 0

Result_rows: 0

Succ: true

IsExplicitTxn: true

IsSyncStatsFailed: false

BEGIN PESSIMISTIC;

No errors or warnings in the PD logs.

| username: xfworld | Original post link

Did the scenario where the latency increased from 0.1ms to 9ms involve an increase in concurrency? And then were there many updates to the same data?

| username: Jellybean | Original post link

During this period, were there any changes in the operation process, such as increased concurrency, different SQL, or changes in the background data?

| username: dba远航 | Original post link

Starting a transaction will increase the lock time, which may cause other waits.