Prewrite Phase Takes a Long Time

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

Original topic: Prewrite 阶段耗时大

| username: TiDB_C罗

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
The statement is as follows:
INSERT INTO
xxxxx ON DUPLICATE KEY
UPDATE
xxxxx,
xxxxx,
xxxxx,
xxxxx;

time:391.6µs, loops:1, prepare: 12.4µs, check_insert: {total_time: 379.2µs, mem_insert_time: 48µs, prefetch: 331.2µs, rpc:{BatchGet:{num_rpc:1, total_time:300.3µs}, tikv_wall_time: 106.3µs, scan_detail: {total_keys: 1, get_snapshot_time: 5.97µs, rocksdb: {block: {cache_hit_count: 9}}}}}, commit_txn: {prewrite:365ms, slowest_prewrite_rpc: {total: 0.365s, region_id: 21002, store: 10.95.6.86:20160, tikv_wall_time: 364.7ms, scan_detail: {get_snapshot_time: 9.28µs, rocksdb: {block: {cache_hit_count: 9}}}, write_detail: {store_batch_wait: 358.2ms, propose_send_wait: 0s, persist_log: {total: 6.06ms, write_leader_wait: 74ns, sync_log: 4.6ms, write_memtable: 38.9µs}, commit_log: 6.23ms, apply_batch_wait: 20.7µs, apply: {total:139.7µs, mutex_lock: 0s, write_leader_wait: 0s, write_wal: 20.7µs, write_memtable: 88.6µs}}}, region_num:3, write_keys:3, write_byte:136}

What is the reason for this?

| username: Jellybean | Original post link

The two-phase commit of transactions is slow in the first phase, prewrite, usually because version checks and acquiring write locks take a long time.

Judging by the SQL you provided, which is insert on duplicate key update, it is highly likely to be a write-write conflict based on experience. At this point, you should check the TiDB logs for keywords like “write conflict.”

Search on the official website or community forums for how to handle write conflicts, and you will find the solutions you need. Generally, these are resolved from the business side (such as reducing concurrency, rewriting SQL, etc.).

| username: redgame | Original post link

High load on the TiKV storage engine, such as busy disk I/O or insufficient storage node resources, can lead to increased latency for prewrite_rpc.

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

It should be a write-write conflict caused by concurrency. Try executing a single one to see.

| username: TiDB_C罗 | Original post link

All updates are based on the primary key, so I understand there shouldn’t be any conflicts.

| username: TiDB_C罗 | Original post link

Executing individually is very fast.

| username: TiDB_C罗 | Original post link

There are many of these
[2023/08/11 05:00:57.223 +00:00] [WARN] [session.go:988] [sql] [conn=650636005754612419] [label=general] [error=“[kv:9007]Write conflict, txnStartTS=443476884114112529, conflictStartTS=443476884114112530, conflictCommitTS=0, key={tableID=226, tableName=xxx.xxx, indexID=2, indexValues={220112010028448478, \u000e+\u000e+\u000e)\u000e*\u000e*\u000e+\u000e)\u000e*\u000e)\u000e)\u000e+\u000e1\u000e-\u000e-\u000e1\u000e-\u000e0\u000e1\u0002\u001b\u000e*\u000e/\u000e2\u000e*\u000e0\u000e,\u000e)\u000e)\u000e.\u000e0\u000e)\u000e)\u000e), }}, originalKey=7480000000000000e25f69800000000000000203830dfeb4f88afade010e2b0e2b0e290e2aff0e2a0e2b0e290e2aff0e290e290e2b0e31ff0e2d0e2d0e310e2dff0e300e31021b0e2aff0e2f0e320e2a0e30ff0e2c0e290e290e2eff0e300e290e290e29ff0000000000000000f7, primary=byte(nil), originalPrimaryKey=, reason=Optimistic [try again later]”] [txn=“Txn{state=invalid}”]

| username: Jellybean | Original post link

The log you posted indicates a write-write conflict. You can search for the keyword “write-write conflict” on the forum or the official website. There will be many solutions available, and you can handle the issue based on the specific situation.

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

How to resolve write-write conflict issues: 乐观事务模型下写写冲突问题排查 | PingCAP 文档中心