Рівні ізоляції транзакцій

Транзакції та обробка помилок у 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

Найменш обмежувальний рівень ізоляції
Читання рядків, змінених іншими транзакціями, без фіксації/скасування.

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes

Брудні читання

Початковий баланс рахунку 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        | Фантом!
| 55555555552020202020 |...| 50000,00        |

Транзакція2

...

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

READ UNCOMMITTED — підсумок

Переваги:

Може бути швидше, не блокує інші транзакції.

Недоліки:

Дозволяє брудні, неповторювані та фантомні читання.

Коли використовувати?

Не хочете блокувань, але приймаєте явища паралельності.
Вам потрібно бачити незафіксовані дані.

Давайте потренуємось!

Транзакції та обробка помилок у SQL Server