Understanding Read Committed Isolation in PostgreSQL

Transaction isolation levels play a crucial role in balancing consistency and performance. Over the course of next few articles, I would be touching on nuggets which could be critical decisions in this direction. PostgreSQL offers several isolation levels. Among them, Read Committed is the default and widely used.

What Is Read Committed?

• Read Committed ensures that a transaction only sees data that was committed before the query began.
• It avoids exposing uncommitted data or changes made by concurrent transactions during query execution.
• It does allow a transaction to see the effects of its own previous updates, even if they are not yet committed.

Concurrency

• Imagine two successive SELECT queries within the same transaction.
• If another transaction commits changes during the execution of the first SELECT:The second transaction waits for the first one to commit or rollback.If the other transaction rolls back, the waiting transaction proceeds.If the other transaction commits, the query re-executes to check if the new row version satisfies the condition.If satisfied, the row is updated.
• This means that a SELECT query may see different data within the same transaction due to concurrent changes.

Example

• Exposing Uncommitted Data:Imagine we have a table called “AccountHolder” with columns “AccountHolderId” and “Balance”.Transaction A starts by reading an account holder’s balance and then updating it (but not committing the change yet).Meanwhile, Transaction B begins and also reads the same account holder’s balance. In terms of SELECT statements:Transaction A:

BEGIN; SELECT Balance FROM AccountHolder WHERE AccountHolderId = 123; 
-- Reads the original balance UPDATE AccountHolder SET Balance = 150 
WHERE AccountHolderId = 123; -- Uncommitted update -- ... other operations ... COMMIT;        

Transaction B:

BEGIN; SELECT Balance FROM AccountHolder WHERE AccountHolderId = 123; 
-- Reads the original balance (not the uncommitted value) 
-- ... other operations ... COMMIT; 
Transaction B will only see the original balance (before Transaction A’s update) and not the uncommitted value.        

• Effects of Previous Updates:Suppose Transaction A reads the same account holder’s balance again (within the same transaction).It will see the updated value (even though the update is not yet committed).In terms of SELECT statements:Transaction A (continued):

BEGIN; SELECT Balance FROM AccountHolder WHERE AccountHolderId = 123;
 -- Reads the updated balance (150) 
-- ... other operations ... COMMIT; 
This behavior allows a transaction to observe its own changes before committing them.        

Use Cases

• Read Committed is the most optimal choice when the need is to have a balance between performance and consistency.
• It’s suitable for many use cases where partial transaction isolation is enough or practically would never cause user any user impact.
• For complex scenarios, there are stronger consistency guarantees like Serializable ( which I will talk about in upcoming articles )

Conclusion

For more in-depth information, explore the official PostgreSQL documentation.

PostgreSQL: Documentation: 16: 13.2.?Transaction Isolation

Feel free to share this article with your network! ????