SQL Transactions Explained | ACID Properties

# CHAPTER 23

Transactions and ACID Properties #

1. Introduction #

1. 1. UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';

1. 2. UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

What happens if the server's power supply explodes the exact millisecond *after* Query 1 finishes, but *before* Query 2 starts? Alice loses her money, and Bob never gets it. The data is corrupted. To solve this terrifying scenario, SQL engines employ Transactions and adhere strictly to the ACID Properties.

2. Learning Objectives #

• Define the acronym ACID in the context of databases.

• Wrap multiple SQL queries into a single Transaction block.

• Execute a COMMIT to permanently save data.

• Execute a ROLLBACK to undo a catastrophic error.

• Understand the concept of "All or Nothing" execution.

3. The ACID Properties #

• A (Atomicity): "All or Nothing." If a transaction has 5 steps, and step 4 fails, the database automatically undoes steps 1, 2, and 3. Partial success is mathematically impossible.

• C (Consistency): The database will never process a transaction that violates a constraint (e.g., trying to set a NOT NULL column to NULL). It will reject the entire transaction.

• I (Isolation): If 1,000 users are modifying the database at the exact same millisecond, the database processes them in a way where they do not interfere with each other.

• D (Durability): Once the database says "Saved", it is permanent. Even if you pull the power cord out of the server one millisecond later, the data is safe on the hard drive.

4. Anatomy of a Transaction #

The syntax relies on three keywords:

1. 1. START TRANSACTION (or BEGIN in PostgreSQL): Turns off auto-save.

1. 2. COMMIT: Tells the database to permanently save all the pending queries.

1. 3. ROLLBACK: The "Undo" button. Erases all pending queries since the transaction started.

5. The Bank Transfer (A Perfect Transaction) #

-- Step 1: Tell SQL to pause Auto-Saving!
START TRANSACTION;

-- Step 2: Deduct money from Alice (Pending state...)
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Step 3: Add money to Bob (Pending state...)
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- Step 4: Both queries succeeded without server crashes! Make it permanent!
COMMIT;

*(If the server crashed during Step 3, the database would automatically trigger a ROLLBACK upon reboot, returning Alice's $100!)*

6. The ROLLBACK Superpower #

START TRANSACTION;

-- Oh no! I forgot the WHERE clause!
DELETE FROM customers; 

-- Phew! Undo the mistake!
ROLLBACK;

*(Result: The customers table is perfectly restored. The transaction is erased).*

7. Mini Project: E-Commerce Checkout #

START TRANSACTION;

-- 1. Create the Order
INSERT INTO orders (user_id, total) VALUES (5, 120.00);

-- 2. Add the Items to the Receipt
INSERT INTO order_items (order_id, product_id, qty) VALUES (99, 10, 1);

-- 3. Reduce the physical inventory
UPDATE inventory SET stock = stock - 1 WHERE product_id = 10;

-- 4. If all 3 steps succeeded, lock it in!
COMMIT;

*(In modern application code like Node.js or PHP, developers wrap these SQL commands in a try/catch block. If the catch block triggers, the server automatically fires a ROLLBACK command!)*

8. Common Mistakes #

• Forgetting to COMMIT: If you run START TRANSACTION, run 5 UPDATE queries, and then close your computer without running COMMIT, the database assumes you abandoned the transaction and automatically ROLLBACKs everything! Your updates will vanish.

• Locking the Database: While a Transaction is open, the database puts a "Lock" on the rows you are modifying to satisfy the "Isolation" property. If you open a transaction, go to lunch for an hour, and forget to commit, no one else in the company can modify those rows until you return!

9. Best Practices #

• Keep Transactions Tiny: A transaction should execute in milliseconds. Never put a slow process (like sending an email or generating a PDF) *inside* an open database transaction block. Do the database work, COMMIT, and then generate the PDF in your Node.js code.

10. Exercises #

1. 1. What does the "A" in ACID stand for, and what does it mathematically guarantee?

1. 2. What SQL command serves as the "Undo" button for a pending transaction block?

11. SQL Challenges #

START TRANSACTION;
UPDATE orders SET status = 'Refunded' WHERE id = 10;
UPDATE wallets SET balance = balance + 50 WHERE user_id = 5;
COMMIT;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Define the ACID properties. Explain why a NoSQL database (which often relaxes these properties for speed) would be a terrifying architectural choice for a core banking application.

• Q: Explain the mechanical interaction between a backend server's try/catch error-handling block and the SQL COMMIT/ROLLBACK commands during a multi-table E-commerce checkout process.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #