SQL - Isolation Levels & Locks
Overview
Isolation defines how transactions see each other's changes. Common levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Locks protect rows/tables to ensure consistency.
At a glance
- Use READ COMMITTED for most OLTP; REPEATABLE READ reduces non-repeatable reads; SERIALIZABLE maximizes correctness at the cost of concurrency.
- MySQL (InnoDB) uses MVCC; gap locks appear under REPEATABLE READ. SQL Server defaults to READ COMMITTED; snapshot isolation is opt-in.
- Keep transactions short and well-indexed to minimize lock contention.
Phenomena
- Dirty read (uncommitted data)
- Non-repeatable read (row changes between reads)
- Phantom read (new rows appear between reads)
Engine Defaults
| Engine | Default Isolation |
|---|---|
| MySQL (InnoDB) | REPEATABLE READ |
| SQL Server | READ COMMITTED |
| SQLite | SERIALIZABLE (with variations; locking is coarse) |
Setting Isolation (examples)
-- Session-level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
-- ...
COMMIT;
-- SQLite uses different locking; explicit isolation settings are limited
BEGIN TRANSACTION; -- acquires locks
-- ...
COMMIT;
Locking
MySQL/SQL Server support row/page/table locks; SQLite uses database-level or page-level locks depending on journaling mode. Use transactions, proper indexes, and short units of work to avoid contention.