Уровни изоляции транзакций

Транзакции и обработка ошибок в SQL Server

Miriam Antona

Software Engineer

Что такое параллелизм?

Параллелизм: две или более транзакций, которые одновременно читают/изменяют общие данные.

Изолируйте свою транзакцию от других транзакций

Уровни изоляции транзакций

READ COMMITTED (по умолчанию)
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

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

Определение текущего уровня изоляции

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

Наименее ограничительный уровень изоляции
Чтение строк, изменённых другой транзакцией, которая ещё не была зафиксирована или откатана

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Наименее ограничительный уровень изоляции
Чтение строк, изменённых другими транзакциями, без фиксации/отката.

	Грязное чтение	Неповторяющееся чтение	Фантомное чтение
READ UNCOMMITTED	да	да	да

Грязное чтение

Исходный баланс счёта 5 = $35 000

Транзакция 1

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

ROLLBACK TRAN;

Транзакция 2

...

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

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

Неповторяющееся чтение

Транзакция 1

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

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

Транзакция 2

...

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

Неповторяющееся чтение

Транзакция 1

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        |

Транзакция 2

...

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

Фантомное чтение

Транзакция 1

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        |

Транзакция 2

...

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

Фантомное чтение

Транзакция 1

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        | Phantom!
| 55555555552020202020 |...| 50000,00        |

Транзакция 2

...

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

READ UNCOMMITTED — итоги

Преимущества:

Может работать быстрее, не блокирует другие транзакции.

Недостатки:

Допускает грязное, неповторяющееся и фантомное чтение.

Когда использовать?:

Когда блокировки нежелательны, а аномалии параллелизма допустимы.
Когда явно требуется чтение незафиксированных данных.

Давайте потренируемся!

Транзакции и обработка ошибок в SQL Server