Mastering Stored Procedures and Cursors in MySQL: Safe Batch Processing and Error Handling - ITFROMZERO - Share tobe shared! A complete guide to writing MySQL Stored Procedures with Cursors for safe batch data processing, featuring Transaction wrapping, Exception Handlers, and database resource optimization.

Table of Contents

Context & Why It Matters

Back when I first started as a backend developer, I experienced an unforgettable crash. When tasked with upgrading user membership tiers, I used an ORM to query tens of thousands of records directly into RAM. Then, the code iterated through each object with a for loop and sent an UPDATE back to the database. During testing with a few hundred records, everything ran smoothly. But right after deploying to production with over 500,000 users, the application crashed immediately due to an Out Of Memory (OOM) error. Hundreds of thousands of network round-trips caused severe network congestion, while the database server pegged at 100% CPU utilization.

Why pull a mountain of data back to the backend only to push it right back? We can let the database engine handle everything on the spot using Stored Procedures and Cursors. A procedure bundles all processing statements internally right on the MySQL server, eliminating network transfer overhead. Meanwhile, a Cursor acts as a pointer iterating through rows one by one. It shines when pure UPDATE ... JOIN statements cannot handle complex branching logic.

However, running batch updates directly on the database always carries risks. If a single record hits a foreign key constraint or a duplicate key error without an Exception Handler, the transaction hangs or leaves data in an inconsistent state. I once had to pull an all-nighter until 3 AM restoring data because a script failed halfway through execution. From that painful lesson, my golden rule has been: every batch processing procedure must run inside a transaction, include an automated rollback handler, and record detailed logs.

Setting Up the Test Environment

Before running any commands, prepare an isolated test database locally or in a staging environment. Never test directly in production. Here is our scenario for customer tier upgrades and loyalty point calculations:

-- 1. Create demo database
CREATE DATABASE IF NOT EXISTS itfromzero_store;
USE itfromzero_store;

-- 2. Customers table
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    tier VARCHAR(20) DEFAULT 'STANDARD',
    total_spent DECIMAL(12, 2) DEFAULT 0.00,
    bonus_points INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. Batch processing history log table
CREATE TABLE process_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    procedure_name VARCHAR(100),
    records_affected INT,
    status VARCHAR(20),
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Insert sample test data
INSERT INTO customers (name, tier, total_spent, bonus_points) VALUES
('Nguyen Van A', 'STANDARD', 1500000.00, 0),
('Tran Thi B', 'STANDARD', 5200000.00, 0),
('Le Van C', 'VIP', 12000000.00, 100),
('Pham Thi D', 'STANDARD', 800000.00, 0),
('Hoang Van E', 'STANDARD', 6100000.00, 0);

Detailed Configuration

A safe cursor-driven procedure requires 4 closely coordinated components:

DELIMITER: Changes the statement termination delimiter (for example, to $$). This prevents MySQL from prematurely executing statements when encountering semicolons inside the procedure body.
Cursor: Retrieves results from a SELECT query to iterate through rows one by one.
CONTINUE HANDLER FOR NOT FOUND: A flag indicating when the cursor has reached the last row.
EXIT HANDLER FOR SQLEXCEPTION: A safety catch. When any error occurs, the handler immediately triggers a rollback and logs error details.

Standard Variable Declaration Order in MySQL

MySQL strictly enforces the declaration order inside a BEGIN ... END block:

Local variables (DECLARE v_variable INT...)
Cursors (DECLARE cur_name CURSOR FOR...)
Error handlers (DECLARE CONTINUE/EXIT HANDLER...)

If you declare variables after a Cursor or Handler, MySQL immediately throws a syntax error: ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration.

Writing the Complete Procedure

DELIMITER $$

CREATE PROCEDURE sp_process_loyalty_upgrade(
    OUT p_total_updated INT,
    OUT p_status_code VARCHAR(20)
)
proc_label: BEGIN
    -- 1. Declare data processing variables
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_spent DECIMAL(12, 2);
    DECLARE v_new_points INT;
    DECLARE v_new_tier VARCHAR(20);
    DECLARE v_counter INT DEFAULT 0;
    
    -- Variables to store SQL error code and message during failures
    DECLARE v_error_code CHAR(5) DEFAULT '00000';
    DECLARE v_error_msg TEXT;

    -- 2. Declare Cursor to retrieve customers eligible for upgrade
    DECLARE cur_customers CURSOR FOR
        SELECT id, total_spent FROM customers WHERE tier = 'STANDARD';

    -- 3. Handler to flag when Cursor finishes reading all rows
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 4. Handler to catch unexpected exceptions (SQLEXCEPTION)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_error_code = RETURNED_SQLSTATE,
            v_error_msg = MESSAGE_TEXT;
            
        ROLLBACK;
        
        INSERT INTO process_logs (procedure_name, records_affected, status, error_message)
        VALUES ('sp_process_loyalty_upgrade', v_counter, 'FAILED', CONCAT('Code: ', v_error_code, ' - ', v_error_msg));
        
        SET p_total_updated = v_counter;
        SET p_status_code = 'ERROR';
    END;

    -- 5. Start Transaction
    START TRANSACTION;

    -- 6. Open Cursor
    OPEN cur_customers;

    read_loop: LOOP
        FETCH cur_customers INTO v_id, v_spent;
        
        -- Exit loop when all records have been processed
        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Logic for classification and point calculation based on spending
        IF v_spent >= 5000000.00 THEN
            SET v_new_tier = 'GOLD';
            SET v_new_points = 500;
        ELSEIF v_spent >= 1000000.00 THEN
            SET v_new_tier = 'SILVER';
            SET v_new_points = 100;
        ELSE
            SET v_new_tier = 'STANDARD';
            SET v_new_points = 10;
        END IF;

        -- Update data row by row
        UPDATE customers
        SET tier = v_new_tier, bonus_points = bonus_points + v_new_points
        WHERE id = v_id;

        SET v_counter = v_counter + 1;
    END LOOP;

    -- 7. Close Cursor to release memory
    CLOSE cur_customers;

    -- 8. Commit data safely
    COMMIT;

    -- Log completion
    INSERT INTO process_logs (procedure_name, records_affected, status, error_message)
    VALUES ('sp_process_loyalty_upgrade', v_counter, 'SUCCESS', NULL);

    SET p_total_updated = v_counter;
    SET p_status_code = 'SUCCESS';
END proc_label$$

DELIMITER ;

Testing & Monitoring

Execution and Result Verification

Invoke the procedure directly in your MySQL client using two output variables:

CALL sp_process_loyalty_upgrade(@updated_count, @status);

-- View returned status
SELECT @updated_count AS total_updated, @status AS status;

-- Verify actual data after update
SELECT id, name, tier, total_spent, bonus_points FROM customers;

Don’t forget to check the log table to confirm the processing history:

SELECT * FROM process_logs ORDER BY id DESC LIMIT 5;

Resource Monitoring When Using Cursors

In MySQL, Cursors are read-only and asensitive. When you open a Cursor, MySQL often creates a temporary table in memory or writes it to disk. If the source table contains millions of rows, RAM consumption can skyrocket within seconds.

To determine whether your procedure is bottlenecking the server, monitor these status variables:

-- Measure temporary tables created in RAM and on disk
SHOW STATUS LIKE 'Created_tmp%tables';

-- Identify processes currently consuming resources
SHOW FULL PROCESSLIST;

Key battle-tested rules for production deployments:

Avoid scanning entire massive tables: For tables with 50,000+ rows, divide data into chunks (1,000 – 5,000 rows each) by ID or pagination instead of pulling everything into a single Cursor.
Reclaim memory: Always invoke CLOSE cur_name; right after the loop so MySQL frees temporary memory.
Prefer Set-based SQL: For the same 100,000-row update task, an UPDATE ... CASE WHEN ... statement takes only about 150ms. In contrast, a row-by-row Cursor loop can take 10 to 15 seconds. Use Cursors only when you truly need complex business branching or sub-routine invocations for each record.