Иллюстрация различных уровней изоляции транзакций SQL Server

Как известно SQL Server поддерживает 5 уровней изоляции транзакций:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

По умолчанию используется READ COMMITTED, а несколько особняком стоит SNAPSHOT.

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

Предположим, что у нас есть 2 транзакции, выполняющиеся одновременно и содержащие различные инструкии. Рассмотрим  примеры взаимного влияния таких транзакций.

Создадим таблицу

CREATE TABLE [dbo].[Transac](

[fid] [int] IDENTITY(1,1) NOT NULL,

[fvalue] [int] NOT NULL

) 

Создадим первичный ключ по полю fid и добавим в нее несколько записей

INSERT INTO [dbo].[Transac]([fvalue])

VALUES (1),(2),(3)

Пример 1: Чтение против обновления

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran UPDATE [dbo].[Transac] set fvalue = 10 where fid=1 WAITFOR DELAY '00:00:10' commit tran	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran SELECT * FROM [dbo].[Transac] where fid between 1 and 2 commit tran

Если запустить сначала транзакцию 1 и затем транзакцию 2, то выполнение второй транзакции будет приостановлено. Это справедливо не только для использованного в примере уровня READ COMMITTED, но и для более высоких уровней REPEATABLE READ и SERIALIZABLE. Следует заметить, что обратное действие (для его реализации придется перенести конструкцию WAITFOR в транзакцию 2 и выполнить ее первой) не приводит к видимой задержке, поскольку блокировка чтения будет снята сразу после получения данных:

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran UPDATE [dbo].[Transac] set fvalue = 10 where fid=1 commit tran	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran SELECT * FROM [dbo].[Transac] where fid between 1 and 2 WAITFOR DELAY '00:00:10' commit tran

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

1. Использование версионирования строк
2. Отключение разделяемой блокировки (shared lock) при выполнении запроса чтения

Для включения поддержки версий строк нужно выполнить следующую команду:

ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON

После этого поведение транзакций уровня READ COMMITTED будет изменено: вместо ожидания последней версии данных транзакция будет получать версию данных актуальную на момент выполнения и приведенный выше пример сможет выполниться без блокирования чтения.
Второй подход предполагает прямой отказ от использования shared locks при выполнени запроса на чтение. Добиться этого можно 2 спсобами:

1. Использовать хинт NO LOCK
2. Переключить уровень транзакции чтения на READ UNCOMMITTED

В первом случае запрос примет вид:

SELECT * FROM [dbo].[Transac] WITH (NOLOCK)
where fid between 1 and 2

Очевидно, что чтение незафиксированных (non committed) данных имеет определенные недостатки: например, считанные данные могут быть позднее откачены выполняющейся транзакцией или они могут представлять собой не окончательный результат работы.

Пример 2: Повторяющееся чтение с уровнем REPEATABLE READ

Добавим новое поле fmax в нашу таблицу и не забудем создать первичный ключ по полю fid

CREATE TABLE [dbo].[Transac](
 [fid] [int] IDENTITY(1,1) NOT NULL,
 [fvalue] [int] NOT NULL,
 [fmax] [int]
)

Поле fmax будет отмечать записи с наибольшим значением поля fvalue. Заполним таблицу значениями:

INSERT INTO [dbo].[Transac]([fvalue], [fmax])
VALUES (1,0),(2,0),(3,1)

Рассмотрим пример, когда мы хотим сначала изменить значение поля fvalue, а затем пересчитать значение поля fmax:

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran declare @max as int UPDATE [dbo].[Transac] SET fvalue = 10 where fid=1 SELECT @max=MAX(fvalue) from [dbo].Transac WAITFOR DELAY '00:00:10' UPDATE [dbo].[Transac] SET fmax = 0 UPDATE [dbo].[Transac] SET fmax = 1 where fvalue=@max commit tran	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran declare @max as int UPDATE [dbo].[Transac] SET fvalue = 15 where fid=2 SELECT @max=MAX(fvalue) from [dbo].Transac WAITFOR DELAY '00:00:10' UPDATE [dbo].[Transac] SET fmax = 0 UPDATE [dbo].[Transac] SET fmax = 1 where fvalue=@max commit tran

Последовательное выполнение этого примера приведет к deadlock, поскольку каждая из транзакций успеет внести изменение в таблицу и попытается выполнить операцию в режиме READ COMMITTED, которая не может быть завершена из-за внесенных изменений другой транзакцией.
Переключим транзакции в режим REPEATABLE READ:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

В данном режиме SQL Server предотвращает изменение данных конкурирующими транзакциями. В итоге, транзакция, выполняющаяся второй, не сможет внести изменения до завершения транзакции, выполняющейся первой и данные будут корректно посчитаны.
Следует отметить, что, если при одновременном выполнении каждая из транзакций выполнит только первую команду UPDATE, все равно произойдет deadlock, поскольку инструкция SELECT не сможет наложить блокировку на изменяемые записи.

Пример 3: Фантомное чтение с уровнем SERIALIZABLE

Ранее мы уже видели ситуацию, когда выполнение чтения в режиме READ COMMITTED снимает блокировку с данных сразу после получения данных, не дожидаясь завершения транзакции. Это создает проблему фантомного чтения в случаях, когда мы хотим посчитать некоторый агрегат и на основе него внести изменения в базу данных. Давайте для нашей базы данных выполним добавление новой записи со значением fvalue, увеличенным на 1 от максимального. В данном случае обе транзакции будут иметь одинаковый код:

Транзакция 1	Транзакция 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran declare @max as int SELECT @max=MAX(fvalue)FROM [dbo].[Transac] WAITFOR DELAY '00:00:10' INSERT INTO [dbo].[Transac] (fvalue) VALUES (@max+1) commit tran	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO begin tran declare @max as int SELECT @max=MAX(fvalue)FROM [dbo].[Transac] WAITFOR DELAY '00:00:10' INSERT INTO [dbo].[Transac] (fvalue) VALUES (@max+1) commit tran

При совеместном выполнении этих транзакций и первоначальном составе данных в таблице мы получим такой результат:

fid	fvalue
1	1
2	2
3	3
4	4
5	4

Проблема, как не трудно догадаться, состоит в том, что значение максимума устарело к моменту выполнения операции вставки. Для обеспечения целостности мы можем дать указание SQL Server обеспечить неизменность набора данных над которым работает транзакция:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Эффект будет состоять в том, что сторонняя транзакция не сможет добавить/изменить записи пока не завершиться текущая транзакция. Обратной стороной является возможность возникновения взаимной блокировки (deadlock) транзакций: после выполнения операций чтения, каждая из транзакций удерживает блокировку на добавление новых записей в таблицу, не позволяя завершиться другой транзакции. В итоге, одна из транзакций завершит свое выполнение, а вторая будет откачена механизмом устранения блокировок, но целостность данных будет сохранена.