Nie czytaj tego !…

…chyba, że masz chwilę i czasu chcesz dowiedzieć się więcej o ciekawym, a zarazem dość ryzykownym scenariuszu związanym z SQL Server i ulotnością danych. Nie będzie zbyt technicznie, jednak w ostatniej części nie uda się uniknąć kawałka kodu, aby zobrazować i potwierdzić spostrzeżenia.

Zacznijmy jednak od początku. In-memory – hasło, które dostawcą rozwiązań bazodanowych towarzyszy od kilku, jeśli nie kilkunastu lat. Pierwsze rozwiązania in-memory w przypadku Microsoft to czas wersji SQL Server 2008 R2, w której po raz pierwszy zaimplementowany był ten rodzaj rozwiązań pod postacią PowerPivot, czyli w obszarze rozwiązań wspierających raportowanie i analitykę. Kolejne wersje to inwestycja również w obszar przetwarzania transakcyjnego (in-memory OLTP). Obecnie dostępna wersja oferuje mechanizmy zoptymalizowane do przetwarzania w pamięci zarówno dla systemów transakcyjnych, raportowych opartych na systemach transakcyjnych (analityka operacyjna), jak również dedykowanych rozwiązaniach hurtownianych i raportowo-analitycznych (Analysis Services Tabular).

Jednak o co ten ambaras, przecież nawet przed wprowadzeniem “in-memory”operacje na danych w SQL Server odbywały się w pamięci. Dla mniej wtajemniczonych, w przypadku SQL Server dane (wiersze) zapisywane są w obrębie stron (kB), strony tworzą extenty (8 kolejnych stron), a strony/extenty przechowywane są na dysku we wcześniej utworzonym pliku danych (.mdf/.ndf). W momencie gdy następuje operacja odczytu, zapisu, aktualizacji,czy usunięcia danych, odpowiednia strona jest odczytywana z dysku, umieszczana w pamięci, aby możliwe było wykonanie odpowiedniej operacji. Niestety niezbędny jest odczyt z dysku, jak również utworzenie odpowiednich blokad/zakleszczeń, aby zapewnić spójność wykonywanej operacji. Ze względu na strukturę danych umieszczanych na stronach blokady/zakleszczenia dotykają właśnie stron.
Przy dużej liczbie żądań w pewnym momencie dochodzi do pojawienia się znaczących czasowo oczekiwań na możliwość wykonania operacji. Efekt – ograniczenie wydajności. Przykład operacji w tradycyjnym modelu obrazuje poniższa ilustracja.

On-disk

Chcąc uniknąć bariery,która ogranicza system w zakresie liczby obsługiwanych transakcji, można wykorzystać rozwiązania in-memory, w których zupełnie zmieniony został sposób przechowywania danych i tym samym realizacji dostępu do wierszy. Przede wszystkim w przypadku tabeli zoptymalizowanej do przetwarzania w pamięci operacje wykonywane są na pojedynczych wierszach, co skutecznie eliminuje blokady i zakleszczenia występujące w wariancie klasycznych tabel dyskowych. Dodatkowo operacje aktualizacji czy kasowania sprowadzają się do wygenerowania nowej wersji rekordu lub oflagowania w przypadku kasowanie danych, co minimalizuje czas operacji oraz eliminuje potencjalne kwestie blokad. Sposób obsługi rekordów w modelu in-memory obrazuje poniższy schemat.

in-memory

Czy tabele umieszczone w całości w pamięci to przypadkiem nie jest zbyt ryzykowne podejście? A co z trwałością danych w przypadku restartu serwera. Za trwałość czy też nie ulotność danych odpowiada jeden z parametrów używanych przy deklaracji table – DURABILITY. Wykorzystać można dwie wartości parametru: SCHEMA_ONLY oraz SCHEMA_AND_DATA. W pierwszym przypadku faktycznie przy restarcie serwera dane umieszczone w tabeli zostaną utracone, zachowana zostanie jedynie struktura tabeli, która zostanie na nowo utworzona po restarcie serwera. W scenariuszu nr 2 (SCHEMA_AND_DATA) dane umieszczane w tabeli są zupełnie bezpieczne, dba o to odpowiedni proces, odpowiedzialny za cykliczną realizację tworzenia punktów kontrolnych(plików) na podstawie, których możliwe jest odtworzenie zawartości tabeli podczas restartu.

W tym miejscu odniosę się do pytania, które padło podczas jednego z moich ostatnich spotkań. A co stanie się, jeśli stworzę dwie tabele, dla uproszczenia obie będą tabelami in-memory. Jednak z nich będzie tabelą nieulotną(SCHEMA_AND_DATA), druga natomiast ulotną(SCHEMA_ONLY). Tabele złączone są relacją poprzez zdefiniowane klucze (PK-FK), jednak z różnych przyczyn to własnie tabela trzymając klucz główny (PK) została utworzona jako SCHEMA_ONLY czyli dane w tej tabeli są ulotne, podczas gdy dane w drugiej tabeli, w której kolumna występuje jako klucz obcy(FK) jest tabela trwała (Pomijam w tym miejscu zasadność takiego podejścia, pytanie miało na celu jedynie weryfikację zachowania systemu w przypadku restartu serwera). I teraz uwaga, przyznam, że sam trochę byłem zaskoczony, ale po chwili przemyśleń i konsultacji mentorskiej (dzięki Paweł 🙂 ), zachowanie serwera wydaje się być dość logiczne. Co zatem się dzieje, krok po kroku bazując na przykładowym kodzie:

— Tworzę bazę danych do testów
CREATE DATABASE InMemOLTPDB
GO

— Zmiana kontekstu na utworzoną bazę
USE [InMemOLTPDB]
GO

— Dodanie grupy plików i kontenera dla obietków in-memory

ALTER DATABASE [InMemOLTPDB]
ADD FILEGROUP [MemoryOptimizedFG] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [InMemOLTPDB] ADD FILE (name=’FootStatsContainer01′, filename=’C:\Temp\InMemoryContainer’) TO FILEGROUP [MemoryOptimizedFG]

–Utworzenie tabeli, tabela in-memory o durability=SCHEMA_ONLY, to jest moja tabela z kluczem głównym

CREATE TABLE [dbo].[tblMemOptNative]
(
[id] [int] PRIMARY KEY NONCLUSTERED NOT NULL,
[firstName] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[lastName] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[emailAddress] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

GO

— Insert kilku wartości
BEGIN TRAN
INSERT tblMemOptNative (id, firstName, lastName, emailAddress)
VALUES(1,’Graczyk’,’Bartek’,’@’),(2,’Graczyk’,’Marcin’,’@’),(3,’Graczyk’,’Maciej’,’@’)

COMMIT TRAN

— Kontrolnie czy są dane
SELECT * FROM tblMemOptNative

— Tworzymy drugą tabelę, tabela również in-memory o durability SCHEMA_AND_DATA
CREATE TABLE tblMemOptNative_FKey
(id int PRIMARY KEY NONCLUSTERED)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA)

–Wprowadzamy kontrolnie rekord o ID=3
INSERT tblMemOptNative_FKey
VALUES (3)

— Dodajemy klucz referencję do tabeli podstawowej – relacja po polu ID
ALTER TABLE tblMemOptNative_FKey
ADD FOREIGN KEY (id)
REFERENCES tblMemOptNative(Id)
GO

–PRÓBA WPROWADZENIA WARTOŚCI NIEZGODNEJ Z WARTOŚCIĄ KLUCZA – ZAKOŃCZONA NIEPOWODZENIEM

INSERT tblMemOptNative_FKey
VALUES (6)

error_1

–RESTART SERWERA

— Kontrola zawartości tabel
SELECT * FROM tblMemOptNative
SELECT * FROM tblMemOptNative_FKey
after_restart

— EFEKT – TABELA  PODSTAWOWA JEST PUSTA – ZGODNIE Z DEFINICJĄ SCHEM_ONLY

— TABELA DRUGA ZAWIERA DANE

Jak zatem doszło do skasowania danych, skoro tabele łączy aktywna relacja, połączenie PK-FK w przypadku tabel dyskowych uniemożliwia usunięcie danych z tabeli głównej przy aktywnej relacji. Zobaczmy czy faktycznie relacja jest aktywna?

error_2

Jak widać po błędzie przy próbie wprowadzenia danych do tabeli – relacja jest aktywna.

Małe podsumowanie zaistniałej sytuacji.
Czy zatem w opisanym przypadku restart serwera wymusił wyłączenie relacje na czas restartu i jej ponowne włącznie? Najwyraźniej tak został zaprojektowany mechanizm, aby zapewnić zgodność z określonym sposobem trwałości danych nie zwracając uwagi na zdefiniowane relacje…Z drugiej strony od samego początku należało się spodziewać potencjalnej utraty danych z tabeli podstawowej, ciekaw tylko byłem, podobnie jak osoba pytająca jak silnik radzi sobie z takim przypadkiem. Szczęśliwie nie prowadzi to do destrukcji bazy i zależności pomiędzy tabelami. 🙂