Optimistic Locking và Pessimistic Locking trong MySQL: Xử lý triệt để Race Condition và Overbooking - ITFROMZERO - Share tobe shared! Phân tích tường tận cơ chế Optimistic và Pessimistic Locking trong MySQL. Hướng dẫn kèm code thực chiến giúp ngăn chặn triệt để Race Condition, Deadlock và lỗi âm kho hàng.

Table of Contents

1. Quick start: Thử nghiệm Race Condition và sửa lỗi trong 5 phút

Kho vé concert chỉ còn đúng 1 suất. Đúng lúc đó, hai khách hàng cùng bấm nút thanh toán trong cùng 1 phần nghìn giây. Cả hai thanh toán thành công. Đáng sợ hơn, database ghi nhận số lượng tồn kho tụt xuống -1. Đây là cơn ác mộng kinh điển của dân backend: Overbooking do Race Condition.

Hãy tạo nhanh bảng tickets và chèn 1 dòng mẫu để test trực tiếp trên MySQL terminal:

CREATE TABLE tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    available_seats INT NOT NULL,
    version INT DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO tickets (id, event_name, available_seats, version) 
VALUES (1, 'Rock Fest 2026', 1, 0);

Chuyện gì xảy ra nếu 2 request cùng đọc rồi trừ số ghế theo cách ngây thơ thường thấy?

-- Request A và Request B cùng đọc ra available_seats = 1
SELECT available_seats FROM tickets WHERE id = 1;

-- Sau đó cả hai đều trừ 1 và update lại
UPDATE tickets SET available_seats = available_seats - 1 WHERE id = 1;

Cả hai lệnh UPDATE đều trả về mã thành công. Dữ liệu lập tức bục toang. Bạn có thể dẹp tan lỗi này bằng hai cơ chế khóa phổ biến sau.

Cách 1: Khóa bi quan (Pessimistic Locking với SELECT … FOR UPDATE)

START TRANSACTION;
-- Giữ chặt dòng id = 1, request khác đến sau buộc phải xếp hàng chờ
SELECT available_seats FROM tickets WHERE id = 1 FOR UPDATE;

-- Code ứng dụng kiểm tra available_seats > 0 rồi mới trừ tiếp
UPDATE tickets SET available_seats = available_seats - 1 WHERE id = 1;
COMMIT;

Cách 2: Khóa lạc quan (Optimistic Locking dùng cột version)

-- Bước 1: Lấy dữ liệu kèm số version hiện tại (ví dụ version = 0)
SELECT available_seats, version FROM tickets WHERE id = 1;

-- Bước 2: Chỉ ghi đè khi version chưa bị ai đổi
UPDATE tickets 
SET available_seats = available_seats - 1, version = version + 1 
WHERE id = 1 AND version = 0;
-- Affected Rows = 1: Mua vé thành công!
-- Affected Rows = 0: Có người nhanh tay hơn, báo hết vé hoặc đẩy vào retry loop.

2. Bản chất bên dưới: Hai tư duy khóa đối lập

Chọn nhầm cơ chế khóa có thể bóp nghẹt throughput hoặc gây sập database khi traffic tăng đột biến.

Pessimistic Locking (Khóa bi quan)

Tư duy của kẻ cẩn trọng: “Cứ chạm vào dữ liệu là phải khóa ngay. Tôi không tin tưởng bất kỳ request nào khác.”

Khi bạn bật transaction và gõ SELECT ... FOR UPDATE trên InnoDB:

MySQL gắn ngay một Exclusive Lock (X-Lock) lên các dòng khớp điều kiện query.
Mọi transaction khác muốn đọc có FOR UPDATE hoặc muốn sửa dòng đó đều bị chặn cứng. Chúng phải nằm chờ cho tới khi bạn gọi COMMIT hoặc ROLLBACK.
Cơ chế này hoàn toàn phụ thuộc vào engine lưu trữ. Bạn bắt buộc phải dùng InnoDB vì MyISAM không hỗ trợ khóa ở cấp độ dòng (row-level lock).

Optimistic Locking (Khóa lạc quan)

Tư duy của kẻ phóng khoáng: “Cứ để mọi người đọc ghi thoải mái. Xung đột hiếm lắm, lúc nào chuẩn bị lưu thì kiểm tra một thể.”

Bản chất của Optimistic Locking là không dùng bất kỳ ổ khóa nào từ MySQL. Toàn bộ logic được điều khiển hoàn toàn bằng câu lệnh SQL điều kiện ở tầng ứng dụng:

Thêm một cột số nguyên version hoặc updated_at (độ chính xác microsecond) vào bảng.
Khi lưu dữ liệu, gắn thêm điều kiện kiểm tra phiên bản: WHERE id = :id AND version = :current_version.
Nếu kết quả affected rows trả về 0, nghĩa là đã có người khác can thiệp trước bạn. Backend sẽ quăng ra ngoại lệ OptimisticLockException hoặc kích hoạt vòng lặp thử lại.

3. Thực chiến: So sánh chỉ số, bẫy Deadlock và Atomic Update

Mỗi giải pháp sinh ra cho một bài toán riêng biệt.

Tiêu chí	Pessimistic Locking	Optimistic Locking
Bản chất xử lý	Dùng row lock thực tế của MySQL InnoDB	Pattern ứng dụng kết hợp kiểm tra cột version
Mức độ tranh chấp	Rất cao, nhiều request cùng tranh nhau 1 dòng dữ liệu	Thấp đến trung bình, đọc nhiều nhưng hiếm khi ghi đè cùng lúc
Throughput	Thấp hơn, các transaction phải xếp hàng đợi nhau	Cực cao khi đọc nhiều; tụt dốc nếu retry loop bị kích hoạt liên tục
Nguy cơ tiềm ẩn	Tắc nghẽn Connection pool, Deadlock diện rộng	Tốn tài nguyên CPU, phình to network do retry dồn dập

Bẫy Deadlock kinh điển khi dùng Pessimistic Locking

Dùng FOR UPDATE bừa bãi rất dễ dẫn tới kẹt xe dữ liệu. Xét ví dụ tính năng chuyển khoản giữa Ví A và Ví B:

Transaction 1 khóa Ví A thành công, đang chuẩn bị khóa tiếp Ví B.
Transaction 2 khóa Ví B thành công, đang nhăm nhe khóa Ví A.
Hai transaction đứng nhìn nhau chờ đợi. MySQL phát hiện deadlock và buộc phải khai tử một transaction.

Kinh nghiệm xương máu: Luôn sắp xếp thứ tự khóa tài nguyên đồng nhất. Trước khi query, hãy sort danh sách ID theo thứ tự tăng dần rồi mới chạy lệnh FOR UPDATE.

Vũ khí tối thượng: Atomic Update (Single Statement)

Với các bài toán trừ kho hay trừ số dư cơ bản, bạn không nhất thiết phải dùng đến cột version hay transaction dài ngoằng. Hãy để MySQL tự xử lý nguyên tử bằng một câu lệnh duy nhất:

UPDATE tickets 
SET available_seats = available_seats - 1 
WHERE id = 1 AND available_seats >= 1;

Cực kỳ ngắn gọn và hiệu quả. Bản thân lệnh UPDATE đơn lẻ này sẽ tự động giữ row lock trong vài microsecond rồi nhả ra ngay. Điều kiện available_seats >= 1 đảm bảo số lượng không bao giờ bị âm. Nhận về affected rows bằng 1 tức là mua thành công, 0 nghĩa là đã hết vé.

4. Những bài học đắt giá từ môi trường Production

Hệ thống hàng nghìn CCU sẽ bộc lộ ngay những góc khuất mà môi trường local không bao giờ thấy:

Tránh bẫy Table Lock: Cột nằm trong mệnh đề WHERE của SELECT ... FOR UPDATE bắt buộc phải có INDEX. Quên đánh index, InnoDB sẽ buộc phải quét toàn bộ bảng và khóa sạch toàn bộ các dòng kèm theo Gap Lock. Một query có thể làm sập nguồn cả database.
Bóp nghẹt timeout chờ: Đừng bao giờ để transaction đợi lock mòn mỏi. Tham số mặc định innodb_lock_wait_timeout của MySQL là 50 giây — quá đủ để làm tràn connection pool của ứng dụng trong giờ cao điểm. Hãy ép nó xuống còn 3 giây cho từng session:
```
SET innodb_lock_wait_timeout = 3;
```
Nếu dùng MySQL 8.0 trở lên, tận dụng ngay cú pháp SELECT ... FOR UPDATE NOWAIT hoặc SKIP LOCKED để ứng dụng fail-fast ngay lập tức thay vì đứng đợi.
Nguyên tắc chọn giải pháp theo bài toán:
- Dùng Pessimistic Lock (hoặc Redis Distributed Lock): Cho các đợt Flash Sale 11/11, đặt vé liveshow, hoặc chọn ghế rạp chiếu phim — nơi có 10.000 người cùng bấm mua đúng một vị trí trong 1 giây.
- Dùng Optimistic Lock: Cho các tác vụ back-office, trang admin CMS, hoặc chỉnh sửa thông tin người dùng — nơi 99% thời gian là đọc dữ liệu và hiếm khi có hai admin cùng sửa chung một bản ghi.