Advanced Transaction Patterns

-- ========================================
-- STORED PROCEDURE WITH TRANSACTIONS
-- ========================================

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = 'Transaction failed - rolled back';
    END;
    
    DECLARE exit handler FOR SQLWARNING
    BEGIN
        ROLLBACK;
        SET result = 'Transaction warning - rolled back';
    END;
    
    START TRANSACTION;
    
    -- Check source account balance
    SELECT balance INTO @current_balance
    FROM accounts
    WHERE account_id = from_account
    FOR UPDATE;  -- Lock the row
    
    IF @current_balance < amount THEN
        ROLLBACK;
        SET result = 'Insufficient funds';
    ELSE
        -- Debit source account
        UPDATE accounts
        SET balance = balance - amount,
            last_transaction = NOW()
        WHERE account_id = from_account;
        
        -- Credit destination account
        UPDATE accounts
        SET balance = balance + amount,
            last_transaction = NOW()
        WHERE account_id = to_account;
        
        -- Log transaction
        INSERT INTO transaction_log (
            from_account, to_account, amount, 
            transaction_date, status
        ) VALUES (
            from_account, to_account, amount,
            NOW(), 'completed'
        );
        
        COMMIT;
        SET result = 'Transfer successful';
    END IF;
END$$

DELIMITER ;

-- ========================================
-- NESTED TRANSACTIONS (Emulated)
-- ========================================

DELIMITER $$

CREATE PROCEDURE process_order(
    IN p_customer_id INT,
    IN p_items JSON,
    OUT p_order_id INT
)
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_idx INT DEFAULT 0;
    
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Order processing failed';
    END;
    
    START TRANSACTION;
    
    -- Create order
    INSERT INTO orders (customer_id, order_date, status)
    VALUES (p_customer_id, NOW(), 'pending');
    
    SET p_order_id = LAST_INSERT_ID();
    
    -- Create savepoint for items
    SAVEPOINT before_items;
    
    -- Process each item
    WHILE v_idx < JSON_LENGTH(p_items) DO
        SET v_product_id = JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].product_id'));
        SET v_quantity = JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].quantity'));
        
        -- Check inventory
        SELECT quantity_available INTO @stock
        FROM inventory
        WHERE product_id = v_product_id
        FOR UPDATE;
        
        IF @stock < v_quantity THEN
            -- Rollback to savepoint if item unavailable
            ROLLBACK TO SAVEPOINT before_items;
            UPDATE orders 
            SET status = 'partial', 
                notes = CONCAT('Product ', v_product_id, ' insufficient stock')
            WHERE order_id = p_order_id;
        ELSE
            -- Add order item
            INSERT INTO order_items (order_id, product_id, quantity)
            VALUES (p_order_id, v_product_id, v_quantity);
            
            -- Update inventory
            UPDATE inventory
            SET quantity_available = quantity_available - v_quantity
            WHERE product_id = v_product_id;
        END IF;
        
        SET v_idx = v_idx + 1;
    END WHILE;
    
    COMMIT;
END$$

DELIMITER ;

-- ========================================
-- DISTRIBUTED TRANSACTION EXAMPLE
-- ========================================

-- XA transactions for distributed systems
XA START 'xatest';

INSERT INTO local_table VALUES (1, 'data');

XA END 'xatest';
XA PREPARE 'xatest';
XA COMMIT 'xatest';

-- Or rollback
XA ROLLBACK 'xatest';

-- Recovery after crash
XA RECOVER;