Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
SADT
Member

Откуда:
Сообщений: 216
Здравствуйте. Изучаю SQL Server 2008 и клиент-серверную технологию. Прочитал статью Уровни изоляции транзакций в SQL и не понял, почему при уровне изоляции Read Committed я получаю неправильные данные. Привожу чуть модифицированный код статьи и результаты запросов:

--Создаем БД для тестирования с наименованием Test

--Создаем таблицу для хранения тестовых данных
CREATE TABLE DevicesData(
DeviceId bigint not null,
Value bigint not null,
CONSTRAINT PK_DevicesData PRIMARY KEY(DeviceId))


--Заполняем таблицу тестовыми данными
TRUNCATE TABLE DevicesData;
DECLARE @n bigint;
DECLARE @max bigint;
SET @n = 0;
SET @max = 1000;
DECLARE @Values TABLE (v bigint not null);
WHILE (@n < @max)
  BEGIN
    INSERT INTO @Values (v)
      SELECT @n;
    SET @n = @n + 1;  
  END
INSERT INTO DevicesData (DeviceId, Value)
  SELECT @max*V1.v + V2.v, 0
    FROM @Values V1 CROSS JOIN @Values V2
SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, STDEV(Value) AS STD FROM DevicesData

--Запускаем ПЕРВУЮ транзакцию изменения данных
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
DECLARE @Id int
WHILE 1 = 1 
  BEGIN 
	SET @Id = 500000 * rand() 
	BEGIN TRANSACTION 
		UPDATE DevicesData 
			SET Value = Value + 1 
			WHERE DeviceId = @Id 
		UPDATE DevicesData 
			SET Value = Value - 1 
			WHERE DeviceId = 500000 + @Id 
	COMMIT 
	WAITFOR DELAY '00:00:00.010' 
  END

--Теперь запускаем ВТОРУЮ транзакцию с заданными уровнями изоляции (варьируем их от варианта к варианту)
--Вариант №1. Запускаем вторую транзакцию с уровнем изоляции упорядочиваемости
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
	SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, SUM(Value) AS Summa, COUNT(*) AS CNT FROM DevicesData
COMMIT
--Результаты
Minimum Maximum Average Summa CNT
-5 5 0 0 1000000

-- Вариант №2. Запускаем вторую транзакцию с уровнем уровнем изоляции повторяемого чтения
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
	SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, SUM(Value) AS Summa, COUNT(*) AS CNT FROM DevicesData
COMMIT
--Результаты
Minimum Maximum Average Summa CNT
-5 5 0 0 1000000

-- Вариант №3. Запускаем вторую транзакцию с уровнем изоляции чтения зафиксированных данных
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
	SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, SUM(Value) AS Summa, COUNT(*) AS CNT FROM DevicesData
COMMIT
--Результаты
Minimum Maximum Average Summa CNT
-5 5 0 -20 1000000

-- Вариант №4. Запускаем вторую транзакцию с уровнем изоляции чтения незафиксированных данных
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
	SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, SUM(Value) AS Summa, COUNT(*) AS CNT FROM DevicesData
COMMIT
--Результаты
Minimum Maximum Average Summa CNT
-5 5 0 3 1000000

-- Вариант №5. Запускаем вторую транзакцию с уровнем изоляции моментального снимка
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
	SELECT MIN(Value) AS Minimum, MAX(Value) AS Maximum, AVG(Value) AS Average, SUM(Value) AS Summa, COUNT(*) AS CNT FROM DevicesData
COMMIT
--Результаты
Minimum Maximum Average Summa CNT
-5 5 0 0 1000000


Почему вторая транзакция в варианте №3 (Read Committed) выдает результат -20? Из-за блокировки страниц? И почему ошибка с уровнем изоляции Read Uncommitted в варианте №4 выдает меньшее отклонение от истинного значения (нуля)?
30 сен 09, 18:40    [7726783]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Почему вторая транзакция в варианте №3 (Read Committed) выдает результат
> -20? Из-за блокировки страниц?

да, при read committed блокировка накладывается тогда, когда приходит
очередь прочитать страницу и, как только она прочитана, тут же
снимается.

> И почему ошибка с уровнем изоляции Read
> Uncommitted в варианте №4 выдает меньшее отклонение от истинного
> значения (нуля)?

при read committed сканирование страниц идет в порядке индекса.
поэтому, ошибка накапливается - вы ведь уменьшаете значения, которые
сканируются последними и увеличиваете те, что сканируются первыми.
соответственно, прочитывается заметно больше зафиксированных уменьшений.
а при read uncommitted сканирование идет по IAM. при этом, Logical Fragmentation
у таблицы высокая (у меня 99.44% получилось), поэтому ошибка нивелируется.
по-моему, так.

Posted via ActualForum NNTP Server 1.4

30 сен 09, 21:11    [7727264]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> по-моему, так.

угу. после:
alter index PK_DevicesData on DevicesData reorganize
и уменьшения логической фрагментации, ошибки в read uncommitted
и read committed примерно одинаковые получаются.

Posted via ActualForum NNTP Server 1.4

30 сен 09, 21:22    [7727286]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
Crimean
Member

Откуда:
Сообщений: 13148
имхо "виновато" хитрое наложение shared локов в read commited без указания хинтов
30 сен 09, 21:26    [7727292]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
SADT
Member

Откуда:
Сообщений: 216
Спасибо за ответы. Хочу уточнить:

daw
да, при read committed блокировка накладывается тогда, когда приходит
очередь прочитать страницу и, как только она прочитана, тут же
снимается.

То есть в случае чтения зафиксированных изменений, если на странице встречается хотя бы одна заблокированная запись, то все остальные записи этой страницы игнорируются при вычислении этой суммы, поскольку вся страница заблокирована?

daw
при read committed сканирование страниц идет в порядке индекса.
поэтому, ошибка накапливается - вы ведь уменьшаете значения, которые
сканируются последними и увеличиваете те, что сканируются первыми.

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

daw
а при read uncommitted сканирование идет по IAM. при этом, Logical Fragmentation
у таблицы высокая (у меня 99.44% получилось), поэтому ошибка нивелируется.
по-моему, так.

А где можно прочитать про IAM и что это - это карта распределения индекса? Я так понял, что в этом случае чтение таблицы идет в порядке физической вставки записей?

2) А что происходит при уровне изоляции повторяемого чтения? Этот
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
DECLARE @Id int
WHILE 1 = 1 
  BEGIN 
	SET @Id = 500000 * rand() 
	BEGIN TRANSACTION 
		UPDATE DevicesData 
			SET Value = Value + 1 
			WHERE DeviceId = @Id 
		UPDATE DevicesData 
			SET Value = Value - 1 
			WHERE DeviceId = 500000 + @Id 
	COMMIT 
	WAITFOR DELAY '00:00:00.010' 
  END
(первый) поток выполняется, затем запускается второй поток с уровнем изоляции повторяемого чтения и ...? Второй поток ждёт пока первый зафиксирует изменения (COMMIT), затем его выполнение приостанавливается до завершения работы второго потока, правильно? Иначе как тогда правильно посчитать сумму?
1 окт 09, 08:19    [7727891]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> То есть в случае чтения зафиксированных изменений, если на странице
> встречается хотя бы одна заблокированная запись, то все остальные записи
> этой страницы игнорируются при вычислении этой суммы, поскольку вся
> страница заблокирована?

нет, почему, все честно - ждем, пока блокировка снята не будет.

> Индекс идёт от максимума к минимуму, то есть получается, что при
> равномерном распределении генератора случайных чисел больше страниц с
> отрицательными значениями окажется незаблокированными?

не понял, что вы имеете в виду. какое-такое равномерное распределение?
у вас же строго: увеличиваются только значения value для DeviceId < 500000
а уменьшаются только те, для которых DeviceId >= 500000 - вы же
как раз такие условия в ваших апдейтах поставили.

> А где можно прочитать про IAM и что это - это карта распределения
> индекса? Я так понял, что в этом случае чтение таблицы идет в порядке
> физической вставки записей?

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


> 2) А что происходит при уровне изоляции повторяемого чтения? Этот
> (первый) поток выполняется, затем запускается второй поток с уровнем
> изоляции повторяемого чтения и ...? Второй поток ждёт пока первый
> зафиксирует изменения (COMMIT), затем его выполнение приостанавливается
> до завершения работы второго потока, правильно? Иначе как тогда
> правильно посчитать сумму?

при repeatable read shared блокировки держатся до конца транзакции.
соответственно, если первому потоку понадобится изменить уже
прочитанную вторым строку, ему придется ждать, пока не закончится
транзакция во втором.

в отличие от read committed, где блокировка будет снята сразу же,
как только страница прочитана. то есть, начинаем читать первую страницу -
накладываем блокировки (если не можем наложить - ждем), прочитали - блокировки
снимаем, начинаем читать вторую страницу и т.д.

допустим, в таблице у нас 500 страниц. мы, подсчитывая сумму, читаем в
данный момент 170 (в порядке индекса - т.е. в порядке двусвязного
списка страниц) страницу. в это время очередная транзакция в первой
сессии изменяет данные так, что увеличивается какое-то значение value
для DeviceId < 500000 и уменьшается для DeviceId >= 500000.
при этом увеличенное значение окажется где-то на одной из первых 250
страниц - может быть после 170, но может быть и до нее. а уменьшаемое
значение гарантированно оказывается на странице, следующей после
уже прочитанной (оно же где-то на одной из последних 250 страниц).
допустим, увеличенное значение оказалось на 80 странице (в порядке
индекса), а уменьшенное - на 400-ой. транзакция успешно выполнится:
мы уже прочитали 80 страницу и больше не держим на ней блокировку, а
400-ую еще не прочитали и еще не успели наложить на ее записи никаких
блокировок. тогда получится, что об увеличении значения на 80 странице
мы не узнаем и в сумме оно не учтется (страница уже прочитана и
возврата к ней не будет). с другой стороны, уменьшение значения на
400-ой странице учтется в сумме, когда мы будем читать эту страницу.
соответственно, пока мы не дошли до 250 страницы все уменьшения значений
будут учтены в сумме (все они происходят на страницах после 250-ой, которые
мы еще не прочитали). с другой стороны, увеличения значений будут учтены
далеко не все - некоторые из них произойдут на уже прочитанных страницах.
после же 250-ой мы уже не будем учитывать и некоторые уменьшения значений,
но зато уже увеличения мы вообще перестанем учитывать (все они будут
происходить на уже прочитанных страницах).
так и получится, что увеличений значений value будет учтено меньше, чем
уменьшений - отсюда и отрицательное значение sum.

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

фуххх. надеюсь, так понятней стало?

Posted via ActualForum NNTP Server 1.4

1 окт 09, 09:02    [7727949]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> А где можно прочитать про IAM и что это - это карта распределения
> индекса?

что-то есть в документации. если хочется более подробно и разжеванно,
читайте книги из серии "Inside MS SQL Server 2005" - конкретнее о физической
структуре бд рассказывается в "Inside MS SQL Server - Storage Engine".

про особенности сканирования кластерного индекса в режиме read uncommitted
рассказывается в этой серии статей:
https://www.sql.ru/articles/mssql/2007/011203ClusteredIndexScansPart1.shtml
https://www.sql.ru/articles/mssql/2007/011204ClusteredIndexScansPart2.shtml
https://www.sql.ru/articles/mssql/2007/011501ClusteredIndexScansPart3.shtml

про то, как накладываются блокировки при read committed такой топик был:
https://www.sql.ru/forum/actualthread.aspx?tid=384931

Posted via ActualForum NNTP Server 1.4

1 окт 09, 09:14    [7727976]     Ответить | Цитировать Сообщить модератору
 Re: Изучаю уровни изоляции транзакций в SQL: Непонятное поведение Read Committed  [new]
SADT
Member

Откуда:
Сообщений: 216
daw, большое спасибо за столь развернутый ответ, я понял.)
1 окт 09, 23:16    [7732171]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить