SNAPSHOT

Transakcje i obsługa błędów w SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Każda modyfikacja jest zapisywana w bazie tempDB
Widoczne tylko zatwierdzone zmiany sprzed rozpoczęcia transakcji SNAPSHOT oraz własne zmiany
Zmiany innych transakcji po jej rozpoczęciu są niewidoczne
Odczyty nie blokują zapisów i odwrotnie
Mogą wystąpić konflikty aktualizacji

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT – porównanie poziomów izolacji

	Brudne odczyty	Niepowtarzalne odczyty	Odczyty fantomowe
READ UNCOMMITTED	tak	tak	tak
READ COMMITTED	nie	tak	tak
REPEATABLE READ	nie	nie	tak
SERIALIZABLE	nie	nie	nie
SNAPSHOT	nie	nie	nie

SNAPSHOT – przykład

Transakcja1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transakcja2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Nie jest blokowana!

SNAPSHOT – przykład

Transakcja1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transakcja2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Nie jest blokowana!

SNAPSHOT – podsumowanie

Zalety:

Dobra spójność danych: zapobiega odczytom brudnym, niepowtarzalnym i fantomowym bez blokowania

Wady:

tempDB rośnie

Kiedy używać?:

Gdy spójność danych jest kluczowa i chcemy uniknąć blokad

READ COMMITTED SNAPSHOT

Zmienia zachowanie READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

Domyślnie OFF
Aby użyć ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Ustawione na ON: każda instrukcja READ COMMITTED widzi tylko zatwierdzone zmiany sprzed jej rozpoczęcia
Brak konfliktów aktualizacji

READ COMMITTED SNAPSHOT – przykład

Transakcja1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transakcja2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT – przykład

Transakcja1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transakcja2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

Służy do odczytu niezatwierdzonych danych
READ UNCOMMITTED dotyczy całego połączenia / WITH (NOLOCK) dotyczy konkretnej tabeli
Używany przy dowolnym poziomie izolacji, gdy potrzebny jest odczyt niezatwierdzonych danych z wybranych tabel

WITH (NOLOCK) – przykład

Pierwotne saldo konta 5 = 35 000 USD

Transakcja1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transakcja2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Czas na ćwiczenia!

Transakcje i obsługa błędów w SQL Server