Database Isolation levels

Isolation is part of a database's ACID guarantees. It ensures that concurrent transactions don't affect each other. The goal is to maintain the state of the data as if transactions are run serially. However, this isn't feasible in practice because it would be too slow.

So databases offer 4 different Isolation levels (in descending order of data integrity guarantees) to accommodate the practicality the real world.

Serializable Using long-held Read and Write locks until commit (two-phase locking), guarantees that concurrent transactions will be serial. Locks with different granularities (row, table, db) allow for improved performance. Many engines now do this via Serializable Snapshot Isolation instead of pure locking.
Repeatable Read Using Multiversion concurrency control MVCC, which takes a snapshot at the start of the transaction, or lock-based (2PL) with long-held shared locks held until commit, keeps the rows you read stable. MySQL/InnoDB defaults to this level. However, new rows matching your query can still appear (phantoms). Postgres and MySQL engines use snapshots and gap locks respectively to avoid these in practice. They are still prone to write-skew errors where transactions read overlapping data and write to different rows based on the result set. The classic example is where there has to be a doctor on call and each doctor sees the other doctor on call, so they both go off call at the same time.
Read Committed This is achieved by reading only the latest data via a read lock that's immediately released, avoiding dirty reads. A write lock is then obtained on update. The window between those locks allows for non-repeatable reads, where subsequent reads in the transaction aren't guaranteed to be the same. Postgres, Oracle, and SQL Server default to this.
Read Uncommitted Only using write locks for updates, these transactions don't take read locks, so they can read another transaction's uncommitted (dirty) changes.

Read locks are shared and only allow other read locks and block write locks. Write locks block all other locks.

Happy Hackin'!

Database Isolation levels

Comments

More from this blog

8 Simple reminders

Design a url shortner

No regrets

Design a distributed unique id generator

References

Command Palette

Comments

More from this blog

References