SNAPSHOT

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

Miriam Antona

Software Engineer

SNAPSHOT

Кожну зміну зберігає база tempDB
Видно лише зафіксовані зміни до старту транзакції SNAPSHOT і власні зміни
Не видно змін інших транзакцій після старту транзакції SNAPSHOT
Читання не блокують запис, а записи — читання
Можливі конфлікти оновлення

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT — порівняння рівнів ізоляції

	Брудні читання	Неповторювані читання	Фантомні читання
READ UNCOMMITTED	так	так	так
READ COMMITTED	ні	так	так
REPEATABLE READ	ні	ні	так
SERIALIZABLE	ні	ні	ні
SNAPSHOT	ні	ні	ні

SNAPSHOT — приклад

Транзакція1

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       |

Транзакція2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Блокування немає!

SNAPSHOT — приклад

Транзакція1

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       |

Транзакція2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Блокування немає!

SNAPSHOT — підсумок

Переваги:

Висока узгодженість даних: запобігає брудним, неповторюваним і фантомним читанням без блокувань

Недоліки:

Зростає tempDB

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

Коли узгодженість критична й не бажані блокування

READ COMMITTED SNAPSHOT

Змінює поведінку READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

За замовчуванням OFF
Щоб увімкнути ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Якщо ON, кожен вираз READ COMMITTED бачить лише зафіксовані зміни до старту цього виразу
Конфліктів оновлення немає

READ COMMITTED SNAPSHOT — приклад

Транзакція1

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

COMMIT TRAN

Транзакція2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT — приклад

Транзакція1

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

COMMIT TRAN

Транзакція2

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)

Використовується для читання незафіксованих даних
READ UNCOMMITTED діє на все з'єднання, а WITH (NOLOCK) — на конкретну таблицю
Застосовуйте за будь-якого рівня ізоляції, коли треба читати незафіксовані дані з окремих таблиць

WITH (NOLOCK) — приклад

Початковий баланс рахунку 5 = $35 000

Транзакція1

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

Транзакція2

...

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

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

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

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