Nếu search trên google các bài hướng dẫn về việc tăng tốc độ truy vấn sql thì chúng ta sẽ thấy rất nhiều bài nhắc đến việc sử dụng WITH (NOLOCK) trong các câu truy vấn. Trước kia mình cũng sử dụng nó trong tất cả các câu truy vấn của mình thì phát hiện ra là tốc độ truy vấn của mình được cải thiện lên rất nhiều khi database được truy cập liên tục tục và nhiều client sử dụng, tuy nhiên cũng có trường hợp nó làm chậm câu truy vấn của mình đi rõ rệt. Vì thế chúng ta thử tìm hiểu xem nó hoạt động như thế nào nhé.

Sử dụng WITH (NOLOCK) có chức năng gì ?

Cho phép SQL đọc dữ liệu từ các bảng kể cả khi đang bị lock mà không bị chặn bởi các tiến trình khác. Việc này giúp cải thiện hiệu suất truy vấn, tuy nhiên có khả năng dữ liệu đọc được bị sai lệch, không chuẩn.

Hãy thử xem ví dụ dưới đây để hiểu rõ hơn nhé:

Đây là một truy vấn trả về tất cả dữ liệu từ bảng Person.Contact (database AdventureWorks). Khi nhìn vào kết quả trả về chúng ta thấy duy nhất có 1 bản ghi có cột Suffix là có giá trị ( khác null), đó là bản ghi có ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20

Bây giờ hãy thử chạy câu truy vấn dưới đây trên một cửa sổ sql studio management khác với transaction xem sao nhé, tuy nhiên chúng ta sẽ không comit tới database để những bản ghi đó bị khóa lại xem sao:

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

Nếu chúng ta chạy lại câu lệnh select bên trên kia thì sẽ thấy câu truy vấn này sẽ cứ treo mãi mà không trả về kết quả nào.

SELECT * FROM Person.Contact WHERE ContactID < 20

Nếu chạy lệnh sp_who2  chúng ta có thể biết được là lệnh SELECT đó đã bị block lại. Nguyên nhân là do lệnh UPDATE trên kia chưa được COMIT hoặc ROLLBACK, các bản ghi đó sẽ bị khóa lại, câu truy vấn SELECT kia sẽ bị treo mãi. Khi đó chỉ có cách hủy câu select đó đi hoặc là COMIT, ROLLBACK để lệnh UPDATE trên kia hoàn thành.

Để truy xuất những bản ghi đã bị khóa đó, Chúng ta có thể sử dụng chỉ thị NOLOCK như dưới đây. Câu lệnh SELECT vẫn sẽ trả về kết quả thậm chí lệnh UPDATE trên kia chưa được COMIT hay ROLLBACK và câu lệnh SELECT tại window 1 vẫn đang loading …

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

Chúng ta có thể thấy cột Suffix bây giờ tất cả giá trị đều là “B”. Nguyên nhân là do câu lệnh UPDATE tại windows 1 đã thực hiện việc update các bản ghi đó. Mặc dù transaction vẫn chưa được commit nhưng nếu sử dụng chỉ thị NOLOCK thì SQL Server sẽ bỏ qua việc lock và trả về dữ liệu. Nếu trong trường hợp lệnh UPDATE  trên kia được rollback lại thì dữ liệu trả về bị sai lệch, không chuẩn là ở chỗ đó.

Nếu chúng ta rollback lại lệnh UPDATE trên kia bằng lệnh ROLLBACK thì câu lệnh SELECT ở window 1 lúc này cũng sẽ trả về kết quả ta có thể thấy cột Suffix giống như ban đầu.

-- run in query window 1
ROLLBACK

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20

Vì vậy với việc sử dụng chỉ thị NOLOCK thì dữ liệu đọc được là đã được thay đổi, tuy nhiên dữ liệu đó chưa thực sự được xác nhận bởi CSDL.  Nếu các bạn đang chạy báo cáo mà không quan tâm đến việc dữ liệu có thể bị ngắt hay không sử dụng transaction thì không vấn đề, nhưng nếu việc tạo transaction và yêu cầu dữ liệu có tính nhất quán thì việc sử dụng chỉ thị NOLOCK có thể khiến dữ liệu báo cáo bị sai hoặc ngắt.

Các kiểu Lock của SQL Server khi suwe dụng với NOLOCK

Nếu chúng ta chạy truy vấn SELECT trên kia mà không có NOLOCK và sử dụng sp_who2 có thể thấy kết quả như sau

Còn vẫn câu truy vấn đó nhưng với NOLOCK và sử dụng sp_who2 có thể thấy kết quả như sau

Điểm khác nhau ở đây là một khóa “S” được đặt trên page (PAG) khi không sử dụng NOLOCK. Tương tự chúng ta thấy khó Sch-S tương tứng với IS cho table (TAB). Vì vậy, các vấn đề chúng ta thấy được là ngay cả khi chúng ta chỉ SELECT dữ liệu thôi nhưng SQL  Server vẫn tạo ra Lock để đảm bảo dữ liệu được nhất quán.

Đây là các kiểu Lock và chế độ Lọc được sử dụng cho cả 2 câu truy vấn select bên trên:

Lock Types:

• MD – metadata lock
• DB – database lock
• TAB – table lock
• PAG – page lock

Lock Mode:

• S – Cho phép truy cập
• Sch-S – Schema được ổn định đảm bảo không thay đổi khi đối tượng đang được sử dụng
• IS – Intent được chia sẻ và chỉ định sử dụng Lock S

READUNCOMMITED và NOLOCK

Chỉ thị NOLOCK tương tự với chỉ thị READUNCOMMITED và cho kết quả giống nhau

SELECT * FROM Person.Contact WITH (READUNCOMMITTED)

With NOLOCK chỉ sử dụng cho lệnh SELECT

Chỉ thị NOLOCK và READUNCOMMITED chỉ được sử dụng duy nhất với lệnh SELECT. Nếu chúng ta sử dụng chúng cho UPDATE, DELETE, INSERT sẽ gặp lỗi.

UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

[su_highlight background=”#FFFFE1″ color=”#666666″]Msg 1065, Level 15, State 1, Line 15 The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.[/su_highlight]

Schema thay đổi việc Block với NOLOCK

NOLOCK cần một khóa Sch-S ( ổn định schema ), SELECT sử dụng NOLOCK vẫn có thể bị chặn nếu một bảng bị thay đổi và không được comit. Đây là một ví dụ:

-- run in query window 1
BEGIN TRAN
ALTER TABLE Person.Contact ADD column_b 
VARCHAR(20) NULL ;

Nếu chúng ta chạy câu lệnh SELECT nó vẫn sẽ bị chặn trừ khi chúng ta comit hoặc rollback.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

Các vấn đề với NOLOCK

Chúng ta đã nhắc đến vấn đề dữ liệu có thể không nhất quán nếu sử dụng NOLOCK, dưới đây là một vài vấn đề chúng ta có thể gặp nếu sử dụng nó

• Dirty Reads – Đọc bẩn, điều này xảy ra khi cập nhật được thực hiện, dữ liệu select ra có thể khác nhau.
• Nonrepeatable Reads – Đọc không lặp lại, việc này xảy ra khi chúng ta cần đọc dữ liệu nhiều lần và dữ liệu thay đổi trong quá trình đó.
• Phantom Reads – Đọc ma, việc này xảy ra khi dữ liệu được insert hoặc delete và transaction đã rollback lại. Vì vậy dữ liệu insert mà chúng ta nhận được có thể nhiều hơn thực tế, tương tự số bản ghi bị xóa sẽ ít hơn nếu như rollback được thực hiện.

Các vấn đề khác và chi tiết chúng ta có thể tham khảo tại link : http://msdn.microsoft.com/en-us/library/ms190805.aspx

Isolation Level

Chúng ta cũng có thể thiết lập Isolation Level cho tất cả truy vấn sử dụng NOLOCK hoặc READUNCOMMITTED . Isolation level sẽ chấp nhận READUNCOMMITTED cho tất cả lệnh SELECT cho đến khi chúng ta tắt nó đi.

Trong ví dụ dưới đây, hai câu lệnh SELECT sẽ sử dụng READUNCOMMITED hoặc NOLOCK và UPDATE sẽ vẫn hoạt động như bình thường. Bằng cách này, bạn có thể đặt một loạt các câu lệnh thay vì sửa đổi mỗi truy vấn.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

SELECT * FROM Person.Contact WHERE ContactID < 20 

UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1 

SELECT * FROM Person.Contact WHERE ContactID < 20 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off