Millised on tehingute isolatsiooni tüübid?

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

The SQL standard defines 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: Guarantees that data read during the transaction will not change if read again. It avoids "dirty" and "non-repeatable" reads. "Phantom" reads may occur.
4. Serializable: The highest level. Ensures that concurrent transactions execute as if they were sequential. It avoids all undesirable phenomena: "dirty" reads, "non-repeatable" reads, and "phantoms".

Undesirable phenomena in concurrent transaction execution:

• Dirty Read: Transaction A reads data modified by transaction B that has not yet been committed. If B rolls back, A works with incorrect data.
• Non-repeatable Read: Transaction A reads the same data twice, but B modifies it in between. A gets different values.
• Phantom Read: Transaction A executes a query returning a set of rows. Transaction B inserts or deletes rows matching A's query criteria. Repeating the query yields a different set.

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 performance and correctness of the application. Higher levels incur higher costs.