Optimistic vs Pessimistic Transactions in TiDB: 10x Performance Boost

Optimistic and pessimistic transaction models are two common approaches in databases. Optimistic assumes low contention, so no lock when reading, conflict checked at commit. Pessimistic assumes possible conflicts, locks rows when reading.

Typical usage: read‑heavy, write‑light with few conflicts → optimistic; write‑heavy with many conflicts → pessimistic. Choosing the wrong model under high concurrency and many conflicts leads to many retries and severe performance drop. This article presents a real‑world scenario, performance testing with TiDB, and guidance on model selection.

Business Scenario

Overview

360 commercial ad real‑time monitoring provides multi‑dimensional consumption data to advertisers via dashboards, enabling quick insight into ad performance and budget. Internal product and operations teams also monitor these metrics to adjust algorithms and improve ROI.

Architecture

Data flow: API logs → Kafka → Flink → TiDB. Every 10‑30 seconds Flink reads Kafka, aggregates, and writes hundreds of thousands of rows into TiDB partitioned by date, keeping 30 days of data per partition (tens of millions to billions per partition).

Write Bottleneck

SQL pattern: INSERT … ON DUPLICATE KEY UPDATE, batch size 50, concurrency 10‑30. Early stage mostly inserts; as data grows, updates dominate (≈1:10).

Although Flink aggregation removes duplicate keys within a batch, multiple concurrent streams can still produce duplicate keys across batches, leading to many write conflicts during peak traffic.

These conflicts cause TiDB write throughput to drop, increasing Kafka lag and delaying ad performance visibility.

TiDB Optimistic Transaction Model

TiDB implements distributed transactions using the Percolator model with two‑phase commit (2PC). The client starts a transaction, executes DML, then commits. During the pre‑write phase TiDB selects a primary key, sends pre‑write requests to involved TiKV nodes, which lock rows that satisfy conditions. After successful pre‑write, TiDB asks PD for a commit timestamp and proceeds to the commit phase, where TiKV finalizes the writes and releases locks.

Advantages: Conflict detection occurs only at commit, reducing lock overhead and improving write throughput in low‑conflict scenarios such as auto‑increment primary keys.

Disadvantages: Conflicts trigger internal retries controlled by two parameters:

tidb_disable_txn_auto_retry: whether automatic retry is enabled (default 1 = disabled). tidb_retry_limit: maximum retry attempts (default 10, effective only when auto‑retry is enabled).

Retries hide conflicts from the client but increase latency; excessive retries can break repeatable‑read isolation.

Note:

TiDB supports pessimistic transactions only from version 3.0 onward; earlier versions default to optimistic.

Upgrading a pre‑3.0.8 cluster to a newer version retains the original optimistic default unless explicitly changed.

TiDB Pessimistic Transaction Model

Based on the optimistic model, TiDB’s pessimistic mode acquires locks at DML execution time. When a transaction runs UPDATE/DELETE, TiDB caches the rows and places a placeholder lock. At commit, these placeholder locks are converted to standard Percolator locks, and the rest of the flow mirrors the optimistic path.

Enabling conditions: (1) TiDB version 3.0.8+ or tidb_txn_mode='pessimistic'; (2) the application uses explicit transactions (BEGIN/COMMIT). Both conditions are required.

Test Procedure Under Write Conflicts

Test Environment

TiDB version: 4.0.9

Cluster: 2 TiDB + 2 PD + 3 TiKV (TiDB/PD share servers)

Hardware: CPU E5‑2630v2 × 2, Memory 16 GB DDR3 × 8, SSD system disk 300 GB, data disk Intel P4600 2 TB, dual‑port 1 GbE NIC, single power supply.

Test Cases

Partitioned table definition:

CREATE TABLE dxl_test_cost ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto‑increment id', metric_request_all bigint(20) NOT NULL DEFAULT '0', metric_clicks bigint(20) NOT NULL DEFAULT '0', metric_views bigint(20) NOT NULL DEFAULT '0', ... PRIMARY KEY (id,create_date), UNIQUE KEY idx_product_metric (adspace_id,time_solt,dsp_type,deal_mode,user_client_category,gray_version,create_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=25864263174 PARTITION BY RANGE COLUMNS(create_date) ( PARTITION p20210410 VALUES LESS THAN ("20210411"), PARTITION p20210411 VALUES LESS THAN ("20210412"), ... PARTITION p20210509 VALUES LESS THAN ("20210510") );

Insert statement (simplified):

INSERT INTO dxl_test_cost (create_date, time_solt, dsp_type, place_id, adspace_id, service_type, adspace_prop, deal_mode, gray_version, user_client_category, publisher_id, metric_request_all, metric_clicks, metric_views) VALUES ('2021-05-06','16:10',1,xx,xxx,1,1,0,0,2,xxx,0,0,0), ... ON DUPLICATE KEY UPDATE metric_request_all=metric_request_all+VALUES(metric_request_all), metric_clicks=metric_clicks+VALUES(metric_clicks), metric_views=metric_views+VALUES(metric_views);

Test Process

(1) 10 concurrent workers, optimistic mode (tidb_txn_mode=pessimistic + autocommit).

Result: average QPS ≈ 8, peak ≈ 50, SQL latency 2‑10 s; many retries observed.

Note: automatic retry was enabled (tidb_disable_txn_auto_retry=0, retry limit 10) to compensate for client‑side retry limits.

(2) 10 concurrent workers, pessimistic mode (tidb_txn_mode=pessimistic, autocommit off, explicit transaction).

Pessimistic mode achieved average QPS ≈ 170, peak up to 251, latency 50‑100 ms, i.e., >10× higher throughput than optimistic.

Conclusion

In a concurrent, high‑conflict write scenario, pessimistic transactions delivered more than tenfold better write performance than optimistic transactions. The main issue with optimistic mode is the latency introduced by automatic retries under heavy conflicts, whereas pessimistic locking avoids retries.

Warning: when using pessimistic mode with high conflict, keep concurrency below ~50 to avoid lock wait timeouts or deadlocks.

References

https://docs.pingcap.com/zh/tidb/stable/optimistic-transaction

https://docs.pingcap.com/zh/tidb/stable/pessimistic-transaction

https://pingcap.com/blog-cn/best-practice-optimistic-transaction

https://pingcap.com/blog-cn/pessimistic-transaction-the-new-features-of-tidb