Why Your TiDB INSERT Takes Minutes: Understanding SQL Retry Strategies

This article uses a small case study to illustrate SQL retry strategies under different transaction models.

A developer reported that a simple INSERT in TiDB sometimes takes over 10 seconds, even up to a minute, and occurs frequently.

OK, Let's GO!

Start troubleshooting and analysis

By checking Grafana’s SQL Duration monitor we observed spikes in execution time that were not caused by transient system resource issues. Monitoring helps rule out hot spots or hardware failures.

After eliminating obvious faults, we examined TiDB server logs and slow‑query logs, discovering significant warnings.

(1) TiDB server logs contain many retry warnings, showing "retrying", "Write conflict", and high retry counts.

<code>[WARN] [session.go:664] [retrying] [conn=1165700] [schemaVersion=13873] [retryCnt=10]
[WARN] [session.go:713] [sql] [conn=1165697] [label=general] [error="[kv:9007]Write conflict, txnStartTS=420135027926630456, conflictStartTS=420135027979059203, conflictCommitTS=420135027979059208, key={tableID=14342, handle=10895814047} primary={tableID=14342, handle=10895814047} [try again later]"] [txn="Txn{state=invalid}"]
[WARN] [session.go:658] [retrying] [conn=1165702] [schemaVersion=13873] [retryCnt=0] [queryNum=0] [sql="INSERT INTO xxx"]</code>

(2) TiDB slow‑query log also records the slow SQL, showing fields such as Commit_backoff_time (retry time) and Backoff_types: [txnLock txnLock] , indicating write‑write conflicts; the retry count Txn_retry was 15.

<code>Time: 2021-08-06T13:40:50.216614662+08:00
Txn_start_ts: 426830302171889757
User: [email protected]
Conn_ID: 363
Query_time: 33.671954617
Parse_time: 0.0011528
Compile_time: 0.000609021
Prewrite_time: 0.489890509 Commit_backoff_time: 0.483 Backoff_types: [txnLock txnLock] Resolve_lock_time: 0.000715966 Write_keys: 50 Write_size: 9980 Prewrite_region: 6 Txn_retry: 15
DB: DB_XXXX
Is_internal: false
Digest: 3234fe71e25b4f3643f7c0c0528cc5544a8133c7628c59dbecb04537223ece8a
Num_cop_tasks: 0
Mem_max: 286500
Prepared: false
Plan_from_cache: false
Has_more_results: false
Succ: true
"INSERT INTO xxxxxxx"</code>

Note: Different TiDB versions may display different fields in the slow‑query log; focus on fields related to retry and backoff times.

https://docs.pingcap.com/zh/tidb/stable/identify-slow-queries#%E6%97%A5%E5%BF%97%E7%A4%BA%E4%BE%8B https://docs.pingcap.com/zh/tidb/stable/identify-slow-queries

Reasons for slow writes

The environment uses optimistic transactions with autocommit. The root cause is write conflicts; high conflict rates trigger SQL retries, and the accumulated retry count makes the INSERT take a long time.

Write retry scenario analysis

SQL retries in TiDB depend on the transaction model (pessimistic vs. optimistic) and whether the transaction is implicit (autocommit) or explicit (BEGIN/COMMIT).

To check the current TiDB transaction mode, run:

show variables like 'tidb_txn_mode';

If the value is pessimistic , the cluster is in pessimistic mode; otherwise it is optimistic (or empty, which defaults to optimistic).

Implicit transactions are autocommit; explicit transactions are non‑autocommit or started with BEGIN/COMMIT.

Note: To emulate MySQL’s pessimistic transaction behavior, set

tidb_txn_mode

to

pessimistic

and enable explicit transactions for all new sessions.

The following table summarizes retry scenarios (image illustrates the matrix):

(1) Optimistic + version < 4.0.6, implicit: default 10 retries for optimistic transactions.

Note: The variables

tidb_disable_txn_auto_retry

(default 1, no retry) and

tidb_retry_limit

(default 10) control this behavior; they apply only to optimistic transactions.

(2) Optimistic + version ≥ 4.0.6, implicit: still default 10 retries.

(3) Optimistic + explicit: tidb_disable_txn_auto_retry defaults to on, so explicit transactions do not retry on write conflict and return an error immediately.

(4) Pessimistic + version < 4.0.6, implicit: although the cluster is pessimistic, without explicit transactions TiDB falls back to optimistic retry with a default of 256 attempts.

Note: The maximum retry count for a single statement in a pessimistic transaction is controlled by

max_retry_count

(default 256).

(5) Pessimistic + version ≥ 4.0.6, implicit: the first attempt uses optimistic commit; if a write conflict occurs, TiDB switches to pessimistic commit to avoid many optimistic retries.

(6) Pessimistic + explicit: no retries, matching MySQL’s pessimistic transaction behavior.

Do you understand TiDB’s SQL retry mechanisms?