Transactions and Concurrency
Why transactions alone don't prevent race conditions
In the previous chapters, you learned how transactions prevent part of a multi-step operation from being saved when another part fails.
But transactions don't solve every problem that can occur when multiple users interact with your application at the same time.
Imagine you have a full-stack application with a React frontend, a Node.js backend, and a PostgreSQL database.
When a user performs an action in the frontend, the frontend sends a request to the Node.js backend. The backend then uses a database client to send SQL statements to PostgreSQL.
Conceptually:
123456789React frontend ↓HTTP request ↓Node.js backend ↓database client ↓PostgreSQLNow imagine that two requests involving the same data reach the backend at almost the same time.
Let's see what can go wrong.
Setting up an order
Imagine that your application allows a pending order to be either shipped or cancelled.
First, let's create an order for the example.
Type the following statement into the SQL editor and run it:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 49.99, 'txn_race_001', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;Now check the order's status:
123SELECT id, statusFROM ordersWHERE payment_reference = 'txn_race_001';You should see:
1pendingWe've now set up the data we need.
From this point, we'll look conceptually at what could happen if two requests work with this order at the same time. You don't need to type the following concurrency examples into the SQL editor.
Two requests read the same order
Suppose two requests reach the Node.js backend at almost the same time.
One request is trying to ship the order.
The other is trying to cancel it.
Before changing the order, each request checks its current status:
1234567Shipping request Cancellation requestSELECT status→ pending SELECT status → pendingBoth requests see that the order is pending.
The shipping request therefore decides that shipping is allowed.
The cancellation request also decides that cancellation is allowed.
The two requests have made decisions based on the same earlier state.
Both requests change the order
Conceptually:
1234567Shipping request Cancellation requestread status = pending read status = pendingdecide shipping is allowed decide cancellation is allowedUPDATE status = 'shipped' UPDATE status = 'cancelled'Suppose the shipping request updates the row first and the cancellation request updates it afterward.
The final row may contain:
1status = cancelledBut the shipping request may already have acted on its earlier decision to ship the order.
Neither request contained invalid SQL.
The problem is that both requests:
read the same earlier state;
made a decision based on that state;
changed the row afterward.
This kind of concurrency bug is called a race condition.
Why a transaction isn't enough
You might think that wrapping each request in a transaction would solve the problem.
Conceptually:
12345678910Shipping request Cancellation requestBEGIN BEGINSELECT status SELECT status→ pending → pendingUPDATE status UPDATE statusCOMMIT COMMITBut starting a transaction doesn't prevent another transaction from running at the same time.
Transactions allow several statements to succeed or fail as one unit of work, but concurrent transactions still need rules that determine which data they can see and what happens when they try to work with the same rows.
PostgreSQL uses Multi-Version Concurrency Control, commonly called MVCC, as a core part of how it manages this concurrency.
In the next chapter, you'll learn what MVCC is and why PostgreSQL uses it.