Learn SQL with PostgreSQL - Transaction Management, ACID & Concurrency Control

#Introduction

Welcome to episode 11 of the Learn SQL with PostgreSQL series! So far we've learned how to process data — but there's one big question we haven't answered: what happens when two users change the same data at the same time? Imagine two people buying a product with only one unit of stock left, two transactions withdrawing money from the same balance, or two orders using the same voucher. Without proper protection, all of these scenarios can produce wrong data and the loss of real money.

The answer to all of these problems is the transaction — a mechanism that guarantees a group of database operations runs as a single inseparable unit. And behind the transaction stand four pillars called ACID. Understanding both is the boundary between an application that "seems to work" and one that is truly safe.

In this episode, we'll cover the ACID principles, the BEGIN, COMMIT, ROLLBACK, and SAVEPOINT transaction commands, concurrency phenomena and isolation levels in PostgreSQL, and explicit locking with SELECT ... FOR UPDATE to prevent race conditions.

#ACID Principles in Databases

ACID is an acronym for four properties that guarantee the reliability of database transactions:

#Transaction Commands

#BEGIN, COMMIT, and ROLLBACK

These three commands are the lifecycle of a transaction:

• BEGIN: starts a transaction.
• COMMIT: saves all changes permanently.
• ROLLBACK: aborts all changes since BEGIN.

BEGIN;
 
INSERT INTO orders (customer_id, total)
VALUES ('1c2d3e4f-0000-0000-0000-000000000001', 150000);
 
UPDATE inventory
SET stock = stock - 1
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001';
 
COMMIT;

If any of the statements above fails — say stock is insufficient and CHECK (stock >= 0) rejects it — you can choose ROLLBACK to restore everything, including the order insert that already ran. Atomicity is guaranteed.

#SAVEPOINT: Partial Recovery Point

Sometimes we don't want to abort the entire transaction — only part of it. SAVEPOINT creates a partial recovery point:

BEGIN;
 
INSERT INTO orders (customer_id, total) VALUES (1, 50000);
 
SAVEPOINT order_created;
 
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (10, 20, 1);
 
ROLLBACK TO order_created;
UPDATE order_items SET quantity = 2 WHERE order_id = 10;
 
COMMIT;

After ROLLBACK TO order_created, only the changes since that savepoint are aborted — the order insert is preserved. This is useful for branching business flows with fallbacks.

#Transaction Isolation Levels & Concurrency Phenomena

Isolation addresses the question: how "isolated" are transactions from one another? Without enough isolation, concurrency phenomena emerge that distort results.

#Concurrency Phenomena

#Isolation Levels in PostgreSQL

PostgreSQL has three isolation levels (PostgreSQL never experiences dirty reads thanks to MVCC):

Read Committed is PostgreSQL's default: each statement sees the newest committed snapshot. For long transactions that need a consistent view, use Repeatable Read. Serializable is used when strict accuracy matters more than throughput.

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

#Explicit Locking: SELECT ... FOR UPDATE

Isolation levels protect against read phenomena, but they don't prevent write-write races. Imagine two transactions both read stock 5, both subtract 1, and both write 4 — when the correct result should have been 3. This is called a race condition, and it's the origin of double spending and overbooking disasters.

The solution is explicit locking: lock rows so other transactions wait.

#FOR UPDATE: Lock for Modifying Data

SELECT ... FOR UPDATE locks the rows being read until the transaction finishes. Other transactions trying to lock or modify the same rows will wait:

BEGIN;
 
SELECT stock
FROM inventory
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
FOR UPDATE;
 
UPDATE inventory
SET stock = stock - 1
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001';
 
COMMIT;

With FOR UPDATE, a second transaction processing an order for the same product waits until the first transaction commits. The double spending race condition is prevented.

#FOR SHARE: Shared Lock for Reading

SELECT ... FOR SHARE locks rows so they can't be modified by other transactions, but they can still be read (even locked in share mode by many transactions). It fits when several processes want to ensure data doesn't change while they process:

BEGIN;
SELECT id, price FROM products WHERE id = 5 FOR SHARE;
COMMIT;

#The Reader's Side: Row Lock vs Read Blocking

To be clear: with MVCC, rows locked by FOR UPDATE can still be read by other transactions (they see the old snapshot version). What's blocked is only updates — other transactions must wait before locking/modifying those rows. It's the best of both worlds: consistency for writers, concurrency for readers.

#Common Mistakes

#Closing

In this episode 11, we've understood the foundation of database reliability: the ACID principles (Atomicity, Consistency, Isolation, Durability), the BEGIN, COMMIT, ROLLBACK, and SAVEPOINT transaction commands, concurrency phenomena and isolation levels, and explicit locking with SELECT ... FOR UPDATE and FOR SHARE to prevent race conditions.

Key takeaways:

• ACID guarantees safe transactions: atomic, consistent, isolated, and durable.
• BEGIN → statement → COMMIT or ROLLBACK is the correct transaction pattern.
• SAVEPOINT enables partial rollback without aborting the entire transaction.
• PostgreSQL's default is Read Committed; Repeatable Read and Serializable are for stricter needs.
• SELECT ... FOR UPDATE is the weapon for preventing double spending and overbooking.

In the next episode, episode 12, we enter the world of advanced database objects: Views, Materialized Views & Generated Columns — from standard views to simplify data access, materialized views for high-speed analytical queries with REFRESH ... CONCURRENTLY, to generated columns that compute values automatically.