MSSQL : Уровни изоляции в MS SQL - REPEATABLE READ

Сегодня попробую рассказать о блокировке REPEATABLE READ (повторяющееся чтение)

Начнем с определений:

Определение INTUIT
REPEATABLE READ – повторяющееся чтение. Повторное чтение строки возвратит первоначально считанные данные, несмотря на любые обновления, произведенные другими пользователями до завершения транзакции. Тем не менее на этом уровне изоляции возможно возникновение фантомов. Его установка реализуется командой:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Определение WIKIPEDIA
Уровень, при котором чтение одной и той же строки или строк в транзакции дает одинаковый результат. (Пока транзакция не завершена, никакие другие транзакции не могут модифицировать эти данные.)

Определение Microsoft
REPEATABLE READ - Указывает на то, что инструкции не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями, а также на то, что другие транзакции не могут изменять данные, читаемые текущей транзакцией, до ее завершения.
Разделяемые блокировки применяются ко всем данным, считываемым любой инструкцией транзакции, и сохраняются до ее завершения. Это запрещает другим транзакциям изменять строки, считываемые текущей транзакцией. Другие транзакции могут вставлять новые строки, соответствующие условиям поиска инструкций, содержащихся в текущей транзакции. При повторном запуске инструкции текущей транзакцией будут извлечены новые строки, что приведет к считыванию фантома. Учитывая то, что разделяемые блокировки сохраняются до завершения транзакции и не снимаются в конце каждой инструкции, степень совпадений ниже, чем при уровне изоляции по умолчанию READ COMMITTED. Используйте этот аргумент только в случае необходимости.

Ужасные определения! Фантомы, блокировки, транзакции... Намешана каша... Конечно я понимаю что значит эта блокировка, но когда пытаюсь вникнуть в эти определения, то крыша легонько едет. Давайте попробуем вместе разобраться, что к чему. Начнем с основного - cоздадим таблицу и заполним ее.

1. IF (OBJECT_ID('tmp_isolation_level') IS NOT NULL) DROP TABLE [dbo].[tmp_isolation_level]
2. GO
3. CREATE TABLE [dbo].[tmp_isolation_level] ( [rowid] int IDENTITY (1,1), [textvalue] varchar (100))
4. GO
5. INSERT INTO [dbo].[tmp_isolation_level] ([textvalue])
6. SELECT '111111111111'
7. UNION ALL
8. SELECT '222222222222'
9. UNION ALL
10. SELECT '333333333333'
11. UNION ALL
12. SELECT '444444444444'
13. UNION ALL
14. SELECT '555555555555'
15. UNION ALL
16. SELECT '666666666666'
17. GO

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

Поток 1	Поток 2	Комментарий
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO	SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO	Устанавливаем уровень изоляции. В данном примере нам не важно какой уровень изоляции будет на 2-м потоке, поэтому я использую значение по умолчанию или "READ COMMITTED"
BEGIN TRANSACTION	BEGIN TRANSACTION	Открываем транзакции
SELECT * FROM[tmp_isolation_level]	-	Читаем таблицу в первом потоке (тут все хорошо)
-	SELECT * FROM [tmp_isolation_level]	Читаем таблицу во втором потоке (тут все хорошо)
-	update [tmp_isolation_level] set [textvalue] = '343434343434' where [rowid] = 4	Пытаемся внести изменения в данные, во втором потоке. В этом месте 2-й запрос блокируется 1-м и ожидает завершения транзакиции 1-го потока
COMMIT TRANSACTION	-	Завершение транзакции 1-го потока разблокирует ресурс (таблицу). Записываются изменения 1-го потока
-	update [tmp_isolation_level] set [textvalue] = '343434343434' where [rowid] = 4	Выполняется разблокированный UPDATE
-	COMMIT TRANSACTION	Завершение транзакции 2-го потока, записываются изменения 2-го потока

Теперь предлагаю рассмотреть немного другой пример, тут мы проследим что произойдет если изменения будут вноситься в 1-м потоке.

Поток 1	Поток 2	Комментарий
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO	Устанавливаем уровень изоляции
BEGIN TRANSACTION	BEGIN TRANSACTION	Открываем транзакции
SELECT * FROM [tmp_isolation_level]	-	Читаем таблицу в первом потоке (тут все хорошо)
-	SELECT * FROM [tmp_isolation_level]	Читаем таблицу во втором потоке (тут все хорошо)
UPDATE [tmp_isolation_level] SET [textvalue] = '343434343434' WHERE [rowid] = 4	-	Пытаемся внести изменения в данные, в первом потоке, но данные являются заблокированными в этом месте 1-й запрос блокируется 2-м и ожидает завершения транзакиции 2-го потока
-	UPDATE [tmp_isolation_level] SET [textvalue] = '343434343434' WHERE [rowid] = 4	Хорошо, пробуем внести изменения данных во 2-м потоке. НО, для 2-го потока данные тоже заблокированы....
Сообщение 1205, уровень 13, состояние 45, строка 2 Транзакция (идентификатор процесса 222) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно.	-	В одном из потоков вылетает ошибка о взаимоблокировке. Т.е. 1-й и 2-й потоки заблокировали друг друга. Ядро базы обнаруживает этот факт, прерывает один из потоков, и завершает второй. Прерваный поток завершается с откатом изменений (ROLLBACK TRANSACTION)
-	COMMIT TRANSACTION	Прерывание 1-го потока разблокирует 2-й, а он в свою очередь завершается без ошибок.
-	Выполнение команд успешно завершено.	-

Вот так, вкратце работает REPEATABLE READ.

Успехов вам на поприще программирования SQL.

Источники:
Уровни изоляции в ядре СУБД (MSDN)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) (MSDN)
Настройка уровня изоляции транзакции (MSDN)
Уровень изолированности транзакций (Википедия)
Уровни изоляции и несогласованность данных (T-SQL.RU)
Уровни изоляции транзакций в SQL. Шпаргалка (Arbinada.com)
Уровни изоляции транзакций в SQL («Мир ПК» , № 07, 2009)