Transaction Processing
Transaction Processing
🌍 Real-World Analogy
Imagine a bank transfer — you transfer 1000 from Account A to Account B:
- Deduct 1000 from Account A
- Add 1000 to Account B
If step 1 succeeds but step 2 fails (e.g., system crash), A has less money but B didn't receive it — that's a big problem.
A transaction binds these two steps into one "atomic operation": either both steps succeed (COMMIT) or both are rolled back (ROLLBACK). There's never a "half-completed" state.
🎯 Core Concepts
What Is a Transaction
A transaction is an indivisible sequence of SQL operations that either all execute successfully or all roll back. It is the core mechanism for ensuring data consistency in databases.
ACID Properties
| Property | English | Meaning |
|---|---|---|
| Atomicity | Atomicity | All operations in a transaction either succeed or are rolled back |
| Consistency | Consistency | The database transitions from one consistent state to another |
| Isolation | Isolation | Concurrent transactions don't interfere with each other, as if executed serially |
| Durability | Durability | Once committed, data is permanently saved, even if the system crashes |
BEGIN / COMMIT / ROLLBACK
-- Start a transaction
BEGIN; -- or START TRANSACTION
-- Execute a series of operations
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- All succeeded, commit
COMMIT;
-- Something went wrong, roll back
ROLLBACK;
SAVEPOINT — Savepoints
Set a "checkpoint" within a transaction. You can roll back to a specific savepoint without affecting operations before it.
BEGIN;
INSERT INTO orders VALUES (1020, 101, 1, '2026-06-28', 500.00, 'pending');
SAVEPOINT sp1;
INSERT INTO order_items VALUES (1020, 1, 2, 100.00);
SAVEPOINT sp2;
-- If the second step has an error, roll back only to sp1
ROLLBACK TO sp1;
-- Retain the first step's operations
COMMIT;
| Command | Purpose |
|---|---|
SAVEPOINT name |
Set a savepoint |
ROLLBACK TO name |
Roll back to the specified savepoint (retains operations before the savepoint) |
RELEASE SAVEPOINT name |
Delete a savepoint |
Transaction Isolation Levels
When multiple transactions operate on the same data simultaneously, the following problems may occur:
| Problem | Description | Example |
|---|---|---|
| Dirty Read | Reading data that another transaction has not committed | Transaction A modifies data but hasn't committed; Transaction B reads the modified value |
| Non-Repeatable Read | Reading the same row twice in the same transaction yields different results | Transaction A reads the same row twice; in between, Transaction B modifies and commits that row |
| Phantom Read | Executing the same query twice in the same transaction yields different row counts | Transaction A queries twice; in between, Transaction B inserts new rows |
SQL defines four isolation levels:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Description |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ Possible | ✅ Possible | ✅ Possible | Lowest level, rarely used |
| READ COMMITTED | ❌ No | ✅ Possible | ✅ Possible | Oracle/PostgreSQL default |
| REPEATABLE READ | ❌ No | ❌ No | ✅ Possible | MySQL default |
| SERIALIZABLE | ❌ No | ❌ No | ❌ No | Highest level, worst performance |
-- View current isolation level
SELECT @@transaction_isolation;
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Deadlocks
A deadlock occurs when two transactions are waiting for each other to release resources:
Transaction A: Locks Table 1 → Waits for Table 2
Transaction B: Locks Table 2 → Waits for Table 1
→ Waiting forever, can never complete
How the database handles it: Automatically detects the deadlock, selects one transaction as the "victim" and rolls it back, allowing the other to continue.
Strategies to avoid deadlocks:
- Access tables and rows in a fixed order
- Reduce the time transactions hold locks
- Use lower isolation levels
- Create indexes for common queries to reduce lock scope
Best Practices
| Principle | Description |
|---|---|
| Keep transactions short | Reduce lock holding time, improve concurrency |
| Don't wait for user input in a transaction | Transactions should complete quickly |
| Use appropriate isolation levels | Don't default to the highest level |
| Use consistent access order | Reduce deadlock probability |
| Add error handling | Roll back promptly when errors occur |
📝 Basic Syntax
-- Start a transaction
BEGIN; -- MySQL, PostgreSQL
START TRANSACTION; -- Also supported by MySQL
-- Commit the transaction (all operations take permanent effect)
COMMIT;
-- Roll back the transaction (undo all operations)
ROLLBACK;
-- Savepoints
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL isolation_level;
-- Isolation levels: READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
-- Set global isolation level (affects all new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;
-- Autocommit switch (MySQL defaults to ON)
SET autocommit = 0; -- Disable autocommit, requires manual COMMIT
SET autocommit = 1; -- Enable autocommit (default)
- MySQL defaults to
autocommit = 1, where each SQL statement is auto-committed. Before using transactions, executeBEGINorSET autocommit = 0 - DDL statements (CREATE, ALTER, DROP) implicitly commit the current transaction
- When a connection is disconnected, uncommitted transactions are automatically rolled back
📌 Examples
Example: Order Creation Transaction (Order + Inventory Deduction)
-- Scenario: A customer places an order for a product; need to create the order and deduct inventory simultaneously
BEGIN;
-- Step 1: Create the order
INSERT INTO orders (order_id, customer_id, employee_id, order_date, total_amount, status)
VALUES (1020, 101, 1, '2026-06-28', 6999.00, 'pending');
-- Step 2: Deduct inventory
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
-- Step 3: Check if inventory is sufficient (roll back if stock < 0)
-- In practice, this is usually handled by application logic or triggers
-- All succeeded, commit
COMMIT;
If an error occurs midway:
BEGIN;
INSERT INTO orders (order_id, customer_id, employee_id, order_date, total_amount, status)
VALUES (1021, 102, 3, '2026-06-28', 129.00, 'pending');
UPDATE products SET stock = stock - 1 WHERE product_id = 2;
-- Suppose we discover insufficient inventory, roll back the entire transaction
ROLLBACK;
-- Both the order and inventory are restored to their pre-transaction state
Example: Using SAVEPOINT for Partial Rollback
BEGIN;
-- First order: successful
INSERT INTO orders VALUES (1022, 103, 4, '2026-06-28', 3500.00, 'pending');
UPDATE products SET stock = stock - 1 WHERE product_id = 4;
SAVEPOINT after_first_order;
-- Second order: error
INSERT INTO orders VALUES (1023, 104, 4, '2026-06-28', 2200.00, 'pending');
UPDATE products SET stock = stock - 1 WHERE product_id = 5;
SAVEPOINT after_second_order;
-- Discovered an issue with the second order, roll back to after the first
ROLLBACK TO after_first_order;
-- The first order is still valid, commit
COMMIT;
-- Result: Order 1022 is retained, Order 1023 is rolled back
Verification:
SELECT order_id, total_amount, status FROM orders WHERE order_id >= 1022;
order_id | total_amount | status
---------+-------------+--------
1022 | 3500.00 | pending
Explanation: ROLLBACK TO after_first_order only undoes operations after the savepoint. The first order and inventory deduction are preserved.
🎬 Scenario Practice
Scenario 1: Batch Employee Salary Adjustment (with Error Handling)
BEGIN;
-- Give Sales department a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
SAVEPOINT after_sales_raise;
-- Give Tech department an 8% raise
UPDATE employees
SET salary = salary * 1.08
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Tech');
SAVEPOINT after_tech_raise;
-- Suppose budget approval didn't pass, only undo the Tech department raise
ROLLBACK TO after_sales_raise;
-- Sales department raise is retained, commit
COMMIT;
Key Point: SAVEPOINT enables "partial rollback," suitable for batch operations that require flexible control.
Scenario 2: Simulating a Transfer and Handling Deadlocks
-- Session A: Transfer budget from department 1 to department 2
BEGIN;
UPDATE departments SET budget = budget - 50000 WHERE department_id = 1;
-- Wait a moment...
UPDATE departments SET budget = budget + 50000 WHERE department_id = 2;
COMMIT;
-- Session B: Simultaneously transfer budget from department 2 to department 1 (another connection)
BEGIN;
UPDATE departments SET budget = budget - 30000 WHERE department_id = 2;
UPDATE departments SET budget = budget + 30000 WHERE department_id = 1;
COMMIT;
Deadlock prevention:
-- Both sessions access departments in ascending order by department_id
-- Session A:
BEGIN;
UPDATE departments SET budget = budget - 50000 WHERE department_id = 1; -- Lock 1 first
UPDATE departments SET budget = budget + 50000 WHERE department_id = 2; -- Then lock 2
COMMIT;
-- Session B:
BEGIN;
UPDATE departments SET budget = budget + 30000 WHERE department_id = 1; -- Also lock 1 first
UPDATE departments SET budget = budget - 30000 WHERE department_id = 2; -- Then lock 2
COMMIT;
Key Point: Using a consistent access order is the simplest and most effective way to avoid deadlocks.
❓ FAQ
Q: MySQL defaults to autocommit — how do I use transactions? A: MySQL defaults to
autocommit = 1, where each SQL statement is auto-committed. To use transactions, executeBEGIN(orSTART TRANSACTION) first; subsequent SQL statements won't auto-commit until you executeCOMMITorROLLBACK.
Q: Can I ROLLBACK after COMMIT? A: No.
COMMITis the end of a transaction; once committed, data is permanently saved and cannot be undone. To "undo" committed data, you must execute a reverse operation in a new transaction (e.g., UPDATE back to the original value).
Q: Which isolation level should I choose? A: For most scenarios, use the database default (MySQL defaults to
REPEATABLE READ, Oracle/PostgreSQL defaults toREAD COMMITTED). Only useSERIALIZABLEwhen you explicitly need to prevent phantom reads and can accept the performance trade-off.
Q: Are DDL statements (CREATE TABLE, ALTER TABLE) controlled by transactions? A: No. DDL statements implicitly commit the current transaction. After executing DDL in a transaction, previous SQL statements can no longer be rolled back. This is MySQL's behavior; in PostgreSQL, DDL can be rolled back.
📖 Summary
| Concept | Description |
|---|---|
| Transaction | A group of indivisible database operations |
| ACID | Atomicity, Consistency, Isolation, Durability |
| BEGIN | Start a transaction |
| COMMIT | Commit a transaction, make it permanent |
| ROLLBACK | Roll back a transaction, undo everything |
| SAVEPOINT | A checkpoint within a transaction, supports partial rollback |
| Isolation Level | Controls visibility between concurrent transactions |
| Deadlock | Two transactions waiting for each other; the database auto-detects and rolls back one |
- Keep transactions short to reduce lock holding time
- Access resources in a fixed order to reduce deadlocks
- Choose the appropriate isolation level based on business needs
📝 Exercises
- Write a transaction that inserts a new order into the
orderstable while deducting the corresponding product's stock from theproductstable. If stock is insufficient, roll back the entire transaction. - Use
SAVEPOINTto implement a transaction that inserts 3 order records, then rolls back the 3rd one while retaining the first 2. - Check your database's default isolation level and try setting it to
READ COMMITTEDto observe the impact on concurrent reads and writes. - Thinking question: If a transaction takes a long time to execute, what impact will it have on other transactions? How can this be optimized?
Next Lesson
👉 22-stored-procedures - Stored Procedures: Learn about creating and calling stored procedures, input/output parameters, flow control statements, and the practical applications of stored procedures.
