MySQL & Database Design

Understanding Transactions

13 min Lesson 26 of 40

Understanding Transactions

Transactions are one of the most important concepts in database management. They allow you to group multiple SQL operations into a single, atomic unit of work that either succeeds completely or fails completely, ensuring data consistency and reliability.

What Are Transactions?

A transaction is a sequence of one or more SQL operations that are treated as a single unit. Think of it like a bank transfer: if you withdraw money from one account, it must be deposited into another. Both operations must succeed, or neither should happen.

Real-World Example: When you purchase a product online, multiple database operations occur: reducing inventory, creating an order record, charging payment, and updating customer history. All these must succeed together, or the entire operation should be reversed.

The ACID Properties

Transactions guarantee four essential properties, known by the acronym ACID:

A - Atomicity: All operations complete or none do
C - Consistency: Database moves from one valid state to another
I - Isolation: Concurrent transactions don't interfere with each other
D - Durability: Once committed, changes are permanent (even after system failure)

Atomicity in Action

Atomicity ensures that transactions are "all or nothing". If any part fails, everything is rolled back:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

If the second UPDATE fails (e.g., account 2 doesn't exist), the first UPDATE is also rolled back, maintaining data consistency.

Consistency

Consistency ensures that all database constraints and rules are maintained:

-- This table has a CHECK constraint: balance >= 0

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- If this would make balance negative, transaction fails

COMMIT;

Important: MySQL enforces consistency through constraints, triggers, and foreign keys. A transaction will fail if it violates any of these rules.

Transaction Control Commands

MySQL provides three main commands for transaction control:

START TRANSACTION;
-- Begin a new transaction

COMMIT;
-- Save all changes permanently

ROLLBACK;
-- Undo all changes since START TRANSACTION

Basic Transaction Example

Here's a complete example of transferring money between two bank accounts:

START TRANSACTION;

-- Step 1: Deduct from sender account
UPDATE accounts
SET balance = balance - 500.00,
    updated_at = NOW()
WHERE account_id = 101;

-- Step 2: Add to receiver account
UPDATE accounts
SET balance = balance + 500.00,
    updated_at = NOW()
WHERE account_id = 202;

-- Step 3: Log the transaction
INSERT INTO transaction_log (from_account, to_account, amount, transaction_date)
VALUES (101, 202, 500.00, NOW());

-- If everything succeeded, commit
COMMIT;

Handling Errors with ROLLBACK

When something goes wrong, use ROLLBACK to undo changes:

START TRANSACTION;

UPDATE products SET stock = stock - 5 WHERE product_id = 123;

-- Check if stock went negative
SELECT stock FROM products WHERE product_id = 123;

-- If stock < 0, undo the change
ROLLBACK;

-- Otherwise, save the change
-- COMMIT;

Best Practice: Always include error handling in your application code. If any operation in a transaction fails, immediately execute ROLLBACK to maintain data integrity.

Autocommit Mode

By default, MySQL operates in autocommit mode, where each SQL statement is automatically committed:

-- Check autocommit status
SELECT @@autocommit;  -- Returns 1 (enabled) or 0 (disabled)

-- Disable autocommit
SET autocommit = 0;

-- Now statements aren't committed until you explicitly COMMIT
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;

-- Re-enable autocommit
SET autocommit = 1;

Warning: When autocommit is disabled, you must remember to COMMIT your changes. Uncommitted changes are lost if the connection is closed or the server restarts.

Savepoints

Savepoints allow you to create checkpoints within a transaction that you can roll back to:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

SAVEPOINT after_withdrawal;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- If something goes wrong with the deposit, rollback to savepoint
ROLLBACK TO SAVEPOINT after_withdrawal;

-- Or commit everything
COMMIT;

When to Use Transactions

Use transactions when:

Multiple related changes must succeed together - E-commerce orders, financial transfers
Data integrity is critical - Banking, medical records, inventory management
Complex business logic requires atomic operations - Multi-step processes
Concurrent users might conflict - Booking systems, ticket sales

Real-World Example: E-Commerce Order

Here's a complete e-commerce transaction handling order placement:

START TRANSACTION;

-- 1. Create the order
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (456, NOW(), 149.99, 'pending');

SET @order_id = LAST_INSERT_ID();

-- 2. Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
    (@order_id, 101, 2, 49.99),
    (@order_id, 205, 1, 49.99);

-- 3. Reduce inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 101;
UPDATE products SET stock = stock - 1 WHERE product_id = 205;

-- 4. Check if any stock went negative
SELECT MIN(stock) INTO @min_stock FROM products WHERE product_id IN (101, 205);

IF @min_stock < 0 THEN
    ROLLBACK;
    SELECT 'Order failed: Insufficient stock' AS message;
ELSE
    COMMIT;
    SELECT 'Order placed successfully' AS message, @order_id AS order_id;
END IF;

Transaction Performance Considerations

Keep Transactions Short: Long transactions lock resources
Minimize Work Inside Transactions: Don't include external API calls
Use Appropriate Isolation Levels: Balance consistency and performance
Handle Deadlocks: Implement retry logic in application code
Monitor Transaction Log Size: Large transactions can fill logs

Practice Exercise:

Scenario: Create a transaction for a library book checkout system.

Requirements:

Update the book's available_copies (decrease by 1)
Insert a checkout record with member_id, book_id, and checkout_date
Update the member's books_checked_out count (increase by 1)
Check if books_checked_out exceeds the limit (5 books)
If limit exceeded, rollback; otherwise commit

Solution:

START TRANSACTION;

-- Update book availability
UPDATE books
SET available_copies = available_copies - 1
WHERE book_id = 789;

-- Create checkout record
INSERT INTO checkouts (member_id, book_id, checkout_date, due_date)
VALUES (123, 789, NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));

-- Update member checkout count
UPDATE members
SET books_checked_out = books_checked_out + 1
WHERE member_id = 123;

-- Check limit
SELECT books_checked_out INTO @count
FROM members
WHERE member_id = 123;

-- Check book availability
SELECT available_copies INTO @available
FROM books
WHERE book_id = 789;

-- Validate conditions
IF @count > 5 OR @available < 0 THEN
    ROLLBACK;
    SELECT 'Checkout failed: Limit exceeded or book unavailable';
ELSE
    COMMIT;
    SELECT 'Book checked out successfully';
END IF;

Summary

In this lesson, you learned:

Transactions are atomic units of work that ensure data consistency
ACID properties guarantee reliable transaction processing
Use START TRANSACTION, COMMIT, and ROLLBACK to control transactions
Autocommit mode affects how statements are committed
Savepoints provide checkpoints within transactions
Transactions are essential for complex, multi-step operations

Next Up: In the next lesson, we'll explore transaction isolation levels and how concurrent transactions interact with each other!