Relacyjny system  SQL Serwer 2012 zachowuje kontrolę transakcji dla wszystkich zmian danych w każdej bazie danych. Ścisłe przestrzeganie kontroli transakcji zapewnia że integralność bazy danych nigdy nie będzie naruszona przez niekompletne transakcje, naruszenie ograniczeń, zakłócenie z innej transakcji lub przerwy serwisowe.

Zrozumienie transakcji

Transakcja jest logiczną jednostką pracy. Albo wszystkie zadania w jednostce zostaną wykonane albo żadne z nich.  W SQL Serwerze wszystkie zmiany danych w bazie danych odbywają się w kontekście transakcji. Inaczej mówiąc, wszystkie operacje które zapisują lub odczytują dane z bazy danych są traktowane jako transakcje. Należą do nich:

  • Wszystkie wyrażenia manipulacji danych (DML) jak INSERT, UPDATE i DELETE.
  • Wszystkie wyrażenia języka definicji danych (DDL) jak CREATE TABLE, CREATE INDEX.

Nawet zwykłe zapytanie SELECT jest typem transakcji w SQL Serwerze. Takie transakcje zwane są transakcjami tylko do odczytu (read-only). Ponieważ nie są one częścią transakcji DML ani DDL to nie będziemy o nich mówić. Terminy commit i rollback służą do opisu kontroli wyniku transakcji w SQL Serwerze. Kiedy praca transakcji została zatwierdzona to SQL Serwer kończy zmiany transakcji poprzez zatwierdzenie ich czyli commit. Jeżeli wystąpi nieprzewidziany błąd lub użytkownik zdecyduje się nie zatwierdzać transakcji to transakcja jest wycofana czyli rollback.

Właściwości ACID transakcji

Akronim ACID jest używany w terminologii baz danych do opisu właściwości transakcji. Te właściwości to:

  • Atomicity (atomowość) - każda transakcja jest atomową jednostką pracy, oznacza to, że wszystkie zmiany w transakcji powiodą się lub żadna z nich się nie powiedzie.
  • Consistency (spójność) - każda transakcja bez względu na to czy się powiedzie czy nie, pozostawia bazę danych w spójnym stanie zdefiniowanym przez wszystkie obiekty i ograniczenia bazy danych. Jeżeli powstaje stan niespójny to SQL Serwer wycofuje transakcje do stanu spójnego.
  • Isolation (izolacja) - każda transakcja jest niezależna od innych transakcji. Stopień izolacji różni się w zależności ot poziomu izolacji.
  • Durability (trwałość) - każda transakcja trwa przez przerwy w działaniu serwisu bazodanowego. Kiedy serwis jest uruchomiony ponownie to wszystkie zakończone transakcje są zapisywane (utrwalane) a wszystkie niezakończone transakcje są wycofywane a ich zmiany są usuwane.

SQL Serwer poprzez swoje mechanizmy zapewnia wszystkie wyżej wymienione właściwości transakcji. Do utrzymania atomowości, SQL Serwer traktuje każdą instrukcję DML lub DDL indywidualnie i nie zezwala na częściowe wykonanie żadnej z komend. Rozważmy np instrukcję UPDATE która aktualizuje 500 wierszy w tabeli w momencie rozpoczęcia transakcji. Komenda nie zakończy się dopóki wszystkie 500 wierszy nie zostanie zaktualizowane. Jeżeli jakieś zdarzenie przerwie aktualizację , SQL Serwer przerwie wykonywanie aktualizacji i cofnie zmiany poprzez ROLLBACK. Jeżeli w transakcji jest więcej niż jedna operacja to SQL Serwer nie pozwoli na zatwierdzenie transakcji poprzez COMMIT dopóki wszystkie operacje nie zostaną wykonane. (Jeżeli XACT_ABORT jest w stanie OFF, który jest domyślny to możemy wstawić kod który decyduje kiedy zatwierdzamy transakcję a kiedy cofamy zmiany.) Dla zachowania spójności, SQL Serwer zapewnia, że wszystkie ograniczenia w bazie danych są spełnione. Jeżeli przykładowo chcemy wstawić wiersz ze złym kluczem obcym to SQL Serwer wykryje, że ograniczenie jest naruszone i wygeneruje błąd. Pisząc instrukcje możemy dodać logikę decydującą kiedy transakcja jest zapisana a kiedy cofnięta. Dla zachowania izolacji, SQL Serwer zapewnia, że kiedy transakcja dokonuje modyfikacji w bazie danych to żaden ze zmodyfikowanych obiektów nie może być modyfikowany przez inną transakcję do momentu jej zakończenia. Innymi słowami zmiany transakcji są wyizolowane od działań innych transakcji. Jeżeli dwie transakcje chcą zmienić te same dane to jedna musi poczekać na zakończenie drugiej. SQL Serwer realizuje izolację transakcji poprzez blokowanie (LOCK) oraz wersjonowanie wierszy. SQL Serwer blokuje obiekty (np wiersze lub tabele) aby zapobiec ingerencji innych transakcji w działanie transakcji. Izolacja może się róznić w zależności jaki stopień izolacji został użyty dla transakcji. SQL Serwer zapewnia trwałość transakcji używając dziennika transakcyjnego bazy danych (transaction log). Każda zmiana w bazie danych jest pierw zapisywana o dziennika transakcji z oryginalnymi wartościami danych. Kiedy transakcja jest zatwierdzona i wszystkie kontrole spójności danych są potwierdzone to fakt, że transakcja została zatwierdzona jest zapisany w dzienniku. Np gdy serwer bazodanowy został niespodziewanie wyłączony zaraz po fakcie zatwierdzenia transakcji i zapisaniu tego do dziennika, to kiedy SQL Serwer zostanie wystartowany to transakcja zostanie wdrożona i wszystkie niezapisane zmiany zostaną zapisane.  Z drugiej strony, jeżeli serwer zostanie wyłączony przed zapisaniem potwierdzenia transakcji do dziennika to SQL Serwer po starcie wycofa wszystkie zmiany jakie dokonała transakcja.

Typy transakcji

SQL Serwer posiada dwa typy transakcji:

  • Transakcje systemowe - SQL Serwer zarządza wszystkimi tabelami systemowymi poprzez użycie transakcje systemowe. Te transakcje nie są kontrolowane przez użytkownika.
  • Transakcje użytkownika - te transakcje są tworzone przez użytkownika w trakcie zmiany danych a nawet czytania danych. Są one tworzone automatycznie, bezpośrednio lub pośrednio. Można przeglądać nazwy aktywnych transakcji w widoku dynamicznym sys.dm_tran_active_transactions. Domyślną nazwą transakcji użytkownika jest user_transaction. Można nadawać nazwy transakcjom poprzez bezpośrednie ich definiowanie.

Komendy transakcji

Komendy opisane w tej sekcji dotyczą definiowania jawnego transakcji i zarządzania transakcjami. Każda transakcja składa się z wyrażenia T-SQLBEGIN TRANSACTION, które wskazuje początek transakcji. Komenda ta może być również zapisana w skrócie jako BEGIN TRAN. Po tej komendzie można zadeklarować nazwę transakcji lecz jest to nieobowiązkowe. Transakcja musi być zakończona poprzez zatwierdzenie lub wycofanie zmian. Aby zatwierdzić transakcję używamy komendy COMMIT TRANSACTION, można również napisać: COMMIT TRAN, COMMIT WORK lub COMMIT. Aby wycofać transakcję używamy komendy ROLLBACK TRANSACTION lub ROLLBACK TRAN, ROLLBACK WORK lub ROLLBACK. Transakcje mogą być zagnieżdżone tzn, że można deklarować je w środku innej transakcji.

Poziomy i stany transakcji

Można wykryć poziom i stan transakcji używając dwuch funkcji systemowych:

  • @@TRANCOUNT - funkcja zwracająca poziom transakcji.
    • Poziom 0 oznacza, że ​​w tym momencie, kod nie jest w ramach transakcji.
    • Poziom >0 oznacza, że kod jest w aktywnej transakcji a liczba >1 oznacza poziom zagnieżdżenia transakcji.
  • XACT_STATE() - funkcja zwracająca stan transakcji.
    • 0 oznacza, że nie ma aktywnej transakcji.
    • 1 oznacza, że jest niezatwierdzona transakcja która może być zatwierdzona lecz poziom zagnieżdżenia transakcji nie jest podany.
    • -1 oznacza, że jest niezatwierdzona transakcja ale nie może ona być zatwierdzona z powodu wcześniejszego błędu krytycznego.

Te dwie funkcje komplementują się nawzajem. @@TRANCOUNT nie zwraca transakcji które nie mogą być zatwierdzone natomiast XACT_STATE() nie zwraca poziomu zagnieżdżenia transakcji.

Tryby transakcji

Istnieją trzy tryby transakcji użytkownika w SQL Serwerze:

  • Autocommit
  • Implicit transaction (niejawny)
  • Explicit transaction (jawya)

Są to tryby działania kodu używając transakcji w SQL Serwerze. Nie zmieniają one zachowań transakcji.

Tryb AUTOCOMMIT

W tym trybie każda pojedyncza zmiana danych lub instrukcja DDL języka T-SQL jest wykonywana w kontekście transakcji która jest automatycznie zatwierdzana jeżeli wykonanie instrukcji powiodło się lub jest odrzucana jeżeli wykonanie instrukcji zgłosiło błąd. Tryb ten jest domyślnym trybem zarządzania transakcjami w SQL Serwerze. W tym trybie nie używa się żadnych komend transakcji jak BEGIN TRAN, ROLLBACK TRAN czy COMMIT TRAN. Dalej idąc wartość funkcji @@TRANCOUNT nie jest normalnie wykrywana dla tych komend.Zmiany w bazie danych są automatycznie obsługiwane, wyrażenie po wyrażeniu, jak transakcje.

Tryb niejawny (Implicit)

W tym trybie kiedy zaczniemy wykonywać instrukcję DML, DDL lub zapytanie SELECT to SQL Serwer rozpocznie transakcję, zwiększy wartość @@TRANCOUNT, lecz nie zatwierdzi ani nie odrzuci automatycznie wyników transakcji. Trzeba użyć COMMIT lub ROLLBACK aby zakończyć transakcję, nawet jeżeli wszystkie instrukcje to SELECT. Aby włączyć ten tryb należy użyć komendy:

SET IMPLICIT_TRANSACTIONS ON;

Można użyć również polecenia które wykona również to wcześniejsze:

SET ANSI_DEFAULTS ON;

Zalety używania trybu niejawnego:

  • Można wycofać każdą transakcję po jej zakończeniu.
  • Można przechwycić błędy po wykonaniu komendy.

Wady używania trybu niejawnego:

  • Wszystkie blokady nałożone przez komendy są trzymane do zakończenia transakcji.
  • Trzeba ustawiać ten tryb dla każdej sesji.
  • Jeżeli zapomnimy zatwierdzić transakcji to możemy zostawić otwarte blokady.

Tryb jawny (EXPLICIT)

Tryb jawny włącza się kiedy użyjemy komendy BEGIN TRANSACTION. Po komendzie BEGIN wartość @@TRANCOUNT jest zwiększana o 1. Następnie wykonywane są komendy DML lub DDL i kiedy wszystko jest wykonane użyta zostaje komenda COMMIT lub ROLLBACK. Trybu jawnego można użyć w trybie niejawnym lecz powoduje to zagnieżdżenie transakcji (@@TRANCOUNT wzrasta z 0 do 2 po komendzie BEGIN TRAN). Nie jest to uważane za dobrą praktykę. Używając trybu jawnego musimy zadbać o przechwytywanie błędów i ich odpowiednią obsługę. Transakcje mogą obejmować więcej niż jeden wsad (batch). Dotyczy to transakcji jawnych i niejawnych (użycie instrukcji GO). Dobrą praktyką jest dbanie o to aby transakcja obejmowała tylko jeden wsad.

Transakcje zagnieżdżone

Jawne transakcje mogą być zagnieżdżone kiedy jedna transakcja ma w swoim ciele zapisaną inną transakcję jawną. Zachowanie COMMIT i ROLLBACK jest inne dla transakcji zagnieżdżonych. Przykładowo mamy dwie transakcje T1 i T2. T1 jest pierwszą transakcją która zaczyna się poprzez BEGIN TRAN T1. Powoduje to zwiększenie wartości @@TRANCOUNT = 1. Po tym uruchamiamy drugą transakcję komendą BEGIN TRAN T2. Wartość @@TRANCOUNT = 2. W tym momencie instrukcja COMMIT nie zatwierdza transakcji tylko zmniejsza @@TRANCOUNT na 1.  Dopiero drugi COMMIT zatwierdza całą transakcję kiedy @@TRANCOUNT = 0. Inaczej jest w przypadku wycofania transakcji. Wystarczy tylko jedna komenda ROLLBACK która wycofa wszystkie zagnieżdżone transakcje.

Nazywanie transakcji

Można nadawać nazwy jawnym transakcjom poprzez wstawienie nazwy na końcu komendy BEGIN TRAN. Nazwa transakcji musi być identyfikatorem SQL Serwera i zawierać nie więcej niż 32 znaki. Nazwa transakcji jest wyświetlana w widoku systemowym sys.dm_tran_active_transactions, oto przykład:

USE TSQL2012;
BEGIN TRANSACTION Tran1;
SELECT name FROM sys.dm_tran_active_transactions

Wynik:

name -------------------------------- Tran1

SQL Serwer zapisuje tylko nazwy transakcji zewnętrznych. Jeżeli używamy transakcji zagnieżdżonych to ich nazwy zostaną zignorowane. Nazywanie transakcji jest używane do umieszczania śladu w dzienniku transakcyjnym aby określić punkt w którym jedna lub więcej baz danych może być przywrócona. Kiedy transakcja jest zapisana w dzienniku transakcji bazy danych to ślad transakcji jest również zapisany jak na przykładzie:

USE TSQL2012;
BEGIN TRAN Tran1 WITH MARK;
-- <transaction work>
COMMIT TRAN; -- or ROLLBACK TRAN
-- <other work>

Aby przywrócić bazę danych do wyznaczonego ślady można uruchomić następujący kod:

RESTORE DATABASE TSQ2012 FROM DISK = 'C:SQLBackups\TSQL2012.bak'
WITH NORECOVERY;
GO
RESTORE LOG TSQL2012 FROM DISK = 'C:\SQLBackups\TSQL2012.trn'
WITH STOPATMARK = 'Tran1';
GO

Zwróć uwagę na kilka rzeczy o użyciu WITH MARK:

  • Musisz użyć nazwy transakcji z STOPATMARK.
  • Można dodać opis po klauzuli WITH MARK lecz SQL Serwer zignoruje go.
  • Można przywrócić stan do tuż przed transakcją z użyciem STOPBEFOREMARK.
  • Można odzyskać zbiór danych przez przywrócenie z WITH STOPATMARK lub STOPBEFOREMARK.
  • Można dodać RECOVERY do listy WITH lecz nie ma to efektu.

Dodatkowe opcje transakcji

Transakcje pozwalają na użycie wielu różnych opcji. Oto ważniejsze z nich:

  • Punkty przywracania (savepoints) - są to punkty w transakcji których można użyć do częściowego wycofania transakcji.  Punkty te definiuje się za pomocą komendy SAVE TRANSACTION <savepoint name>. Komenda ROLLBACK musi zawierać nazwę punktu przywracania inaczej wycofa całą transakcję.
  • Transakcje między bazami danych (Cross-database transactions) - transakcje mogą używać więcej niż jednej bazy danych na jednej instancji serwera bez żadnych dodatkowych deklaracji.
  • Transakcje rozproszone (Distributed transactions) - można użyć transakcji pomiędzy więcej niż jednym serwerem za pomocą serwerów połączonych (linked server).

Podstawy blokowania

Aby zachować izolację transakcji SQL Serwer stosuje szereg protokołów blokowania. Na poziomie podstawowym istnieją dwa podstawowe typy blokad:

  • Blokady współdzielone (shared locks) - używane przez sesje czytające dane.
  • Blokady wyłączne (exclusive locks) - używane w sesjach modyfikujących dane.

Istnieją bardziej zaawansowane typy blokad używane do specjalnych celów zwane: update lock, intent lock i schema lock. Kiedy sesja chce modyfikować dane to SQL Serwer będzie próbował zabezpieczyć dane włączając blokadę na odpowiednich obiektach. Te wyłączne blokady zawsze występują w kontekście transakcji, nawet jeżeli sesja nie wystartuje transakcji jawnej. Kiedy sesja ma wyłączną blokadę na obiekcie (np wiersz lub tabela) to żadna inna transakcja nie będzie mogła zmienić tych obiektów aż ta transakcja nie zostanie zatwierdzona lub wycofana. Wyjątkami są specjalne poziomy izolacji gdzie inne sesje nie mogą nawet czytać zablokowanych obiektów.

Kompatybilność blokad

Kiedy sesja tylko odczytuje dane to domyślnie SQL Serwer zakłada bardzo krótkie współdzielona blokady na zasobach. Dwie lub więcej sesji może odczytywać te same dane ponieważ blokady współdzielone są kompatybilne z innymi blokadami współdzielonymi. Jednakże kiedy sesja ma zasób zablokowany na wyłączność (exclusively), to żadna inna sesja nie może czytać ani modyfikować tego zasobu. Blokowanymi zasobami mogą być różne obiekty lecz najbardziej ziarnistym dla SQL Serwera jest wiersz tabeli. Blokowane są również strony lub całe tabele.

Blokowanie

Jeżeli dwie sesje chcą założyć wyłączną blokadę na ten sam zasób i jedna z nich dostanie przyzwolenie na nałożenie tej blokady to druga z sesji musi czekać aż pierwsza zwolni tę blokadę. W transakcjach blokady wyłączne są trzymane aż do końca transakcji, więc jeżeli pierwsza sesja wykonuje transakcję to druga musi czekać aż do końca wykonania całej transakcji. Blokada wyłączna może również blokować dostęp do czytania danych jeżeli sesja czytająca chce uzyskać blokadę współdzieloną. Dzieje się tak ponieważ blokada wyłączna jest niekompatybilna z blokadami współdzielonymi. W transakcjach używających poziomu izolacji READ COMMITED, blokady wyłączne są zwalniane gdy dane są odczytane i nie czekają na zakończenie transakcji.

Zakleszczenia

Zakleszczenie jest to impas blokowania więcej niż jednej sesji. Czasami sekwencje blokowania pomiędzy sesjami nie mogą być rozwiązane poprzez czekanie na zakończenie transakcji.  Dzieje się to ze względu na cykliczny związek pomiędzy dwoma lub więcej poleceń. SQL Serwer wykrywa te cykle jako zakleszczenie pomiędzy dwoma sesjami, zawiesza jedną z nich i zwraca błąd numer 1205 do klienta. Przykładowe zakleszczenie można wygenerować poprzez symulację dwóch sesji jako osobne okna w SSMS i wykonanie w nich w odpowiedniej kolejności komend:

  1. (Sesja 1) 
    USE TSQL2012;
BEGIN TRAN;
  2. (Sesja 2) 
    	
USE TSQL2012;
BEGIN TRAN;
  3. (Sesja 1) 
    	
UPDATE HR.Employees
SET Region = N'10004'
WHERE empid = 1;
  4. (Sesja 2) 
    UPDATE Production.Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;
  5. (Sesja 1) 
    	
UPDATE Production.Suppliers
SET Fax = N'555-1212'
WHERE supplierid = 1;
  6. (Sesja 2) 
    UPDATE HR.Employees
SET phone = N'555-9999'
WHERE empid = 1;
  7. Następuje zakleszczenie. Jedna z transakcji jest zakończona a druga zwraca kod błędu 1205. Aby przywrócić wszystko do porządku należy wycofać zakończoną transakcję poprzez użycie kodu: 
    IF @@TRANCOUNT > 0 ROLLBACK

Poziomy izolacji transakcji

Atomowość, spójność i trwałość są właściwościami transakcji które SQL Serwer zawsze egzekwuje tak samo bez żadnych kompromisów. Stopień izolacji transakcji zależy natomiast od poziomu ustawionego dla sesji. W czasie kiedy transakcja modyfikuje pewne dane, SQL Serwer nigdy nie pozwala na to aby te dane były modyfikowane przez inną transakcję dopóki pierwsza transakcja nie zostanie zakończona. Dlatego pewne blokady i zakleszczenia są zawsze możliwe kiedy transakcja modyfikuje dane. Transakcja zapisująca zawsze blokuje inne transakcje zapisujące i blokady wyłączne w jednej transakcji nie są nigdy kompatybilne z blokadami wyłącznymi innej transakcji. Blokowanie i zakleszczenia mogą być zwiększone lub zmniejszone w zależności od poziomu izolacji transakcji. Najczęściej używane poziomy izolacji to:

  • READ COMMITED - jest to domyślny poziom izolacji. Wszystkie transakcje czytające dane odczytują modyfikacje danych po ich zatwierdzeniu. Dlatego wszystkie zapytania SELECT podejmą próbę uzyskania blokady współdzielonej i każdy zasób który będzie modyfikowany przez inną sesję będzie miał blokadę wyłączną i zablokuje sesję READ COMMITED.
  • READ UNCOMMITED - ten poziom izolacji pozwala na odczyt niezatwierdzonych danych. To ustawienie usuwa blokadę współdzieloną nałożoną przez zapytanie SELECT i sesje odczytujące nie blokują sesji zapisujących. Jednakże SELECT może odczytać niezapisane dane które później w czasie transakcji zostaną wycofane poprzez ROLLBACK. Nazywa się to czytaniem brudnych danych (reading dirty data).
  • READ COMMITED SNAPSHOT - nie jest to osobny poziom izolacji tylko opcjonalny sposób używania READ COMMITED (domyślny dla SQL Azure). Posiada on następujące cechy:
    • W skrócie nazywany RCSI używa tempdb do przechowywania oryginalnej wersji zmienianych danych. Wersja ta jest przechowywana do czasu aż jest potrzebna dla sesji odczytujących dane w postaci oryginalnej. W rezultacie zapytanie SELECT nie potrzebuje więcej blokad współdzielonych do odczytu zatwierdzonych danych.
    • RCSI jest opcją bazy danych i jest ustawiana na stałe dla bazy danych.
    • RCSI jest innym sposobem implementacji poziomu READ COMMITED.
    • RCSI jest domyślnym poziomem dla Windows Azure Sql Database.
  • REPEATABLE READ - ten poziom izolacji jest ustawiany dla sesji. Gwarantuje on, że każde dane odczytane w transakcji mogą być odczytane później w tej samej transakcji bez zmian. Aktualizacja i usuwanie odczytanych danych jest zabronione. Blokady współdzielone są trzymane do końca transakcji. Jednakże w transakcji można odczytać nowe wiersze które zostały dodane po pierwszym czytaniu co zwane jest odczytem widmo (phantom read).
  • SNAPSHOT - ten poziom izolacji również używa wersjonowania danych w tempdb (jak RCSI). Jest on ustawiany jako trwała właściwość bazy danych i ustawiany dla transakcji. Transakcja używająca tego poziomu może powtórzyć każdy odczyt i nie zobaczy żadnych odczytów widmowych. Ponieważ używa wersjonowania to poziom ten nie potrzebuje blokad współdzielonych.
  • SERIALIZABLE - jest to najbardziej restrykcyjny poziom izolacji i jest ustawiany dla sesji. Na tym poziomie wszystkie odczyty są powtarzalne i nowe wiersze nie mogą być dodane do odczytywanych tabel.

Używając wskazówek można wymusić aby zapytanie zachowywało się jakby było na poziomie izolacji READ UNCOMMITED. Np za pomocą WITH (NOLOCK) lub WITH(READUNCOMMITED).

Ćwiczenia

I. Praca z trybami transakcji.

  1. Wykonaj kod zamieszczony poniżej demonstrujący działanie transakcji niejawnych. Wykonaj go krokowo podglądając wartość @@TRANCOUNT. 
    	
USE TSQL2012;
SET IMPLICIT_TRANSACTIONS ON;
SELECT @@TRANCOUNT; -- 0
SET IDENTITY_INSERT Production.Products ON;
-- Issue DML or DDL command here
INSERT INTO Production.Products(productid, productname, supplierid, categoryid,unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
SELECT @@TRANCOUNT; -- 1
COMMIT TRAN;
SET IDENTITY_INSERT Production.Products OFF;
SET IMPLICIT_TRANSACTIONS OFF;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted
  2. Wykonaj kod zamieszczony poniżej demonstrujący działanie transakcji jawnych. Wykonaj go krokowo podglądając wartość @@TRANCOUNT. 
    USE TSQL2012;
SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
SELECT @@TRANCOUNT; -- 1
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid,
categoryid, unitprice, discontinued)
VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
SELECT @@TRANCOUNT; -- 1
SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted
  3. Wykonaj kod zamieszczony poniżej demonstrujący działanie transakcji zagnieżdżonych. Wykonaj go podglądając wartość @@TRANCOUNT. 
    USE TSQL2012;
SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
SELECT @@TRANCOUNT; -- = 1
BEGIN TRAN;
SELECT @@TRANCOUNT; -- = 2
-- Issue data modification or DDL commands here
COMMIT
SELECT @@TRANCOUNT; -- = 1
COMMIT TRAN;
SELECT @@TRANCOUNT; -- = 0
  4. Wykonaj kod zamieszczony poniżej demonstrujący działanie transakcji zagnieżdżonych i ROLLBACK. Wykonaj go podglądając wartość @@TRANCOUNT. 
    USE TSQL2012;
SELECT @@TRANCOUNT; -- = 0
BEGIN TRAN;
SELECT @@TRANCOUNT; -- = 1
BEGIN TRAN;
SELECT @@TRANCOUNT; -- = 2
-- Issue data modification or DDL command here
ROLLBACK; -- rolls back the entire transaction at this point
SELECT @@TRANCOUNT; -- = 0

II. Praca z blokadami i zakleszczeniami.

  1. Wykonaj krokowo kod zamieszczony poniżej demonstrujący blokowanie sesji zapisujących dane. Otwórz dwa okna zapytania w SSMS. Kiedy blokady są niekompatybilne sesja niekompatybilnej blokady musi czekać w stanie blokowania. Sesja 1 założy blokadę wyłączną na zmienionym wierszu. Sesja 2 będzie próbowała zaktualizować ten sam wiersz. Ponieważ blokada jest utrzymywana aż do końca transakcji to sesja 2 musi poczekać aż do zakończenia transakcji w sesji 1.
    1. Sesja 1. 
      USE TSQL2012;
BEGIN TRAN;
    2. Sesja 2. 
      USE TSQL2012;
    3. Sesja 1. 
      UPDATE HR.Employees
SET postalcode = N'10004'
WHERE empid = 1;
    4. Sesja 2. 
      UPDATE HR.Employees
SET phone = N'555-9999'
WHERE empid = 1;
    5. W trakcie dalszej pracy sesji 1 sesja 2 jest blokowana.
    6. Sesja 1. 
      COMMIT TRAN;
    7. Koniec blokady sesji 2.
    8. Sesja 1. 
      -- Cleanup:
UPDATE HR.Employees
SET postalcode = N'10003'
WHERE empid = 1;
  2. Wykonaj krokowo kod zamieszczony poniżej demonstrujący blokowanie sesji czytającej dane przez sesję zapisującą. Otwórz dwa okna zapytania w SSMS.
    1. Sesja 1. 
      USE TSQL2012;
BEGIN TRAN;
    2. Sesja 2. 
      USE TSQL2012;
    3. Sesja 1. 
      UPDATE HR.Employees
SET postalcode = N'10005'
WHERE empid = 1;
    4. Sesja 2. 
      SELECT lastname, firstname
FROM HR.Employees;
    5. W trakcie dalszej pracy sesji 1 sesja 2 jest blokowana.
    6. Sesja 1. 
      COMMIT TRAN;
    7. Koniec blokady sesji 2.
    8. Sesja 1. 
      -- Cleanup:
UPDATE HR.Employees
SET postalcode = N'10003'
WHERE empid = 1;

III. Praca z poziomami izolacji.

  1. Wykonaj krokowo kod zamieszczony poniżej demonstrujący poziom izolacji READ COMMITED. Otwórz dwa okna zapytania w SSMS. Zauważ, że sesja 2 jest blokowana.
    1. Sesja 1. 
      USE TSQL2012;
BEGIN TRAN;
    2. Sesja 2. 
      USE TSQL2012;
SET TRANSACTION ISOLATION
LEVEL READ COMMITTED;
    3. Sesja 1. 
      USE TSQL2012;
BEGIN TRAN;
    4. Sesja 1. 
      UPDATE HR.Employees
SET postalcode = N'10006'
WHERE empid = 1;
    5. Sesja 2. 
      SELECT lastname, firstname
FROM HR.Employees;
    6. W trakcie dalszej pracy sesji 1 sesja 2 jest blokowana.
    7. Sesja 1. 
      COMMIT TRAN;
    8. Koniec blokady sesji 2.
    9. Sesja 1. 
      -- Cleanup:
UPDATE HR.Employees
SET postalcode = N'10003'
WHERE empid = 1;
  2. Wykonaj krokowo kod zamieszczony poniżej demonstrujący poziom izolacji READ UNCOMMITED. Otwórz dwa okna zapytania w SSMS.
    1. Sesja 1. 
      USE TSQL2012;
BEGIN TRAN;
    2. Sesja 2. 
      USE TSQL2012;
SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED;
    3. Sesja 1. 
      UPDATE HR.Employees
SET region = N'1004'
WHERE empid = 1;
    4. Sesja 2. 
      SELECT lastname, firstname, region
FROM HR.Employees
      Dane są zwracane!
    5. Sesja 1. 
      ROLLBACK TRAN;
    6. Sesja 2. 
      SELECT lastname, firstname, region
FROM HR.Employees;
    7. Sesja 1. 
      -- Cleanup:
UPDATE HR.Employees
SET region = N'1003'
WHERE empid = 1;
  3. Wykonaj kod zamieszczony poniżej demonstrujący poziom izolacji READ UNCOMMITED użyty za pomocą wskazówki. 
    SELECT lastname, firstname
FROM HR.Employees WITH (READUNCOMMITTED);

    SELECT lastname, firstname
FROM HR.Employees WITH (NOLOCK);
    Wskazówka WITH (NOLOCK) jest przestarzała i nie będzie więcej wspierana przez SQL Serwer.
  4. Wykonaj krokowo kod zamieszczony poniżej demonstrujący poziom izolacji READ COMMITTED SNAPSHOT. Otwórz dwa okna zapytania w SSMS.
    1. Sesja 1. 
      USE TSQL2012;
ALTER DATABASE TSQL2012 SET
READ_COMMITTED_SNAPSHOT ON;BEGIN TRAN;
    2. Sesja 2. 
      USE TSQL2012;
    3. Sesja 1. 
      UPDATE HR.Employees
SET postalcode = N'10007'
WHERE empid = 1;
    4. Sesja 2. 
      SELECT lastname, firstname, region
FROM HR.Employees;
      Dane są zwracane!
    5. Sesja 1. 
      ROLLBACK TRAN;
    6. Sesja 1. 
      -- Cleanup:
UPDATE HR.Employees
SET postalcode = N'10003'
WHERE empid = 1;

Podsumownie

  1. Wszystkie modyfikacje danych w Serwerze SQL odbywają się w kontekście transakcji. Wykonanie komendy ROLLBACK na dowolnym poziomie transakcji natychmiast wycofuje całą transakcję.
  2. Każda komenda COMMIT redukuje wartość @@TRANCOUNT o 1. COMMIT który zmniejsza ją do wartości 0 zatwierdza całą transakcję.
  3. SQL Serwer używa blokad aby wyegzekwować izolację transakcji.
  4. Pomiędzy dwoma lub więcej sesjami może wystąpić zakleszczenie.
  5. Poziom izolacji READ COMMITTED jest poziomem domyślnym.
  6. Poziom READ COMMITTED SNAPSHOT (RCSI) jest opcją domyślnego poziomu izolacji pozwalającą na odczytywanie danych które zostały już zapisane. jest on poziomem domyślnym dla SQL Azure.
  7. Poziom READ UNCOMMITTED pozwala na odczyt niezapisanych danych.