Autocommit to jeden z dwóch podstawowych trybów obsługi transakcji w przypadku silnika SQL Server i większości innych liczących się na rynku RDBMS. Mamy z nim do czynienia w sytuacji, kiedy flaga IMPLICIT_TRANSACTIONS jest dla danego połączenia wyłączona (SET IMPLICIT_TRANSACTIONS OFF).
Szereg wyrażeń T-SQL wykonywanych jest transakcyjnie. W takim przypadku wszelkie zmiany najpierw trafiają do loga transakcyjnego, a dopiero później - w drodze zatwierdzenia transakcji - przenoszone są do pliku bazy danych. Komendy transakcyjne to: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, TRUNCATE TABLE, UPDATE. Co ciekawe, komenda SELECT bywa w pewnym sensie transakcyjna w trybie implicit transactions - jej wywołanie skutkuje otwarciem transakcji, o ile transakcja nie została wcześniej założona. Podstawowa różnica między trybem autocommit (AC) a implicit transactions (IT) dotyczy oczywiście sposobu zatwierdzenia transakcji. W trybie AC dzieje się to automatycznie (o ile wyrażenie wykona się bez błędu), w trybie IT natomiast wymagane jest jawne zatwierdzenie lub wycofanie transakcji. Jeżeli to nie nastąpi, SQL Server w momencie zamykania połączenia wycofa wszystkie aktywne transakcje.
Tryb AC jest trybem domyślnym zarówno dla providera Native Client (OLE DB), jak i ODBC (nie jest to zgodne ze standardem ANSI). Naturalnie jawne otwarcie transakcji przy pomocy BEGIN TRAN zawiesza stosowania AC i od tego momentu transakcja musi być zatwierdzona lub wycofana przez użytkownika (aplikację).
Wybór trybu obsługi transakcji w sposób bezpośredni wpływa na ilości operacji wykonywanych na logu transakcyjnym (transaction log, TL), skalę tego wpływu postaram się poniżej zademonstrować. SQL Server nie dostarcza udokumentowanych poleceń do podglądana zawartości TL, co nie znaczy, że jest to niemożliwe. Wykorzystamy do tego celu funkcję
fn_dblog (SQL Server 2000, 2005). Funkcja wywołana w kontekście określonej bazy danych zwróci nam zawartość loga transakcyjnego tej bazy:
SELECT * FROM ::fn_dblog(NULL,NULL);
-- parametrami dla funkcji są startowy i końcowy LSN; wartości NULL
-- powodują, że zwrócony będzie cały log
Możemy więc łatwo obserwować, jakie wpisy i w którym momencie trafiły do TL. Komenda CHECKPOINT posłuży nam do oczyszczania loga między poszczególnymi próbami (polecenie to powoduje zapis brudnych stron na na dysk lub inne stable media i co za tym idzie - zwolnienie zapisów transakcji z loga).
Przygotowania środowiska do testów obejmuje stworzenie bazy testowej i prostej tabeli, włączenie trybu AC i wykonanie checkpointa:
CREATE DATABASE testowa05;
GO
USE testowa05;
GO
CREATE TABLE tabA (colA int);
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO
CHECKPOINT;
GO
W tym momencie w logu transakcyjnym bazy
testowa05 będą znajdować się dwa wpisy dotyczące operacji CHECKPOINT (inne wpisy także są możliwe, choć mało prawdopodobne). Następnie dodajmy do tabeli 10 rekordów:
INSERT INTO tabA(colA) VALUES(1);
INSERT INTO tabA(colA) VALUES(2);
INSERT INTO tabA(colA) VALUES(3);
INSERT INTO tabA(colA) VALUES(4);
INSERT INTO tabA(colA) VALUES(5);
INSERT INTO tabA(colA) VALUES(6);
INSERT INTO tabA(colA) VALUES(7);
INSERT INTO tabA(colA) VALUES(8);
INSERT INTO tabA(colA) VALUES(9);
INSERT INTO tabA(colA) VALUES(10);
W efekcie powyższych operacji ilość rekordów w logu transakcyjnym wzrosła do:
-- SELECT count(*) FROM ::fn_dblog(NULL,NULL);
51
W zależności od stanu serwera uzyskana ilość wpisów w logu może się różnić o kilka wierszy - wszystko zależy od sposobu wykonania insertów przez procesor kwerend. Po oczyszczeniu tabeli i wywołaniu checkpointa, wykonamy analogiczną operację, z jedną różnicą - 10 operacji INSERT obejmie jedna transakcja:
-- czyszczenie tabeli i loga
TRUNCATE TABLE tabA;
GO
CHECKPOINT;
GO
BEGIN TRAN;
INSERT INTO tabA(colA) VALUES(1);
INSERT INTO tabA(colA) VALUES(2);
INSERT INTO tabA(colA) VALUES(3);
INSERT INTO tabA(colA) VALUES(4);
INSERT INTO tabA(colA) VALUES(5);
INSERT INTO tabA(colA) VALUES(6);
INSERT INTO tabA(colA) VALUES(7);
INSERT INTO tabA(colA) VALUES(8);
INSERT INTO tabA(colA) VALUES(9);
INSERT INTO tabA(colA) VALUES(10);
COMMIT TRAN;
Aktualna ilość wpisów w TL to:
-- SELECT count(*) FROM ::fn_dblog(NULL,NULL);
31
czyli o 40% mniej w stosunku do poprzedniego sposobu. Różnica jest jak widać zasadnicza. Trzeba pamiętać, że zgodnie z implementowanym przez SQL Server protokołem WAL (Write-Ahead Logging Protocol), wszystkie wpisy w logu transakcyjnym związane z określoną stroną danych (8-kB data page) muszą zostać zapisane na stabilnym medium (np. dysku w pliku ldf) zanim zapisana zostanie ta strona (w pliku mdb). Ilość transakcji przekłada się więc wprost na ilość operacji IO wykonywanych przez log managera podczas tzw. flush request.
Z jednej strony mamy więc wydajność, która nakazywałaby zakładanie stosunkowo rozległych transakcji, z drugiej - konkurencyjność, która dużych transakcji nie lubi, gdyż stoją za nimi dłuższe i bardziej zachłanne blokady zasobów. Jak to zwykle bywa w bazach danych - wyjściem jest odpowiedni kompromis. Jeżeli jakiś proces wrzuca do danej tabeli dziesiątki tysięcy rekordów i w tym momencie dostęp do niej nie będzie nikomu potrzebny - jedna transakcja będzie bardzo dobrym rozwiązaniem. Nie jest prawdą, że log transakcyjny strasznie "spuchnie" w takiej sytuacji, co czasem mam nieszczęście słyszeć od SQL-twardzieli (pokazałem powyżej, że log bardziej puchnie właśnie w sytuacji odwrotnej - wielu transakcji).
Chciałbym na koniec zaznaczyć, że duża ilość małych transakcji nie jest w każdym przypadku remedium na problem blokad. Trzeba pamiętać, że każda taka transakcja to oddzielne żądanie najczęściej blokady wyłącznej na określonym zasobie (exclusive lock, X) - czasem dużo bardziej opłaca się jednokrotnie założyć blokadę na większy zakres ilościowy (nawet całą tabelę) - wtedy operacja potrwa dużo szybciej, a więc i docelowy zasób szybciej przestanie być potencjalnym przedmiotem rywalizacji procesów.
