Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.
Lock trong InnoDB: từ global lock tới gap lock, đọc bằng data_locks
1Chuyển tiền làm số dư âm
Hai request cùng đọc số dư 50, cùng thấy đủ tiền, cùng trừ: số dư thành -20. Đây là race check-then-act; bọc trong transaction RR vẫn sai vì SELECT thường không khoá.
sequenceDiagram
participant R1 as A→B 30
participant DB as account A
participant R2 as A→C 40
R1->>DB: SELECT amount (50)
R2->>DB: SELECT amount (50)
R1->>DB: UPDATE amount - 30 (20)
R2->>DB: UPDATE amount - 40 (-20)
2Ba cách sửa
Từ rẻ tới đắt. Khi đọc để quyết định ghi, dùng FOR UPDATE (khoá X ngay từ đầu, người sau chờ), không phải FOR SHARE.
-- 1. Gộp điều kiện vào câu ghi
UPDATE account SET amount = amount - 30
WHERE id = 'A' AND amount >= 30;
-- 2. Pessimistic: khoá dòng khi đọc
SELECT amount FROM account
WHERE id = 'A' FOR UPDATE;
-- 3. Optimistic: kiểm version khi ghi
UPDATE account SET amount = 20, version = 8
WHERE id = 'A' AND version = 7;
3Các loại row lock
Shared (S) cho nhiều người cùng đọc có khoá; Exclusive (X) chỉ cho một người. Bốn loại row lock khác nhau ở chỗ có khoá khoảng trống giữa các bản ghi không.
Lock
Tên trong data_locks
Khoá gì
Record
X,REC_NOT_GAP
Đúng một bản ghi index
Gap
X,GAP
Khoảng trước bản ghi, chặn insert
Next-key
X
Bản ghi + khoảng trước nó
Insert intention
X,GAP,INSERT_INTENTION
Xin chèn, chờ gap của người khác
4Một câu UPDATE khoá gì
Đừng đoán: mở transaction, chạy câu lệnh, đọc performance_schema.data_locks, rollback. Ở RR, InnoDB khoá khoảng index đã quét để chống phantom.
PK so sánh bằng: một record lock, cả RR lẫn RC
Khoảng trên PK, RR: next-key, chặn insert vào khoảng
Index phụ, RR: khoá cả index phụ, PK và gap
Cột không index, RR: khoá cả bảng; RC chỉ dòng khớp
5Dòng nóng
Contention là mức các request giành cùng một tài nguyên. Giữ khoá một dòng nóng 5 ms thì tối đa ~200 transaction/giây, thêm máy chủ không giúp.
Kịch bản
Contention
1.000 req/s rải trên 1 triệu tài khoản
Thấp, hiếm khi chờ
1.000 req/s vào bộ đếm ghế một chuyến
Cao, xếp hàng chờ khoá
💡 Ghi nhớ
SELECT thường đọc qua MVCC, không bị khoá X chặn.
Intention lock (IS/IX) báo bảng có dòng sắp bị khoá.
RC khoá ít hơn hẳn RR.
Theo dõi Innodb_row_lock_waits, Innodb_row_lock_time_avg.
⭐ Điểm phỏng vấn
Tự trả lời trước, rồi bấm từng câu để xem đáp án.
Chuyển tiền dùng lock gì?
Exclusive record lock, FOR UPDATE theo khoá chính.
Vì sao FOR SHARE deadlock?
Hai bên cùng giữ S, cùng xin X.
Gap lock để làm gì?
Chống phantom ở REPEATABLE READ.
Xong phần tự kiểm tra, bài này đã được đánh dấu đã học ✓
⚠️ Lỗi thường gặp
UPDATE trên cột không index ở RR, khoá cả bảng.
Tin transaction RR tự chống check-then-act.
Đoán lock thay vì đọc data_locks trên đúng phiên bản.
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ờ ở đâyUPDATE account SET amount = amount - 30 WHERE id = 'A';COMMIT;-- 3. Optimistic: không khoá khi đọc, phát hiện xung đột khi ghiUPDATE 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âu INSERT, 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.
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, WAITING trên bản ghi 5), còn VALUES (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_id từ 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: 21blocking_query: DO SLEEP(6)wait_age: 00:00:01sql_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 <<SQLSET SESSION TRANSACTION ISOLATION LEVEL $1;START TRANSACTION;$2;SELECT index_name, lock_type, lock_mode, lock_dataFROM performance_schema.data_locksWHERE 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 SHARE gây deadlock trong bài chuyển tiền còn FOR UPDATE thì 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 UPDATE khoá 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
Shared lock và exclusive lock khác nhau thế nào?
S là khoá đọc, nhiều transaction giữ cùng lúc được; X là khoá ghi, chỉ một transaction giữ và chặn cả S lẫn X.
S chặn người muốn ghi. FOR SHARE lấy S; FOR UPDATE, UPDATE, DELETE lấy X. SELECT thường không lấy lock nào nhờ MVCC.
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.
SELECT thường trong InnoDB có đặt shared lock không?
Không. Ở READ UNCOMMITTED, READ COMMITTED và REPEATABLE READ, SELECT thường là consistent read: đọc snapshot từ undo log, không xin khoá. Chỉ SELECT ... FOR SHARE mới đặt S lock.
Vì vậy người đọc không chặn người ghi và ngược lại. Ngoại lệ là SERIALIZABLE: khi autocommit tắt, InnoDB tự đổi SELECT thường thành FOR SHARE. Mô tả “SELECT đặt shared lock, đọc xong thì nhả” là của hệ thống khoá thuần (ví dụ SQL Server ở READ COMMITTED khi chưa bật snapshot), không phải InnoDB hay PostgreSQL.
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.
Khi nào InnoDB khoá cả bảng? Nó có lock escalation như SQL Server không?
InnoDB không bao giờ nâng nhiều row lock thành table lock. Nó khoá cả bảng khi bạn yêu cầu (LOCK TABLES), khi DDL cần metadata lock, và “gần như cả bảng” khi câu ghi phải quét mọi dòng vì không có index phù hợp.
Lock escalation (gộp hàng nghìn row lock thành một table lock để đỡ bộ nhớ) có ở SQL Server, khoảng 5.000 lock trong một câu. InnoDB lưu row lock theo bitmap trên từng page nên rẻ, không cần gộp.
UPDATE ... WHERE name LIKE '%a%' không có index dùng được: InnoDB khoá mọi bản ghi nó quét (cộng gap ở RR). Vẫn là row lock, nhưng hiệu quả như khoá cả bảng.
Metadata lock: ALTER TABLE chờ mọi transaction đang dùng bảng, và mọi câu đến sau xếp hàng sau nó.
innodb_autoinc_lock_mode 0 hoặc 1: INSERT ... SELECT giữ AUTO-INC lock mức bảng tới hết câu.
Cách phòng: index cho cột trong WHERE của câu ghi, chia batch nhỏ (LIMIT theo PK), chạy DDL với lock_wait_timeout ngắ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.