Auswirkungen von Transaktionsisolationsstufen

Imagine managing money in a bank. When you transfer funds, you don't want the money to disappear or duplicate. This is where transactions come in!

A transaction is a sequence of operations performed as a single logical unit of work. It either completely succeeds (commits) or completely fails (rolls back).

Transactions ensure database reliability through ACID properties:

• Atomicity: All or nothing.
• Consistency: Valid state before and after.
• Isolation: Concurrent transactions don't interfere.
• Durability: Committed changes are permanent.

When multiple users or applications access the database at the same time, strange things can happen without proper control. These are called concurrency anomalies:

• Dirty Read: Reading uncommitted data from another transaction.
• Non-Repeatable Read: Reading the same row twice in one transaction, but getting different values because another transaction committed a change in between.
• Phantom Read: Rerunning a query and seeing new rows (or missing rows) that another transaction committed.

Isolation levels help prevent these issues.

SQL databases define different isolation levels to control how much one transaction is affected by others running concurrently.

These levels are a trade-off: higher isolation means fewer concurrency anomalies but often comes with increased overhead or reduced concurrency, as transactions might wait for each other.

PostgreSQL supports three standard isolation levels: READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. (It also technically has READ UNCOMMITTED, but it behaves like READ COMMITTED).

READ COMMITTED is PostgreSQL's default and most commonly used isolation level. It's a good balance between concurrency and consistency for many applications.

With READ COMMITTED:

• You cannot see uncommitted changes from other transactions (prevents Dirty Reads).
• You can see changes committed by other transactions *after* your current statement began.

This means if you run the same SELECT query multiple times within a transaction, you might get different results if another transaction commits changes in between your SELECTs.

Let's visualize READ COMMITTED with two sessions:

1. Session A starts a transaction.
2. Session A reads balance = 100.
3. Session B starts, updates balance to 90, and commits.
4. Session A reads balance again. Because Session B committed, Session A now sees balance = 90.

This behavior is generally acceptable for many applications, as you always see committed data, even if it changes during your transaction.

Try running these commands in a PostgreSQL client. Pay attention to the output of the two SELECT statements.

If you were to run UPDATE products SET price = 950 WHERE id = 1; COMMIT; in a separate client session *between* the two SELECT statements below, the second SELECT would show the updated price (950), while the first would show the original (1000).

The REPEATABLE READ isolation level offers stronger guarantees than READ COMMITTED. It ensures that any data a transaction reads will remain unchanged for the duration of that transaction.

With REPEATABLE READ:

• You cannot see uncommitted changes (prevents Dirty Reads).
• You cannot see committed changes from other transactions *after* your transaction started (prevents Non-Repeatable Reads).

This means if you run the same SELECT query multiple times, you are guaranteed to get the same result set, even if other transactions commit changes to those rows.

SERIALIZABLE is the highest isolation level. It guarantees that the outcome of concurrently executing transactions is the same as if they had executed one after another, serially.

With SERIALIZABLE:

• It prevents all concurrency anomalies (Dirty, Non-Repeatable, and Phantom Reads).
• It provides the strongest data consistency.

However, this comes at a cost. Transactions might be forced to wait or even be rolled back (a serialization failure) if they conflict with another transaction, leading to higher overhead and potential retries.

Selecting the right isolation level is crucial:

• READ COMMITTED: Good for most applications where high concurrency is needed and slight data changes within a transaction are acceptable. It's PostgreSQL's default for a reason.
• REPEATABLE READ: Use when you need to ensure that data you've read doesn't change during your transaction, like for complex reports or data analysis where consistency of a snapshot is vital.
• SERIALIZABLE: Reserve for critical operations requiring absolute data consistency, such as financial transactions or inventory systems, where any anomaly is unacceptable. Be prepared to handle serialization failures in your application logic.

Which PostgreSQL isolation level prevents Non-Repeatable Reads but still allows Phantom Reads?

Great job! In this lesson, we explored PostgreSQL's transaction isolation levels.

• We learned about concurrency anomalies like Dirty Reads, Non-Repeatable Reads, and Phantom Reads.
• We understood how READ COMMITTED (the default) prevents dirty reads but allows others.
• We saw that REPEATABLE READ adds protection against non-repeatable reads.
• Finally, SERIALIZABLE offers the strongest guarantee, preventing all anomalies at the cost of potential serialization failures.

Choosing the right isolation level is key to balancing data consistency with application performance and concurrency.