Isolation Level READ COMMITTED

Let’s break down what the READ COMMITTED isolation level actually does. The name kind of gives it away: everything you read inside a transaction is already “committed” by other transactions. It’s like in real life — you only trust the gossip that’s officially written down in the protocol.

Seriously though, this level guarantees that a transaction can’t see changes made by other transactions that haven’t been committed yet. This solves the problem known as “dirty read” (Dirty Read). But keep in mind: the data you read might change if another transaction manages to commit between your queries. That’s where the risk of “non-repeatable read” (Non-Repeatable Read) comes in.

In PostgreSQL, the READ COMMITTED isolation level is set by default. It’s like the default mode — you don’t have to do anything special to use it.

Example of Using READ COMMITTED Isolation Level

Let’s see how this works in practice. Imagine we have a table called accounts that stores info about users and their balances:

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL
);

INSERT INTO accounts (account_name, balance)
VALUES ('Alice', 1000.00), ('Bob', 500.00);

Now, picture this scenario. Session 1 and Session 2 — the two heroes of our story — are both working with the accounts table. Here’s what happens:

Session 1:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_name = 'Alice';
-- No COMMIT or ROLLBACK yet.

At this point, 100 units have been temporarily subtracted from Alice’s balance, but the result isn’t committed yet.

Session 2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT balance FROM accounts WHERE account_name = 'Alice';

Result: Session 2 sees Alice’s balance as 1000.00, because Session 1’s transaction isn’t finished yet (COMMIT hasn’t happened). The READ COMMITTED level protects us from “dirty reads.”

Session 1 finishes the transaction:

COMMIT;

Now Alice’s balance is updated, and the value 900.00 is committed in the database.

Session 2 runs the query again:

SELECT balance FROM accounts WHERE account_name = 'Alice';

Result: now Session 2 sees Alice’s updated balance — 900.00. Notice that the result is different from the previous query, which returned 1000.00. That’s the “non-repeatable read” problem.

When Should You Use READ COMMITTED?

The READ COMMITTED isolation level is a balance between performance and consistency. But there are a few scenarios where it’s just perfect:

1. Simple CRUD operations: when you’re just reading or updating data without complex relationships.

2. Updating records: for example, bulk updating data in a table where it’s important to see committed changes right away.

3. Transaction processing: payment systems that let users see only confirmed data.

But if you’re running complex analytical queries or working with large amounts of data, you might want to consider another isolation level, like REPEATABLE READ.

Pros and Cons of READ COMMITTED Isolation Level

The READ COMMITTED isolation level is kind of a golden mean. It protects you from dirty data: you won’t see changes that another transaction started but hasn’t finished yet. So nobody reads “raw” info that could be rolled back at any moment.

This mode is faster than stricter levels (REPEATABLE READ or SERIALIZABLE) because it doesn’t need complex locks and extra checks. It’s pretty lightweight and still reliable — that’s why it’s the default and works great for most everyday tasks.

Even though it prevents dirty reads, READ COMMITTED doesn’t protect you from:

• “Non-repeatable read” (Non-Repeatable Read): the value of data can change if another transaction makes changes between your queries.
• “Phantom read” (Phantom Read): another transaction can add rows that affect your query result.

Tips for Working with READ COMMITTED

Always finish your transactions: don’t forget to use COMMIT or ROLLBACK, or you might run into locking issues.

Make sure the isolation level is enough: if you need to guarantee that data won’t change during your transaction, consider REPEATABLE READ.

Use indexing: this helps PostgreSQL find data and apply changes faster.

Example: Order Processing

Let’s say we have a table called orders that stores order data:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (customer_name, status)
VALUES ('Alice', 'pending'), ('Bob', 'pending');

We want to update the status of orders that are currently “pending”:

BEGIN;
SELECT * FROM orders WHERE status = 'pending';
UPDATE orders SET status = 'completed' WHERE status = 'pending';
COMMIT;

If, during this process, another transaction adds a new order with status “pending” and does a COMMIT, our transaction won’t see that row, since it was added after we started reading.

This is an example of a “phantom read.” If you want to avoid situations like this, you need to use SERIALIZABLE.

The READ COMMITTED isolation level is the default choice for most databases, including PostgreSQL. It protects you from dirty reads, making it a solid option for most standard operations. But in scenarios that need strict consistency, you might need stricter isolation levels. The isolation level you pick should depend on your specific tasks and performance requirements.

2

Task

SQL SELF, level 40, lesson 0

Locked

Creating a Transaction with READ COMMITTED Isolation Level

Creating a Transaction with READ COMMITTED Isolation Level

Open