Transactions and ACID Properties

# CHAPTER 20

Transactions and ACID Properties #

1. Introduction #

1. 1. UPDATE accounts SET balance = balance - 1000 WHERE user = 'A';

1. 2. UPDATE accounts SET balance = balance + 1000 WHERE user = 'B';

What happens if the server loses power exactly one microsecond *after* query 1 finishes, but *before* query 2 runs? User A lost their money, but User B never got it. The $1,000 evaporated into digital thin air. This is the ultimate nightmare of database administration. To solve it, we bundle queries into Transactions, governed by the laws of ACID.

2. Learning Objectives #

• Explain the four ACID properties of a database.

• Start a MySQL Transaction using START TRANSACTION.

• Permanently save a transaction using COMMIT.

• Abort and undo a transaction using ROLLBACK.

• Prevent partial data corruption in critical workflows.

3. What are ACID Properties? #

• Atomicity: "All or Nothing." A transaction of 5 queries must completely succeed, or completely fail. If query 4 fails, queries 1, 2, and 3 are automatically undone. No partial updates exist.

• Consistency: The database must move from one valid state to another. All CHECK and FOREIGN KEY constraints must be satisfied before and after.

• Isolation: If 1,000 users are transferring money at the exact same millisecond, their transactions must run in isolation. They cannot mathematically interfere with each other's calculations.

• Durability: Once a transaction is COMMITted, it is permanently written to the physical hard drive. Even if the server explodes 1 second later, the data is safe.

4. Writing a Transaction #

-- Step 1: Open the transaction envelope
START TRANSACTION;

-- Step 2: Execute the vulnerable queries
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- Step 3: If NO errors occurred in your PHP/Backend, seal the envelope!
COMMIT;

*Once COMMIT executes, the money transfer becomes a permanent, durable reality.*

5. The Power of ROLLBACK #

START TRANSACTION;

-- Deduct the money
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

-- OH NO! We accidentally tried to add it to ID 999, which doesn't exist! ERROR!
UPDATE accounts SET balance = balance + 1000 WHERE id = 999;

-- Because an error happened, we ABORT the mission!
ROLLBACK;

*When ROLLBACK executes, the database engine time-travels backward. The $1,000 deduction from ID 1 is instantly erased as if it never happened. Atomicity is preserved!*

6. Mini Project: E-Commerce Checkout #

START TRANSACTION;

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

-- 2. Insert the specific item they bought
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 12);

-- 3. Deduct the item from physical inventory
UPDATE products SET stock = stock - 1 WHERE id = 12;

-- If all 3 succeed, finalize it!
COMMIT;

7. Common Mistakes #

• Forgetting to COMMIT: If you run START TRANSACTION, type 50 INSERT commands, and then close your terminal without typing COMMIT, MySQL will assume you aborted and will silently ROLLBACK all 50 commands. Your data will not be saved!

• Using MyISAM: MySQL supports different internal "storage engines". The modern default is InnoDB, which fully supports ACID transactions. The older MyISAM engine does NOT support transactions! If you try to ROLLBACK on a MyISAM table, it will fail, and your data will be permanently corrupted.

8. Best Practices #

• Backend Orchestration: You rarely write START TRANSACTION in the raw MySQL terminal. You execute it via your application code. In PHP (PDO), you use $conn->beginTransaction(). You put your queries inside a try {} block. If any query throws an exception, the catch {} block executes $conn->rollBack().

9. Exercises #

1. 1. Define the "A" in ACID and explain what it prevents.

1. 2. What command must you issue to permanently save the changes made during an active transaction?

10. MCQ Quiz with Answers #

11. Interview Questions #

• Q: Explain the four ACID properties (Atomicity, Consistency, Isolation, Durability) and why they are critical for a banking database.

• Q: Describe a real-world web application scenario where failing to use a Transaction would result in catastrophic data inconsistency.

12. FAQs #

13. Summary #

14. Next Chapter Recommendation #