Livelli di isolamento delle transazioni

Transazioni e gestione degli errori in SQL Server

Miriam Antona

Software Engineer

Cos'è la concorrenza?

Concorrenza: due o più transazioni leggono/modificano gli stessi dati nello stesso momento.

Isolare la nostra transazione dalle altre

Livelli di isolamento delle transazioni

READ COMMITTED (predefinito)
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

SET TRANSACTION ISOLATION LEVEL 
    {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT}

Conoscere il livello di isolamento corrente

SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'UNSPECIFIED' 
    WHEN 1 THEN 'READ UNCOMMITTED' 
    WHEN 2 THEN 'READ COMMITTED' 
    WHEN 3 THEN 'REPEATABLE READ ' 
    WHEN 4 THEN 'SERIALIZABLE' 
    WHEN 5 THEN 'SNAPSHOT' 
END AS transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

| transaction_isolation_level |
|-----------------------------|
| READ COMMITTED              |

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Livello di isolamento meno restrittivo
Legge righe modificate da un'altra transazione non ancora confermata o annullata

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Livello di isolamento meno restrittivo
Legge righe modificate da altre transazioni non ancora confermate/annullate.

	Letture sporche	Letture non ripetibili	Letture fantasma
READ UNCOMMITTED	sì	sì	sì

Letture sporche

Saldo iniziale conto 5 = $35.000

Transazione1

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

ROLLBACK TRAN;

Transazione2

...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

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

Letture non ripetibili

Transazione1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
    SELECT * FROM accounts WHERE account_id = 5;

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

Transazione2

...

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

Letture non ripetibili

Transazione1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
    SELECT * FROM accounts WHERE account_id = 5;

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

    SELECT * FROM accounts WHERE account_id = 5;

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

Transazione2

...

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

Letture fantasma

Transazione1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00        |

Transazione2

...

BEGIN TRAN
INSERT INTO accounts
    VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN

Letture fantasma

Transazione1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts 
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00        |

SELECT * FROM accounts 
    WHERE current_balance BETWEEN 45000 AND 50000

| account_number       |...| current_balance |
|----------------------|---|-----------------|
| 55555555553939393939 |...| 45000,00        | Fantasma!
| 55555555552020202020 |...| 50000,00        |

Transazione2

...

BEGIN TRAN
INSERT INTO accounts
    VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN

READ UNCOMMITTED - riepilogo

Pro:

Più veloce, non blocca le altre transazioni.

Contro:

Consente letture sporche, non ripetibili e fantasma.

Quando usarlo?

Non vuoi essere bloccato dalle altre transazioni e accetti fenomeni di concorrenza.
Vuoi esplicitamente vedere dati non confermati.

Andiamo a praticare!

Transazioni e gestione degli errori in SQL Server