SNAPSHOT

Transazioni e gestione degli errori in SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Ogni modifica è salvata nel database tempDB
Vedi solo le modifiche confermate prima dell’inizio della transazione SNAPSHOT e le tue
Non vedi modifiche fatte da altre transazioni dopo l’avvio della transazione SNAPSHOT
Le letture non bloccano le scritture e viceversa
Possibili conflitti di aggiornamento

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - confronto livelli di isolamento

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	sì	sì	sì
READ COMMITTED	no	sì	sì
REPEATABLE READ	no	no	sì
SERIALIZABLE	no	no	no
SNAPSHOT	no	no	no

SNAPSHOT - esempio

Transazione1

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       |

Transazione2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Non è bloccata!

SNAPSHOT - esempio

Transazione1

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       |

Transazione2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Non è bloccata!

SNAPSHOT - riepilogo

Pro:

Buona coerenza dei dati: evita dirty, non-repeatable e phantom reads senza blocchi

Contro:

Crescita di tempDB

Quando usarlo?

Quando serve massima coerenza dei dati senza blocchi

READ COMMITTED SNAPSHOT

Cambia il comportamento di READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

OFF di default
Per usare ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Con ON, ogni istruzione READ COMMITTED vede solo modifiche confermate prima dell’avvio dell’istruzione
Nessun conflitto di aggiornamento

READ COMMITTED SNAPSHOT - esempio

Transazione1

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

COMMIT TRAN

Transazione2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT - esempio

Transazione1

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

COMMIT TRAN

Transazione2

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)

Usato per leggere dati non confermati
READ UNCOMMITTED vale per l’intera connessione / WITH (NOLOCK) per una tabella specifica
Usalo con qualsiasi livello di isolamento quando vuoi leggere dati non confermati da tabelle specifiche

WITH (NOLOCK) - esempio

Saldo iniziale conto 5 = $35.000

Transazione1

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

Transazione2

...

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

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

Ayo berlatih!

Transazioni e gestione degli errori in SQL Server