commit, checkpoint dwa bratanki …

Jakiś czas temu prowadziłem szkolenie i padło pytanie jak działa ‘recovery interval’ ? Będę o tym pisał jeszcze w cyklu dotyczącym sp_configure, ale warto chyba temat potraktować dokładniej…

Na początek kilka słów o tym jak SQL Server przetwarza transakcje:

1. BEGIN TRAN
2. zapytanie użytkownika
3. pobranie stron z dysku/cache
4. wpis do logu transakcyjnego o wykonanych modyfikacjach
5. modyfikacje są zapisywane na stronach w cache (blokady są dalej utrzymywane)
6. odpowiedź do użytkownika
7. polecenie COMMIT – powoduje zatwierdzenie zmian jakich dokonała transakcja – zmniejsza liczbę aktywnych transakcji o 1

Powyższe kroki przedstawiają w jaki sposób SQL Server zapewnia bezpieczeństwo transakcjom – wpis do logu transakcyjnego jest wykonywany zanim nastąpi faktyczna modyfikacja danych. Ale nawet jeśli wszystko powiedzie się dobrze to sama transakcja nie modyfikuje fizycznie danych na dysku! Dane na dysku są później na dwa sposoby:

1. aktywność wątku Lazy Writer, który przepisuje zmodyfikowane strony na dysk – tym samym zwalniając miejsce w buforach (robi to asynchronicznie względem wykonywanych transakcji)
2. polecenie CHECKPOINT, które przepisuje zmodyfikowane strony na dysk

Co dokładnie robi CHECKPOINT ?

“# Writes all dirty log and data pages to disk.
# Writes a record marking the end of the checkpoint to the log file.
# Writes the LSN of the start of this chain to the database boot page.”

Zgodnie z tym i tym CHECKPOINT jest uruchamiany w takich sytuacjach jak:

1. wykonanie polecenia CHECKPOINT ;-)
2. zatrzymanie usługi SQL Server – w każdym przypadku (services.msc, shutdown, SSCM,SSMS, net stop) poza wykonaniem polecenia SHUTDOWN WITH NOWAIT i zabiciem procesu
3. aktywny fragment logu przekracza ilość jaką SQL Server może odtworzyć w czasie wyspecyfikowanym za pomocą sp_configure ‘recovery interval’ (default: 0)
4. wykonano polecenie “minimalnie logowane” takie jak np. BULK na bazie, która pracuje w bulk-logged recovery model
5. dodano lub usunięto pliki bazy danych poleceniem ALTER DATABASE
6. wykonywany jest backup bazy danych
7. ustawiona jest opcja AUTO_CLOSE = ON, i odłącza sie ostatni zalogowany użytkownik – żeby poprawnie zamknąć bazę danych konieczny jest do wykonania CHECKPOINT
8. ustawienie grupy klastrowej na offline
9. simple recovery model: log transakcyjny jest w 70% pełen (tutaj BOL ma pewne nieścisłości więc traktujcie go z ostrożnością – w jednym miejscu podawane są jeszcze inne konieczne warunki np. wykonanie operacji minimalnie logowanej)

No dobrze ale po co nam ten CHECKPOINT, zapisze całą bazę na dysku i co tylko tyle ? Prawie ;-) Sam fakt przepisania wszystkich danych na dysk jest bardzo ważny w procesie recovery bazy danych, ponieważ jest on miejscem startowym od którego analizowany jest log transakcyjny w trakcie jego trwania. Dokładniej działa to w taki sposób:

1. Zlokalizowanie ostatniej wykonanej instrukcji checkpoint w danej bazie danych
2. Budowana jest tabela aktywnych transakcji (nie zatwierdzonych w momencie checkpointa)
3. Budowana jest tabela brudnych stron (ang. dirty pages) – pomagająca określić, które strony nie były przepisane na dysk z pamięci RAM
4. Rozpoczyna się faza REDO (2/3) – na podstawie tablicy brudnych stron ponownie wykonywane są transakcje i modyfikowane są strony danych (następuje tzw. rollforward transakcji)
5. Wersja Enterprise przenosi bazę w tryb Online oraz zakłada blokady na obiekty, na których będzie wycofywała transakcje
6. Rozpoczyna się faza UNDO(3/3) – następuje rollback nie zatwierdzonych transakcji
7. Wersje Standard/Workgroup/Web/Express przestawiają bazę w tryb Online; wersja Enterprise zwalnia blokady

Dla jasności – taki proces recovery ma miejsce przy montowaniu bazy danych – czyli np. w przypadku startu SQL Server. Wiedząc że sp_configure ‘recovery interval’ – odnosi się do czasu jaki SQL Server potrzebuje na przeprowadzenie procesu recovery opisanego powyżej można już bardziej świadomie zarządzać czasem recovery. Warto jeszcze zauważyć że w BOL możemy znaleźć taką informacje: “The default is 0, indicating automatic configuration by SQL Server. In practice, this means a recovery time of less than one minute and a checkpoint approximately every one minute for active databases.” – co oczywiście jak znamy z własnych doświadczeń prawie zawsze jest prawdą ;-)

Podsumowując

COMMIT zatwierdza transakcje, natomiast CHECKPOINT zapisuje zatwierdzone transakcje na dysku – dzieki czemu stanowi punkt wyjścia w procesie recovery bazy danych. Pozostaje wiele pytań typu a kiedy dokładnie dzieje się checkpoint (bo BOL ma pewne nieścisłości…), jak mogę zweryfikować kiedy wykonał się ostatni itd ale o tym w następnym odcinku ;-)