Hôm nay chúng ta cùng tiềm hiểu một số loại wait type phổ biến của disk I/O và lock. Đó là các wait type PAGEIOLATCH_[XX], WRITELOG và liên quan đến lock có dạng LCK_M_[XX].

PAGEIOLATCH_[XX]

Dữ liệu trong sql server được lưu trữ trên disk dưới dạng page. Tốc độ đĩa sẽ ảnh hưởng lớn đến hiệu năng của sql server. Để hạn chế sự ảnh hưởng của disk, SQL Server lưu những page đã từng truy cập trước đây vào bộ nhớ đệm buffer cache (memory) nhằm tái sử dụng cho những lần sau.

Khi câu truy vấn cần truy xuất data của pageID nào đó. Buffer manager sẽ kiểm tra page này đã có trên buffer cache chưa. Nếu đã có rồi sẽ tiếp tục xử lý. Nếu page này chưa có trong buffer cache, buffer manager sẽ gửi một physical I/O request đến disk để tải page này lên memory.

Trong quá trình chờ data page được tải từ disk lên buffer cache, SQL Server sẽ đặt một latch độc quyền trên page để giành trước một vùng không gian trên buffer. Latch độc quyền này được sử dụng để tránh tình trạng hai request cùng tải một page lên memory cùng lúc.

Loại latch trong tình huống này được gọi là I/O latch và wait type của nó sẽ có dạng PAGEIOLATCH_[XX]. Nếu request load page lên để đọc thì mode của latch sẽ là dạng chia sẻ PAGEIOLATCH_SH. Nếu request load page lên để thay đổi (ghi) thì mode sẽ có dạng PAGEIOLATCH_EX.

Nếu SQL Server có nhiều thời gian chờ của hai loại wait type này có thể hiểu là tốc độ đĩa cứng của bạn đang là điểm nghẽn của hệ thống. Nhưng điều này không có nghĩa chúng ta kết luận vấn đề nằm ở đĩa cứng vì nó có thể gián tiếp gây ra bởi lý do khác – có quá nhiều physical I/O request phải phục vụ.

Hai tình huống thường thấy có thể tạo ra quá tải cho đĩa cứng đó là server không đủ memory hoặc câu truy vấn chưa tối ưu. Bên cạnh đó bản thân các đĩa cứng (I/O subsystem) cũng là một nguyên nhân phải kể đến. Đây là ba lý do phổ biến gây ra hai loại wait type trên.

Nếu là do cấu hình I/O subsystem thì bạn cần liên hệ với team hệ thống để kiểm tra và tìm giải pháp khắc phục.

Nếu tình huống gây ra là do memory quá tải, bạn cần tìm hiểu lý do gây ra, có thể do windows server gửi ra tín hiệu memory pressure (external pressure) hoặc do bản thân SQL Server quá tải memory (internal pressure) để có giải pháp cụ thể, nếu cần có thể gắn thêm RAM cho server.

Nếu là do câu truy vấn viết chưa tốt, hoặc chưa hỗ trợ index phù hợp nên gây ra tình trạng load nhiều data không cần thiết lên memory, không đủ chỗ chứa hết nên phải load đi load lại nhiều lần, tăng áp lực cho đĩa cứng. Bạn cần áp dụng các kĩ thuật tối ưu truy vấn để giải quyết tình huống này.

WRITELOG

Mỗi khi bạn thực hiện insert/update/delete một dòng dữ liệu SQL Server sẽ thay đổi nội dung data page chứa dòng đó trên buffer cache. Ngoài ra, nó còn ghi lại chi tiết những thay đổi này vào các dòng log trong buffer log.

Để đảm bảo tính toàn vẹn dữ liệu cho database, SQL Server đặt các câu lệnh insert/update/delete trong transaction.

Khi việc sửa đổi dữ liệu đã xong SQL Server cần thực hiện thao tác COMMIT transaction để ghi nhận thông tin sửa đổi đã được lưu vào database. Chỉ khi nào lệnh commit hoàn thành SQL Server mới báo câu lệnh insert/update/delete kết thúc.

Commit transaction là hành động đẩy các dòng log trong buffer log xuống đĩa cứng, hay nói cách khác nó transfer log từ memory xuống disk. Đây là một yêu cầu I/O bất đồng bộ (async I/O request) nên thread thực hiện câu lệnh COMMIT chỉ có thể chờ cho đến khi có tín hiệu I/O request đã hoàn thành.

SQL Server sẽ gán wait type là WRITELOG cho khoảng thời gian chờ này. Thời gian chờ WRITELOG phụ thuộc vào tốc độ của đĩa cứng (và một vài nhân tố khác mà ta không bàn ở đây) nên đây được xem là wait type liên quan đến disk I/O.

Data page đã sửa đổi chỉ bị đánh dấu là dirty page và vẫn giữ trên memory, chỉ đến khi process CHECKPOINT định kỳ chạy mới ghi những data page này xuống đĩa cứng. Hoặc khi SQL Server cần free memory thì process lazy writer cũng sẽ ghi những dirty page này xuống đĩa cứng.

Đây là loại wait type hơi khó nhai, nó không thể giải quyết bằng các kĩ thuật tuning thông thường, bạn có thể tham khảo thêm ở bài này.

Lock wait type

Locking và blocking là chủ đề lớn, ở bài này mình sẽ không bàn chi tiết mục đích và cách hoạt động của lock. Thay vào đó, chỉ đề cập kiến thức vừa đủ để hiểu wait type liên quan.

Lock được dùng để ngăn chặn các transaction đồng thời truy cập một đơn vị dữ liệu với hành động xung đột nhau.

Một transaction đang sửa đổi dữ liệu của dòng A thì các transaction khác không được đọc hoặc ghi vào dòng này. Ta nói transaction này đã đặt lock X trên dòng A

Một transaction đang đọc dữ liệu trên dòng A thì các transaction khác được phép truy cập cùng đọc dòng này, nhưng sẽ không cho phép transaction có hành động sửa đổi truy cập. Những transaction đọc này đã đặt lock S trên dòng A.

Lock S (shared) và lock X (exclusive) này được gọi là lock mode hay loại lock. Có nhiều loại lock và chúng có thể tương thích hoặc xung đột nhau phụ thuộc vào mục đích của chúng. Bạn có thể tham khảo bảng lock compatibility ở link này.

Nếu một request muốn đọc dữ liệu dòng A cần xin cấp phát lock S trên đơn vị dữ liệu này. Nhưng vì dòng A đã có lock X bị giữ bỡi session khác, nên lock S sẽ không được cấp phát, khi đó request đọc dữ liệu sẽ phải chờ, và wait type LCK_M_S sẽ được gán cho request này.

Nhằm tăng khả năng truy cập đồng thời SQL Server còn phân cấp các đối tượng dữ liệu ra thành nhiều cấp độ khác nhau như database, table, extent, page, row (RID nếu là heap table và KEY nếu là clustered table). Đây gọi là lock hierarchy.

Hãy cùng xét demo dưới đây để hiểu rõ hơn những kiến thức đề cập ở trên. Mình dùng session 51 để update một dòng dữ liệu và chưa commit, một session khác có id là 62 muốn đọc dòng này lên và tất hiên là xung đột với session 51 nên không thể thực hiện được.

Lock hierarchy trong SQL Server.

Để có được lock X trên row, session 51 cần phải có các lock IX khác ở các level cao hơn. Tương tự như vậy cho session 62, đã được cấp phát các lock IS ở level phía trên, nhưng lock S trên row thì phải chờ.

Script dưới đây được chạy trong session 51 trên máy của mình. Tạo bảng T và insert 100 dòng, sau đó begin transaction và update dòng có ID là 2, không commit.



 
USE tempdb 
GO
   
CREATE TABLE T (id INT PRIMARY KEY CLUSTERED, data1 CHAR(2000), createdDate DATETIME)
 
DECLARE @i INT = 1
 
BEGIN TRAN
WHILE @i <= 100
BEGIN
    INSERT T
    SELECT @i, CAST(@i AS CHAR(10)), GETDATE()
    SET @i += 1 
END
COMMIT


GO

BEGIN TRAN
	UPDATE T 
	SET createdDate = GETDATE() 
	WHERE id = 2

--COMMIT

Ở session 62, chúng ta chạy câu lệnh SELECT data như sau.


USE tempdb 
GO

SELECT * FROM T WHERE id = 2


Kết quả sẽ như hình minh họa ở trên, chúng ta có thể kiểm chứng điều này thông qua việc quan sát những lock trên đối tượng nào đã được grant cho session cụ thể nào ở DMV sys.dm_tran_locks


select request_session_id session_id, resource_type, resource_database_id,resource_description,
	resource_associated_entity_id, request_mode, request_type, request_status 
from sys.dm_tran_locks
order by request_session_id


Session 62 đang chờ lock S cho resource type là KEY. Cột resource_associated_entity_id chứa các id liên quan tới object, nó có thể là partition_id hoặc là object_id. Cột resource_description chứa thông tin thêm nhằm giúp xác định cụ thể vị trí của page và row.

Chạy lại câu truy vấn ở phần 2 để thấy session 62 đang có wait type là LCK_M_S.

Có rất nhiều blog hay để tìm hiểu và giải quyết vấn đề locking và blocking, một trong những trang đề cập đầy đủ và khá chất lượng mà các bạn có thể tham khảo là aboutsqlserver.com.

Bên cạnh những loại wait type mình đề cập từ phần 2 đến bây giờ, còn có rất nhiều wait type khác nữa và chúng đại diện cho những tài nguyên khác thể hiện những điểm nghẽn có thể có trong SQL Server. Bạn hãy làm quen với việc phân tích hiệu năng SQL Server bằng wait statistics để xác định phạm vi vấn đề nhanh chóng hơn, từ đó sử dụng các công cụ khác như performance monitor, DMVs, extended events để kiểm chứng và tìm giải pháp cho nó. Cảm ơn các bạn đã đọc tới lúc này.

Nguồn tham khảo