Gap lock và next-key lock của InnoDB là gì? Vì sao `UPDATE` trên cột không index có thể khoá gần hết bảng?

InnoDB ở mức REPEATABLE READ không chỉ khoá hàng tồn tại mà còn khoá khoảng trống giữa các giá trị index để chặn phantom read.

• Record lock — khoá một bản ghi index cụ thể.
• Gap lock — khoá khoảng trống giữa hai giá trị index, ngăn transaction khác chèn giá trị mới vào khoảng đó.
• Next-key lock — kết hợp cả hai: bản ghi cộng khoảng trống liền trước nó. Đây là mức khoá mặc định khi quét index ở REPEATABLE READ.

-- id có index; câu này khoá cả khoảng (10, 20]
SELECT * FROM orders WHERE id > 10 AND id <= 20 FOR UPDATE;
-- transaction khác INSERT id = 15 sẽ phải chờ, dù hàng đó chưa hề tồn tại

Vì sao cột không index gây khoá diện rộng: InnoDB chỉ khoá được qua index. Nếu WHERE không có index phù hợp, engine phải quét toàn bộ clustered index và đặt next-key lock trên mọi hàng nó đi qua, kể cả những hàng không khớp điều kiện. Kết quả là một UPDATE tưởng chạm vài hàng lại chặn gần như mọi thao tác ghi khác trên bảng.

Hệ quả thực tế: thiếu index không chỉ làm query chậm mà còn làm giảm mức song song và tăng deadlock. Đây cũng là khác biệt lớn với PostgreSQL — Postgres không dùng gap lock; nó chống phantom bằng snapshot ở REPEATABLE READ và bằng SSI ở SERIALIZABLE. Hạ MySQL xuống READ COMMITTED sẽ bỏ phần lớn gap lock, đổi lại chấp nhận phantom read.