SQL Server và câu chuyện về Lock — Từ transaction bạn nghĩ lock cả bảng à?

Ngày xưa học SQL mình cũng như bạn — chỉ đủ điểm qua môn, nhớ lơ mơ SELECT *, INNER JOIN, WHERE, GROUP BY.

Nhưng rồi khi làm web thật, nhất là backend .NET, mình mới nhận ra: SQL không chỉ là ngôn ngữ truy vấn, nó còn quyết định hệ thống của bạn chạy mượt hay nghẽn, đúng dữ liệu hay sai loạn xạ.

Và trong đó, thứ ít ai để ý nhưng cực quan trọng chính là Lock (khóa) — cơ chế giúp SQL Server kiểm soát việc đọc/ghi dữ liệu cùng lúc.

⚙️ 1. Lock là gì?

Lock hiểu nôm na là “chiếc khóa tạm thời” SQL Server đặt lên dữ liệu khi bạn đọc, sửa hoặc xóa.

Mục tiêu: tránh 2 người cùng lúc chỉnh 1 dòng, gây sai lệch hoặc lỗi.

Khi có nhiều truy vấn cùng truy cập, SQL Server dùng lock để:

1. 🔒 Giữ cho dữ liệu nhất quán (consistency).
2. 🔄 Giúp các transaction cô lập (isolation) — không dẫm chân nhau.
3. 🚫 Tránh “race condition” (2 người ghi đè lẫn nhau).

📚 2. Các loại Lock chính

🧩 3. Cấp độ khóa (Lock Granularity)

SQL Server có thể khóa ở nhiều “độ lớn” khác nhau:

1. 🔸 Row Lock — khóa 1 dòng.
2. 🔸 Page Lock — khóa 1 trang (~8KB, chứa vài chục dòng).
3. 🔸 Table Lock — khóa nguyên bảng.
4. 🔸 Database Lock — cực hiếm, khi thay đổi toàn bộ DB.

Nó sẽ tự động quyết định lock cấp nào cho phù hợp:

1. Ít dòng → dùng ROWLOCK để tiết kiệm.
2. Quá nhiều dòng → SQL Server tự “nâng cấp” lên PAGE LOCK hoặc TABLE LOCK để tối ưu chi phí quản lý lock.

⚠️ Nếu không hiểu cơ chế này, bạn có thể thấy “tự nhiên cả bảng bị khóa” trong log — thật ra đó là SQL tự động lock escalation (nâng cấp khóa).

🔄 4. Tình huống thực tế — vì sao lock gây chậm?

Ví dụ 1: NOLOCK

Mình tạo bảng này và thêm 2 dòng dữ liệu

Câu chuyện là bạn muốn lấy danh sách userbalance lên, nhưng có hơn hàng ngàn user đang thực hiện giao dịch update, hàng loạt các session được thực hiện:

Có nghĩa bạn phải chờ mấy thằng này commit hết bạn mới lấy được dữ liệu lên, bạn sẽ gặp hiện tượng

Giải pháp dùng with(nolock)

=> Vì khi một session đang UPDATE, SQL Server đặt Exclusive Lock (X) lên dòng đó, khiến các session khác chỉ có thể đọc sau khi transaction kia COMMIT.

=> Càng nhiều transaction bị “chờ nhau” → hệ thống treo, timeout, hoặc queue nghẽn.

Đó là lý do dev không hiểu lock rất dễ viết code khiến cả hệ thống chậm, dù query nhìn có vẻ “đơn giản”.

Giải pháp này rất nhanh(hầu hết các select đều dùng thế này). Nhưng cũng có rủi ro khá lớn. Ví dụ Session A chạy update balance còn 0 đồng nhưng chưa commit <= 1 session khác vô tình đọc vào dữ liệu thấy là 0 đồng nên báo thanh toán lỗi. Nhưng 1ns sau session A rollback do sai dữ liệu hoàn tiền còn 100000. Bên kia lại báo lỗi ê mày hết tiền kìa.

Ví dụ 2: UPDLOCK

Có 2 session cùng trừ tiền x2 lần. Ví dụ mình chỉ trừ tiền user khi user có >=100k (user hiện có 100k). Mình select ra check điều kiện trước rồi update

Khi 2 session chạy cùng lúc có nghĩa cả 2 đều thỏa điều kiện @moeny >= 100000 và cả 2 đều vô được update. Kết quả expect là tiền phải = 0 và báo lỗi 1 thằng hết tiền nhưng cuối cùng cả 2 lại thành công và tiền lại về -100k

=> Cách fix: mình thêm with(updlock) trong select là ok rồi có nghĩa session 2 phải chờ thằng đầu xong mới được select tiền

🔍 5. Các Hint phổ biến để điều khiển Lock

SQL Server cho phép bạn chủ động “ra lệnh” lock kiểu nào qua Table Hints, ví dụ:

🧠 6. Deadlock — khi hai người chờ nhau mãi mãi

➡️ Cả hai đều chờ nhau, SQL Server phải “giết” 1 transaction để thoát.

Hiểu UPDLOCK sẽ giúp bạn tránh được tình huống này.

💬 Kết luận

Lock trong SQL Server không phải lỗi, mà là tính năng.

Vấn đề là bạn cần hiểu nó để:

1. Tránh block vô lý.
2. Biết khi nào nên dùng NOLOCK hay UPDLOCK.
3. Viết code transaction an toàn, hiệu năng cao.