Skip to main content
PostgreSQL
CHAPTER 20 Intermediate

Transactions and ACID Properties

Updated: May 16, 2026
7 min read

# CHAPTER 20

Transactions and ACID Properties

1. Introduction

Imagine you are building a banking application. John transfers $500 to Sarah. The backend code executes two SQL queries:
  1. 1. UPDATE accounts SET balance = balance - 500 WHERE name = 'John';
  1. 2. UPDATE accounts SET balance = balance + 500 WHERE name = 'Sarah';

What happens if the database server loses power in the microsecond *after* query 1 finishes, but *before* query 2 runs? John's $500 is gone forever, and Sarah never receives it. The data is fatally corrupted. To prevent this, relational databases use Transactions. In this chapter, we will learn how to group multiple queries into unbreakable "all-or-nothing" operations.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Explain the four pillars of ACID compliance.
  • Start a manual Transaction using BEGIN.
  • Save a Transaction to the hard drive using COMMIT.
  • Abort and undo a Transaction using ROLLBACK.
  • Utilize SAVEPOINT for partial rollbacks.

3. What is ACID Compliance?

PostgreSQL is renowned for being strictly ACID compliant. ACID is an acronym representing the gold standard of database reliability:
  • A (Atomicity): "All or nothing." If a transaction has 5 queries, and query 5 fails, queries 1-4 are instantly undone.
  • C (Consistency): Data must always meet all Constraints (like the CHECK constraints we built in Chapter 19).
  • I (Isolation): If 1,000 users are transferring money at the exact same millisecond, their transactions operate in isolation and do not overlap or read each other's half-finished math.
  • D (Durability): Once PostgreSQL replies "Success" (Committed), the data is permanently saved to the physical hard drive. Even if you pull the power plug 0.1 seconds later, the data survives.

4. Anatomy of a Transaction

By default, every single INSERT or UPDATE you type is its own micro-transaction. It automatically commits. To group multiple queries together, you must manually open a transaction block using the BEGIN command.
sql
123456 
BEGIN; -- Open the vault!

UPDATE accounts SET balance = balance - 500 WHERE name = 'John';
UPDATE accounts SET balance = balance + 500 WHERE name = 'Sarah';

COMMIT; -- Close the vault and save everything permanently!

*(If the power fails before COMMIT is executed, PostgreSQL will automatically erase the entire transaction upon reboot).*

5. Time Travel with ROLLBACK

If you are inside a BEGIN block, and you realize you made a catastrophic mistake (like forgetting a WHERE clause on a DELETE statement), you do NOT type COMMIT. You type ROLLBACK. This acts as a massive "Undo" button, instantly reverting the database to the exact state it was in before you typed BEGIN.
sql
12345 
BEGIN;

DELETE FROM employees; -- Oops! We just deleted the entire company!

ROLLBACK; -- Phew! We time-traveled. The employees are back.

6. Using Savepoints

Transactions can be massive. If you execute 100 queries inside a BEGIN block, and query 101 fails, ROLLBACK undoes all 100 successful queries. To prevent this, you can drop SAVEPOINT flags inside the transaction. Think of them as video game checkpoints.
sql
12345678910 
BEGIN;

INSERT INTO users (name) VALUES ('Alice');
SAVEPOINT inserted_alice; -- Checkpoint created!

INSERT INTO users (name) VALUES ('Bob');
-- Oops, we didn't want Bob!
ROLLBACK TO inserted_alice; -- We undo Bob, but Alice survives!

COMMIT; -- We finalize Alice into the database.

7. Concurrency and Locks (Why PostgreSQL is Amazing)

If John is updating his profile (inside an open transaction), and Sarah tries to read John's profile at the exact same moment, what does Sarah see? PostgreSQL uses MVCC (Multi-Version Concurrency Control). Unlike older databases that would "lock" the table and make Sarah wait, PostgreSQL takes a snapshot. Sarah sees the *old* version of John's profile instantly, without waiting. Once John hits COMMIT, the old version is deleted and the new version becomes reality. "Readers never block writers, and writers never block readers."

8. Mini Project: The E-Commerce Checkout

A complex checkout operation MUST be wrapped in a transaction. Let's draft the logic.
sql
123456789101112131415 
BEGIN;

-- 1. Deduct the inventory
UPDATE products SET quantity = quantity - 1 WHERE product_id = 5;

-- 2. Create the Order
INSERT INTO orders (user_id, total) VALUES (42, 99.99);

-- 3. Log the payment
INSERT INTO payments (order_id, status) VALUES (currval('orders_id_seq'), 'Paid');

-- If anything above failed (e.g., inventory hit negative 0 and violated a CHECK constraint), 
-- the PHP code would catch the error and execute a ROLLBACK.
-- If everything succeeds, we finalize it:
COMMIT;

9. Common Mistakes

  • Forgetting to COMMIT: If you type BEGIN;, run an UPDATE, and close your terminal window without typing COMMIT;, PostgreSQL assumes the connection was severed and automatically executes a ROLLBACK. The update will be lost!
  • Long-Running Transactions: Never leave a BEGIN block open while waiting for user input. It consumes database resources and can cause internal locks. Transactions should execute in milliseconds.

10. Best Practices

  • Let the Backend handle it: In modern web development, you rarely type BEGIN; manually. Your ORM framework (like Laravel's Eloquent or Node's Prisma) handles the BEGIN and COMMIT automatically under the hood when you use their Transaction helper functions.

11. Exercises

  1. 1. What does the "A" in ACID stand for, and what does it mean?
  1. 2. Write the command to manually abort an open transaction block.

12. SQL Challenges

You are inside a BEGIN; block. You update an employee's salary. You suddenly realize it was the wrong employee. You have NOT typed COMMIT yet. What exact command do you type to revert the change?
sql
1 
ROLLBACK;

13. MCQ Quiz with Answers

Question 1

In the context of ACID properties, what does "Atomicity" guarantee?

Question 2

What happens if a server physically loses power while a massive UPDATE query is actively running inside a BEGIN transaction, but before COMMIT is reached?

14. Interview Questions

  • Q: Explain the four ACID properties (Atomicity, Consistency, Isolation, Durability) and why they are the foundational requirement for a financial database.
  • Q: Describe PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture. How does it handle a situation where User A is reading a row while User B is actively updating that exact same row?

15. FAQs

Q: Does MySQL support Transactions? A: Yes, modern MySQL (using the InnoDB engine) supports Transactions and ACID compliance, though historically, PostgreSQL has possessed a much stricter and more robust implementation of these features.

16. Summary

Transactions are your ultimate safety net against chaos. By understanding ACID principles and wrapping critical multi-step logic inside BEGIN and COMMIT blocks, you guarantee that power failures, server crashes, and runtime code errors will never result in corrupted, half-finished data states.

17. Next Chapter Recommendation

We have written highly defensive, complex transaction logic. But we are writing it manually. What if we want to save this logic inside the database itself, creating custom "functions" that we can call like a programming language? In Chapter 21: Stored Procedures and Functions in PostgreSQL, we will explore the powerful PL/pgSQL language.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Constraints and Data Integrity
Next Chapter Stored Procedures and Functions in PostgreSQL

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6