-- Full transaction with error handling pattern:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

-- Atomicity: if next statement fails, both revert

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

-- Consistency: CHECK (balance >= 0) enforced at commit

COMMIT;

-- Durability: after COMMIT returns, WAL is fsynced to disk

-- Isolation level selection:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- default, safe

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- prevents phantom reads

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- full isolation, slowest

-- Pessimistic locking — reserve the row before updating:

BEGIN;

SELECT * FROM seats WHERE seat_id = 42 FOR UPDATE; -- row-level lock

UPDATE seats SET status = 'booked' WHERE seat_id = 42;

COMMIT;

-- Optimistic locking — detect conflict via version column:

UPDATE inventory

SET quantity = quantity - 1, version = version + 1

WHERE product_id = 101 AND version = 7;

-- If rows_affected = 0, someone else changed it first → retry

BEGIN / BEGIN TRANSACTION — starts a transaction block

COMMIT — makes all changes permanent and releases locks

ROLLBACK — reverses all changes back to the BEGIN state

SAVEPOINT name — creates a partial rollback point within a transaction

ROLLBACK TO SAVEPOINT name — reverts to a savepoint without aborting the full transaction

SET TRANSACTION ISOLATION LEVEL — must be set before any data operation in the transaction

SELECT ... FOR UPDATE — acquires an exclusive row lock; other transactions must wait

SELECT ... FOR SHARE — acquires a shared lock; allows other readers but blocks writers

MVCC for all isolation levels. Default: READ COMMITTED. VACUUM cleans dead versions. WAL-based recovery. Advisory locks for app-level coordination. Excellent for mixed read/write workloads.

InnoDB: full ACID + MVCC. Default isolation: REPEATABLE READ. MyISAM: NO transactions (never use for critical data). Gap locks prevent phantom reads at REPEATABLE READ.

Full ACID. Default: READ COMMITTED. Optional RCSI (Read Committed Snapshot Isolation) enables MVCC-like behaviour. Multiple isolation levels including SNAPSHOT. Excellent monitoring via DMVs.

MVCC for all reads — readers never block writers by design. Default: READ COMMITTED. Undo tablespace stores old row versions. Strong ACID with row-level locking.

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=