84 isolation
Isolation is the I of ACID acronym.
As mentioned before, Isolation guarantees that multiple transactions occur concurrently without seeing each other. For example, one transaction update a row in Customer table, second transaction will not see this update row.
Isolation prevents Read Phenomenas of dirty reads,non-repeatable reads and phantom reads. For example, isolation ensures that two users could not change same row at the same time.
| Isolation level vs Read phenomenon | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Serializable | no | no | no |
| Repeatable read | no | no | yes |
| Read committed | no | yes | yes |
| Read uncommitted | yes | yes | yes |
Most relational databases support serializable isolation by default since it prevents read phenomenas.
- Sqlite transactions are serializable
- Changes made in one database connection is not visible to all other database connections
- A query sees the state of the database prior to start of the query. That is all transactions completed before the query runs.
In addition to serializable isolation, some databases support Snapshot isolation. Snapshot isolation guarantees that reads in a transaction will see a consistent snapshot of the database. Different from serializable, transactions in the snapshot isolation will only commit if there is no conflicting update done after that snapshot. Actually, this means that if same rows are changed by two different transactions, second transaction will be rolled back. Snapshot isolation has better performance than serializable isolation. Snapshot isolation is supported by Oracle, MySQL, PostgreSQL, Microsoft SQL Server.
Snapshot isolation is called serializable by Oracle and PostgreSQL (prior to 9.1)