niedziela, 12 stycznia 2014

NOLOCK. Te same dane, różne wyniki.

Czy zdażyło Wam się kiedyś uruchomić to samo zapytanie, na tych samych danych i dostać dwa różne wyniki?

Otóż sytuacja taka może mieć miejsce w przypadku wykorzystania w zapytaniu NOLOCK. Załóżmy, że baza OLTP ma tabele z zamówieniami, gdzie dane są tylko dodawane i nie następują żadne modyfikacje. Jak to bywa z zamówieniami przeważnie zachodzi potrzeba by analizować zapisane dane.

Zakładając, że nie zależy nam na danych związanych z zamówieniami bieżącymi, raport wykonywane jest za poprzedni miesiąc, dzień czy inny okres co do którego mamy pewność, że żadne nowe dane nie będą dodane.

Jako, że jest to system OLTP, występuje duże prawdopodobieństwo, że raport mógłby być blokowany przez inne operacje. Ale zakładając, że dane z których będzie raport wygenerowany już się nie zmienią zachodzi pokusa użycia NOLOCK lub READ UNCOMMITED. Czasami niestety takie podejście może dać nieporządane rezultaty.

Poniższy skrypt tworzy przykładową tabelę z zamówieniami. Tabela ta zawiera klucz podstawowy będący GUID-em. Domyślnie klucz podstawowy jest też indeksem klastrowym.
USE master
GO
 
SET NOCOUNT ON
GO
 
--Create database if it doesn't exist
IF NOT EXISTS(SELECT * FROM sys.databases WHERE NAME = 'SQLTest')
CREATE DATABASE SQLTest
GO
 
--Set to simple recovery model to be able to quickly clear the transaction log
ALTER DATABASE SQLTest SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE SQLTest SET AUTO_CREATE_STATISTICS OFF
GO
 
USE SQLTest
GO
 
--Create sample table if it doesn't exist
IF EXISTS(SELECT * FROM sys.tables WHERE NAME = 'Orders')
DROP TABLE Orders
GO
 
CREATE TABLE Orders (
    OrderID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    OrderDate Date,
    Cost Money,
    Notes char(1000)
)
GO
 
IF EXISTS(SELECT * FROM sys.Views WHERE NAME = 'OrdersView')
DROP VIEW OrdersView
GO
 
CREATE VIEW OrdersView
AS
SELECT OrderID, OrderDate, Cost, Notes FROM Orders WITH(NOLOCK)
GO
 
DECLARE @LASTYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
 
INSERT INTO Orders(OrderID, OrderDate, Cost, Notes)
SELECT NEWID(), DATEADD(DAY, NUMBER * -1, @LASTYEARDATE), (ABS(CHECKSUM(NEWID())) % 100), 'New note'
FROM (
    SELECT TOP(2000) NUMBER = ROW_NUMBER() OVER(ORDER BY OBJECT_ID) FROM sys.all_objects
) N
GO
 
DECLARE @LASTYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
SELECT SUM(Cost) FROM Orders WHERE OrderDate <= @LASTYEARDATE
GO
Powyższy skrypt dodaje także trochę danych i zwraca sumę rekordów z kolumny cost.

W koim przypadku wynikiem skryptu jest liczba 99500,00. Jak uruchomicie sami powyższy skrypt to będziecie mieli zapewne inną wartość.

Wynik coprawda już mam ale z jakiegoś powodu zachodzi potrzeba wygenerowana raportu ponownie. Nic się nie zmiena. Dane jak i warunki zapytania są te same. Jedynie co to z racji tego, że jest to system OLTP trochę nowych rekordów może być dodanych, ale one nie “powinny” powodować problemów, bo są poza zakresem raportu.

Do tego celu posłużą dwa kolejne zapytania, które będą się wykonywać równolegle w dwóch osobnych sesjach.

Pierwszy skrypt przez minutę odczytuję pożądaną wartość i zapisuję ją do zmiennej
SET NOCOUNT ON
GO
 
DECLARE @RESULTS TABLE(COST MONEY)
 
DECLARE @STOPTIME DATETIME
DECLARE @COST MONEY
 
DECLARE @LASTYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
SET @STOPTIME = DATEADD(MINUTE, 1, GETDATE())
 
WHILE @STOPTIME > GETDATE()
BEGIN
    SELECT @COST = SUM(COST) FROM Orders WITH(NOLOCK) WHERE OrderDate <= @LASTYEARDATE
    INSERT INTO @RESULTS(COST) VALUES(@COST)
END
 
SELECT DISTINCT COST FROM @RESULTS
GO
Drugi natomiast przez minutę dodaje rekordy do tabeli za zamówieniami.
SET NOCOUNT ON
GO
 
CHECKPOINT
GO
 
DECLARE @STOPTIME DATETIME
DECLARE @THISYEARDATE DATETIME
SET @THISYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))+'0101' AS DATETIME)
SET @STOPTIME = DATEADD(MINUTE, 1, GETDATE())
 
WHILE @STOPTIME > GETDATE()
BEGIN
    INSERT INTO Orders(OrderID, OrderDate, Cost, Notes)
    SELECT NEWID(), DATEADD(DAY, (ABS(CHECKSUM(NEWID())) % 100), @THISYEARDATE), (ABS(CHECKSUM(NEWID())) % 10000), 'New note'
END
GO
Wynikiem pierwszego sktyptu w moim przypadku jest… od 60-140 różnych wartości!

Wow, a to niespodzianka. Przecierz dane wejściowe się nie zmieniły. Powodem tego, że wyniki są różne jest to, że indeks klastrowy, będący jednocześnie kluczem podstawowym ulega bardzo duzej fragmentacji. Rekordy przenoszoną są pomiędzy stronami, strony są dzielone, a to wszystko by indeks klastrowy na kolumnie będącej losowym ciągiem znaków mógł być w odpowiednio zbudowany.

Ostatnio w jednym ze skryptów pokazałem użycie funkcji fn_dblog.
select [description] from fn_dblog(null, null) where [description] <> ''
Szybka analiza wyników powyższego zapytania tylko to potwierdza.

Co ciekawe zmiana indeksu na kolunie OrderID tak by był nieklastrowy daje zupełnie inne rezultaty i przez minutę wykonywania skryptu pierwszego, równolegle z drugiem, wyniki są takie same. Jednakże, to nie indeks klastrowy jako tako jest winny przekłamaniom a to na jakiej kolumnie (typ danych) został utworzony.
Co ciekawe to nie ma znaczenia czy NOLOCK zostanie użyty bezpośrednio w zapytaniu, czy w widoku. W przypadku, gdy dane w indeksie klastrowym przenoszone są pomiędzy stronami przekłamania mogą występować.

Poniżej to samo zapytanie z wykorzystaniem widoku.
SET NOCOUNT ON
GO
 
DECLARE @RESULTS TABLE(COST MONEY)
 
DECLARE @STOPTIME DATETIME
DECLARE @COST MONEY
 
DECLARE @LASTYEARDATE DATETIME
SET @LASTYEARDATE = CAST(CAST(DATEPART(YEAR,GETDATE())-1 AS CHAR(4))+'1231' AS DATETIME)
SET @STOPTIME = DATEADD(MINUTE, 1, GETDATE())
 
WHILE @STOPTIME > GETDATE()
BEGIN
    SELECT @COST = SUM(COST) FROM OrdersView WHERE OrderDate <= @LASTYEARDATE
    INSERT INTO @RESULTS(COST) VALUES(@COST)
END
 
SELECT DISTINCT COST FROM @RESULTS
GO
Czy to oznacza, żeby nigdy nie używać NOLOCK? Myślę, że jest to ektremalne podejście, jednakże warto mieć na uwadze, że nolock to nie tylko odczytywanie danych, które nie zostały jeszcze zatwierdzone w innej transakcji. Pomimo tego, że zakładamy odczytanie takiej samej ilości rekordów w przypadku, gdy są one własnie przenoszone pomiędzy stronami wyniki mogą się zmieniać.

W przypadku, gdy musicie wygenerować raport finansowy nie polecam używania NOLOCK. To może być bomba z opóźnionym zapłonem i debugowanie błędów związanych z niepoprawnymi liczbami, może prowadzić do dość dużego bólu głowy.

Natomiast w przypadku, gdy dopuszczalne jest jakieś stopień przekłamania wyników, np sporządzenie limi trendu, gdzie wynik błędu rzędu kilku procent nie ma wielkiego znaczenia szybkość wygenerowania raportu bez blokowania zasobów (nie wszystkie zasoby nie są blokowane – o tym w innym poście). może być atutem.

Jeżli macie zapamętać jedną rzecz z tego wpisu, to to, że NOLOCK to nie tylko odczytywanie danych nie zatwierdzonych w innej transakcji, ale także margines błędu wynikający z przenoszenia danych pomiędzy stronami.

Prześlij komentarz