Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMSenior15 phút đọc

Lock trong InnoDB: từ global lock tới gap lock, đọc bằng data_locks

Ôn 9 thẻ
Trong bài này

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â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.

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, 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: 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 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.

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.