Làm chủ Stored Procedure và Cursor trong MySQL: Xử lý dữ liệu hàng loạt an toàn và kiểm soát lỗi - ITFROMZERO - Share tobe shared! Hướng dẫn viết Stored Procedure kết hợp Cursor trong MySQL để xử lý dữ liệu hàng loạt an toàn. Đi kèm kỹ thuật bọc Transaction, bắt lỗi bằng Exception Handler và tối ưu tài nguyên database.

Table of Contents

Bối cảnh & Tại sao cần

Hồi mới làm backend, mình từng dính một cú sập nhớ đời. Khi cần cập nhật hạng thành viên cho user, mình dùng ORM query cả chục nghìn record lên RAM. Sau đó, code chạy vòng lặp for từng object rồi bắn UPDATE ngược về database. Lúc test vài trăm bản ghi thì mượt mà. Thế nhưng khi vừa đẩy lên production với hơn 500.000 user, app crash ngay lập tức vì Out Of Memory (OOM). Hàng trăm nghìn lượt round-trip qua lại khiến mạng nghẽn đặc, còn database server thì chạm trần 100% CPU.

Tại sao phải kéo cả núi dữ liệu về backend rồi đẩy ngược lại? Ta hoàn toàn có thể để database engine tự giải quyết tại chỗ bằng Stored Procedure và Cursor. Procedure gom toàn bộ câu lệnh xử lý nội bộ ngay trên MySQL server, triệt tiêu overhead truyền tải qua mạng. Còn Cursor đóng vai trò như con trỏ duyệt từng dòng. Nó phát huy sức mạnh khi các câu lệnh UPDATE ... JOIN thuần túy không gánh nổi logic điều kiện rẽ nhánh phức tạp.

Dù vậy, batch update trên database luôn tiềm ẩn rủi ro. Chỉ cần một record dính lỗi foreign key hoặc duplicate key mà không có Exception Handler, transaction sẽ treo đứng hoặc dữ liệu rơi vào cảnh ‘nửa nạc nửa mỡ’. Mình từng phải thức trắng lúc 3 giờ sáng để restore dữ liệu vì một câu script chạy lỗi giữa chừng. Từ bài học xương máu đó, nguyên tắc của mình là: mọi procedure xử lý hàng loạt bắt buộc phải nằm trong transaction, có handler tự động rollback và ghi log chi tiết.

Cài đặt môi trường thử nghiệm

Trước khi gõ lệnh, hãy chuẩn bị một database test độc lập trên máy local hoặc staging. Tuyệt đối không thử nghiệm trực tiếp trên production. Ta dựng bài toán nâng hạng và tính điểm thưởng (loyalty points) cho khách hàng:

-- 1. Tạo database demo
CREATE DATABASE IF NOT EXISTS itfromzero_store;
USE itfromzero_store;

-- 2. Bảng khách hàng
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. Bảng ghi log lịch sử batch processing
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. Chèn dữ liệu mẫu kiểm tra
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);

Cấu hình chi tiết

Một procedure duyệt con trỏ an toàn đòi hỏi 4 mảnh ghép phối hợp chặt chẽ:

DELIMITER: Đổi ký tự kết thúc lệnh (ví dụ sang $$). Nhờ đó, MySQL không thực thi ngắt quãng khi thấy dấu chấm phẩy bên trong thân procedure.
Cursor: Con trỏ lấy kết quả từ SELECT để duyệt từng dòng một.
CONTINUE HANDLER FOR NOT FOUND: Cờ đánh dấu khi con trỏ đã đọc đến dòng cuối cùng.
EXIT HANDLER FOR SQLEXCEPTION: Chốt chặn an toàn. Khi phát sinh lỗi bất kỳ, handler lập tức kích hoạt rollback và lưu chi tiết lỗi vào log.

Thứ tự khai báo biến chuẩn trong MySQL

MySQL bắt buộc bạn tuân thủ nghiêm ngặt thứ tự khai báo trong block BEGIN ... END:

Biến cục bộ (DECLARE v_variable INT...)
Con trỏ (DECLARE cur_name CURSOR FOR...)
Bộ xử lý lỗi (DECLARE CONTINUE/EXIT HANDLER...)

Nếu đặt biến sau Cursor hoặc Handler, MySQL sẽ lập tức quẳng lỗi cú pháp ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration.

Viết Procedure hoàn chỉnh

DELIMITER $$

CREATE PROCEDURE sp_process_loyalty_upgrade(
    OUT p_total_updated INT,
    OUT p_status_code VARCHAR(20)
)
proc_label: BEGIN
    -- 1. Khai báo biến xử lý dữ liệu
    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;
    
    -- Biến lưu mã lỗi SQL khi có sự cố
    DECLARE v_error_code CHAR(5) DEFAULT '00000';
    DECLARE v_error_msg TEXT;

    -- 2. Khai báo Cursor lấy khách hàng chuẩn bị nâng hạng
    DECLARE cur_customers CURSOR FOR
        SELECT id, total_spent FROM customers WHERE tier = 'STANDARD';

    -- 3. Handler đánh dấu khi Cursor đọc hết dữ liệu
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 4. Handler bắt ngoại lệ bất ngờ (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. Bắt đầu Transaction
    START TRANSACTION;

    -- 6. Mở Cursor
    OPEN cur_customers;

    read_loop: LOOP
        FETCH cur_customers INTO v_id, v_spent;
        
        -- Thoát vòng lặp khi duyệt hết bản ghi
        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Logic phân loại và tính điểm theo mức chi tiêu
        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;

        -- Cập nhật dữ liệu từng dòng
        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. Đóng Cursor để giải phóng bộ nhớ
    CLOSE cur_customers;

    -- 8. Xác nhận lưu dữ liệu an toàn
    COMMIT;

    -- Ghi nhận log hoàn tất
    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 ;

Kiểm tra & Monitoring

Thực thi và kiểm tra kết quả

Gọi procedure trực tiếp trên MySQL client với hai biến output:

CALL sp_process_loyalty_upgrade(@updated_count, @status);

-- Xem trạng thái trả về
SELECT @updated_count AS total_updated, @status AS status;

-- Kiểm tra dữ liệu thực tế sau khi cập nhật
SELECT id, name, tier, total_spent, bonus_points FROM customers;

Đừng quên kiểm tra bảng log để xác nhận lịch sử xử lý:

SELECT * FROM process_logs ORDER BY id DESC LIMIT 5;

Giám sát tài nguyên khi sử dụng Cursor

Trong MySQL, Cursor là dạng read-only và asensitive. Khi bạn mở Cursor, MySQL thường phải tạo bảng tạm (temporary table) trong bộ nhớ hoặc ghi xuống ổ đĩa. Nếu bảng nguồn có hàng triệu dòng, dung lượng RAM tiêu thụ sẽ tăng vọt chỉ sau vài giây.

Muốn biết procedure có đang làm nghẽn máy chủ hay không, hãy theo dõi các biến trạng thái:

-- Đo lường số bảng tạm được tạo trên RAM và disk
SHOW STATUS LIKE 'Created_tmp%tables';

-- Phát hiện các tiến trình đang chiếm dụng tài nguyên
SHOW FULL PROCESSLIST;

Vài nguyên tắc thực chiến khi triển khai trên production:

Không quét toàn bộ bảng lớn: Với bảng từ 50.000 dòng trở lên, hãy chia nhỏ theo từng chunk (1.000 – 5.000 dòng/lần) bằng ID hoặc phân trang thay vì gom hết vào một Cursor duy nhất.
Thu hồi vùng nhớ: Luôn gọi CLOSE cur_name; ngay sau vòng lặp để MySQL giải phóng bộ nhớ tạm.
Ưu tiên Set-based SQL: Cùng bài toán cập nhật trên 100.000 dòng, câu lệnh UPDATE ... CASE WHEN ... chỉ tốn khoảng 150ms. Trong khi đó, Cursor lặp từng dòng có thể mất từ 10 đến 15 giây. Chỉ dùng Cursor khi bạn thực sự cần rẽ nhánh nghiệp vụ phức tạp hoặc gọi hàm con cho từng record.