Transactions and Concurrency
Understanding READ COMMITTED
In the previous chapter, you learned that PostgreSQL uses snapshots to determine which row versions are visible to an operation.
But when does PostgreSQL take a snapshot?
That depends on the transaction's isolation level, which determines which changes made by concurrent transactions the transaction is allowed to see.
PostgreSQL's default isolation level is READ COMMITTED.
Checking the default isolation level
Run:
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, each SELECT without a locking clause receives a new snapshot when the statement begins.
The snapshot allows the SELECT to see data committed before the statement began.
A SELECT can also see changes made by earlier statements in its own transaction, even if the transaction hasn't been committed.
Suppose an order currently has:
1status = pendingTransaction A updates the order but doesn't commit:
1234567Transaction ABEGINUPDATE status = 'shipped'transaction still openIf Transaction A reads the order again, it sees its own change:
1234Transaction ASELECT status→ shippedBut if Transaction B reads the same order while Transaction A is still open, it sees the earlier committed value:
123456Transaction A Transaction BUPDATE status = 'shipped'not committed SELECT status → pendingThis gives us two rules:
A transaction can see changes made by its own earlier statements.
It can't see uncommitted changes made by another transaction.
The important part of READ COMMITTED is that the snapshot belongs to the statement, not to the entire transaction.
This means two SELECT statements inside the same transaction can see different committed data.
Let's see how that can happen.
Two SELECT statements can see different data
Suppose an order currently has:
1status = pendingTransaction A begins and reads the order:
123456Transaction ABEGINSELECT status→ pendingPostgreSQL takes a snapshot when the SELECT begins. The order's committed status is pending, so that's what the statement sees.
Transaction B now updates the same order and commits:
1234567Transaction BBEGINUPDATE status = 'shipped'COMMITThe committed status is now shipped.
Transaction A is still open. It runs the same SELECT again:
123456Transaction ASELECT status→ shippedCOMMITThe complete sequence looks like this:
1234567891011121314151617Transaction A Transaction BBEGINSELECT status→ pending BEGIN UPDATE status = 'shipped' COMMITSELECT status→ shippedCOMMITBoth SELECT statements ran inside Transaction A.
The first SELECT saw:
1pendingThe second SELECT saw:
1shippedThis happened because each SELECT received a new snapshot when it began.
The first snapshot was taken before Transaction B committed. The second snapshot was taken after Transaction B committed.
BEGIN doesn't freeze the database
Running BEGIN starts a transaction, but it doesn't freeze the database at that moment.
Under READ COMMITTED, the transaction doesn't keep one snapshot for its entire lifetime. Each SELECT without a locking clause receives a new snapshot based on the committed database state available when that statement begins.
Other transactions can therefore commit changes while the transaction remains open, and later statements can see those changes.
The core rule is:
Under READ COMMITTED, two SELECT statements inside the same transaction can see different committed data.
What you need to remember
The core ideas are:
READ COMMITTEDis PostgreSQL’s default isolation level.PostgreSQL takes a new snapshot for each
SELECTwithout a locking clause when the statement begins.A transaction can see changes made by its own earlier statements, even before it commits.
A transaction can’t see uncommitted changes made by another transaction.
Two
SELECTstatements inside the same transaction can see different committed data if another transaction commits between them.
In the next chapter, you'll learn how to safely update data when concurrent transactions might change the same row.