Kiến thức về Blocking trong SQL Server

Monday 26/05/2025

Blocking là một hiện tượng phổ biến và đôi khi là không thể tránh khỏi trong bất kỳ hệ quản trị cơ sở dữ liệu quan hệ nào, bao gồm cả SQL Server. Nó xảy ra khi một tiến trình (process) hoặc phiên (session) giữ một khóa (lock) trên một tài nguyên (ví dụ: một hàng, một trang, một bảng) và ngăn chặn các tiến trình khác truy cập hoặc sửa đổi cùng tài nguyên đó. Hiểu rõ về blocking là rất quan trọng để chẩn đoán và giải quyết các vấn đề hiệu suất trong SQL Server.

Mục lục

1. Blocking xảy ra như thế nào?

Khi một giao dịch (transaction) bắt đầu sửa đổi dữ liệu, SQL Server sẽ đặt các khóa lên các tài nguyên liên quan để đảm bảo tính nhất quán và toàn vẹn của dữ liệu (ACID properties). Các loại khóa phổ biến bao gồm:

Shared Locks (S): Được đặt khi đọc dữ liệu. Nhiều phiên có thể giữ Shared Lock trên cùng một tài nguyên đồng thời.
Exclusive Locks (X): Được đặt khi sửa đổi dữ liệu (INSERT, UPDATE, DELETE). Chỉ một phiên duy nhất có thể giữ Exclusive Lock trên một tài nguyên tại một thời điểm.
Update Locks (U): Một loại khóa trung gian được đặt khi chuẩn bị sửa đổi dữ liệu. Nó giúp ngăn chặn deadlock khi nhiều phiên muốn cập nhật cùng một tài nguyên.
Intent Locks (IS, IX, IU): Được đặt ở cấp độ cao hơn (ví dụ: bảng) để thông báo ý định của một giao dịch sẽ đặt khóa ở cấp độ thấp hơn (ví dụ: hàng).

Blocking xảy ra khi một phiên yêu cầu một khóa không tương thích với một khóa đang được giữ bởi một phiên khác trên cùng một tài nguyên. Phiên yêu cầu khóa sẽ phải chờ (wait) cho đến khi phiên đang giữ khóa nhả khóa ra.

Ví dụ:

Phiên A cập nhật một hàng trong bảng Orders, và SQL Server đặt một Exclusive Lock (X) trên hàng đó.
Phiên B cố gắng đọc hoặc cập nhật cùng hàng đó.
Vì Exclusive Lock đang được giữ bởi Phiên A, Phiên B sẽ bị block và phải chờ cho đến khi Phiên A hoàn tất giao dịch và nhả khóa.

2. Tác động của Blocking

Blocking có thể gây ra nhiều vấn đề về hiệu suất, bao gồm:

2.1 Tăng thời gian phản hồi

Các ứng dụng và người dùng có thể thấy hệ thống chậm chạp hoặc không phản hồi.

2.2.Giảm thông lượng (throughput)

Hệ thống xử lý ít giao dịch hơn trong cùng một khoảng thời gian.

2.3 Deadlock

Một trường hợp đặc biệt và nghiêm trọng của blocking, nơi hai hoặc nhiều phiên khóa lẫn nhau và không thể giải phóng tài nguyên. SQL Server sẽ tự động chọn một “nạn nhân” của deadlock để kết thúc giao dịch của họ, gây ra lỗi cho ứng dụng.

2.4. Tăng tài nguyên sử dụng

Các phiên bị block có thể giữ kết nối và tài nguyên hệ thống trong khi chờ đợi.

3. Nguyên nhân phổ biến gây ra blocking

Giao dịch chạy dài: Các giao dịch mở và giữ khóa trong thời gian dài (ví dụ: do ứng dụng không commit/rollback kịp thời).
Giao dịch lớn: Các giao dịch cập nhật hoặc chèn một lượng lớn dữ liệu, giữ khóa trên nhiều tài nguyên.
Truy vấn không tối ưu: Các truy vấn quét toàn bộ bảng thay vì sử dụng chỉ mục, dẫn đến việc khóa nhiều hàng hơn mức cần thiết.
Thiết kế chỉ mục kém: Thiếu chỉ mục hoặc chỉ mục không phù hợp có thể khiến SQL Server phải quét nhiều dữ liệu hơn để tìm kiếm, dẫn đến nhiều khóa hơn.
Mức độ cô lập giao dịch (Isolation Level) cao: Các mức độ cô lập như SERIALIZABLE hoặc REPEATABLE READ cung cấp tính nhất quán dữ liệu cao nhưng cũng có xu hướng đặt và giữ nhiều khóa hơn.
Chạy các tác vụ bảo trì: Các tác vụ như tạo lại chỉ mục (index rebuild) hoặc sao lưu cơ sở dữ liệu có thể đặt khóa độc quyền trên bảng.

4. Cách phát hiện blocking

Việc phát hiện blocking kịp thời là chìa khóa để giải quyết vấn đề. Một số công cụ và phương pháp chúng ta có thể sử dụng:

sp_who2: Một thủ tục hệ thống đơn giản để xem các phiên hiện tại và trạng thái của chúng, bao gồm cả cột BlkBy cho biết phiên nào đang block.
sys.dm_exec_requests và sys.dm_os_waiting_tasks: Các DMV (Dynamic Management Views) này cung cấp thông tin chi tiết hơn về các yêu cầu đang chạy, các phiên đang chờ và loại chờ.
Activity Monitor: Một công cụ GUI trong SQL Server Management Studio (SSMS) hiển thị các phiên đang chạy, khóa và blocking.
Extended Events / SQL Trace: Để theo dõi và ghi lại các sự kiện blocking theo thời gian.

Ví dụ truy vấn sử dụng DMV:

5. Cách giảm thiểu và giải quyết blocking

Thiết kế chỉ mục hiệu quả: Đảm bảo các chỉ mục được tạo đúng cách và được sử dụng bởi các truy vấn để giảm số lượng hàng được khóa.
Tối ưu hóa truy vấn: Viết các truy vấn hiệu quả, tránh quét toàn bộ bảng không cần thiết.
Giảm thiểu thời gian giao dịch: Đảm bảo các giao dịch chạy càng ngắn càng tốt và commit/rollback nhanh chóng.
Sử dụng mức độ cô lập phù hợp: Đánh giá và chọn mức độ cô lập giao dịch phù hợp với yêu cầu ứng dụng (ví dụ: READ COMMITTED hoặc READ COMMITTED SNAPSHOT có thể giảm blocking đáng kể).
Thiết kế lại lược đồ (schema): Chia nhỏ các bảng lớn hoặc sử dụng các kỹ thuật phân vùng (partitioning) để giảm tranh chấp khóa.
Xử lý các giao dịch lớn: Đối với các tác vụ chèn/cập nhật/xóa lớn, hãy xem xét thực hiện theo từng lô (batch processing) nhỏ hơn.
Sử dụng Hint khóa (Lock Hints): Chỉ sử dụng khi chúng ta hiểu rõ tác dụng của chúng, vì chúng có thể gây ra vấn đề khác nếu dùng không đúng cách.
Kiểm tra Deadlock: Thường xuyên kiểm tra và phân tích biểu đồ deadlock để xác định nguyên nhân gốc rễ.
Giám sát thường xuyên: Sử dụng các công cụ giám sát hiệu suất để theo dõi blocking và nhận cảnh báo khi có vấn đề.

Blocking là một phần không thể tránh khỏi của môi trường cơ sở dữ liệu đồng thời. Tuy nhiên, bằng cách hiểu rõ cơ chế của nó và áp dụng các phương pháp tốt nhất, chúng ta có thể giảm thiểu tác động tiêu cực của blocking lên hiệu suất hệ thống SQL Server của mình.

Hy vọng qua bài viết này mọi người sẽ có thông tin hữu ích và hiểu rõ hơn một vài kiến thức cơ bản về kiến thức về Blocking trong SQL Server.

P.A Việt Nam cung cấp đa dạng các Plan Hosting đáp ứng yêu cầu của khách hàng
Hosting Phổ Thông
Hosting Chất Lượng Cao
Hosting WordPress

Tham khảo các ưu đãi: https://www.pavietnam.vn/vn/tin-khuyen-mai/

5/5 - (1 bình chọn)