This content originally appeared on DEV Community and was authored by Sajjad Rahman
This lab is for anyone who knows just enough PostgreSQL to open psql, but wants to actually **see* how transactions, rollback, isolation, and durability work in real life.*
🧠 Step 0: If You’ve Never Used PostgreSQL Before…
Before starting this lab, make sure you know how to open psql and create a simple database. If not, start here first: A Beginner’s Journey with PostgreSQL
That guide shows you:
- How to connect using
psql, create your first database and table , Common beginner errors (and how to fix them)
Once you can run:
psql -U postgres
and see the postgres=# prompt — you’re ready to start this ACID Lab!
What You’ll Learn
By the end of this lab, you’ll see and understand all 4 parts of the ACID model:
| Property | Meaning | You’ll See It In Action |
|---|---|---|
| Atomicity | All-or-nothing transactions | Rollback demo |
| Consistency | Constraints keep data valid | Check constraint demo |
| Isolation | Transactions don’t interfere | Two sessions test |
| Durability | Committed data stays saved | After restart check |
⚙️ Lab Setup
Assumptions
PostgreSQL is installed and
psqlworks.-
We will use two terminal windows:
- Session A → to make changes
- Session B → to observe from another user
User:
postgres
✅ Create Database and Table
Run this once in Git Bash terminal:
psql -U postgres
CREATE DATABASE acid_lab;
Now enter the database
postgres=# \c acid_lab
acid_lab=#
output
Then in psql:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (name, balance) VALUES
('A', 1000.00),
('B', 1000.00);
SELECT * FROM accounts ORDER BY id;
Expected Output
id | name | balance
----+------+---------
1 | A | 1000.00
2 | B | 1000.00
(2 rows)
📸 Caption: “Initial data — both accounts start at 1000 and 1500”
🧩 1. Atomicity — The All-or-Nothing Rule
Open Session A and start a transaction:
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
SELECT * FROM accounts;
Output inside Session A:
1 | A | 800.00
2 | B | 1300.00
Now open Session B (new terminal):
SELECT * FROM accounts;
Output in Session B (unchanged):
1 | A | 1000.00
2 | B | 1500.00
📸“Uncommitted changes are invisible to other sessions.”
Now rollback in Session A:
ROLLBACK;
SELECT * FROM accounts;
Output:
1 | A | 1000.00
2 | B | 1500.00
📸 “Atomicity — no partial changes.”
Instead of Rollback, if I write COMMIT the change can see in the both terminals
📸 “Atomicity — Fully Changes.”
Next Part we will see about the
🌐 Connect & Share
If this lab helped you, leave a comment or share your screenshots —
Let’s connect and keep building together 💬
🐙 GitHub – @sajjadrahman56
💼 LinkedIn – Connect with me
🐦 X (Twitter) – @sajjadrahman56
📺 YouTube – Tutorials & tips for data engineers
This content originally appeared on DEV Community and was authored by Sajjad Rahman
Sajjad Rahman | Sciencx (2025-10-21T03:30:00+00:00) Hands-On ACID in PostgreSQL : Part-1. Retrieved from https://www.scien.cx/2025/10/21/hands-on-acid-in-postgresql-part-1/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.