What types of transaction isolation levels exist?

Transaction isolation determines how much one transaction can see the intermediate changes made by other concurrently executing transactions.

SQL standards define four levels of isolation:

1. Read Uncommitted: The lowest level. A transaction can read uncommitted changes made by other transactions (dirty reads).
2. Read Committed: A transaction can only read data that has been committed by other transactions. It avoids dirty reads.
3. Repeatable Read: Ensures that data read during a transaction will not change if read again. It avoids dirty reads and non-repeatable reads. Phantom reads are possible.
4. Serializable: The highest level. Guarantees that concurrent transactions execute as if they were sequential. It avoids all undesirable phenomena: dirty reads, non-repeatable reads, and phantom reads.

Undesirable phenomena in concurrent transactions:

• Dirty Read: Transaction A reads data modified by Transaction B that has not yet been committed. If Transaction B rolls back, Transaction A will work with incorrect data.
• Non-repeatable Read: Transaction A reads the same data twice, but between reads, Transaction B commits a change to that data. As a result, Transaction A gets different values on repeated reads.
• Phantom Read: Transaction A executes a query returning a set of rows. Transaction B inserts or deletes rows matching the query criteria. When the same query is executed again, Transaction A gets a different set of rows.

Summary table of undesirable phenomena by isolation levels:

Isolation Level	Dirty Read	Non-repeatable Read	Phantoms
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

Choosing an isolation level affects application performance and correctness. The higher the isolation level, the greater the overhead.