Некоторое время назад я участвовал в переписке по электронной почте, где люди обсуждали некоторое «странное» поведение SQL Server. Проблема возникала в SQL Server 2016 при использовании уровня изоляции по умолчанию — read committed. Сценарий был следующим:
- Создать таблицу с несколькими столбцами
- Пакет 1: В одном окне SSMS выполнить следующее (что занимает 10 секунд):
- Начать транзакцию
- Вставить 1000 строк в таблицу с задержкой WAITFOR DELAY 0.01 секунды между каждой вставкой
- Зафиксировать транзакцию
- Пакет 2: Во втором окне SSMS:
- Выполнить
SELECT *из таблицы
- Выполнить
«Странное» поведение заключается в том, что когда выборка «Пакета 2» завершается после того, как была заблокирована транзакцией «Пакета 1», она возвращает не все 1000 строк (даже несмотря на то, что «Пакет 1» завершился). Более того, в зависимости от того, когда была запущена выборка «Пакета 2» в течение 10 секунд выполнения «Пакета 1», «Пакет 2» возвращает разное количество строк. Такое поведение также сообщалось и в более ранних версиях SQL Server. Это легко воспроизвести в SQL Server 2016/2017 и можно воспроизвести во всех более ранних версиях с одним изменением конфигурации (подробнее чуть позже).
Кроме того, если в таблице создан кластерный индекс, каждый раз возвращаются все 1000 строк во всех версиях SQL Server.
Так почему же это странно? Многие ожидают, что все 1000 строк будут возвращаться каждый раз И что структура таблицы или версия SQL Server не должны иметь никакого значения.
К сожалению, это предположение неверно при использовании read committed. Уровень изоляции read committed гарантирует, что будут читаться только зафиксированные данные; он НЕ гарантирует, сколько зафиксированных данных будет возвращено, и подвержен проблемам непротиворечивого анализа (inconsistent analysis). Если вы хотите избежать непротиворечивого анализа, вам нужно повысить уровень изоляции (при использовании блокировок) или перейти на версионирование для read committed (или даже на изоляцию моментальных снимков).
Однако я согласен, что предположение разумно, хотя и неверно.
Определение требования, тем не менее, простое. SQL Server гарантирует, что будут прочитаны только зафиксированные данные, а не то, сколько их будет прочитано, и поэтому количество возвращаемых зафиксированных данных в этом случае может варьироваться. Более того, в зависимости от структуры таблицы, версии SQL Server и параметра конфигурации (который я уже дважды упомянул, но не объяснил, так как не хочу раскрывать суть истории…), количество возвращаемых строк будет сильно различаться.
Итак, что же происходит? Почему расхождение в количестве строк?
Потому что таблица является кучей.
«Странное» поведение проявляется, когда для кучи сразу выделяется экстент, из которого распределяются первые страницы данных. Когда сканер по порядку распределения для выборки начинает работу, он смотрит на байты PFS для 8 страниц в экстенте, чтобы увидеть, какие из них распределены. Они будут просканированы. В зависимости от того, когда начинается выборка в течение 10 секунд выполнения «пакета 1», сканер прочитает больше или меньше строк (и вернёт в результатах «пакета 2»), потому что больше или меньше битов 0x40 в байте PFS будут установлены, указывая, что страница в экстенте распределена.
Чтобы таблице сразу был выделен экстент, смешанные страницы должны быть отключены, что является значением по умолчанию для SQL Server 2016 и выше, поэтому «странное» поведение проявляется с небольшим количеством строк в SQL Server 2016. Однако, если вы включили флаг трассировки 1118, вы увидите это поведение во всех версиях. Вот почему некоторые сообщают о «странном» поведении в версиях, предшествующих SQL Server 2016.
Когда смешанные экстенты НЕ отключены (т.е. в версиях до SQL Server 2016, когда 1118 не включён), первые 8 распределённых страниц являются смешанными, поэтому сканер по порядку распределения должен собирать их по отдельности и видит их все (не вдаваясь в подробности синхронизации вокруг массива слотов для одной страницы на первой странице IAM в цепочке IAM/единице распределения таблицы). Таким образом, пока вставкой используется не более 8 страниц данных, все строки на них будут возвращены выборкой в этом примере.
Вот и всё, поведение является «преднамеренным» и совсем не изменилось, но оно, понятно, может сбивать с толку, если вы не знаете точно, как механизм выполнения выполняет выборку под капотом.
И если вы создадите кластерный индекс, «странное» поведение в этом примере не произойдёт в любой версии. Это связано с тем, что выборка задерживается на блокировке S(hare) строки для первой строки в кластерном индексе, и к тому времени, когда выборке предоставляется блокировка S, вставка завершена, и просмотр листовых страниц индекса собирает все 1000 строк.
Не думайте, что использование кластерного индекса останавливает «странное» поведение при использовании read committed; это не так. Однако он может уменьшить некоторые формы непротиворечивости. В этом старом посте блога я демонстрирую сценарий, при котором сканирование кластерного индекса возвращает четыре строки, когда существует только три строки, при использовании read committed. Кимберли также описывает и показывает «непротиворечивый анализ» в этих статьях:
Таким образом, подводя итог: изоляция read committed гарантирует, что читаются только зафиксированные данные; однако существует множество форм потенциальных проблем (известных как «непротиворечивый анализ»). Действительно полезно понимать, что это такое и что разрешают и не разрешают различные уровни изоляции, предоставляемые SQL Server. В дополнение к постам блога выше, ознакомьтесь с уроком MSDN под названием: Lesson 1: Understanding the Available Transaction Isolation Levels.
Комментариев нет:
Отправить комментарий