← Back to Curriculum

MySQL Transactions and ACID

📚 Lesson 8 of 15 ⏱️ 45 min

MySQL Transactions and ACID

45 min

MySQL transactions ensure data consistency by grouping multiple SQL operations into atomic units. Transactions follow ACID properties: Atomicity (all operations succeed or all fail), Consistency (database remains in valid state), Isolation (concurrent transactions don't interfere), and Durability (committed changes persist). Understanding transactions is essential for building reliable database applications that maintain data integrity.

Transactions are started with START TRANSACTION or BEGIN, and ended with COMMIT (save changes) or ROLLBACK (discard changes). All operations within a transaction are atomic - either all succeed or all are rolled back. This ensures data consistency even if errors occur. Understanding transaction boundaries enables reliable multi-step operations.

Transaction isolation levels control how transactions interact with each other. MySQL supports READ UNCOMMITTED (lowest isolation, allows dirty reads), READ COMMITTED (prevents dirty reads), REPEATABLE READ (default, prevents non-repeatable reads), and SERIALIZABLE (highest isolation, prevents phantom reads). Each level provides different consistency guarantees with different performance trade-offs. Understanding isolation levels enables you to choose appropriate consistency for your application.

Locking mechanisms prevent concurrent transactions from interfering. MySQL uses row-level locking in InnoDB, allowing multiple transactions to work on different rows simultaneously. Locks can be shared (for reads) or exclusive (for writes). Deadlocks can occur when transactions wait for each other's locks. Understanding locking enables you to design applications that avoid deadlocks and maintain performance.

Savepoints enable partial rollbacks within transactions. You can create savepoints with SAVEPOINT name and roll back to them with ROLLBACK TO SAVEPOINT name. This enables complex transaction logic where you can roll back part of a transaction while keeping other changes. Understanding savepoints enables sophisticated transaction management.

Best practices for transactions include keeping transactions short to reduce lock contention, handling errors appropriately, understanding isolation levels, and avoiding deadlocks. Long-running transactions can block other operations and impact performance. Understanding best practices enables efficient, reliable transaction usage.

Key Concepts

  • Transactions group operations into atomic units.
  • ACID properties ensure data consistency and reliability.
  • Isolation levels control transaction interaction.
  • Locking prevents concurrent transaction interference.
  • Savepoints enable partial transaction rollbacks.

Learning Objectives

Master

  • Using transactions for atomic operations
  • Understanding ACID properties
  • Configuring transaction isolation levels
  • Handling transaction errors and rollbacks

Develop

  • Understanding database consistency mechanisms
  • Designing reliable multi-step operations
  • Managing concurrent database access

Tips

  • Start transaction: START TRANSACTION; or BEGIN;
  • Commit changes: COMMIT; to save all operations.
  • Rollback changes: ROLLBACK; to discard all operations.
  • Set isolation level: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Common Pitfalls

  • Not committing transactions, leaving locks and uncommitted data.
  • Using wrong isolation level, causing consistency or performance issues.
  • Creating long-running transactions, blocking other operations.
  • Not handling errors, leaving transactions open.

Summary

  • Transactions ensure atomic, consistent operations.
  • ACID properties provide reliability guarantees.
  • Isolation levels balance consistency with performance.
  • Understanding transactions enables reliable database operations.

Exercise

Demonstrate transaction handling and isolation levels.

-- Start a transaction
START TRANSACTION;

-- Perform multiple operations
INSERT INTO orders (user_id, total_amount, order_date) 
VALUES (1, 99.99, CURDATE());

SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 1, 2, 49.99);

-- Update product stock
UPDATE products SET stock = stock - 2 WHERE id = 1;

-- Check if everything is OK, then commit
-- If there's an error, this will be rolled back
COMMIT;

-- Example with error handling
START TRANSACTION;

INSERT INTO orders (user_id, total_amount) VALUES (1, 50.00);

-- Simulate an error
UPDATE products SET stock = stock - 5 WHERE id = 999; -- Non-existent product

-- This will cause a rollback
ROLLBACK;

-- Check transaction isolation level
SELECT @@transaction_isolation;

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Exercise Tips

  • Use transactions for multi-step operations: ensure all succeed or all fail.
  • Handle errors: use error handling to rollback on failures.
  • Keep transactions short: reduce lock time and improve concurrency.
  • Use savepoints: SAVEPOINT name; ROLLBACK TO SAVEPOINT name; for partial rollbacks.

Code Editor

Output