Уровни изоляции и несогласованность данных

by Alexey Knyazev 17. февраля 2011 00:31

Транзакции указывают уровень изоляции, который определяет степень, до которой одна транзакция должна быть изолирована от изменений ресурса или данных, произведенных другими транзакциями. Уровни изоляции описаны с точки зрения того, какие из побочных эффектов параллелизма разрешены (например, "грязные"; чтения или фантомные чтения).
Более подробно о всех уровнях изоляции можно прочитать в любой книге по SQL Server и на сайте Майкрософт - http://msdn.microsoft.com/ru-ru/library/ms189122.aspx

А в этой статье хочу поговорить об уровне изоляции READ UNCOMMITTED ( = подсказке NOLOCK ) и несогласованности данных, а так же о чудесах сиквела при уровне изоляции READ COMMITTED (уровень изоляции по умолчанию).

READ UNCOMMITTED - Указывает, что инструкции могут считывать строки, которые были изменены другими транзакциями, но еще не были зафиксированы. Установка этого параметра позволяет считывать незафиксированные изменения, которые называются чтением "грязных" данных. Значения в данных могут быть изменены и до окончания транзакции строки могут появляться и исчезать в наборе данных. Этот параметр действует так же, как и настройка NOLOCK всех таблиц во всех инструкциях SELECT в транзакции. Это наименьшее ограничение уровней изоляции.
Основное преимущество этого уровня изоляции - это то, что мы накладываем только блокировку стабильности схемы (Sch-S), а это блокировка совместима с большинством других, тем самым мы не блокируем объекты БД и при этом сами можем читать данные из объектов заблокированных монопольно. Расплата - "грязное" чтение...мы можем прочитать записи, которые ещё не зафиксированы и при последующем чтении этих данных может не оказаться в БД.
На SQL.RU есть хорошая статья, демонстрирующая проблемы грязного чтения - при использовании подсказки NOLOCK можно обработать одну и ту же строку несколько раз. Стоит быть в курсе того, как осуществляется чтение данных с подсказкой NOLOCK (или чтение с уровнем изоляции read uncommitted). Это не только вопрос о "грязном" чтении, или о данных в промежуточном состоянии транзакции; это скорее вопрос о возможности получения одной и той же строки дважды, или о том, что SQL Server может потерять позицию просмотра в течение сканирования.
Сейчас я покажу ещё один пример, который возможно навсегда отобьет желание использовать бездумно подсказку NOLOCK.

set nocount on

-- Создаем таблицу для тестов с дефолтами,
-- в качестве кластерного индекса uniqueidentifier
if object_id ( 'dbo.TestTable', 'U' ) is not null
drop table dbo.TestTable
go
create table dbo.TestTable ( i uniqueidentifier unique clustered default ( newid() )
                           , val char(1024) default replicate( 'A', 1024 )
                           )

-- Наполняем её данными - 100.000 строк
declare @i int
set @i = 0

while @i < 100000
begin
insert into dbo.TestTable default values
set @i = @i + 1
end
Убедимся, что записей в таблице действительно 100 тысяч
exec sp_spaceused 'dbo.TestTable'
Результат:
name         rows        reserved   data       index_size  unused
------------ ----------- ---------- ---------- ----------- --------
TestTable    100000      170952 KB  170016 KB  872 KB      64 KB
А теперь сам эксперимент, в 1-ой сессии запустим бесконечный цикл на вывод кол-ва записей в нашей таблице через count(*) с NOLOCK
declare @rows int
, @rows2 int
, @iter int

--Определяем сколько у нас в действительности записей и сохраняем в параметр @rows
select @rows = count(*), @iter = 0 from TestTable

while 1 = 1
begin
waitfor delay '00:00:00.300'
--Определяем сколько сейчас записей в таблице ("грязное" чтение)
set @rows2 = ( select count(*) from TestTable with ( nolock, index = 1 ) )
--Если кол-во отличается от действительности, то выводим сообщение
if @rows != @rows2
begin
print ' Кол-во записей в таблице = ' + cast( @rows2 as varchar(10) ) + ' и это отличается от действительности на ' + cast( @rows - @rows2 as varchar(10) )
set @iter = @iter + 1
--Чтоб сильно не насиловать сервер, выходим из цикла если уже есть >3 нужных нам записей
if @iter > 3
break
end
end 
Теперь во второй сессии запусти простенький скрипт на изменение нашей таблички:
update dbo.TestTable set i = newid()
Мы не изменяем кол-во записей в таблице, мы лишь меняем данные, но если переключиться на 1-ую сессию, где работает наш бесконечный цикл, то через какое-то время он прервется и мы получим результат схожий с этим:
 Кол-во записей в таблице = 100023 и это отличается от действительности на -23
 Кол-во записей в таблице = 100027 и это отличается от действительности на -27
 Кол-во записей в таблице = 99985 и это отличается от действительности на 15
 Кол-во записей в таблице = 99974 и это отличается от действительности на 26
Т.е. при уровне изоляции READ UNCOMMITTED мы прочитали в каких-то случаях меньше записей, а в каких-то одну и туже запись несколько раз.

Конечно это ужасно, но мы сами идём на подобный риск, если используем уровень изоляции READ UNCOMMITTED, об этом даже написано в BOL:
Транзакции, работающие на уровне READ UNCOMMITTED, не используют совмещаемые блокировки, чтобы предотвратить изменение считываемых текущей транзакцией данных другими транзакциями. Транзакции READ UNCOMMITTED также не блокируются монопольными блокировками, которые не позволили бы текущей транзакции считывать измененные другими транзакциями, но не зафиксированные строки. Установка этого параметра позволяет считывать незафиксированные изменения, которые называются чтением«грязных» данных. Значения в данных могут быть изменены и до окончания транзакции строки могут появляться и исчезать в наборе данных. Этот параметр действует так же, как и настройка NOLOCK всех таблиц во всех инструкциях SELECT в транзакции. Это наименьшее ограничение уровней изоляции.


Больше всего пугает поведение SQL Server`a при уровне изоляции READ COMMITTED (уровень изоляции по умолчанию), которое я проиллюстрирую ниже. Если верить тому же BOL, то:
READ COMMITTED
Указывает, что инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы. Это предотвращает чтение«грязных» данных.
...при выполнении операций считывания текущей транзакцией использует совмещаемые блокировки для предотвращения изменения строк другими транзакциями. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция.
Ну а теперь о чудесах:

set nocount on

-- Создаем таблицу для тестов уровня изоляции READ COMMITTED
if object_id ( 'dbo.TestTable2', 'U' ) is not null
drop table dbo.TestTable2
go
create table dbo.TestTable2 ( id int primary key clustered, val varchar(1024) );

--Заполняем данными - 1.000.000 записей
with cte(i)
as
(
select 1
union all
select 1 + i from cte where i < 1000000
)
insert into dbo.TestTable2
select i, replicate ('A', 1024)
  from cte
option ( maxrecursion 0 ) 
В таблице ровно 1 млн. записей. Теперь для эксперимента в 1-ой сессии запустим следующий скрипт:
select count(*) from dbo.TestTable2
Во второй сессии запускаем следующий код (делаем это быстро, пока результат 1-ой сессии не вернулся):
update dbo.TestTable2
set id = 5000000
where id = 1
Переключаемся на 1-ую сессию и видим "чудо": результат запроса select count(*) from dbo.TestTable2 при уровне изоляции READ COMMITTED вернул 1000001 запись. Пойдем дальше, запустим запрос из первой сессии повторно, а во второй сессии запускаем (так же, пока результат не вернулся):
update dbo.TestTable2
set id = 1
where id = 5000000
результат каунта = 999999, т.е. опять несогласованные данные

Причина такого поведения в том, что мы накладываем блокировки S (Shared) постранично, а не на всю таблицу (на таблице все это время, пока читаем кол-во записей, блокировка IS). И, когда мы читаем кол-во записей в таблице, то, прочитав данные с какой-то страницы полностью, мы её "отпускаем" и другая транзакция может произвести изменения на этой странице. Т.е. мы прочитали 1-ую запись на (предположим) 1-ой странице и пошли читать данные дальше, а другая сессия переместила (update) эту запись на другую страницу, которую мы прочитали позже, тем самым одна и та же запись была прочитана дважды. Во втором случаи мы просто не прочитали запись, т.к. сперва её не нашли на 1-ой странице, а к тому моменту, когда дошли до последней страницы, эту запись уже переместили в начало.

Ситуация правится повышением уровня изоляции до REPEATABLE READ или до SERIALIZABLE,
select count(*) from dbo.TestTable2 with (holdlock)
тогда блокировка S удерживается на всей таблице на все время, пока мы читаем кол-во записей, а наша вторая сессия с UPDATE будет ждать (будет заблокирована) завершения работы 1-ой сессии.
REPEATABLE READ 
Указывает на то, что инструкции не могут считывать данные, которые были изменены, но еще не зафиксированы другими транзакциями, а также на то, что другие транзакции не могут изменять данные, читаемые текущей транзакцией, до ее завершения.

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

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

Другие транзакции не могут изменять данные, считываемые текущей транзакцией, до ее завершения.

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

Блокировка диапазона устанавливается в диапазоне значений ключа, соответствующих условиям поиска любой инструкции, выполненной во время транзакции. Обновление и вставка строк, удовлетворяющих инструкциям текущей транзакции, блокируется для других транзакций. Это гарантирует, что если какая-либо инструкция транзакции выполняется повторно, она будет считывать тот же самый набор строк. Блокировки диапазона сохраняются до завершения транзакции. Это самый строгий уровень изоляции, поскольку он блокирует целые диапазоны ключей и сохраняет блокировку до завершения транзакции. Из-за низкого параллелизма этот параметр рекомендуется использовать только при необходимости. Этот параметр действует так же, как и настройка HOLDLOCK всех таблиц во всех инструкциях SELECT в транзакции. 


Так же можно включить версионность (появилась в SQL Server 2005) и использовать уровень изоляции SNAPSHOT.
SNAPSHOT 
Указывает на то, что данные, считанные любой инструкцией транзакции, будут согласованы на уровне транзакции с версией данных, существовавших в ее начале. Транзакция распознает только те изменения, которые были зафиксированы до ее начала. Инструкции, выполняемые текущей транзакцией, не видят изменений данных, произведенных другими транзакциями после запуска текущей транзакции. Таким образом достигается эффект получения инструкциями в транзакции моментального снимка зафиксированных данных на момент запуска транзакции.

Транзакции моментальных снимков не требуют блокировки при считывании данных, за исключением случаев восстановления базы данных. Считывание данных транзакциями моментальных снимков не блокирует запись данных другими транзакциями. Транзакции, осуществляющие запись данных, не блокируют считывание данных транзакциями моментальных снимков.

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

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

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

Транзакция, работающая с уровнем изоляции моментального снимка, может просматривать внесенные ею изменения. Например, если транзакция выполняет инструкцию UPDATE, а затем инструкцию SELECT для одной и той же таблицы, измененные данные будут включены в результирующий набор.

Tags: , , , ,

SQL Server

Комментарии (1) -

Alexey Knyazev
Alexey Knyazev Russia
24.01.2012 0:28:19 #

msdn.microsoft.com/ru-ru/library/ms190805.aspx - Эффекты параллелизма

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading