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 — пример

Transaction1

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       |

Transaction2

...

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 — пример

Transaction1

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       |

Transaction2

...

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 — пример

Transaction1

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

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT — пример

Transaction1

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

COMMIT TRAN

Transaction2

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

Transaction1

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

Transaction2

...

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

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

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

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