MySQL Transactions

Overview

Transactions are sequences of database operations that are executed as a single unit of work. Transactions ensure data integrity by ensuring that all operations in a transaction either complete successfully or are rolled back completely.

ACID Properties

• Atomicity: All operations succeed or all fail
• Consistency: Database remains valid state
• Isolation: Transactions don't interfere
• Durability: Committed changes persist

Transaction Basics

START TRANSACTION / START TRANSACTION

-- Start a transaction
START TRANSACTION;

-- Alternative syntax
BEGIN;

-- Start with specific isolation level
START TRANSACTION ISOLATION LEVEL READ COMMITTED;

COMMIT / COMMIT

-- Commit changes
START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 100);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

COMMIT;
-- Changes are now permanent

ROLLBACK / ROLLBACK

-- Rollback changes
START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 100);
-- Something goes wrong
ROLLBACK;
-- Changes are undone

Complete Example

-- Start transaction
START TRANSACTION;

-- Check inventory
SELECT quantity INTO @available 
FROM inventory 
WHERE product_id = 1 FOR UPDATE;

-- Check if enough stock
IF @available >= 10 THEN
    -- Create order
    INSERT INTO orders (customer_id, product_id, quantity, total) 
    VALUES (1, 1, 10, 99.99);
    
    -- Update inventory
    UPDATE inventory 
    SET quantity = quantity - 10 
    WHERE product_id = 1;
    
    -- Commit if successful
    COMMIT;
    SELECT 'Order placed successfully';
ELSE
    -- Rollback if not enough stock
    ROLLBACK;
    SELECT 'Insufficient inventory';
END IF;

Transaction Commands

SET AUTOCOMMIT / SET AUTOCOMMIT

-- Disable autocommit (transaction mode)
SET AUTOCOMMIT = 0;
SET AUTOCOMMIT = OFF;

-- Enable autocommit (auto-commit each statement)
SET AUTOCOMMIT = 1;
SET AUTOCOMMIT = ON;

-- Check autocommit status
SELECT @@AUTOCOMMIT;

Autocommit Behavior

-- With autocommit ON (default)
INSERT INTO users (name) VALUES ('John');  -- Immediately committed
UPDATE users SET email = 'john@example.com' WHERE name = 'John';  -- Immediately committed

-- With autocommit OFF
INSERT INTO users (name) VALUES ('Jane');  -- Not committed
COMMIT;  -- Now committed

-- Or rollback
INSERT INTO users (name) VALUES ('Bob');  -- Not committed
ROLLBACK;  -- Undone

Isolation Levels

Isolation Levels Overview

| Level |----------------|-------------------|-------------------------------------|-----------------------| | READ UNCOMMITTED | Yes | READ COMMITTED | No | REPEATABLE READ | No | SERIALIZABLE | No

Setting Isolation Level

-- Global level
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Session level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current isolation level
SELECT @@GLOBAL.tx_isolation;
SELECT @@SESSION.tx_isolation;

-- For specific transaction
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Isolation Level Examples

-- READ COMMITTED (prevents dirty reads)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM products WHERE category = 'electronics';
-- Other transaction can insert new products
COMMIT;

-- REPEATABLE READ (prevents non-repeatable reads)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM products WHERE category = 'electronics';
-- Other transaction's inserts not visible until you commit
COMMIT;

-- SERIALIZABLE (prevents phantom reads)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM products WHERE category = 'electronics';
-- Other transactions blocked until you commit
COMMIT;

Transaction Control Statements

SAVEPOINT

-- Create savepoints within transaction
START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT order_created;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
SAVEPOINT inventory_updated;

-- Rollback to specific savepoint
ROLLBACK TO SAVEPOINT inventory_updated;
-- inventory_update undone, order_created remains

-- Rollback to earlier savepoint
ROLLBACK TO SAVEPOINT order_created;
-- Both undone, back to start

-- Release savepoint
RELEASE SAVEPOINT inventory_updated;
-- savepoint removed

Conditional Rollback

START TRANSACTION;

INSERT INTO orders (customer_id, total) 
VALUES (1, 100);
SAVEPOINT after_order;

-- Update inventory
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 1;

-- Check if update affected rows
IF ROW_COUNT() > 0 THEN
    COMMIT;
    SELECT 'Order completed';
ELSE
    ROLLBACK TO SAVEPOINT after_order;
    SELECT 'Order failed: insufficient inventory';
END IF;

Practical Examples

Bank Transfer

-- Ensure both accounts updated or neither
START TRANSACTION;

-- Debit from account A
UPDATE accounts 
SET balance = balance - 500 
WHERE id = 1;

-- Credit to account B
UPDATE accounts 
SET balance = balance + 500 
WHERE id = 2;

-- Verify both succeeded
SELECT @balance_a := balance FROM accounts WHERE id = 1;
SELECT @balance_b := balance FROM accounts WHERE id = 2;

IF @balance_a >= 0 AND @balance_b >= 0 THEN
    COMMIT;
    SELECT 'Transfer successful';
ELSE
    ROLLBACK;
    SELECT 'Transfer failed';
END IF;

Order Processing

START TRANSACTION;

-- Create order
INSERT INTO orders (customer_id, total, status) 
VALUES (1, 150.00, 'pending');
SET @order_id = LAST_INSERT_ID();
SAVEPOINT order_created;

-- Add order items
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES (@order_id, 10, 2, 75.00);
SAVEPOINT items_added;

-- Update inventory
UPDATE inventory 
SET quantity = quantity - 2 
WHERE product_id = 10;

-- Check inventory
SELECT @qty := quantity FROM inventory WHERE product_id = 10;

IF @qty >= 0 THEN
    -- Update order status
    UPDATE orders SET status = 'confirmed' WHERE id = @order_id;
    
    COMMIT;
    SELECT 'Order confirmed';
ELSE
    -- Not enough inventory
    ROLLBACK TO SAVEPOINT items_added;
    UPDATE orders SET status = 'failed' WHERE id = @order_id;
    COMMIT;
    SELECT 'Order failed: insufficient inventory';
END IF;

Batch Operations

START TRANSACTION;

-- Process multiple records
DECLARE done INT DEFAULT FALSE;
DECLARE user_id INT;

DECLARE cur CURSOR FOR 
    SELECT id FROM users WHERE status = 'pending';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
    FETCH cur INTO user_id;
    IF done THEN
        LEAVE read_loop;
    END IF;
    
    -- Process each user
    UPDATE users SET status = 'active' WHERE id = user_id;
    INSERT INTO user_logs (user_id, action) VALUES (user_id, 'activated');
    
    SAVEPOINT after_user;
END LOOP;

CLOSE cur;

COMMIT;

Transaction Logs

Binary Logging

-- Check if binary logging is enabled
SHOW VARIABLES LIKE 'log_bin';

-- View binary log files
SHOW BINARY LOGS;

-- Check current binary log position
SHOW MASTER STATUS;

InnoDB Logs

-- Check InnoDB log settings
SHOW VARIABLES LIKE 'innodb_log%';

-- Check transaction isolation
SELECT @@tx_isolation;

Transaction Locking

Lock Types

-- Shared lock (read lock)
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (write lock)
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- Nowait lock (returns immediately if locked)
SELECT * FROM products WHERE id = 1 FOR UPDATE NOWAIT;

Lock Timeout

-- Set lock timeout
SET SESSION innodb_lock_wait_timeout = 50;

-- Transaction with timeout
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Waits up to 50 seconds for lock

Transaction Performance

Optimization Tips

-- Keep transactions short
-- Long transactions hold locks longer

-- Minimize data in transactions
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 1;
COMMIT;  -- Good

-- Avoid large transactions
START TRANSACTION;
UPDATE orders SET status = 'processed';  -- Locks all rows
COMMIT;  -- Bad

-- Use appropriate isolation levels
-- Higher isolation = more locking overhead
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Monitoring Transactions

-- View current transactions
SELECT * FROM information_schema.INNODB_TRX;

-- View transaction locks
SELECT * FROM information_schema.INNODB_LOCKS;

-- View lock waits
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- View long-running transactions
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

Deadlocks

Deadlock Detection

-- InnoDB automatically detects deadlocks
-- Rolls back one transaction to resolve

-- Deadlock example
-- Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Waits for lock on id=2

-- Transaction 2:
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Waits for lock on id=1

-- Deadlock! One transaction rolled back automatically

Handling Deadlocks

-- Retry logic for deadlocks
DECLARE retry_count INT DEFAULT 0;
DECLARE max_retries INT DEFAULT 3;
DECLARE success BOOLEAN DEFAULT FALSE;

deadlock_retry: WHILE retry_count < max_retries AND NOT success DO
    BEGIN
        DECLARE CONTINUE HANDLER FOR 1213 BEGIN
            -- Deadlock error code
            SET retry_count = retry_count + 1;
        END;
        
        START TRANSACTION;
        -- Your transaction logic here
        UPDATE accounts SET balance = balance - 100 WHERE id = 1;
        UPDATE accounts SET balance = balance + 100 WHERE id = 2;
        
        COMMIT;
        SET success = TRUE;
    END;
END WHILE deadlock_retry;

IF success THEN
    SELECT 'Transaction completed';
ELSE
    SELECT 'Transaction failed after retries';
END IF;

Distributed Transactions

Two-Phase Commit

-- Simplified two-phase commit concept

-- Phase 1: Prepare
PREPARE 'UPDATE accounts SET balance = balance - 100 WHERE id = 1';
PREPARE 'UPDATE accounts SET balance = balance + 100 WHERE id = 2';

-- Phase 2: Commit
EXECUTE PREPARE1;
EXECUTE PREPARE2;

DEALLOCATE PREPARE PREPARE1;
DEALLOCATE PREPARE PREPARE2;

Best Practices

Transaction Design

-- 1. Keep transactions short
START TRANSACTION;
-- Few statements
COMMIT;

-- 2. Minimize data accessed
-- Use WHERE clause to lock only necessary rows
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- 3. Use appropriate isolation level
-- Don't use SERIALIZABLE unless necessary
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 4. Handle errors properly
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    -- Log error
END;

-- 5. Use savepoints for complex transactions
START TRANSACTION;
-- Operation 1
SAVEPOINT sp1;
-- Operation 2
-- If error, rollback to sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Error Handling

-- Error handling in stored procedures
DELIMITER //
CREATE PROCEDURE safe_transfer(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = from_account;
    
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE id = to_account;
    
    COMMIT;
END //
DELIMITER ;

Summary

MySQL transactions provide:

• Data Integrity: ACID properties ensure reliability
• Control: COMMIT and ROLLBACK for changes
• Flexibility: Savepoints for partial rollback
• Isolation: Multiple levels for concurrency control
• Safety: Automatic deadlock detection

---

Previous: Regular Expressions

Next: ALTER Command