I in ACID properties

This property ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

Example: If two customers simultaneously withdraw an amount from the same account, isolation ensures that each transaction sees the account balance as it was before the other transaction started and deducts the amount accordingly. This prevents the account balance from going into an inconsistent state.

In PostgreSQL, isolation is achieved through a system known as Multi-Version Concurrency Control (MVCC). This system allows each SQL transaction to see a snapshot of the database, providing transaction isolation for each database session.

Here’s a basic overview of how it works:

1. When a transaction starts, it’s given a unique transaction ID.
2. This transaction ID is then used to create a “snapshot” of the database at that point in time.
3. Any changes made by the transaction are not visible to other transactions until the original transaction is committed.
4. If two transactions try to modify the same data at the same time, one of them will be forced to wait until the other one finishes. This is called “row-level locking”.

PostgreSQL supports four levels of transaction isolation which are defined by the SQL standard:

Read Uncommitted

This is the lowest level of isolation. It allows transactions to see the changes made by other transactions even before they are committed. PostgreSQL, in fact, doesn't really allow this level of isolation, treating it as "Read Committed" for compatibility.

This level allows dirty reads, which means a transaction can read uncommitted changes of another transaction. This can indeed lead to inconsistencies if a transaction is reading data that might later be rolled back.

Let’s consider a hypothetical scenario. Suppose Transaction 1 is updating the price of a product in an e-commerce database. If Transaction 2 reads the product’s price while Transaction 1 is still in progress (i.e., before it’s committed), it would see the uncommitted changes. This can lead to inconsistent data if Transaction 1 is later rolled back.

Read Committed

This is the default level in PostgreSQL. It ensures that within a transaction, a SELECT query sees only data committed before the query (not the transaction) began.

This is the default level in PostgreSQL.

This level prevents dirty reads, meaning a transaction can only see changes that have been committed by other transactions. This reduces the chance of inconsistencies compared to Read Uncommitted, but it can still lead to non-repeatable reads and phantom reads.

Let’s consider a scenario where Transaction 1 starts and reads the price of a product. Meanwhile, Transaction 2 updates the price of the same product and commits. If Transaction 1 reads the price again within the same transaction, it will see the updated price. This is because “Read Committed” sees the data that was committed before the query began, not before the transaction began.

Repeatable Read

This level provides a stricter isolation by ensuring that a transaction sees only the data that was committed before the transaction itself began.

This level prevents dirty reads and non-repeatable reads, meaning a transaction sees a consistent snapshot of the database. However, it can still lead to phantom reads.

Suppose Transaction 1 starts and reads the price of a product. Meanwhile, Transaction 2 updates the price of the same product and commits. However, if Transaction 1 reads the price again within the same transaction, it will see the original price, not the updated price. This is because “Repeatable Read” sees only the data that was committed before the transaction itself began, ensuring that the same query within a transaction always returns the same result.

Serializable

This is the highest level of isolation. It performs the same as “Repeatable Read” and additionally ensures that the transaction sequence is serializable. If a set of transactions cannot be serialized, one of them will be rolled back with a serialization_failure error.

Let’s consider a scenario where Transaction 1 reads the price of a product and based on that, decides to update the price. Meanwhile, Transaction 2, which started after Transaction 1, also reads the same price and decides to update it. Even though each transaction individually maintains consistency, the end result might be inconsistent because the decisions were based on stale data. The “Serializable” isolation level prevents this by rolling back one of the transactions with a serialization_failure error, ensuring overall data consistency.

Remember that while higher isolation levels provide greater consistency, they can reduce the concurrency level and make the system more prone to deadlocks. Therefore, it’s important to choose the appropriate isolation level based on specific application requirements.

Simplified Example

In the Repeatable Read isolation level, both transactions can reach the committed state, but they will see a consistent snapshot of the data as of the time each transaction started.

Here’s an example:

1. Transaction 1 starts and reads a value (let’s say the balance of a bank account is $500).
2. Transaction 2 starts, reads the same value (500), and then updates it (let's say it adds 100, so the balance is now $600). Transaction 2 then commits.
3. If Transaction 1 tries to read the balance again within the same transaction, it will still see the original 500, not the updated 600, because it's working with a snapshot of the data from the time Transaction 1 started.
4. Now, if Transaction 1 also tries to update the balance (let’s say it subtracts 200), it will subtract it from the original 500 (because that's the value it sees), and the balance will be $300. When Transaction 1 commits, this is the value that will be stored in the database.
5. The final committed state of the database will be the result of Transaction 1, i.e., $300.

This is a simplified example and does not take into account conflict resolution strategies that might be in place in a real-world database system. In some cases, such as conflicting updates, the database system might roll back one of the transactions to maintain data consistency. The specifics can depend on the exact database system and its configuration.

If a row is being locked in MVCC, how does the isolation level impact consistency?

The concept of row-level locking and transaction isolation levels work together to manage how transactions interact, but they serve different purposes and one does not necessarily negate the effects of the other.

Row-level locking is a mechanism to prevent conflicts when two transactions attempt to modify the same row of data. When a row is locked by a transaction, other transactions cannot modify that row until the lock is released. This ensures that two transactions cannot simultaneously modify the same piece of data.

On the other hand, transaction isolation levels determine the visibility of data from other transactions. They control what data a transaction can see when other transactions are also reading and writing data.

Even with row-level locking, different isolation levels can still lead to different behaviors:

1. Read Committed: Even if a row is locked by one transaction, other transactions can still read the committed data. If the first transaction modifies the data and commits, the other transactions will see the new data when they read the row after the commit.
2. Repeatable Read: Once a transaction reads a row, it will see the same data for that row for the duration of the transaction, even if other transactions modify and commit the data. This is because it’s working with a snapshot of the data taken at the start of the transaction.
3. Serializable: This level provides the strictest isolation. It ensures that transactions appear as if they’re executed one after the other. If two transactions attempt to modify the same row, one will be forced to wait until the other completes, even if the first transaction has not yet committed.

So while row-level locking prevents direct conflicts over individual rows of data, isolation levels manage the visibility and consistency of data across the entire transaction. They deal with the broader issue of ensuring a consistent view of the data while multiple transactions are taking place.