logo

Lộ trình

Khóa học

Tài liệu

Mock Interview

Liên hệ

Quay lại
  • Trang chủ

    /

  • Tài liệu

    /

  • Transaction trong Store Procedure: Vấn đề gì xảy ra khi quên ROLLBACK?
Tài liệu

Transaction trong Store Procedure: Vấn đề gì xảy ra khi quên ROLLBACK?

Ronin Engineer

6 Tháng 7 2025

<p>By <a href="https://www.linkedin.com/in/cndvn/?ref=roninhub.com" rel="noreferrer">Đức Hiếu</a></p><h2 id="store-procedure-v%C5%A9-kh%C3%AD-hai-l%C6%B0%E1%BB%A1i-trong-t%E1%BB%91i-%C6%B0u-h%C3%B3a-performance"><strong>Store Procedure: Vũ khí hai lưỡi trong tối ưu hóa performance</strong></h2><p>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.</p><h2 id="b%E1%BB%91i-c%E1%BA%A3nh-d%E1%BB%B1-%C3%A1n"><strong>Bối cảnh dự án</strong></h2><p>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ư:</p><ul><li>Giảm số lần giao tiếp giữa Backend với Database</li><li>Tăng hiệu xuất xử lý dữ liệu</li></ul><p>Vì vậy team quyết định tận dụng ưu điểm của Store Procedure trong quá trình migrate:</p><ul><li>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</li><li>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</li><li>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</li></ul><p>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.</p><h2 id="ph%C3%A1t-hi%E1%BB%87n-v%E1%BA%A5n-%C4%91%E1%BB%81"><strong>Phát hiện vấn đề</strong></h2><p>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, <strong>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</strong>.</p><p>Khi theo dõi hệ thống, chúng mình phát hiện một hiện tượng đáng chú ý:</p><ul><li>Có những transaction đã chạy trong hơn 100 giây</li><li>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</li></ul><p>Nhưng vẫn đang chạy và lock 2 record của một bảng quan trọng</p><figure class="kg-card kg-image-card"><img src="https://lh7-rt.googleusercontent.com/docsz/AD_4nXdPoitgzrPnSQjMd_phkDfZUzgA9UOwlo6P1qsjc--MFx_2ChRPgBed1yplC6PWpgXJmaR6yMA5Xfzv9naPoQKW5naRFj7m4P4kLoFyLXqMPkDznKnowQL7e-wN2XqVubywSEzYcg?key=CszsfI5irhZ0GknuCaRKUQ" class="kg-image" alt="" loading="lazy" width="1143" height="184"></figure><p>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.</p><h2 id="nguy%C3%AAn-nh%C3%A2n-g%E1%BB%91c-r%E1%BB%85"><strong>Nguyên nhân gốc rễ</strong></h2><p>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.</p><p>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.</p><p>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!</p><p>Suy ra nguyên nhân chính ở đây là do:</p><ul><li><strong>Transaction không nhả lock khi procedure gặp lỗi.</strong></li><li><strong>Logic xử lý của procedure chưa hiệu quả: do chưa&nbsp; có cơ chế try/catch và xử lý lỗi rõ ràng.</strong></li></ul><h3 id="nguy%C3%AAn-l%C3%BD-transaction-trong-mysqlmariadb"><strong>Nguyên lý transaction trong MySQL/MariaDB</strong></h3><ul><li><strong>Transaction chỉ thực sự kết thúc khi có lệnh COMMIT hoặc ROLLBACK rõ ràng </strong>(<a href="https://dev.mysql.com/doc/refman/8.4/en/commit.html?ref=roninhub.com"><u>1</u></a>)<strong>.</strong></li><li>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 (<a href="https://dev.mysql.com/doc/refman/8.4/en/innodb-error-handling.html?ref=roninhub.com"><u>2</u></a>)</li><li>Ngoại lệ chỉ xảy ra khi gặp deadlock hoặc khi cấu hình đặc biệt được bật (<a href="https://dev.mysql.com/doc/refman/8.4/en/innodb-error-handling.html?ref=roninhub.com"><u>2</u></a>)</li><li>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 (<a href="https://dev.mysql.com/doc/refman/8.4/en/innodb-error-handling.html?ref=roninhub.com"><u>2</u></a>)</li></ul><p>Bây giờ mình có thể giải thích chính xác vấn đề đã xảy ra:</p><p>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 😂"</p><h2 id="t%C3%A1i-hi%E1%BB%87n-v%C3%A0-x%C3%A1c-minh-v%E1%BA%A5n-%C4%91%E1%BB%81"><strong>Tái hiện và xác minh vấn đề</strong></h2><p>Để 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).</p><h3 id="b%C6%B0%E1%BB%9Bc-1-thi%E1%BA%BFt-l%E1%BA%ADp-c%C6%A1-s%E1%BB%9F-d%E1%BB%AF-li%E1%BB%87u-m%E1%BA%ABu"><strong>Bước 1: Thiết lập cơ sở dữ liệu mẫu</strong></h3><pre><code class="language-SQL">CREATE TABLE customer ( &nbsp;&nbsp;&nbsp;&nbsp;id INT auto_increment NOT NULL, &nbsp;&nbsp;&nbsp;&nbsp;name varchar(100) NULL, &nbsp;&nbsp;&nbsp;&nbsp;balance INT, &nbsp;&nbsp;&nbsp;&nbsp;CONSTRAINT customer_PK PRIMARY KEY (id), &nbsp;&nbsp;&nbsp;&nbsp;CONSTRAINT customer_CHECK CHECK (balance &gt;= 0) ); -- Tạo dữ liệu mẫu INSERT INTO customer(id, name, balance) &nbsp;&nbsp;&nbsp;&nbsp;VALUES(1, "nhan", 100), (2, "hoang", 100), (3, "nghia", 100), (4, "trang", 100), (5, "nam", 100);</code></pre><h3 id="b%C6%B0%E1%BB%9Bc-2-t%E1%BA%A1o-store-procedure-ch%C6%B0a-x%E1%BB%AD-l%C3%BD-l%E1%BB%97i"><strong>Bước 2: Tạo Store Procedure chưa xử lý lỗi</strong></h3><pre><code class="language-SQL">CREATE PROCEDURE transferAmount( &nbsp;&nbsp;&nbsp;&nbsp;IN fromCustomerId int, &nbsp;&nbsp;&nbsp;&nbsp;IN toCustomerId int, &nbsp;&nbsp;&nbsp;&nbsp;IN amount int ) BEGIN &nbsp;&nbsp;&nbsp;&nbsp;START TRANSACTION; &nbsp;&nbsp;&nbsp;&nbsp;-- thêm tiền cho người nhận &nbsp;&nbsp;&nbsp;&nbsp;UPDATE customer set balance = balance + amount where id = toCustomerId; &nbsp;&nbsp;&nbsp;&nbsp;-- trừ tiền người gửi &nbsp;&nbsp;&nbsp;&nbsp;UPDATE customer set balance = balance - amount where id = fromCustomerId; &nbsp;&nbsp;&nbsp;&nbsp;COMMIT; END;</code></pre><h3 id="b%C6%B0%E1%BB%9Bc-3-t%C3%A1i-hi%E1%BB%87n-v%E1%BA%A5n-%C4%91%E1%BB%81-v%E1%BB%9Bi-5-connection-%C4%91%E1%BB%93ng-th%E1%BB%9Di"><strong>Bước 3: Tái hiện vấn đề với 5 connection đồng thời</strong></h3><p>Trước tiên, chuẩn bị môi trường:</p><pre><code class="language-SQL">-- 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, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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;</code></pre><p>Sau đó, thực hiện các giao dịch lần lượt:</p><p><strong>Connection 2:</strong></p><pre><code class="language-SQL">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</code></pre><p><strong>Connection 3:</strong></p><pre><code class="language-SQL">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&nbsp;</code></pre><p><strong>Connection 4:</strong></p><pre><code class="language-SQL">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</code></pre><p><strong>Connection 5:</strong></p><pre><code class="language-SQL">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</code></pre><p>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.</p><h3 id="b%C6%B0%E1%BB%9Bc-4-gi%E1%BA%A3i-ph%C3%A1pstore-procedure-v%E1%BB%9Bi-x%E1%BB%AD-l%C3%BD-l%E1%BB%97i"><strong>Bước 4: Giải pháp - Store Procedure với xử lý lỗi</strong></h3><pre><code class="language-SQL">CREATE PROCEDURE transferAmount( &nbsp;&nbsp;&nbsp;&nbsp;IN fromCustomerId int, &nbsp;&nbsp;&nbsp;&nbsp;IN toCustomerId int, &nbsp;&nbsp;&nbsp;&nbsp;IN amount int ) BEGIN &nbsp;&nbsp;&nbsp;&nbsp;DECLARE EXIT HANDLER FOR SQLEXCEPTION &nbsp;&nbsp;&nbsp;&nbsp;BEGIN &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GET DIAGNOSTICS CONDITION 1 @error_message = MESSAGE_TEXT; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROLLBACK; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_message; &nbsp;&nbsp;&nbsp;&nbsp;END; &nbsp;&nbsp;&nbsp;&nbsp;START TRANSACTION; &nbsp;&nbsp;&nbsp;&nbsp;-- thêm tiền cho người nhận &nbsp;&nbsp;&nbsp;&nbsp;UPDATE customer set balance = balance + amount where id = toCustomerId; &nbsp;&nbsp;&nbsp;&nbsp;-- trừ tiền người gửi &nbsp;&nbsp;&nbsp;&nbsp;UPDATE customer set balance = balance - amount where id = fromCustomerId; &nbsp;&nbsp;&nbsp;&nbsp;COMMIT; END</code></pre><h2 id="k%E1%BA%BFt-lu%E1%BA%ADn-v%C3%A0-b%C3%A0i-h%E1%BB%8Dc"><strong>Kết luận và Bài học</strong></h2><p><strong>Bài học quan trọng:</strong></p><ul><li>Luôn xử lý lỗi đúng cách trong Store Procedure với ROLLBACK rõ ràng</li><li>Hiểu rõ cách engine database (như InnoDB) xử lý transaction và lock</li><li>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ế</li></ul><p>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&nbsp; cấu hình innodb_rollback_on_timeout=ON đối với version MySQL mới</p><p>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.</p>

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.

database
middle
duchieu

Bài viết liên quan

ACID - A Deep Dive into Transactions

By @wuan580 Trong thế giới khắc nghiệt của các hệ thống dữ liệu, mọi thứ đều có thể xảy ra: * Phần mềm hoặc phần cứng cơ sở dữ liệu có thể bị lỗi bất cứ lúc nào (kể cả giữa thao tác ghi) * Ứng dụng có thể sập giữa chừng, khi một loạt thao tác vẫn chưa hoàn tất. * Sự cố mạng có thể bất ngờ cắt đứt ứng dụng khỏi cơ sở dữ liệu hoặc một nút cơ sở dữ liệu này khỏi nút khác. * Nhiều client có thể ghi đồng thời, vô tình ghi đè lên thay đổi của nhau. * Client có thể đọc dữ liệu vô nghĩa vì nó chỉ

Áp dụng Connection Multiplexing trong ProxySQL để tối ưu kết nối Database

By Đức Hiếu 1. Tổng quan Connection Multiplexing là một trong những tính năng nổi bật nhất của ProxySQL, giúp giải quyết vấn đề bottleneck về kết nối với hệ thống Database. Khác với connection pooling truyền thống, multiplexing cho phép nhiều frontend connections chia sẻ các backend connections thông qua tỷ lệ N:M thay vì 1:1, từ đó giảm đáng kể áp lực lên Database layer. 2. Vấn đề với mô hình Thread-per-Connection của MySQL Trong mô hình truyền thống, MySQL sử dụng thread-per-connection m

MySQL Thực Thi Lệnh SELECT Như Thế Nào?

select * from Member where CardNo = 1; Nhưng đã bao giờ bạn tự hỏi, điều gì đã diễn ra trong quá trình MySQL thực thi một câu lệnh truy vấn select chưa?

Sự khác biệt giữa Count(*) và Count(1)? Cái nào hiệu quả hơn?

Khi chúng ta đếm các bản ghi trong bảng dữ liệu, chúng ta đã quen với việc sử dụng hàm count để đếm, nhưng có nhiều loại tham số có thể được truyền trong hàm count, chẳng hạn như count(1), count(), count(column), … Vậy sử dụng cái nào là hiệu quả nhất? ngoài ra, có những cách count nào khác?

Java Virtual Thread: Cuộc cách mạng cho lập trình đồng thời

By @wuan.580 Bạn đã bao giờ viết một ứng dụng xử lý hàng ngàn request cùng lúc, và cảm thấy như mình đang chiến đấu với chính Java? Bạn từng dùng ThreadPoolExecutor và vắt óc cân chỉnh số lượng thread cho "vừa đủ dùng", tránh thiếu nhưng cũng không dám dư vì sợ OutOfMemoryError? Bạn từng nhăn mặt khi phải viết những dòng code callback chằng chịt, chỉ để tránh block một luồng? Và rồi đau đầu gỡ bug vì stacktrace rối như tơ vò? Nếu câu trả lời là "có", thì bạn không đơn độc. Và bạn cũng sắp có

Tất cả bài viết
logo

HỘ KINH DOANH LẬP VƯƠNG

Giấy chứng nhận đăng ký doanh nghiệp số: 8656162915-001. Cấp ngày 21/02/2024. Nơi cấp: Sở Kế hoạch và Đầu tư TP. Hà Nội

PHƯƠNG THỨC THANH TOÁN

vnpay

LIÊN HỆ

roninengineer88@gmail.com

0362228388

26 ngõ 156 Hồng Mai, Hai Bà Trưng, Hà Nội

THEO DÕI CHÚNG TÔI

Facebook

Youtube

Tiktok

CHÍNH SÁCH

Chính sách bảo mật

Chính sách thanh toán

Đổi trả/Hoàn tiền

Hướng dẫn thanh toán VNPAY

PHƯƠNG THỨC THANH TOÁN

vnpay

Ronin Engineer 2024