Cụm bài Transaction và locking chuyên sâu: 1. Transaction từ bên trong · 2. Transaction gây hại · 3. Lock trong InnoDB · 4. Optimistic, pessimistic và bài đặt vé
Khi hai request cùng sửa một thứ, kết quả đúng hay sai do thứ tự quyết định, và đó là việc của lock. Đọc xong, bạn biết các loại lock của InnoDB, đọc được performance_schema.data_locks để thấy mỗi câu khoá gì, và hiểu vì sao một dòng nóng giới hạn throughput. Nên đọc trước transaction, isolation level và locking (S/X lock, gap lock, deadlock cơ bản) và phần 1 (consistent read và current read).
1. Bài toán: chuyển tiền làm số dư âm
Ý chính: hai request cùng đọc số dư 50, cùng thấy đủ tiền, cùng trừ, và số dư thành -20. Lỗi nằm ở chỗ quyết định dựa trên một giá trị đã cũ.
Ví dụ: A chuyển B 30 và chuyển C 40 cùng lúc; số dư không được âm.
sequenceDiagram
participant R1 as Request A→B 30
participant DB as account A
participant R2 as Request A→C 40
R1->>DB: SELECT amount (50)
R2->>DB: SELECT amount (50)
Note over R1: 50 >= 30, cho qua
Note over R2: 50 >= 40, cho qua
R1->>DB: UPDATE amount = amount - 30 (20)
R2->>DB: UPDATE amount = amount - 40 (-20)
Đây là race condition (kết quả phụ thuộc thứ tự chạy chen nhau) kiểu check-then-act, không phải lost update vì amount = amount - x không ghi đè giá trị của nhau. Bọc trong transaction không cứu được: ở REPEATABLE READ, SELECT thường đọc snapshot, không khoá gì.
Ba cách sửa, từ rẻ tới đắt:
-- 1. Gộp kiểm tra vào câu ghi: một câu, nguyên tử
UPDATE account SET amount = amount - 30 WHERE id = 'A' AND amount >= 30;
-- affected rows = 0 nghĩa là không đủ tiền
-- 2. Pessimistic: khoá dòng ngay khi đọc (exclusive lock)
START TRANSACTION;
SELECT amount FROM account WHERE id = 'A' FOR UPDATE; -- request kia chờ ở đây
UPDATE account SET amount = amount - 30 WHERE id = 'A';
COMMIT;
-- 3. Optimistic: không khoá khi đọc, phát hiện xung đột khi ghi
UPDATE account SET amount = 20, version = version + 1 WHERE id = 'A' AND version = 7;
Slide hỏi “dùng loại lock nào?”: exclusive lock (FOR UPDATE), cụ thể là record lock trên một bản ghi vì điều kiện là khoá chính (mục 3 chứng minh).
2. Các loại lock trong MySQL
Ý chính: lock chia theo thao tác (S, X, intention) và theo phạm vi (instance, bảng, AUTO-INC, dòng). Với ứng dụng, row lock là phần quan trọng nhất.
2.1. Theo thao tác: shared, exclusive và intention
Ý chính: S cho nhiều người cùng đọc có khoá, X chỉ cho một người và chặn cả S lẫn X. Intention lock (IS/IX) đặt trên bảng để báo “tôi sắp khoá vài dòng”.
- Shared (S): khoá đọc, nhiều transaction cùng giữ được. Lấy bằng
SELECT ... FOR SHARE. - Exclusive (X): khoá sửa, chỉ một transaction giữ. Lấy bằng
SELECT ... FOR UPDATE,UPDATE,DELETE.
X chỉ chặn đọc có khoá; SELECT thường vẫn đọc được nhờ MVCC (database giữ bản cũ để người đọc không chờ người ghi).
Intention lock giúp lệnh khoá cả bảng (LOCK TABLES ... WRITE) biết có xung đột mà không phải quét từng dòng. Giống tấm biển “phòng có người” ở cửa toà nhà: muốn khoá cả toà thì nhìn biển, không phải gõ từng phòng.
Bảng tương thích của intention lock
| Đang giữ \ Xin | IS | IX | S (bảng) | X (bảng) |
|---|---|---|---|---|
| IS | được | được | được | chặn |
| IX | được | được | chặn | chặn |
| S (bảng) | được | chặn | được | chặn |
| X (bảng) | chặn | chặn | chặn | chặn |
Hai intention lock không bao giờ chặn nhau: nghìn transaction cùng IX trên một bảng vẫn chạy song song, xung đột thật diễn ra ở mức dòng.
2.2. Theo phạm vi: từ instance tới bản ghi
Ý chính: phạm vi đi từ cả instance (backup) tới một bản ghi index. Bốn loại row lock khác nhau ở chỗ có khoá khoảng trống giữa các bản ghi hay không.
| Mức | Ví dụ | Khi nào gặp |
|---|---|---|
| Global | FLUSH TABLES WITH READ LOCK |
Công cụ backup cần một điểm nhất quán; chặn mọi thao tác ghi trên cả instance |
| Bảng | LOCK TABLES t READ/WRITE, metadata lock (MDL), intention lock |
MDL: ALTER TABLE chờ mọi transaction đang dùng bảng |
| AUTO-INC | Khoá đặc biệt khi cấp giá trị AUTO_INCREMENT |
Insert hàng loạt |
| Dòng | Record, gap, next-key, insert intention | Mọi UPDATE, DELETE, đọc có khoá, INSERT |
Row lock:
| Lock | Tên trong data_locks |
Khoá khoảng trống? | Ý nghĩa |
|---|---|---|---|
| Record lock | X,REC_NOT_GAP |
Không | Khoá đúng một bản ghi index |
| Gap lock | X,GAP |
Có | Khoá khoảng trống trước một bản ghi, chặn insert vào đó |
| Next-key lock | X |
Có | Bản ghi + khoảng trống trước nó |
| Insert intention | X,GAP,INSERT_INTENTION |
(đợi gap) | Xin chèn vào một khoảng; chặn bởi gap/next-key của người khác |
AUTO-INC lock mode và khoá global khi backup
AUTO-INC lock đã được cải tiến nhiều (slide có ghi chú này). Tham số innodb_autoinc_lock_mode:
0(traditional): khoá mức bảng tới hết câuINSERT, các insert đồng thời xếp hàng.1(consecutive): insert đơn giản chỉ giữ mutex rất ngắn; insert hàng loạt không biết trước số dòng (INSERT ... SELECT) vẫn khoá bảng.2(interleaved): mặc định từ MySQL 8.0 (mình kiểm tra trên 8.4: giá trị 2). Không khoá bảng; id của các insert hàng loạt chạy song song có thể xen kẽ nhau. An toàn vì binlog mặc định làROW.
Ở mọi mode, id đã cấp cho một insert bị rollback không được dùng lại, nên dãy id có lỗ là bình thường.
3. Đọc performance_schema.data_locks
Ý chính: đừng đoán, hãy xem: mở transaction, chạy một câu, đọc data_locks, rollback. Khoá chính so sánh bằng chỉ khoá một bản ghi; khoảng, index phụ và cột không index ở RR khoá thêm khoảng trống.
SELECT index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'account_balance';
Bảng thử: account_balance(account_number PK, owner_id, balance, note), index phụ idx_owner(owner_id), 4 dòng với account_number = 1, 2, 5, 9 (owner_id lần lượt 10, 20, 20, 30). Cột note không có index. Chạy trên MySQL 8.4.11 trong Docker.
| Câu lệnh | REPEATABLE READ | READ COMMITTED |
|---|---|---|
UPDATE ... WHERE account_number = 2 |
X,REC_NOT_GAP trên 2 |
như RR |
UPDATE ... WHERE account_number BETWEEN 2 AND 5 |
X,REC_NOT_GAP trên 2, X (next-key) trên 5 |
X,REC_NOT_GAP trên 2 và 5 |
UPDATE ... WHERE owner_id = 20 (index phụ) |
idx_owner: X trên (20,2), (20,5), X,GAP trên (30,9); PRIMARY: REC_NOT_GAP trên 2, 5 |
REC_NOT_GAP trên (20,2), (20,5) và PK 2, 5 |
UPDATE ... WHERE note = 'c' (không index) |
X trên cả 4 dòng + supremum pseudo-record |
chỉ REC_NOT_GAP trên 5 |
SELECT ... WHERE account_number = 4 FOR UPDATE (không tồn tại) |
X,GAP trên 5 |
không khoá dòng nào |
Mọi trường hợp có thêm một dòng TABLE ... IX (intention lock).
- Khoá chính, so sánh bằng: một record lock ở cả hai level. Rẻ nhất, và là đáp án “record lock” cho bài chuyển tiền.
- Khoảng trên khoá chính, RR: next-key trên 5 khoá cả khoảng (2, 5). Thử:
INSERT ... VALUES (4, ...)ở session khác chờ (X,GAP,INSERT_INTENTION,WAITINGtrên bản ghi 5), cònVALUES (7, ...)chạy ngay. - Index phụ không unique, RR: hai bộ khoá (index phụ và khoá chính), cộng gap lock chặn insert
owner_idtừ 20 tới 30. - Không có index, RR: khoá mọi dòng đã quét, tức cả bảng, kể cả “supremum” (bản ghi ảo đánh dấu khoảng sau dòng cuối). RC nhả ngay các dòng không khớp
WHERE.
“Vì sao nó khoá cả những dòng thừa?” Để giữ REPEATABLE READ: chạy lại một câu đọc có khoá trong cùng transaction không được thấy dòng mới (phantom). Nên InnoDB khoá khoảng trên index đã quét, không chỉ dòng khớp; index càng kém chọn lọc, khoảng càng rộng. RC không hứa điều này nên khoá ít hơn hẳn. (Slide trích Nichter khuyên đừng đào sâu vì “InnoDB là hộp đen”.)
Sơ đồ cũ về khoá khoảng và cách xem ai chặn ai
Khi có chờ đợi thật, view sys.innodb_lock_waits cho biết ai chặn ai, kèm câu KILL gợi ý:
waiting_query: INSERT INTO account_balance VALUES (4,40,0,'x')
blocking_pid: 21
blocking_query: DO SLEEP(6)
wait_age: 00:00:01
sql_kill_blocking_connection: KILL 21
blocking_query là câu đang chạy của connection chặn, không phải câu đã lấy lock (ở đây lock do câu UPDATE trước đó). Muốn biết câu gây ra lock thì xem performance_schema.events_statements_history của thread đó.
4. Lock contention: thấp hay cao
Ý chính: contention là mức độ các request giành cùng một tài nguyên. Contention cao giới hạn throughput theo thời gian giữ lock, và thêm máy chủ không giúp.
Cùng 1.000 request mỗi giây:
- Thấp: rải trên 1 triệu tài khoản, hai request chạm cùng một dòng là hiếm.
- Cao: dồn vào một dòng, như bộ đếm ghế còn lại của một chuyến bay đang mở bán.
Giữ khoá dòng nóng 5 ms thì dòng đó phục vụ tối đa ~200 transaction mỗi giây. Theo dõi bằng SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%' (Innodb_row_lock_waits, Innodb_row_lock_time_avg) và sys.innodb_lock_waits.
Mức contention quyết định chọn pessimistic hay optimistic. Phần 4 so sánh pessimistic, optimistic, conditional update và Redis lock bằng số đo trên bài đặt vé.
Bài tập
Bài 1: Lock nào giải bài chuyển tiền
Đề (diễn đạt lại): với bài chuyển tiền ở mục 1, (a) cần loại lock nào theo thao tác, (b) loại row lock nào.
Lời giải Bài 1
(a) Exclusive lock, vì request sau phải chờ cả việc đọc-để-quyết-định lẫn việc ghi. FOR SHARE không đủ: hai bên cùng giữ S rồi cùng xin X sẽ deadlock.
(b) Record lock: điều kiện là khoá chính so sánh bằng, nên data_locks chỉ có X,REC_NOT_GAP trên đúng tài khoản A (dòng đầu của bảng ở mục 3). Tốt hơn nữa là không cần đọc trước: UPDATE account SET amount = amount - 30 WHERE id = 'A' AND amount >= 30 lấy đúng record lock đó trong một câu.
Bài 2: Dự đoán rồi kiểm chứng lock
Đề (tự đặt thêm): trước khi đưa một câu UPDATE vào luồng nóng, dự đoán nó khoá gì rồi kiểm chứng.
Lời giải Bài 2: script đọc data_locks
Quy trình mình dùng cho bảng ở mục 3: tạo bảng nhỏ có đủ các loại index như production, mở transaction ở đúng isolation level của ứng dụng, chạy câu lệnh, đọc data_locks, rollback. Viết thành script nhận câu lệnh làm tham số để chạy trong CI khi thay đổi truy vấn nóng:
run() { # $1 = isolation level, $2 = câu lệnh
mysql -t lab <<SQL
SET SESSION TRANSACTION ISOLATION LEVEL $1;
START TRANSACTION;
$2;
SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'account_balance' ORDER BY index_name, lock_data;
ROLLBACK;
SQL
}
run "REPEATABLE READ" "UPDATE account_balance SET balance = balance + 10 WHERE owner_id = 20"
Kết quả bất ngờ thường gặp nhất: câu lọc bằng index phụ không unique khoá thêm một gap trên bản ghi kế tiếp, và câu thiếu index khoá cả bảng.
Ghi nhớ
- Giải thích được vì sao đọc-kiểm tra-ghi trong transaction RR vẫn làm số dư âm, và ba cách sửa.
- Biết vì sao
FOR SHAREgây deadlock trong bài chuyển tiền cònFOR UPDATEthì không. - Phân biệt được record, gap, next-key, insert intention lock và tên của chúng trong
data_locks. - Dự đoán được một câu
UPDATEkhoá gì ở RR và RC, rồi kiểm chứng. - Tính được trần throughput của một dòng nóng từ thời gian giữ lock.
Câu hỏi phỏng vấn
Junior
Vì sao UPDATE theo khoá chính là kiểu khoá rẻ nhất trong InnoDB?
Vì nó chỉ đặt một record lock (X,REC_NOT_GAP) trên đúng một bản ghi, ở cả REPEATABLE READ lẫn READ COMMITTED.
So sánh bằng trên khoá chính tìm đúng một bản ghi trong clustered index. Không có gap lock, không khoá thêm bản ghi trên index phụ, nên không chặn insert hay update của dòng khác.
Mid
Intention lock dùng để làm gì?
Để một yêu cầu khoá cả bảng biết ngay có xung đột hay không, mà không phải quét từng dòng.
Đó là lock mức bảng (IS/IX) đặt trước khi khoá dòng, báo “transaction này đang giữ lock S/X ở vài dòng”. Intention lock không chặn nhau, nên không làm giảm song song ở mức dòng.
Câu UPDATE trên cột không có index ở REPEATABLE READ khoá những gì?
Gần như cả bảng: next-key lock trên mọi bản ghi đã quét cộng supremum.
Nó phải quét clustered index, nên chặn mọi update và insert khác. Ở READ COMMITTED, InnoDB nhả ngay lock của các dòng không khớp WHERE. Cách sửa đúng là thêm index cho điều kiện.
Senior
Đọc data_locks thấy X,GAP và X,GAP,INSERT_INTENTION WAITING trên cùng một bản ghi. Chuyện gì đang xảy ra?
Một transaction giữ gap lock trên khoảng trước bản ghi đó, transaction kia muốn INSERT vào khoảng ấy nên đang chờ.
Gap (hoặc next-key) lock thường do đọc có khoá hoặc UPDATE theo khoảng ở RR, hoặc SELECT ... FOR UPDATE trên một giá trị không tồn tại. Nếu cả hai cùng giữ gap rồi cùng insert thì thành deadlock. Hướng xử lý: dùng unique constraint thay cho check-then-insert, khoá theo khoá chính, hoặc cân nhắc READ COMMITTED.
Một sơ đồ cho thấy câu UPDATE theo khoảng khoá cả bản ghi sau khoảng. Bạn kiểm chứng thế nào?
Chạy câu đó trong một transaction trên đúng phiên bản MySQL đang dùng, rồi đọc performance_schema.data_locks.
Xem index_name, lock_mode, lock_data. Trên MySQL 8.4, BETWEEN 2 AND 5 trên khoá chính chỉ khoá record 2 và next-key 5, không khoá bản ghi kế tiếp; các bản cũ thì có. Hành vi khoá thay đổi giữa các phiên bản, nên luôn đo thay vì tin sơ đồ.
Từ vựng tiếng Anh
| Term | Phát âm | Nghĩa | Ví dụ câu tiếng Anh dùng trong phỏng vấn |
|---|---|---|---|
| shared lock | /ʃerd lɑːk/ | khoá đọc | “Several transactions can hold a shared lock on the same row.” |
| exclusive lock | /ɪkˈskluː.sɪv lɑːk/ | khoá ghi | “SELECT FOR UPDATE takes an exclusive lock until commit.” |
| intention lock | /ɪnˈten.ʃən lɑːk/ | khoá ý định mức bảng | “Intention locks let a table lock detect conflicts without scanning rows.” |
| record lock | /ˈrek.ɚd lɑːk/ | khoá một bản ghi | “An equality match on the primary key takes a single record lock.” |
| gap lock | /ɡæp lɑːk/ | khoá khoảng trống | “The gap lock blocked inserts between 2 and 5.” |
| next-key lock | /nekst kiː lɑːk/ | khoá bản ghi + khoảng trước | “Repeatable read uses next-key locks to prevent phantoms.” |
| insert intention lock | /ɪnˈsɝːt ɪnˈten.ʃən lɑːk/ | khoá xin chèn vào khoảng | “The insert was waiting on an insert intention lock.” |
| lock contention | /lɑːk kənˈten.ʃən/ | tranh chấp khoá | “High lock contention on one row caps throughput no matter how many servers we add.” |
| supremum | /sʊˈpriː.məm/ | bản ghi ảo sau bản ghi cuối | “Without an index, the update locked every row and the supremum.” |
Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.