Pessimistic vs Optimistic

TiDB supports two transaction models: pessimistic (default) and optimistic. The choice of model affects how transaction conflicts are handled.

Pessimistic Transaction (Default)

Pessimistic transactions lock data when it is read. If another transaction tries to access the same data, that transaction will wait or time out.

-- Set pessimistic mode (default in TiDB v3.0.8+)
SET GLOBAL tidb_txn_mode = 'pessimistic';

-- Pessimistic example
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Locks the row
UPDATE accounts SET balance = balance - 500000 WHERE id = 1;
COMMIT;

When to Use Pessimistic?

• Transactions with frequent conflicts
• Banking and financial systems
• When the application needs MySQL InnoDB-like behavior

Optimistic Transaction

Optimistic transactions do not lock data when it is read. Conflicts are detected only at commit time.

-- Set optimistic mode
SET GLOBAL tidb_txn_mode = 'optimistic';

-- Optimistic example
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;  -- Does not lock
UPDATE accounts SET balance = balance - 500000 WHERE id = 1;
COMMIT;  -- Conflicts are detected here

When to Use Optimistic?

• Transactions with few conflicts
• Read-heavy workloads
• When throughput matters more than conflict safety

Comparison

Conflicts in Optimistic Mode

-- Transaction 1
START TRANSACTION;
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Waiting...

-- Transaction 2 (concurrent)
START TRANSACTION;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;  -- Error: concurrent conflict!

If a conflict occurs, the application must retry the transaction.

Best Practice

-- For critical applications, use pessimistic
SET SESSION transaction_isolation = 'READ-COMMITTED';
SET SESSION tidb_txn_mode = 'pessimistic';

-- For high throughput, use optimistic
SET SESSION tidb_txn_mode = 'optimistic';

In the next chapter, we will look at table partitioning in TiDB.