What is a Transaction
A transaction is a sequence of SQL statements executed as a single unit. Either ALL statements succeed (COMMIT) or ALL are undone (ROLLBACK) — there's no partial success. Transactions prevent data corruption when multiple operations must succeed or fail together.
Why Transactions Exist
- Prevent partial updates: moving money requires debit AND credit to both succeed
- Protect against application crashes mid-operation
- Handle concurrent users safely — isolation between sessions
- Allow safe multi-step complex operations with rollback on error
- Every single SQL statement is implicitly a transaction (auto-commit mode)
- Explicit transactions: BEGIN ... COMMIT (manual control)
Transaction Basic Syntax
-- Explicit transaction: process an order
BEGIN; -- or START TRANSACTION (MySQL)
-- Step 1: insert the order
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 999.99, 'pending')
RETURNING id; -- use the id in subsequent steps
-- Step 2: insert payment
INSERT INTO payments (order_id, amount, payment_method, paid_at)
VALUES (1, 999.99, 'credit_card', NOW());
-- Step 3: decrement product stock
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- All succeeded — make permanent
COMMIT;
-- If any step fails, undo everything:
ROLLBACK;
-- Auto-commit mode (default): each statement is its own transaction
INSERT INTO users (name, email) VALUES ('Tom', 'tom@.com');
-- Immediately committed, can't rollback
-- MySQL START TRANSACTION
START TRANSACTION;
UPDATE products SET stock = 50 WHERE id = 1;
INSERT INTO orders (user_id, total_amount, status) VALUES (2, 599.99, 'pending');
COMMIT;Quick Quiz
Tip
Tip
Practice What is a Transaction in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
READ COMMITTED default in PostgreSQL. REPEATABLE READ in MySQL. SERIALIZABLE for strict consistency.
Common Mistake
Warning
A common mistake with What is a Transaction is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of What is a Transaction from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A transaction is a sequence of SQL statements executed as a single unit.
- Prevent partial updates: moving money requires debit AND credit to both succeed
- Protect against application crashes mid-operation
- Handle concurrent users safely — isolation between sessions