SNAPSHOT

Transakce a zpracování chyb v SQL Serveru

Miriam Antona

Software Engineer

SNAPSHOT

Každá změna je uložena v databázi tempDB
Vidí pouze potvrzené změny provedené před zahájením transakce SNAPSHOT a vlastní změny
Nevidí žádné změny provedené jinými transakcemi po zahájení transakce SNAPSHOT
Čtení neblokuje zápis a zápis neblokuje čtení
Mohou nastat konflikty při aktualizaci

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT – porovnání úrovní izolace

	Špinavá čtení	Neopakovatelná čtení	Fantomová čtení
READ UNCOMMITTED	ano	ano	ano
READ COMMITTED	ne	ano	ano
REPEATABLE READ	ne	ne	ano
SERIALIZABLE	ne	ne	ne
SNAPSHOT	ne	ne	ne

SNAPSHOT – příklad

Transakce 1

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       |

Transakce 2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Není blokována!

SNAPSHOT – příklad

Transakce 1

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       |

Transakce 2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Není blokována!

SNAPSHOT – shrnutí

Výhody:

Dobrá konzistence dat: zabraňuje špinavým, neopakovatelným a fantomovým čtením bez blokování

Nevýhody:

Nárůst tempDB

Kdy použít?:

Když je konzistence dat nezbytná a nechceme blokování

READ COMMITTED SNAPSHOT

Mění chování READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

Ve výchozím nastavení OFF
Pro použití ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Nastaveno na ON: každý příkaz READ COMMITTED vidí pouze potvrzené změny provedené před zahájením tohoto příkazu
Nemohou nastat konflikty při aktualizaci

READ COMMITTED SNAPSHOT – příklad

Transakce 1

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

COMMIT TRAN

Transakce 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT – příklad

Transakce 1

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

COMMIT TRAN

Transakce 2

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)

Slouží ke čtení nepotvrzených dat
READ UNCOMMITTED platí pro celé připojení / WITH (NOLOCK) platí pro konkrétní tabulku
Použijte na libovolné úrovni izolace, když chcete číst nepotvrzená data z konkrétních tabulek

WITH (NOLOCK) – příklad

Původní zůstatek účtu 5 = 35 000 $

Transakce 1

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

Transakce 2

...

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

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

Pojďme cvičit!

Transakce a zpracování chyb v SQL Serveru