Serializable vs. Snapshot Isolation Level

Эта статья была опубликована на SQL.RU Другие опубликованные там статьи на тему MS SQL Server можно найти в блоге https://mssqlforever.blogspot.com/ Telegram-канал блога тут: https://t.me/mssqlhelp

По материалам статьи Craig Freedman: Serializable vs. Snapshot Isolation Level

Уровни изоляции транзакций Serializable и Snapshot обеспечивают согласованное чтение из базы данных. На любом из этих уровней изоляции транзакция может читать только зафиксированные данные. Более того, транзакция может читать одни и те же данные несколько раз, не заботясь о каких-либо параллельных транзакциях, вносящих изменения в эти же данные. Те нежелательные эффекты, которые были продемонстрированы в предыдущих статьях при Read Committed и Repeatable Read, на уровнях изоляции Serializable и Snapshot просто невозможны.

Обратите внимание, что я использовал фразу «не заботясь о каких-либо … вносящих изменения». Такой подбор слов является преднамеренным. На уровне изоляции Serializable ядро SQL Server накладывает блокировку диапазона ключей и удерживает её до окончания транзакции. Блокировка диапазона ключей гарантирует, что после того, как транзакция прочитает данные, никакая другая транзакция не сможет изменить эти данные (даже для вставки фантомных строк) до тех пор, пока не завершится транзакция, удерживающая блокировку. На уровне изоляции Snapshot ядро SQL Server не накладывает никаких блокировок. Таким образом, одновременная транзакция может изменять данные, которые уже прочитаны второй транзакцией. Вторая транзакция просто не замечает этих изменений и продолжает использовать старую версию данных.

Уровень изоляции Serializable основан на пессимистическом контроле параллелизма. Он гарантирует согласованность, предполагая, что две транзакции могут пытаться обновить одни и те же данные, и использует блокировки, чтобы гарантировать, что они этого не cделают, но (за счет уменьшения параллелизма) одна транзакция должна ждать завершения другой, и две транзакции могут заблокироваться. Уровень изоляции Snapshot основан на оптимистичном управлении параллелизмом. Это позволяет транзакциям выполняться без блокировок и с максимальным параллелизмом, но может произойти сбой и последующий откат транзакции, если две транзакции одновременно попытаются изменить одни и те же данные.

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

Рассмотрим, чем работа в Serializable и Snapshot отличаются с точки зрения обеспечиваемой ими изоляции транзакции. При Serializable всё довольно просто. Чтобы результат двух транзакций считался сериализуемым, они должны выполняться в некотором порядке по одной транзакции за раз.

Snapshot не гарантирует такой уровень изоляции. Несколько лет назад Джим Грей предложил прекрасный пример, демонстрирующий различия этих уровней. Представьте, что у нас есть мешок, содержащий смесь белых и чёрных шаров. Предположим, мы хотим запустить две транзакции. Одна транзакция перекрашивает каждый белый шар в чёрный шар. Вторая транзакция перекрашивает каждый чёрный шар в белый шар. Если мы запускаем эти транзакции с изоляцией Serializable, они будут исполняться поочерёдно. После первой транзакции останется мешок с шарами только одного цвета. После этого вторая транзакция изменит все эти шары на другой цвет. Есть только два возможных исхода: мешок только с белыми шарами или мешок только с чёрными шарами.

Если мы запускаем эти транзакции с изоляцией Snapshot, появляется третий результат, который невозможен при изоляции Serializable. Каждая транзакция может одновременно делать снимок мешка с шарами в том виде, в каком он был до внесения изменений. Теперь одна транзакция находит белые шары и перекрашивает их в чёрные шары. В то же время другие транзакции находят чёрные шары (но только те шары, которые были чёрными, когда мы сделали снимок, а не те шары, которые первая транзакция изменила на чёрные) и перекрашивает их в белый цвет. В результате, мешок будет содержать смесь белых и чёрных шаров. На самом деле, с этим уровнем изоляции мы правильно поменяли цвет каждого шара.

Следующий рисунок иллюстрирует эти различия:

Мы можем продемонстрировать подобное поведение средствами SQL Server. Обратите внимание, что Snapshot изоляция доступна только с SQL Server 2005 и должна быть явно включена для используемой базы данных:

alter database database_name set allow_snapshot_isolation on

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

create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')

Затем в первом сеансе начните транзакцию с уровнем изоляции Snapshot:

set transaction isolation level snapshot
begin tran
update marbles set color = 'White' where color = 'Black'

Теперь, прежде чем зафиксировать изменения, запустите во втором сеансе следующее:

set transaction isolation level snapshot
begin tran
update marbles set color = 'Black' where color = 'White'
commit tran

Наконец, зафиксируйте транзакцию в первом сеансе и проверьте данные в таблице:

commit tran
select * from marbles

Вот какой получился результат:

id color
-- -----
1  White
2  Black

Как можно видеть, шар 1, который изначально был чёрным, теперь стал белым, а шар 2, который изначально был белым, стал чёрным. Если вы попробуете тот же эксперимент с уровнем изоляции Serializable, одна транзакция будет ждать завершения другой, и, в зависимости от порядка, оба шара будут белыми или чёрными.