By Đức Hiếu

Store Procedure: Vũ khí hai lưỡi trong tối ưu hóa performance

Trong quá trình phát triển phần mềm, chúng ta thường tin rằng Store Procedure là công cụ mạnh mẽ để tối ưu hiệu suất hệ thống. Tuy nhiên, qua trải nghiệm thực tế, mình nhận ra rằng nếu không sử dụng đúng cách, chúng có thể gây ra những vấn đề nghiêm trọng về hiệu năng. Bài viết này chia sẻ một bài học quý giá về trường hợp Store Procedure có thể trở thành nguyên nhân làm chậm hệ thống nếu không được xử lý lỗi đúng cách.

Bối cảnh dự án

Mình từng tham gia một dự án lớn về việc migrate hệ thống cũ sử dụng Java Desktop và Oracle sang Web Application với NestJS và MariaDB. Hệ thống cũ phụ thuộc nhiều vào Store Procedure để phát triển. Nhận thấy Store Procedure có một số ưu điểm như:

• Giảm số lần giao tiếp giữa Backend với Database
• Tăng hiệu xuất xử lý dữ liệu

Vì vậy team quyết định tận dụng ưu điểm của Store Procedure trong quá trình migrate:

• Các logic nghiệp vụ phức tạp cần tương tác nhiều với database được đặt trong Store Procedure
• Mục đích là tận dụng execution plan có sẵn và giảm thiểu truyền dữ liệu giữa Database và Backend
• Chúng mình kỳ vọng hiệu suất sẽ được cải thiện đáng kể với chiến lược này

Tuy nhiên, khi hệ thống vận hành thực tế, đặc biệt trong các khung giờ cao điểm, chúng tôi ghi nhận hiện tượng latency p(90) tăng đột ngột, dẫn đến hiệu năng hệ thống giảm và tính ổn định không đảm bảo như kỳ vọng.

Phát hiện vấn đề

Mỗi khi hệ thống chậm, chúng mình đều nhận được thông báo lỗi lock wait timeout. Nghiêm trọng hơn, các transaction gây ra lỗi này vẫn giữ lock trên database và không tự giải phóng cho đến khi connection bị đóng.

Khi theo dõi hệ thống, chúng mình phát hiện một hiện tượng đáng chú ý:

• Có những transaction đã chạy trong hơn 100 giây
• Chúng ở có state là RUNNING nhưng command lại là Sleep - không thực hiện bất kỳ hoạt động nào

Nhưng vẫn đang chạy và lock 2 record của một bảng quan trọng

May mắn thay, vấn đề chỉ xảy ra trên một tính năng, giúp chúng mình thu hẹp phạm vi điều tra. Mình bắt đầu rà soát code từ controller đến service, repository và cuối cùng là Store Procedure.

Nguyên nhân gốc rễ

Sau khi phân tích kỹ lưỡng, mình nhận ra điểm khác biệt quan trọng: hầu hết các Store Procedure do mình viết đều có xử lý catch lỗi và rollback transaction, nhưng Procedure đang gây vấn đề lại không có cơ chế try/catch và transaction rollback.

Mình thử bổ sung xử lý lỗi với rollback vào Procedure, tái hiện lỗi và nhận thấy một điều thú vị: khi có lỗi, transaction sẽ tự động kết thúc và giải phóng lock thay vì duy trì ở command "sleep" và giữ lock.

Sau khi áp dụng giải pháp này vào môi trường production, vấn đề đã biến mất hoàn toàn!

Suy ra nguyên nhân chính ở đây là do:

• Transaction không nhả lock khi procedure gặp lỗi.
• Logic xử lý của procedure chưa hiệu quả: do chưa  có cơ chế try/catch và xử lý lỗi rõ ràng.

Nguyên lý transaction trong MySQL/MariaDB

• Transaction chỉ thực sự kết thúc khi có lệnh COMMIT hoặc ROLLBACK rõ ràng (1).
• InnoDB không tự động rollback toàn bộ transaction khi gặp lỗi thông thường - nó chỉ rollback những statement gây lỗi (2)
• Ngoại lệ chỉ xảy ra khi gặp deadlock hoặc khi cấu hình đặc biệt được bật (2)
• Biến innodb_rollback_on_timeout có thể được cấu hình để tự động rollback toàn bộ transaction khi gặp lỗi lock wait timeout (2)

Bây giờ mình có thể giải thích chính xác vấn đề đã xảy ra:

Khi một session đang thực thi Store Procedure gặp lỗi đột ngột (vi phạm constraint, deadlock,...), nhưng Procedure không xử lý rollback/commit, transaction vẫn tồn tại và tiếp tục giữ lock trên các bản ghi đã được truy cập. Các session tiếp theo cần truy cập cùng dữ liệu này sẽ phải chờ và cuối cùng gặp lỗi lock wait timeout - "vì một thằng lỗi mà làm toàn bộ các thằng sau phải chờ và lỗi theo 😂"

Tái hiện và xác minh vấn đề

Để minh họa rõ hơn, hãy xem qua ví dụ tái hiện vấn đề này: chúng ta xây dựng một tính năng chuyển tiền và business logic chuyển tiền nằm ở Store Procedure. nghiệp vụ của chúng ta là customer sẽ không có balance là âm nên chúng ta có 1 constaint là balance ≥ 0. Để tránh race condition thì chúng ta thống nhất mọi request chuyển tiền đều sẽ đi tuần tự (kiểu như chúng ta sẽ nhét hết vô queue vậy).

Bước 1: Thiết lập cơ sở dữ liệu mẫu

CREATE TABLE customer (
    id INT auto_increment NOT NULL,
    name varchar(100) NULL,
    balance INT,
    CONSTRAINT customer_PK PRIMARY KEY (id),
    CONSTRAINT customer_CHECK CHECK (balance >= 0)
);

-- Tạo dữ liệu mẫu
INSERT INTO customer(id, name, balance)
    VALUES(1, "nhan", 100), (2, "hoang", 100), (3, "nghia", 100), (4, "trang", 100), (5, "nam", 100);

Bước 2: Tạo Store Procedure chưa xử lý lỗi

CREATE PROCEDURE transferAmount(
    IN fromCustomerId int,
    IN toCustomerId int,
    IN amount int
)
BEGIN
    START TRANSACTION;
    -- thêm tiền cho người nhận
    UPDATE customer set balance = balance + amount where id = toCustomerId;
    -- trừ tiền người gửi
    UPDATE customer set balance = balance - amount where id = fromCustomerId;
    COMMIT;
END;

Bước 3: Tái hiện vấn đề với 5 connection đồng thời

Trước tiên, chuẩn bị môi trường:

-- Connection 1: Thiết lập và theo dõi

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- check xem biến này có đang bật hay không (theo mặc định sẽ không bật). Nếu bật thì mình tắt đi để thấy rõ quá trình tái hiện
SELECT @@innodb_rollback_on_timeout;

-- Đặt thời gian timeout ngắn để dễ quan sát
SET GLOBAL innodb_lock_wait_timeout = 3;

-- Theo dõi các transaction đang hoạt động
SELECT pl.`USER`, pl.COMMAND, tx.trx_id, tx.trx_query, tx.trx_state,
       tx.trx_tables_locked, tx.trx_rows_locked, tx.trx_started, pl.`TIME`
FROM performance_schema.processlist pl
RIGHT JOIN information_schema.INNODB_TRX tx ON pl.ID = tx.trx_mysql_thread_id;

Sau đó, thực hiện các giao dịch lần lượt:

Connection 2:

CALL transferAmount(1, 2, 200);
-- Sẽ lỗi ở câu UPDATE thứ 2 trong Procedure. Vì customer 1 chỉ có số dư là 100 mà chuyển 200 thì lúc này vi phạm constraint balance ≥ 0

Connection 3:

CALL transferAmount(2, 3, 50);
-- Sẽ gặp lock wait timeout. do ở connection 2 đang giữ lock customer 2 nên lúc này ta update thông tin customer 2 phải đợi nhả lock ở connection 2 

Connection 4:

CALL transferAmount(3, 4, 50);
-- Sẽ gặp lock wait timeout. do ở connection 3 đang giữ lock customer 3 nên lúc này ta update thông tin customer 3 phải đợi nhả lock ở connection 3

Connection 5:

CALL transferAmount(4, 5, 50);
-- Sẽ gặp lock wait timeout. do ở connection 4 đang giữ lock customer 4 nên lúc này ta update thông tin customer 4 phải đợi nhả lock ở connection 4

Khi chạy Connection 2, giao dịch sẽ lỗi vì vi phạm constraint (số dư không đủ). Transaction sẽ rollback statement trừ tiền, nhưng vẫn giữ lock trên customer id = 2. Các connection tiếp theo sẽ liên tiếp gặp lock timeout và tạo thêm các transaction "Sleep" đang giữ lock.

Bước 4: Giải pháp - Store Procedure với xử lý lỗi

CREATE PROCEDURE transferAmount(
    IN fromCustomerId int,
    IN toCustomerId int,
    IN amount int
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @error_message = MESSAGE_TEXT;
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_message;
    END;

    START TRANSACTION;
    -- thêm tiền cho người nhận
    UPDATE customer set balance = balance + amount where id = toCustomerId;
    -- trừ tiền người gửi
    UPDATE customer set balance = balance - amount where id = fromCustomerId;
    COMMIT;
END

Kết luận và Bài học

Bài học quan trọng:

• Luôn xử lý lỗi đúng cách trong Store Procedure với ROLLBACK rõ ràng
• Hiểu rõ cách engine database (như InnoDB) xử lý transaction và lock
• Theo dõi và phân tích cẩn thận các vấn đề performance trong môi trường thực tế

Ngoài cách xử lý lỗi trong Store Procedure, bạn cũng có thể cân nhắc việc kết hợp với  cấu hình innodb_rollback_on_timeout=ON đối với version MySQL mới

Store Procedure vẫn là công cụ mạnh mẽ để tối ưu hiệu năng database, nhưng cần được sử dụng đúng cách với sự hiểu biết đầy đủ về cách thức hoạt động của transaction.