Transactions and Concurrency
Why transactions exist
Imagine a customer clicks Place order in an application.
From the customer's perspective, this is one operation: place an order. But completing that operation requires two changes to our database:
Create an order in the
orderstable.Add the purchased products to the
order_itemstable.
Both changes are necessary for the order to be complete.
This creates an important question:
What happens if the first database operation succeeds, but the second one fails?
Let's find out.
Create the order
First, create an order.
Run:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 1, 'pending', 49.99, 'txn_demo_001', CURRENT_TIMESTAMP);You should see INSERT 0 1. The final 1 means PostgreSQL successfully inserted one row into the orders table.
Now verify that it exists:
12345678SELECT id, customer_id, status, total_amount, payment_referenceFROM ordersWHERE payment_reference = 'txn_demo_001';You should see the new order.
We have created the order in the orders table. Now we need to add the purchased items to the order_items table.
Add the order item
For this exercise, we'll deliberately make this second operation fail.
Run:
12345678910111213INSERT INTO order_items ( order_id, product_id, quantity, unit_price)SELECT id, 1, 1, 'not-a-price'FROM ordersWHERE payment_reference = 'txn_demo_001';PostgreSQL rejects the statement because unit_price is a numeric column and 'not-a-price' is not a valid numeric value.
So our operation now looks like this:
12Create the order → succeededAdd the order item → failedFrom the application's perspective, placing the order failed.
But what happened to the order created by the first statement?
Check the database
Run:
12345678SELECT id, customer_id, status, total_amount, payment_referenceFROM ordersWHERE payment_reference = 'txn_demo_001';The order is still there.
This is the problem transactions are designed to solve.
The customer performed one operation:
1Place orderBut PostgreSQL received two separate SQL statements:
12INSERT INTO ordersINSERT INTO order_itemsWe didn't tell PostgreSQL that those statements needed to succeed or fail together.
Because they were run separately, PostgreSQL treated them independently.
The first statement succeeded, so its change was committed. The second statement failed, so that statement made no changes.
The failure of the second statement doesn't undo the first statement that has already been committed.
We are left with:
12345orders└── order existsorder_items└── no item for the orderThe database now contains only part of the operation that the application intended to perform.
We need one outcome for both statements
What we actually want is:
1234Create the orderAdd the order item ↓ one outcomeIf both operations succeed:
1keep both changesIf either operation fails:
1keep neither changeWe therefore need a way to tell PostgreSQL:
These SQL statements belong to the same unit of work.
That is what a transaction allows us to do.
Instead of treating the statements independently:
123statement 1 → commitstatement 2 → failwe can group them together:
123transaction├── create the order└── add the order itemThe transaction gives the group a single outcome.
If all the required statements succeed, we can commit the changes. If something goes wrong, we can roll back the transaction and discard the changes made during it. This all-or-nothing behavior is called atomicity.
So the core idea is:
A transaction lets multiple SQL statements behave as one unit of work, so the database doesn't keep a partially completed operation.
In the next chapter, we'll see how PostgreSQL lets us control this using BEGIN, COMMIT, and ROLLBACK.