Tabele tylko do odczytu w SQL Server

Zabezpieczanie danych przed przypadkową zmianą

Ochrona danych w bazie jest jednym z najważniejszych zadań administratora. Najczęściej stosowaną techniką jest okresowe tworzenie kopii zapasowych (ang. backup) różnego typu: pełnych, przyrostowych, logu transakcyjnego. Nie o tym jednak zamierzam pisać, przynajmniej nie tym razem. Można stosować mniej lub bardziej wykwintne rozwiązania programowe, między innymi wyzwalacze blokujące operacje modyfikacji danych, można to zrealizować poprzez zabranie praw do modyfikacji dla wybranej tabeli. Każde z tych rozwiązań ma pewne wady i zalety oraz obszar, w którym mogą się sprawdzić. Ja przedstawię rozwiązanie jeszcze inne, bardzo łatwe w zastosowaniu - tabele tylko do odczytu.

Od razu wyjaśnię pewien skrót myślowy, który zastosowałem. W SQL Server, przynajmniej w wersjach niższych niż SQL Server 2014, nie ma czegoś takiego jak tabele tylko do odczytu. Tylko do odczytu może być kontener nieco szerszy niż tabela - grupa plików. Tabela umieszczona w grupie plików tylko do odczytu automatycznie staje się tabelą tylko do odczytu. Zarządzanie tabelami tylko do odczytu z poziomu grupy plików ma oczywiście swoje konsekwencje.

Przygotowanie grupy plików pod tabelę tylko do odczytu

Wspomniałem, że atrybut tylko do odczytu zakłada się na całą grupę plików. Jeżeli zatem chcemy umieścić w naszej bazie danych tabele tylko do odczytu i tabele zwykłe, należy utworzyć dwie oddzielne, całkowicie niezależne grupy. Wymaga to zdefiniowania trzech plików: pliku logów, pliku grupy domyślnej i pliku grupy tylko do odczytu. Dlaczego aż trzech? Każda baza musi posiadać przynajmniej dwa pliki: plik logów, najczęściej z rozszerzeniem ldf oraz plik z danymi, zwany plikiem głównym (ang. primary), najczęściej z rozszerzeniem mdf. W pliku głównym umieszczone są informacje startowe bazy danych, w tym informacje o innych plikach i grupach plików, o ile takowe istnieją. W bazie danych musi też istnieć przynajmniej jedna grupa plików, zwana grupą główną (ang. primary). To tam znajdują się wszystkie bazy systemowe oraz wszystkie obiekty, dla których nie wskazano innej grupy. Pozostałe grupy plików zwane są grupami definiowanymi przez użytkownika (and. user defined). Ta dodatkowa, druga grupa, będzie zawierała rzeczony trzeci plik. To ten trzeci plik stanie się plikiem tylko do odczytu. Tyle teorii, popatrzmy na przykład bazy danych:

CREATE DATABASE [ReadOnlyTest]
ON PRIMARY 
(
  NAME = N'SimpleTables',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SimpleTables.mdf',
  SIZE = 5120KB,
  FILEGROWTH = 20%
), 
FILEGROUP ReadOnlyTables
(
  NAME = N'ReadOnlyTables',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReadOnlyTables.ndf',
  SIZE = 5120KB,
  FILEGROWTH = 20%
)
LOG ON 
(
  NAME = N'ReadOnlyTest_log',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReadOnlyTest_log.ldf',
  SIZE = 1024KB,
  FILEGROWTH = 10%
)

Powyższy skrypt zakłada bazę ReadOnlyTest, w które znajdą się odpowiednio:

• plik z danymi SimpleTables umieszczony w głównej, domyślnej grupie plików,
• plik z danymi ReadOnlyTables umieszczony w grupie plików ReadOnlyTables,
• plik logów ReadOnlyTest_log.

Reszta atrybutów nie ma znaczenia z punktu widzenia celu wpisu. Co można z takimi plikami i grupami plików zrobić? Gdzie jest jakiś atrybut zabraniający modyfikacji?

Przypisywanie tabel do poszczególnych grup plików

Przyjrzyjmy się tradycyjnemu sposobowi dodawania tabeli:

CREATE TABLE Table1
(
  ID int IDENTITY
);

Tak wygląda tabela, która, z braku jawnej specyfikacji grupy plików, zostanie przypisana do domyślnej grupy plików (PRIMARY). Popatrzmy teraz na drugą tabelę, wprost przypisanej do grupy plików ReadOnlyTables:

CREATE TABLE Table2
(
  ID int IDENTITY
) ON [ReadOnlyTables];

Na tym etapie mamy dwie tabele umieszczone w dwóch różnych grupach plików. Do obu tabel można wstawiać dane:

INSERT INTO Table1 DEFAULT VALUES;
INSERT INTO Table2 DEFAULT VALUES;

Tabele zachowują się identycznie. Teraz nadszedł czas, aby to zmienić.

Zmiana atrybutu grupy plików

Cały wpis miał dotyczyć tabel tylko do odczytu. Do tej pory dotyczył tylko przygotowań. Dlaczego? Bowiem sama instrukcja zabezpieczająca grupę plików przed modyfikacjami zajęłaby zaledwie jedną linijkę. Ważniejsze od samej instrukcji jest jednak zrozumienie całej infrastruktury takiego rozwiązania. Popatrzmy na wspomnianą instrukcję:

ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READONLY;

To by było na tyle. Byłoby, gdyby nie pewien drobny szczegół. Jeżeli z bazy danych korzystają inni użytkownicy, instrukcja może się nie powieść. Otrzymamy następujący komunikat:

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'ReadOnlyTest'

I ten problem można rozwiązać. Należy jednak coś zrobić z użytkownikami, którzy cały czas mogą się z bazą danych łączyć. Najłatwiej przejść w tryb jednego użytkownika, brutalnie pozbywając się innych:

ALTER DATABASE [ReadOnlyTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READONLY;
ALTER DATABASE [ReadOnlyTest] SET MULTI_USER;

Na tym etapie grupa plików ReadOnlyTables powinna być grupą tylko do odczytu, a tabela Table2 - tabelą tylko do odczytu.

Próba modyfikacji danych

Grupa niedowiarków, zapewne liczna, zechce w tym momencie sprawdzić prawdziwość dokumentacji SQL Server i wykona prosty test:

INSERT INTO Table1 DEFAULT VALUES;
--Msg 652, Level 16, State 1, Line 1
--The index "" for table "dbo.Table2" (RowsetId 72057594039107584) resides on a read-only filegroup ("ReadOnlyTables"), which cannot be modified.
INSERT INTO Table2 DEFAULT VALUES;

Druga instrukcja zakończy się niepowodzeniem. Nad instrukcją umieściłem komunikat, który powinien się pojawić przy próbie jej wykonania. Podobne testy można wykonać dla pozostałych instrukcji modyfikujących: UPDATE, DELETE i MERGE. Efekt będzie podobny.

Próba usunięcia tabeli

Usunięcie pojedynczego rekordu nie jest tak straszne jak usunięcie całej tabeli. Pozwoliłem sobie przedstawić jeszcze jeden test:

DROP TABLE Table1;
--Msg 3740, Level 16, State 2, Line 1
--Cannot drop the table 'Table2' because at least part of the table resides on a read-only filegroup.
DROP TABLE Table2;

Pierwsza tabela zostanie usunięta. Druga, ta z grupy plików tylko do odczytu, zostanie zachowana. Zainteresowanym polecam dokładnie wczytać się w treść komunikatu: nie można usunąć tabeli, ponieważ przynajmniej cześć tej tabeli znajduje się w grupie plików tylko do odczytu. Kluczowe jest tu słowo część.

Okresowe modyfikacje w tabeli tylko do odczytu

To, że przestawimy tabelę w stan tylko do odczytu nie znaczy wcale, że zostanie ona tam na wieki wieków. Dość powszechną praktyką jest zastosowanie tabeli tylko do odczytu jako pewnego rodzaju konfigurację systemu. Raz na jakiś czas administrator wprowadza drobne poprawki i chce, aby ta tabela dalej była tabelą tylko do odczytu. Problem w tym, że sam również nie może wprowadzać modyfikacji - otrzyma ten sam komunikat co inni. Musi tymczasowo wyłączyć atrybut tylko do odczytu:

ALTER DATABASE [ReadOnlyTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READWRITE;
ALTER DATABASE [ReadOnlyTest] SET MULTI_USER;

INSERT INTO Table2 DEFAULT VALUES;
Inne operacje...

ALTER DATABASE [ReadOnlyTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READONLY;
ALTER DATABASE [ReadOnlyTest] SET MULTI_USER;

Jeżeli wszystkie wykonywane operacje są już zapisane w postaci skryptów i ich wykonanie nie trwa długo, można cały proces nieco uprościć:

ALTER DATABASE [ReadOnlyTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READWRITE;

INSERT INTO Table2 DEFAULT VALUES;
Inne operacje...

ALTER DATABASE [ReadOnlyTest] MODIFY FILEGROUP [ReadOnlyTables] READONLY;
ALTER DATABASE [ReadOnlyTest] SET MULTI_USER;

Wydajność tabel tylko do odczytu

Zabezpieczanie danych przed usunięciem to nie jedyna zaleta tabel tylko do odczytu. SQL Server może z tego faktu skorzystać i pobierać dane w bardziej optymalny sposób. Co oznacza to enigmatyczne słowo optymalny?

Zakładanie blokad na dane

Przede wszystkim nie musi zakładać żadnych blokad na dane. W tradycyjnych tabelach blokady są konieczne, żeby użytkownik nie odczytywał danych które są modyfikowane, żeby różni użytkownicy nie modyfikowali tych samych danych jednocześnie czy też po to, żeby dwa odczyty w jednej procedurze zwracały ten sam zestaw wyników. Wydawać by się mogło, że SQL Server skorzysta z takich założeń i nie będzie zakładał żadnych blokad. Co prawda będą to blokady współdzielonego odczytu, czyli nieblokujące, ale jednak. Samo zakładanie i sprawdzanie blokad to też jakiś koszt. Zanim przejdziemy do sprawdzania blokad, zapamiętajmy identyfikator badanej tabeli:

--1 Blokada na całą tabelę
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM Table2;
EXEC sp_lock @@SPID;
COMMIT

Dla pokazanych poniżej przypadków identyfikator tabeli wynosi 261575970. Popatrzmy na przykładowe poziomy izolacji i blokady zakładane na tabele. Na początek poziom SERIALIZABLE:

--1 Blokada na całą tabelę
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM Table2;
EXEC sp_lock @@SPID;
COMMIT

Po uruchomieniu skryptu otrzymamy następujący rezultat:

Widać wyraźnie, że założona została jedna blokada na całą tabelę. Sposób dostępu został oznaczony jako S, czyli dostęp współdzielony. Pozwala to na pełną równoległość odczytów. Przejdźmy do innego poziomu izolacji, REPEATABLE READ:

--5 blokad: 3 x RID, PAG, TAB
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM Table2;
EXEC sp_lock @@SPID;
COMMIT

Tym razem blokad będzie więcej:

Spośród pięciu blokad można dostrzec blokadę tabeli w trybie IS (celowe współdzielenie zasobu), blokadę strony w trybie IS (strona 3:8) oraz trzy blokady na wiersze w trybie S. Warto wspomnieć, że tabela zawiera trzy wiersze i wszystkie trzy są pobierane (SELECT * FROM Table2). Dlaczego jest tak źle, skoro mogłoby być tak dobrze? Po co te blokady? Na szczęście w domyślnym poziomie izolacji, READ COMMITED, wszystko jest jak należy:

--Brak blokad, domyślny poziom izolacji
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
SELECT * FROM Table2;
EXEC sp_lock @@SPID;
COMMIT

Tym razem wynik jest satysfakcjonujący:

Jakieś blokady są, ale nie należy się nimi przejmować. Ta z wartością DB w kolumnie Type to blokada na bazę danych. Ta z wartością 1 w kolumnie dbid to widok dbo.spt_values w tabeli master. To trzecie, z identyfikatorem 32767 kolumnie dbid to baza danych z zasobami serwera. To tam przechowywane są metadane baz danych, tabel, widoków, funkcji, procedur i innych obiektów.

Wróćmy jednak do blokad i wydajności. Okazuje się, że takie same blokady będą zakładane na zwykłą tabelę. O ile nie zaczniemy modyfikować rekordów i nie będzie wymagane założenie blokad niewspółdzielonych - nie dostrzeżemy różnicy. Podpunkt ten dałem celowo - chciałem obalić dość powszechny mit. Być może w kolejnych wersjach SQL Server zostanie to zmienione. Do tego czasu warto pamiętać o trzymaniu się domyślnego poziomu izolacji.

Kopie zapasowe tabel tylko do odczytu

Miałem zamiar zakończyć wpis, ale chciałem uniknąć dramatycznego zakończenia. Żadnej poprawy wydajności? Żadnych zysków? Nie do końca. Założenia co do niezmienności danych możemy wykorzystać sami. Tabela, a w zasadzie grupa plików tylko do odczytu może być dość swobodnie traktowana podczas wykonywania kopii zapasowych. Na tyle swobodnie, że może być całkowicie pominięta. Wystarczy taką kopię zrobić raz po każdej zmianie, które z pewnością będą rzadkim zjawiskiem - w końcu to tabela tylko do odczytu a nie do zmieniania. Zaoszczędzone w ten sposób miejsce dyskowe można przeznaczyć na lepsze rzeczy. Powiem więcej - zaoszczędzony czas, bo mniej danych trzeba archiwizować, też można lepiej wykorzystać. Tego dobrze wykorzystanego czasu życzę. A bajka pod tytułem tabele tylko do odczytu kończy się dobrze i szczęśliwie.

Kategoria:Optymalizacja SQLSQL Server