Чтение зафиксированного снимка и уровня изоляции снимка

Question

Может ли кто-нибудь помочь мне понять, когда использовать уровень изоляции SNAPSHOT над READ COMMITTED SNAPSHOT в SQL Server?

Я понимаю, что в большинстве случаев READ COMMITTED SNAPSHOT работает, но не уверен, когда пойдет на изоляцию SNAPSHOT.

Спасибо

Answer 1

READ COMMITTED SNAPSHOT делает оптимистичные чтения и пессимистичные записи. По сравнению, SNAPSHOT делает оптимистичные чтения и оптимистичные записи.

Microsoft рекомендует READ COMMITTED SNAPSHOT для большинства приложений, которые требуют контроля версий строк.

Прочитайте эту превосходную статью Microsoft: Выбор уровней изоляции на основе управления версиями строк. Это объясняет преимущества и затраты обоих уровней изоляции.

А вот более тщательный: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

Answer 2

[![Таблица уровней изоляции][2]][2]

Смотрите пример ниже:

Чтение совершенного снимка

Измените свойство базы данных, как показано ниже

ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

Сессия 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1

Сессия 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 1

Результат - запрос в сеансе 2 показывает старое значение (1, ОДИН), поскольку текущая транзакция НЕ зафиксирована. Это способ избежать блокировки и чтения зафиксированных данных.

Сессия 1

COMMIT

Сессия 2

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

Результат - запрос в сеансе 2 не показывает строк, потому что строка обновляется в сеансе 1. Итак, мы снова видим зафиксированные данные.

Уровень изоляции снимка

Это новый уровень изоляции, который был доступен начиная с SQL Server 2005. Для этой функции необходимо внести изменения в приложение, поскольку оно должно использовать новый уровень изоляции.

Измените настройки базы данных, используя ниже. Нам нужно убедиться, что в базе данных нет транзакций.

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

Теперь нам также нужно изменить уровень изоляции соединения, используя ниже

Сессия 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2

Сессия 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 2

Результат - даже если мы изменили значение на 10, мы все равно увидим старую запись в сеансе 2 (2, ДВА).

Теперь давайте совершим транзакцию в сеансе 1

Сессия 1

COMMIT

Давайте вернемся к сеансу 2 и снова запустим select.

Сессия 2

SELECT *
FROM   DemoTable
WHERE i = 2

Мы все еще увидим запись, потому что сеанс 2 объявил транзакцию с изоляцией моментального снимка. Если мы не завершим транзакцию, мы не увидим последнюю запись.

Сессия 2

COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

Теперь мы не должны видеть строку, так как она уже обновлена.

См. SQL Authority, Safari Books Online

Answer 3

Ни одно сравнение Snapshot и Snapshot Read Committed не будет полным без обсуждения страшного исключения "конфликт обновления снимка", которое может произойти в Snapshot, но не Snapshot Read Committed.

В двух словах, изоляция моментальных снимков получает моментальный снимок зафиксированных данных в начале транзакции, а затем использует оптимистическую блокировку как для чтения, так и для записи. Если при попытке зафиксировать транзакцию окажется, что что-то еще изменило некоторые из этих же данных, база данных откатит всю транзакцию и выдаст ошибку, вызывающую исключение конфликта обновления снимка в вызывающем коде. Это связано с тем, что версия данных, затронутых транзакцией, в конце транзакции отличается от версии в начале транзакции.

Функция Snapshot Read Committed не страдает от этой проблемы, поскольку использует блокировку при записи (пессимистичные записи) и получает информацию о версии моментального снимка всех зафиксированных данных в статистике каждого оператора.

Возможность конфликтов обновления снимка, происходящих в Snapshot и NOT Snapshot Read Committed, является чрезвычайно существенной разницей между ними.

Answer 4

Все еще актуально, начиная с комментариев Билла, я прочитал больше и сделал заметки, которые могут быть полезны для кого-то еще.

По умолчанию отдельные операторы (включая "SELECT") работают с "зафиксированными" данными (READ COMMITTED), вопрос заключается в следующем: ждут ли они "простоя" данных и не дают другим работать при чтении?

Настройка через правый клик БД "Свойства -> Параметры -> Разное":

Параллелизм / Блокировка: Снимок подтвержден при считывании включен [по умолчанию выключен, должен быть включен]:

• Используйте SNAPSHOT для выбора (чтения), не ждите других и не блокируйте их.
• Работа с эффектами без изменения кода
• ALTER DATABASE SET READ_COMMITTED_SNAPSHOT [ON|OFF]
• ВЫБЕРИТЕ имя,is_read_committed_snapshot_on FROM sys.databases

Консистенция: Разрешить изоляции Snapshot [ по умолчанию выключен, спорно - OK выкл]:

• Разрешить клиенту запрашивать SNAPSHOT для операторов SQL (транзакций).
• Код должен запрашивать снимки "транзакции" (например, SET TRANSACTION...)
• ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION [ON | OFF]
• ВЫБЕРИТЕ имя,is_read_committed_snapshot_on FROM sys.databases

На вопрос: это не одно или другое между Read Committed Snapshot и Allow Snapshot Isolation. Это два случая снэпшота, и они могут быть включены или выключены независимо, а Allow Snapshot Isolation - немного более сложная тема. Разрешить изоляцию моментальных снимков позволяет коду продвинуться дальше, управляя землей моментальных снимков.

Проблема кажется очевидной, если подумать об одной строке: по умолчанию система не имеет копии, поэтому читатель должен ждать, если кто-то еще пишет, и писатель также должен ждать, если кто-то еще читает - строка должна заблокировать все время. Включение "Read Read Committed Snapshot On" активирует базу данных для поддержки "моментальных копий", чтобы избежать этих блокировок.

Рамблинг на...

По моему мнению, "Read Read Committed Snapshot On" должен быть "TRUE" для любых обычных баз данных MS SQLServer, и это преждевременная оптимизация, поскольку по умолчанию он выдает "FALSE".

Однако мне сказали, что блокировка одной строки ухудшается не только потому, что вы можете обращаться к нескольким строкам в разных таблицах, но и потому, что в SQL Server блокировки строк реализованы с использованием блокировок на уровне блоков (блокировка случайных строк, связанных с близостью хранилища), и что существует порог, когда множественные блокировки запускают блокировку таблицы - предположительно, более "оптимистичная" оптимизация производительности с риском блокирования проблем в загруженных базах данных.

Answer 5

Позвольте мне описать 2 момента, которые не были упомянуты.

Во-первых, давайте проясним, как использовать оба, потому что это не интуитивно понятно.

SNAPSHOT и READ_COMMITTED_SNAPSHOT — это два разных уровня изоляции.

SNAPSHOT — это уровень изоляции, который вы можете явно использовать в своей транзакции, как обычно:

      begin transaction
set transaction isolation level snapshot;
-- ...
commit

READ_COMMITTED_SNAPSHOT нельзя использовать таким образом. READ_COMMITTED_SNAPSHOT — это как параметр уровня базы данных, так и неявный/автоматический уровень изоляции. Чтобы использовать его, вам нужно включить его для всей базы данных:

      alert database ... set read_committed_snapshot on;

Что делает вышеуказанная настройка базы данных, так это то, что каждый раз, когда вы запускаете транзакцию следующим образом:

      begin transaction
set transaction isolation level read committed;
-- ...
commit

Если этот параметр включен, все транзакции READ_COMMITTED будут выполняться с уровнем изоляции READ_COMMITTED_SNAPSHOT . Это происходит автоматически, затрагивая все транзакции READ_COMMITTED, выполненные для базы данных, если для этого параметра установлено значение ON. Невозможно выполнить транзакцию с уровнем изоляции READ_COMMITTED, поскольку все транзакции с этим уровнем будут автоматически преобразованы в READ_COMMITTED_SNAPSHOT.

Во-вторых, вы не должны слепо использовать опцию READ_COMMITTED_SNAPSHOT.

Чтобы проиллюстрировать, какие проблемы это может создать, представьте, что у вас есть простая таблица событий, подобная этой:

      create table Events (
  id int not null identity(1, 1) primary key,
  name nvarchar(450) not null
  -- ...
)

И вы периодически опрашиваете его с помощью такого запроса:

      begin transaction
set transaction isolation level read committed; -- automatically set to read committed snapshot when this setting is ON on database level 
select top 100 * from Events where id > ${lastId} order by id asc; 
commit

Вышеупомянутый запрос не должен быть заключен с транзакцией и явным уровнем изоляции. READ_COMMITTED — это уровень изоляции по умолчанию, и если вы вызываете запрос, не заключая его в блок транзакции, он будет неявно выполняться в транзакции READ_COMMITTED.

Вы обнаружите, что на уровне изоляции READ_COMMITTED_SNAPSHOT автоматически увеличивающиеся значения идентификаторов могут иметь пробелы, которые появятся позже.

Вы можете легко имитировать это с помощью вставки следующим образом:

      begin transaction
insert into Events (name) values ('test 1');
waitfor delay '00:00:10'
commit

... с последующей обычной вставкой:

      insert into Events (name) values ('test 2');

Ваша функция опроса, вызванная в течение 10 секунд, вернет одну строку с идентификатором 2.

Следующий опрос после обновления lastId ничего не вернет. Строка с идентификатором 1 появится через 10 секунд.

Событие с идентификатором 1 будет фактически пропущено.

Этого не произойдет, если вы используете READ_COMMITTED без параметра автоматического продвижения READ_COMMITTED_SNAPSHOT.

Стоит понять этот сценарий. Это не связано с тем, что столбец IDENTITY не гарантирует уникальность. Это не связано с тем, что столбец IDENTITY не гарантирует строгой монотонности. Даже когда и уникальность, и строгая монотонность не нарушаются, вы все равно получаете пробелы — возможность увидеть коммиты с более высокими идентификаторами, прежде чем увидеть коммиты с более низкими идентификаторами.

При READ_COMMITTED этой проблемы не существует.

В разделе READ_COMMITTED вы также можете увидеть пробелы, т.е. транзакциями, которые откатились. Но эти пробелы будут постоянными - т.е. вы не пропускаете события, потому что они никогда не появятся снова. Т.е. вы не увидите, как более низкие идентификаторы снова появляются позже после того, как вы видели более высокие идентификаторы.

Прежде чем включать READ_COMMITTED_SNAPSHOT, разберитесь с описанной выше проблемой и ее последствиями.

Управление этой опцией находится в серой зоне ответственности разработчика и администратора базы данных. Если вы являетесь администратором, вы не должны использовать его вслепую, так как разработчики могли полагаться на семантику изоляции READ_COMMITTED при разработке приложения, и включение READ_COMMITTED_SNAPSHOT может нарушить эти предположения очень неявным образом, трудно найти ошибку.