Transactions in TiDB

Transactions ensure that a series of database operations are executed atomically — either all succeed or all fail.

Basic Syntax

-- Start a transaction
START TRANSACTION;
-- or
BEGIN;

-- SQL operations
INSERT INTO accounts (id, balance) VALUES (1, 1000000);
UPDATE accounts SET balance = balance - 500000 WHERE id = 1;
UPDATE accounts SET balance = balance + 500000 WHERE id = 2;

-- Commit (save permanently)
COMMIT;

-- Rollback (undo all changes)
ROLLBACK;

Example: Bank Transfer

-- Start a transaction
START TRANSACTION;

-- Debit the sender's balance
UPDATE accounts
SET balance = balance - 500000
WHERE id = 1 AND balance >= 500000;

-- Credit the receiver's balance
UPDATE accounts
SET balance = balance + 500000
WHERE id = 2;

-- Check whether the transfer succeeded
-- If everything is OK, commit
COMMIT;

-- If there is an error, rollback
-- ROLLBACK;

ACID in TiDB

TiDB guarantees the ACID properties:

Atomicity — all operations in the transaction succeed or all fail
Consistency — the database is always in a valid state after the transaction
Isolation — transactions run in isolation (default: REPEATABLE READ)
Durability — committed data will not be lost

Isolation Levels

-- Check the current isolation level
SELECT @@transaction_isolation;

-- Set the isolation level
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION transaction_isolation = 'READ-COMMITTED';

Implicit Transaction

TiDB supports implicit transactions. DML statements without an explicit transaction are committed automatically:

-- This runs as an implicit transaction
INSERT INTO users (name, email) VALUES ('John', '[email protected]');
-- Committed immediately

Auto Commit

By default, TiDB has auto commit enabled:

-- Check the auto commit status
SELECT @@autocommit;

-- Turn off auto commit for the session
SET autocommit = 0;

Savepoint

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100000);
SAVEPOINT sp1;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);

ROLLBACK TO SAVEPOINT sp1;

-- order_items is rolled back, orders remains
COMMIT;

Transaction Size Limit

TiDB supports transactions up to 1 TB in a single transaction. This is much larger than MySQL (default 16 MB per transaction for InnoDB).

In the next chapter, we will look at the differences between pessimistic and optimistic transactions in TiDB.