Transactions — Ensuring Data Integrity

---

title: “Transactions” part: 3 topic_number: 6 slug: “transactions” difficulty: “Advanced” prerequisites: “insert, update, delete” —

Transactions — Ensuring Data Integrity

What Is It?

A transaction is a sequence of SQL operations that execute as a single unit of work. Either ALL operations succeed, or NONE do — ensuring data consistency even if errors occur midway.

Real-world analogy: Bank transfer — money must leave one account AND enter another. If either step fails, both must be undone (you can’t lose money to the void!).

ACID Properties:

• Atomicity: All-or-nothing
• Consistency: Data stays valid
• Isolation: Transactions don’t interfere with each other
• Durability: Committed changes persist (even after crash)

---

Syntax Breakdown

START TRANSACTION;  -- or BEGIN
-- SQL statements here
COMMIT;  -- Save changes

-- Or if something goes wrong:
ROLLBACK;  -- Undo everything since START TRANSACTION

Key commands:

• START TRANSACTION / BEGIN: Start a transaction
• COMMIT: Permanently save all changes
• ROLLBACK: Undo all changes since transaction started
• SAVEPOINT: Create a rollback point within a transaction

---

Basic Examples

Simple Transaction

START TRANSACTION;

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1;

-- Check if it looks right
SELECT first_name, salary FROM employees WHERE department_id = 1;

-- If good:
COMMIT;

-- If not:
-- ROLLBACK;

What happens:

• Changes are temporary until COMMIT
• ROLLBACK undoes everything
• Other sessions don’t see changes until COMMIT

Transaction with Error Handling

START TRANSACTION;

-- Attempt a series of operations
UPDATE employees SET salary = 100000 WHERE employee_id = 1;
UPDATE employees SET salary = 95000 WHERE employee_id = 2;

-- Simulate error check
SELECT @error := COUNT(*) FROM employees WHERE salary < 0;

-- Conditional commit/rollback
IF @error > 0 THEN
    ROLLBACK;
    SELECT 'Transaction rolled back due to errors';
ELSE
    COMMIT;
    SELECT 'Transaction committed successfully';
END IF;

---

Going Deeper

Bank Transfer Example

START TRANSACTION;

-- Deduct from Account A
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';

-- Check if sufficient funds
SELECT @new_balance := balance FROM accounts WHERE account_id = 'A';

IF @new_balance < 0 THEN
    ROLLBACK;
    SELECT 'Insufficient funds, transaction rolled back';
ELSE
    -- Add to Account B
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
    COMMIT;
    SELECT 'Transfer successful';
END IF;

Either both updates happen, or neither does.

Savepoints (Partial Rollback)

START TRANSACTION;

INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 70000);
SAVEPOINT sp1;

UPDATE employees SET salary = 75000 WHERE first_name = 'John' AND last_name = 'Doe';
SAVEPOINT sp2;

DELETE FROM employees WHERE salary < 60000;

-- Oops, didn't mean to delete those!
ROLLBACK TO sp2;  -- Undo only the DELETE, keep INSERT and UPDATE

COMMIT;

Savepoints let you rollback to specific points without losing everything.

Transaction Isolation Levels

-- Set isolation level for current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
-- Your queries here
COMMIT;

Isolation levels (least to most strict):

1. READ UNCOMMITTED: Can see uncommitted changes from other transactions (dirty reads)
2. READ COMMITTED: Only sees committed changes (default in most databases)
3. REPEATABLE READ: Same query always returns same result within transaction (MySQL default)
4. SERIALIZABLE: Full isolation, transactions execute as if sequential

Multi-Table Transaction

START TRANSACTION;

-- Insert new department
INSERT INTO departments (department_name, location) VALUES ('R&D', 'Austin');
SET @new_dept_id = LAST_INSERT_ID();

-- Move employees to new department
UPDATE employees SET department_id = @new_dept_id WHERE department_id = 8;

-- Verify counts
SELECT @moved := COUNT(*) FROM employees WHERE department_id = @new_dept_id;
SELECT @old_count := COUNT(*) FROM employees WHERE department_id = 8;

IF @old_count = 0 AND @moved > 0 THEN
    COMMIT;
    SELECT CONCAT('Successfully moved ', @moved, ' employees to R&D');
ELSE
    ROLLBACK;
    SELECT 'Transaction failed, rolled back';
END IF;

---

Watch Out — Common Mistakes

Mistake #1: Forgetting to COMMIT

START TRANSACTION;
UPDATE employees SET salary = salary * 1.05;
-- Session ends here without COMMIT

Result: Changes are LOST! Always COMMIT explicitly.

In production: Use auto-commit OFF for explicit control, or ensure COMMIT is called.

Mistake #2: Long-Running Transactions

START TRANSACTION;
-- ... slow operations ...
-- ... waiting for user input ...
-- ... 10 minutes pass ...
COMMIT;

Problem: Locks held for too long, blocking other users.

Fix: Keep transactions SHORT. Don’t wait for user input inside a transaction.

Mistake #3: Mixing DDL and DML in Transactions

START TRANSACTION;

CREATE TABLE temp (id INT);  -- DDL statement
INSERT INTO employees VALUES (...);

ROLLBACK;  -- Won't undo the CREATE TABLE!

Issue: In MySQL, DDL statements (CREATE, ALTER, DROP) auto-commit! They can’t be rolled back.

Fix: Separate DDL from transactions, or know that DDL commits immediately.

---

Edge Case Spotlight

Deadlock

-- Session 1:
START TRANSACTION;
UPDATE employees SET salary = 100000 WHERE employee_id = 1;
-- (waiting for Session 2)
UPDATE employees SET salary = 90000 WHERE employee_id = 2;
COMMIT;

-- Session 2 (running simultaneously):
START TRANSACTION;
UPDATE employees SET salary = 95000 WHERE employee_id = 2;
-- (waiting for Session 1)
UPDATE employees SET salary = 85000 WHERE employee_id = 1;
COMMIT;

Deadlock! Each waits for the other’s lock. MySQL detects this and kills one transaction.

Prevention:

• Always update tables/rows in the same order
• Keep transactions short
• Use appropriate isolation levels

Auto-Commit Mode

SHOW VARIABLES LIKE 'autocommit';  -- Check current setting

SET autocommit = 0;  -- Disable (every statement needs explicit COMMIT)
SET autocommit = 1;  -- Enable (every statement auto-commits)

Default: autocommit = 1 (each statement is its own transaction)

---

Try This

Exercise 1 (Guided)

Create a transaction that gives a 10% raise to all employees in the Engineering department (dept_id = 1), but only if the total new payroll doesn’t exceed $300,000. If it exceeds, rollback and show a message.

Hint

START TRANSACTION, UPDATE, calculate SUM(salary), check condition, COMMIT or ROLLBACK.

Exercise 2 (Independent)

Implement a “swap salaries” transaction: swap the salaries of two specific employees (IDs 1 and 2). Use savepoints to allow partial rollback if needed.

Hint

Store salaries in variables, UPDATE both employees, use SAVEPOINT, verify with SELECT, COMMIT or ROLLBACK TO savepoint.

Exercise 3 (Challenge)

Create a “cascading promotion” transaction:

1. Promote employee 5 to manager (set their manager_id to NULL)
2. Assign all former manager’s direct reports to employee 5
3. Give employee 5 a 20% raise
4. If new salary exceeds $120k, rollback entirely

Hint

Multiple UPDATEs, save old manager_id, reassign reports, calculate new salary, check condition, COMMIT or ROLLBACK.

---

Answer Key

Exercise 1 Answer

```sql START TRANSACTION; -- Give raises UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1; -- Calculate new total SELECT @new_payroll := SUM(salary) FROM employees WHERE department_id = 1; -- Check limit IF @new_payroll > 300000 THEN ROLLBACK; SELECT CONCAT('Rollback: New payroll (', @new_payroll, ') exceeds limit') AS message; ELSE COMMIT; SELECT CONCAT('Success: New payroll is ', @new_payroll) AS message; END IF; ``` **Output:** Either commits with success message, or rolls back with error message.

Exercise 2 Answer

```sql START TRANSACTION; -- Store current salaries SELECT @sal1 := salary FROM employees WHERE employee_id = 1; SELECT @sal2 := salary FROM employees WHERE employee_id = 2; -- Swap UPDATE employees SET salary = @sal2 WHERE employee_id = 1; SAVEPOINT after_first_update; UPDATE employees SET salary = @sal1 WHERE employee_id = 2; -- Verify SELECT @check1 := salary FROM employees WHERE employee_id = 1; SELECT @check2 := salary FROM employees WHERE employee_id = 2; IF @check1 = @sal2 AND @check2 = @sal1 THEN COMMIT; SELECT 'Salaries swapped successfully'; ELSE ROLLBACK TO after_first_update; SELECT 'Swap failed, rolled back to savepoint'; END IF; ``` **Demonstrates savepoint usage for controlled rollback.**

Exercise 3 Answer

```sql START TRANSACTION; -- Store old manager SELECT @old_manager := manager_id FROM employees WHERE employee_id = 5; -- Promote employee 5 to top level UPDATE employees SET manager_id = NULL WHERE employee_id = 5; -- Reassign all reports from old manager to employee 5 UPDATE employees SET manager_id = 5 WHERE manager_id = @old_manager AND employee_id != 5; -- Give 20% raise UPDATE employees SET salary = salary * 1.20 WHERE employee_id = 5; -- Check new salary SELECT @new_salary := salary FROM employees WHERE employee_id = 5; IF @new_salary > 120000 THEN ROLLBACK; SELECT CONCAT('Promotion canceled: New salary (', @new_salary, ') exceeds cap') AS message; ELSE COMMIT; SELECT CONCAT('Promotion successful! New salary: ', @new_salary) AS message; END IF; ``` **Complex multi-step transaction with conditional logic.**

---

Quick Recap

• Transactions ensure all-or-nothing execution (ACID)
• START TRANSACTION begins, COMMIT saves, ROLLBACK undoes
• Changes invisible to others until COMMIT
• SAVEPOINT allows partial rollback
• Keep transactions SHORT to avoid blocking
• DDL statements auto-commit (can’t be rolled back)
• Watch for deadlocks (update in consistent order)
• Use appropriate isolation levels for your needs

---

Part 3 Complete!

Congratulations! You’ve completed all advanced topics:

• Recursive CTEs for hierarchies
• Date/time functions
• Views for reusable logic
• Window functions (ROW_NUMBER, RANK, DENSE_RANK)
• Transactions for data integrity

You’re now a proficient MySQL developer!

---

Up Next

Final Capstone Challenge! → Mini Challenge 11

You’ve completed all Part 3 topics! Take on the ultimate capstone challenge combining everything you’ve learned!