Niveaux d'isolation des transactions
Transactions et gestion des erreurs dans SQL Server
Miriam Antona
Software Engineer
Qu'est-ce que la concurrence ?
Concurrence : deux transactions ou plus lisent/modifient en même temps des données partagées.
Isoler notre transaction des autres
Niveaux d'isolation des transactions
READ COMMITTED(par défaut)READ UNCOMMITTEDREPEATABLE READSERIALIZABLESNAPSHOT
SET TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT}
Connaître le niveau d'isolation actuel
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
- Niveau d'isolation le moins restrictif
- Lit des lignes modifiées par une autre transaction pas encore validée ni annulée
READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- Niveau d'isolation le moins restrictif
- Lit des lignes modifiées par d'autres transactions non encore validées/annulées.
| Lectures sales | Lectures non répétables | Lectures fantômes | |
|---|---|---|---|
| READ UNCOMMITTED | oui | oui | oui |
Lectures sales (dirty reads)
Solde initial du compte 5 = 35 000 $
Transaction1
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
ROLLBACK TRAN;
Transaction2
...
...
...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 30000,00 |
Lectures non répétables
Transaction1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
Transaction2
...
...
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000 WHERE account_id = 5;
COMMIT TRAN
Lectures non répétables
Transaction1
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 |
Transaction2
...
...
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000 WHERE account_id = 5;
COMMIT TRAN
Lectures fantômes
Transaction1
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 |
Transaction2
...
...
BEGIN TRAN
INSERT INTO accounts
VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN
Lectures fantômes
Transaction1
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 | Fantôme !
| 55555555552020202020 |...| 50000,00 |
Transaction2
...
...
BEGIN TRAN
INSERT INTO accounts
VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN
READ UNCOMMITTED – récapitulatif
Avantages :
- Plus rapide, ne bloque pas les autres transactions.
Inconvénients :
- Autorise les lectures sales, non répétables et fantômes.
Quand l'utiliser ?
- Vous ne voulez pas être bloqué par d'autres transactions et acceptez ces phénomènes de concurrence.
- Vous souhaitez explicitement consulter des données non validées.
Passons à la pratique !
Transactions et gestion des erreurs dans SQL Server
