Transactions and Concurrency
Understanding READ COMMITTED
In the previous chapter, you learned that PostgreSQL uses snapshots to determine which row versions are visible.
But when does PostgreSQL take a snapshot?
That depends on the transaction's isolation level.
An isolation level determines what changes made by other concurrent transactions a transaction is allowed to see.
PostgreSQL's default isolation level is `READ COMMITTED`.
Checking the default isolation level
Type the following statement into the SQL editor and run it:
1SHOW transaction_isolation;You should see:
1read committedUnless you explicitly choose another isolation level, PostgreSQL uses READ COMMITTED.
What does READ COMMITTED mean?
Under READ COMMITTED, a statement sees data committed before that statement begins.
It doesn't see uncommitted changes made by other transactions.
The following example is conceptual.
Imagine Transaction A reads an order:
1234Transaction ASELECT status→ pendingMeanwhile, Transaction B changes the order:
12345Transaction BUPDATE status = 'shipped'COMMITOnce Transaction B commits, statements that begin afterward can see that change.
Each statement can see a newer database state
Suppose Transaction A remains open and runs the same SELECT again after Transaction B commits:
1234567891011121314151617Transaction A Transaction BBEGINSELECT status→ pending UPDATE status = 'shipped' COMMITSELECT status→ shippedCOMMITBoth SELECTs ran inside the same transaction.
But the first one saw:
1pendingand the second one saw:
1shippedUnder READ COMMITTED, each statement works from the committed database state available when that statement begins.
So an important rule to remember is:
Under `READ COMMITTED`, two statements inside the same transaction can see different committed data.
READ COMMITTED doesn't mean the database is frozen
This is particularly important in full-stack applications.
Imagine your Node.js backend does this inside a transaction:
1234567891011121314BEGINSELECT status→ pendingapplication checks:"Is the order still pending?"→ yes...some other work...UPDATE orderCOMMITStarting the transaction doesn't freeze the database at the state seen by that first SELECT.
Other transactions can commit changes while this transaction remains open.
A later statement can therefore operate in a database state different from the one an earlier statement observed.
This is why this application pattern deserves careful attention:
12345read value ↓make decision in application code ↓write valueREAD COMMITTED doesn't expose uncommitted changes
Suppose Transaction B changes an order but hasn't committed:
1234567Transaction BBEGINUPDATE status = 'shipped'-- transaction still openAnother READ COMMITTED transaction doesn't simply see shipped.
Until Transaction B commits, another transaction can continue seeing the previous committed version.
The new material demonstrates exactly this: the second transaction continues seeing the old value while the first transaction's change remains uncommitted.
This prevents one transaction from reading another transaction's unfinished work.
Why this matters for race conditions
We can now better understand the race condition from the earlier chapter.
Two requests can both read:
1status = pendingand make decisions based on that value.
A normal SELECT doesn't reserve the row for the request that read it.
Sometimes we therefore need either:
to lock the row while making a decision based on it; or
to move the condition directly into the statement that changes the row.
You'll learn both approaches in the next chapter.