Differences Between TiDB and MySQL: Transactions, Queries, Server‑Side Prepared Statements, and Batch Processing

TiDB, released in December 2016, is a world‑class open‑source distributed NewSQL database that has gained wide adoption among internet companies. It combines the scalability of NoSQL with full ACID compliance and MySQL protocol compatibility, making it attractive for business developers.

Key features from a developer’s perspective include MySQL protocol support (low integration cost), 100% transaction support (simple and reliable consistency), and infinite horizontal scalability (no need for manual sharding).

Transaction Differences

MySQL uses row‑level exclusive locks during updates, which serialize concurrent transactions and increase DBA overhead. TiDB adopts the Percolator optimistic‑lock model: no locks are taken during the transaction; locks are checked only at commit using a global timestamp. If lock checking fails, the transaction may retry or raise a write‑conflict error.

To avoid TiDB’s transaction anomalies, developers can serialize transactions using a distributed lock, for example via a Spring‑based transaction manager that implements PlatformTransactionManager with getTransaction, commit, and rollback methods.

Query Differences

TiDB may not use indexes as MySQL does. For a query like SELECT id FROM t_test WHERE (a=1 AND b=1) OR (c=2) , MySQL can use both idx_a_b and idx_c , while TiDB may treat it as a slow query and require rewriting with a UNION. Similarly, cold‑hot data patterns can cause performance gaps because TiDB’s underlying LSM‑Tree storage performs multi‑level scans and asynchronous merges, leading to higher latency.

Optimizations include narrowing filter ranges (e.g., adding time bounds) and rewriting queries to reduce scan overhead, which can cut execution time from hundreds of milliseconds to around 10 ms.

Server‑Side Prepared Statements

In MySQL, statements can be prepared with PREPARE stmt_name FROM preparable_stm and executed via EXECUTE stmt_name , reducing parsing overhead for repeated executions. The MySQL JDBC driver implements Statement , PreparedStatement , and the more efficient ServerPreparedStatement , which truly performs server‑side preparation and is faster for high‑frequency, limited‑size SQL workloads.

Batch Processing

For bulk writes, MySQL typically uses INSERT … VALUES (…),(…) or complex UPDATE … CASE … END statements, which increase parsing cost as the number of rows grows. Using JDBC’s addBatch and executeBatch methods enables a simple batch SQL to be reused, yielding more stable and lower latency performance.

Overall, understanding TiDB’s optimistic transaction model, LSM‑Tree storage, and leveraging server‑side preparation and batch APIs can help developers design efficient, reliable applications on this distributed database platform.