4 SQL Phenomena and How to avoid it in Postgresql

Explanation of Four SQL Phenomena

When dealing with concurrent transactions in a relational database, certain issues can arise if proper isolation is not maintained. These issues are called phenomena, and they reflect inconsistencies in data access and modification across transactions. The four main phenomena are:

Dirty Read

- Dirty Read occurs when a transaction reads data that has been written by another transaction that has not yet been committed. This means the data could be rolled back, leading to invalid or inconsistent results.

Example:

• Transaction A updates a record but doesn't commit yet.
• Transaction B reads the updated data from Transaction A.
• If Transaction A rolls back, Transaction B has read invalid data.

Nonrepeatable Read

Nonrepeatable Read occurs when a transaction reads the same row twice but sees different data each time because another transaction has modified and committed changes to that row in the meantime.

Example:

• Transaction A reads the salary of an employee.
• Transaction B updates the salary of the same employee and commits.
• Transaction A reads the salary again and sees a different value, indicating a nonrepeatable read.

Phantom Read

Phantom Read happens when a transaction re-executes a query and finds that the result set has changed because another transaction added or deleted rows that match the search criteria.

Example:

• Transaction A queries all employees with a salary above $50,000.
• Transaction B inserts a new employee with a salary of $55,000 and commits.
• When Transaction A re-executes the query, it now sees an extra row (the new employee), leading to a phantom read.

Serialization Anomaly

Serialization Anomaly occurs when the result of executing concurrent transactions is not consistent with any possible sequence of those transactions being run one by one. This can lead to logical inconsistencies in the data.

Example:

• Transaction A deducts $1,000 from an account and checks the balance.
• Transaction B concurrently checks the balance and transfers funds without realizing Transaction A's deduction has occurred.
• The end result is a corrupted state, where the balance may reflect incorrect information.

Isolation Levels in PostgreSQL

To mitigate these phenomena, SQL provides different isolation levels. These levels control the degree to which one transaction is isolated from the actions of other concurrent transactions. PostgreSQL supports three primary isolation levels:

Read Committed

This is the default isolation level in PostgreSQL. At this level, a transaction only sees data that has been committed at the time each query starts. It prevents dirty reads but allows nonrepeatable reads and phantom reads to occur.

Phenomena prevented:

• Dirty Read

Phenomena that can still occur:

• Nonrepeatable Read
• Phantom Read

Use case:

• Read Committed is suitable for applications that need a balance between consistency and performance, where it is acceptable for data to change between reads as long as no uncommitted data is read.

Repeatable Read

- At the Repeatable Read level, all rows read during a transaction remain stable for the duration of that transaction, regardless of changes committed by other transactions. This eliminates nonrepeatable reads, but phantom reads may still occur.

Phenomena prevented:

• Dirty Read
• Nonrepeatable Read

Phenomena that can still occur:

• Phantom Read

Use case:

• Repeatable Read is used when you need to ensure that the data read by a transaction remains consistent for the transaction’s duration, but you are willing to allow some new rows (phantoms) to appear in the result set.

Serializable

• Serializable is the highest isolation level. It ensures that all transactions behave as if they were executed one after the other in a serial fashion, even if they run concurrently. This level prevents all four phenomena but can result in transactions being aborted due to serialization failures if conflicts are detected.

Phenomena prevented:

• Dirty Read
• Nonrepeatable Read
• Phantom Read
• Serialization Anomaly

Use case:

• Serializable is ideal for applications that require strict consistency and correctness, where it is crucial to avoid any concurrent transaction anomalies. However, this level may cause more transaction rollbacks and affect performance.

Conclusion

In PostgreSQL, isolation levels help control how transactions interact with each other and determine the degree of consistency and isolation between concurrent operations. Here's how the phenomena are addressed by different isolation levels:

Each isolation level offers a trade-off between performance and consistency. Read Committed provides a good balance for most applications, Repeatable Read is more strict, ensuring stable data reads, and Serializable offers maximum consistency but at the cost of potentially lower performance due to transaction rollbacks.