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 provides fully ACID-compliant distributed transactions using a two-phase commit protocol backed by TiKV. Transaction behavior is largely MySQL-compatible with several TiDB-specific extensions.
Basic transaction control
-- Explicit transaction with COMMIT
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Roll back on error
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something went wrong
ROLLBACK;
START TRANSACTION is an alias for BEGIN and is also accepted:
START TRANSACTION;
INSERT INTO audit_log (action, actor) VALUES ('transfer', 'app');
COMMIT;
Autocommit mode
By default, TiDB runs in autocommit mode. Every statement that is not inside an explicit transaction is automatically committed.
-- Check current autocommit setting
SELECT @@autocommit;
-- Disable autocommit for the session
SET autocommit = 0;
-- All subsequent statements require an explicit COMMIT or ROLLBACK
INSERT INTO orders (user_id, total) VALUES (1, 59.99);
COMMIT;
-- Re-enable autocommit
SET autocommit = 1;
Disabling autocommit is a session-level setting and does not affect other connections.
Optimistic vs pessimistic transaction mode
TiDB supports two transaction modes. The default since TiDB v3.0.8 is pessimistic.
| Mode | Lock behavior | Best for |
|---|
| Pessimistic | Acquires row locks on first write; blocks conflicting writers | High-contention workloads; familiar MySQL behavior |
| Optimistic | No locks during the transaction; detects conflicts at commit | Low-contention workloads; higher throughput with rare conflicts |
-- Switch to optimistic mode for the current session
SET tidb_txn_mode = 'optimistic';
-- Switch back to pessimistic mode
SET tidb_txn_mode = 'pessimistic';
In optimistic mode, a transaction that conflicts with a concurrent write will fail at COMMIT time with Error 9007: Write conflict. Your application must handle this error and retry the transaction. In pessimistic mode, conflicting writes block and wait instead of failing.
Isolation levels
TiDB supports REPEATABLE READ (default) and READ COMMITTED.
-- Set isolation level for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Set isolation level for the entire session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
| Isolation level | Dirty reads | Non-repeatable reads | Phantom reads |
|---|
READ COMMITTED | No | Possible | Possible |
REPEATABLE READ | No | No | No (TiDB uses snapshot isolation) |
TiDB’s REPEATABLE READ implementation uses snapshot isolation rather than the ANSI standard definition. This means phantom reads are also prevented, giving stronger guarantees than MySQL’s REPEATABLE READ.
Savepoints
Savepoints let you partially roll back a transaction without aborting the entire transaction:
BEGIN;
INSERT INTO orders (user_id, total) VALUES (5, 120.00);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 99, 2);
-- Something is wrong with the items; roll back to the savepoint
ROLLBACK TO SAVEPOINT before_items;
-- The order row is still present; add a corrected item instead
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 99, 1);
COMMIT;
-- Release a savepoint (frees resources without rolling back)
RELEASE SAVEPOINT before_items;
Savepoints are supported in both optimistic and pessimistic transaction modes starting with TiDB v6.2.
Large transactions
By default, TiDB limits transaction size to prevent memory and latency issues in a distributed cluster. For bulk data operations that exceed this limit, use tidb_dml_type = 'bulk':
SET tidb_dml_type = 'bulk';
-- This INSERT can now span multiple internal transactions automatically
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2022-01-01';
SET tidb_dml_type = 'standard';
Bulk DML mode bypasses the normal transaction size guard. The operation is split internally into multiple sub-transactions, which means it is not atomic — a failure midway through will leave partial data. Use this mode only for idempotent or resumable operations.
Stale Read
Stale Read allows a query to read data from a past timestamp, bypassing the TiKV Raft leader and reading from any replica. This reduces latency for read-heavy workloads that can tolerate slightly stale data.
-- Read a snapshot from exactly 5 seconds ago
SELECT * FROM products AS OF TIMESTAMP NOW() - INTERVAL 5 SECOND;
-- Read a snapshot at a specific point in time
SELECT * FROM orders AS OF TIMESTAMP '2024-06-01 12:00:00'
WHERE created_at >= '2024-06-01'
AND created_at < '2024-06-02';
-- Enable Stale Read for the session with a staleness bound
SET tidb_read_staleness = '-5'; -- up to 5 seconds stale
-- Disable session-level Stale Read
SET tidb_read_staleness = '';
Stale Read queries are routed to follower replicas. They cannot see writes that were committed after the chosen timestamp. Do not use Stale Read for queries where reading the latest committed data is required (for example, balance checks before a transfer).
Quick reference
-- Begin a transaction
BEGIN;
START TRANSACTION;
-- Commit
COMMIT;
-- Rollback
ROLLBACK;
-- Savepoint
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;
-- Transaction mode
SET tidb_txn_mode = 'pessimistic'; -- or 'optimistic'
-- Isolation level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Stale Read
SELECT ... AS OF TIMESTAMP NOW() - INTERVAL 10 SECOND;
