Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/pingcap/tidb/llms.txt

Use this file to discover all available pages before exploring further.

TiDB implements fully ACID-compliant transactions that span multiple storage nodes. You use the same BEGIN/COMMIT syntax you know from MySQL, and TiDB handles the distributed coordination transparently.

​
ACID guarantees

TiDB transactions satisfy all four ACID properties:

Atomicity

All writes in a transaction either commit together or are fully rolled back. Partial writes never become visible.

Consistency

Constraints are enforced at commit time. The database moves from one valid state to another.

Isolation

Concurrent transactions do not see each other’s uncommitted data. TiDB defaults to Repeatable Read isolation.

Durability

Committed data survives node failures. Transactions are only acknowledged after being written to a majority of Raft replicas.

​
Two-phase commit (2PC)

TiDB uses a two-phase commit protocol to coordinate writes that span multiple TiKV nodes. The TiDB server acts as the transaction coordinator. Phase 1 — Prewrite: The coordinator selects one key as the primary lock and writes tentative (“prewrite”) records for all keys involved in the transaction. Each prewrite points back to the primary lock. Phase 2 — Commit: Once all prewrites succeed, the coordinator commits the primary lock. From this moment the transaction is considered committed. Secondary locks are resolved asynchronously in the background. If the coordinator crashes mid-transaction, any TiKV node can determine the transaction’s outcome by checking the status of the primary lock, ensuring correctness without coordinator recovery.
Transactions are committed only after a majority of Raft replicas have acknowledged the writes, providing strong durability guarantees even when individual nodes fail.

​
Transaction modes

TiDB supports two transaction modes. You can select the mode that fits your workload’s conflict rate and latency requirements.

​
Optimistic mode

Optimistic transactions buffer all writes locally and detect conflicts only at commit time. Reads never acquire locks.
  • Best for: Low-conflict workloads where most transactions succeed on the first try.
  • Benefit: Lower per-statement latency — no round trips to TiKV during execution.
  • Drawback: Commit may fail with a write-write conflict error, requiring the application to retry.
-- Enable optimistic mode for this session
SET tidb_txn_mode = 'optimistic';

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If a conflict occurs, COMMIT returns an error and the application must retry

​
Pessimistic mode

Pessimistic transactions acquire locks on rows as each DML statement executes, before commit time. This is the default mode.
  • Best for: High-conflict workloads, or when you need guaranteed forward progress without application-level retry logic.
  • Benefit: Commit rarely fails due to conflicts; blocking behavior matches MySQL semantics.
  • Drawback: Each write statement adds a lock round trip to TiKV.
-- Pessimistic mode is the default; you can also set it explicitly
SET tidb_txn_mode = 'pessimistic';

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- acquires lock immediately
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Start with pessimistic mode (the default). Switch to optimistic mode only after profiling your workload and confirming that conflict rates are low enough to make retries infrequent.

​
Choosing between modes

FactorOptimisticPessimistic
Conflict rateLowHigh
Application retry logicRequiredNot required
Per-statement latencyLowerSlightly higher
MySQL behavioral compatibilityLowerHigher
DefaultNoYes

​
Transaction isolation levels

TiDB supports two isolation levels.

​
Repeatable Read (default)

Each transaction reads from a consistent snapshot taken at the start of the transaction. Writes from other committed transactions that happen after your snapshot are not visible within your transaction. 
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT * FROM orders WHERE status = 'pending';
-- Other sessions can commit new 'pending' orders; you will not see them here
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- same result as above
COMMIT;
TiDB’s Repeatable Read implementation uses Multi-Version Concurrency Control (MVCC). Phantom reads are prevented within a single transaction’s snapshot, but the behavior differs slightly from MySQL’s gap-lock-based approach. For write operations (SELECT ... FOR UPDATE), TiDB reads the latest committed data rather than the snapshot.

​
Read Committed

Each statement within the transaction reads the latest committed data at the time the statement executes, rather than a snapshot from transaction start. 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT * FROM orders WHERE status = 'pending'; -- reads latest committed data
-- Another session commits a new order here
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- may return a different count
COMMIT;
Read Committed is useful for workloads that need to see concurrent commits within a long-running transaction, at the cost of non-repeatable reads.

​
Stale Read

Stale Read lets you query data at a point in the past. Because the query can be served from any replica — including followers — without a network round trip to the leader, it significantly reduces read latency for read-heavy workloads.
Stale Read returns committed data as of the specified timestamp. It does not return data from ongoing transactions. Do not use Stale Read when your query requires the absolute latest committed state.
TiDB provides four ways to use Stale Read:
Attach AS OF TIMESTAMP directly to a SELECT statement to read from a specific point in time.
-- Read as of an exact timestamp
SELECT * FROM orders AS OF TIMESTAMP '2024-01-15 10:00:00' WHERE status = 'pending';

-- Read as of a relative time (20 seconds ago)
SELECT * FROM orders AS OF TIMESTAMP NOW() - INTERVAL 20 SECOND WHERE status = 'pending';
Begin a read-only transaction at a specific timestamp. All reads within the transaction use that snapshot.
START TRANSACTION READ ONLY AS OF TIMESTAMP '2024-01-15 10:00:00';
SELECT * FROM orders WHERE status = 'pending';
SELECT SUM(amount) FROM orders WHERE status = 'completed';
COMMIT;
Set the staleness for the next transaction or statement before beginning it.
SET TRANSACTION READ ONLY AS OF TIMESTAMP NOW() - INTERVAL 10 SECOND;
BEGIN;
SELECT * FROM inventory WHERE product_id = 42;
COMMIT;
Enable Stale Read for all subsequent SELECT statements in a session, up to a maximum staleness.
-- Allow reads up to 5 seconds stale
SET @@tidb_read_staleness = '-5';
SELECT * FROM orders WHERE status = 'pending'; -- served from any replica

-- Disable session-wide stale read
SET @@tidb_read_staleness = '';

​
Large transaction support

TiDB handles large transactions that write many rows or consume significant memory. By default, TiDB limits total transaction size to prevent unbounded memory use (controlled by tidb_txn_total_size_limit). For workloads that require writing large batches, TiDB supports Pipelined DML, which flushes write buffers to TiKV incrementally rather than holding everything in memory until commit. 
-- Enable pipelined DML for large bulk writes
SET tidb_dml_type = 'bulk';

BEGIN;
-- Insert or update millions of rows
INSERT INTO archive SELECT * FROM events WHERE created_at < '2023-01-01';
COMMIT;
When using pipelined DML, the transaction is not fully atomic in memory — a rollback after a partial flush requires TiDB to clean up already-flushed data. Use this mode only for batch operations where you control the workflow end-to-end.

​
Basic transaction syntax

-- Standard transaction
BEGIN;
INSERT INTO transfers (from_account, to_account, amount) VALUES (1, 2, 500.00);
UPDATE accounts SET balance = balance - 500.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 500.00 WHERE id = 2;
COMMIT;

-- Equivalent using START TRANSACTION
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 99;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 99, 1);
COMMIT;

-- Roll back on error
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something went wrong
ROLLBACK;

-- Savepoints (supported in pessimistic mode)
BEGIN;
INSERT INTO logs (message) VALUES ('step 1');
SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('step 2');
ROLLBACK TO SAVEPOINT sp1; -- undo 'step 2' only
COMMIT; -- commits 'step 1'

Build docs developers (and LLMs) love

Get started for freeTalk to us