Решили на пилотном проекте применить технологию в коробке от Microsoft под названием Filetable. Суть технологии заключается в использовании таблицы с заранее предопределенной схемой, которая проецируется на диск для хранения неструктурированных данных. Возможны два варианта доступа к элементам хранения через таблицу и файловую шару, всю работу по «синхронизации» берет на себя СУБД. В нашем случае использовался транзакционный доступ через таблицу, причем вставка данных выполнялась параллельно. Схема таблицы представлена по ссылке Схема FileTable. В виде DDL инструкции она выглядит так:
DDL
create table dbo.IssueFiles2
(
stream_id uniqueidentifier not null default (newsequentialid())
,file_stream varbinary(max) null
,name nvarchar(255) not null
,path_locator hierarchyid primary key nonclustered not null
,parent_path_locator hierarchyid null-- as (case when path_locator.GetLevel()=(1) then null else path_locator.GetAncestor((1)) end)
,file_type nvarchar(255) -- as ((getfileextension(@filename)))
,cached_file_size bigint -- as (datalength(@file))
,creation_time datetimeoffset(7) not null
,last_write_time datetimeoffset(7) not null
,last_access_time datetimeoffset(7) not null
,is_directory bit not null
,is_offline bit not null
,is_hidden bit not null
,is_readonly bit not null
,is_archive bit not null
,is_system bit not null
,is_temporary bit not null
)
go
alter table dbo.IssueFiles2 add unique nonclustered(stream_id)
alter table dbo.IssueFiles2 add unique nonclustered(parent_path_locator, name)
alter table dbo.IssueFiles2 add constraint FK_IssueFiles2_paren_path_locator
foreign key(parent_path_locator) references dbo.IssueFiles2(path_locator)
go
В целом от FILETABLE можно абстрагироваться и вести речь только про таблицу определенной структуры, с некоторыми особенностями на которые стоит обратить внимание:
1. Схема таблицы изменению не подлежит, за исключением добавления индексов.
2. В таблице отсутствует кластерный индекс
3. В таблице присутствует FK на саму себя для обеспечения целостности древовидной структуры (Примечание: таблица содержит каталоги, внутри которых хранятся файлы и каталоги)
Продолжительное время проблем с таблицей не было – пользователи вставляли данные, пользователи выбирали данные. И как водится в таких историях, неожиданно наступил день, когда массово посыпались проблемы, а точнее взаимоблокировки. Как показал последующий разбор ситуации, таблица накопила несколько сотен тысяч записей, и увеличилось как количество пользователей работающих с системой, так и количество файлов, которые они загружали. Скажу сразу, проблема решена, но остался неприятный осадок и два аспекта, которые хотелось бы обсудить и разобраться в них. На данный момент «на руках» информация по взаимоблокировкам из лога ошибок рабочей среды и тестовая среда, где можно посмотреть внутренние механизмы и поставить эксперименты. Полностью повторить ситуацию и подтвердить или опровергнуть свои догадки не представляется возможным.
Итак, по порядку.
1. Причина возникновения взаимоблокировки
Информация о взаимоблокировках из журнала ошибок рабочей среды:
Из представленной выше информации видно, что взаимоблокировка возникает на простых операторах INSERT,
insert into dbo.IssueFiles(name, file_stream, path_locator) select @p_filename, @file, @path_locator
причем в качестве ресурса выступает некластерный индекс, созданный на PK поля path_locator типа hierarchyid.
Вставка данных двумя процессами T1 и T2 выглядит следующим образом:
1. T1 вставляет одну запись в кучу и накладывает эксклюзивную блокировку (X) на строку не кластерного индекса PK
2. T2 вставляет одну запись в кучу и накладывает эксклюзивную блокировку (X) на строку не кластерного индекса PK
3. T1 выполняет «поиск/сканирование» первичного ключа PK для того чтобы проверить ссылочную целостность для FK и запрашивает совмещаемую (S) блокировку на строку, на которой уже лежит эксклюзивная блокировка из T2.
4. T2 выполняет «поиск/сканирование» первичного ключа PK для того чтобы проверить ссылочную целостность для FK и запрашивает совмещаемую (S) блокировку на строку, на которой уже лежит эксклюзивная блокировка из T1.
5. Для продолжения работы каждый из процессов ожидает освобождения ресурса другим процессом, как результат взаимоблокировка.
Важно отметить, что данные вставляются разные по одной записи, но предок (FK) у них один и тот же – это каталог с месяцем года (возможные значения от 1 до 12). Он не модифицируется, не вставляется, а уже существует на момент вставки записей и готов к тому, чтобы два процесса наложили на него совмещаемые (S) блокировки без конфликтов. План запроса на тестовой среде, без нагрузки выглядит следующим образом:
PLAN
Нет ясного понимания, для каких целей СУБД обращается к записи, которая только что вставлена соседним процессом. Ведь для верификации FK достаточно выполнить index seek, по PK индексу, не затрагивая грязные данные. Единственное объяснение, которое у меня возникло – по каким-то неведомым причинам при вставке одной записи(!) оптимизатор для верификации ссылочной целостности вместо поиска предлагает использовать сканирование индекса. Возможно это влияние нагрузки, объема уже существующих данных или еще какие-то внешние факторы. Насколько я прав в своих рассуждениях, возможно истинные причины в чем-то другом и я не правильно интерпретирую взаимоблокировку? Повторить это поведение не удалось на тестовой БД без нагрузки. Даже при открытой транзакции repeatable read два процесса вставляют данные и без проблем читают одного и того же предка для верификации.
2. Решение проблемы
Второй момент, на котором хотелось бы остановиться – это способ решения проблемы. В таблице из коробки отсутствует кластерный индекс. Цель кластерного индекса, на мой взгляд – снизить время вставки данных в таблицу по сравнению с кучей, уменьшить время блокирования и использования ресурсов и как результат исключить взаимоблокировки. Единственное поле кандидат на эту роль – поле stream_id типа uniqueidentifier. Из схемы этого не видно, но практика показывает, что для заполнения этого поля используются значения из newsequentialid(), т.е. монотонно возрастающие. Действительно, создание кластерного индекса привело к успеху, взаимоблокировки исчезли, но неприятный осадок остался. Во-первых, проверка вставки на двух параллельных запросах на тесте не впечатлила. Запрос с sys.dm_tran_locks не показал уменьшения количества заблокированных ресурсов по сравнению с кучей (страницы, индексы и прочее). Возможно, я слишком «идеалистично» представлял себе этот процесс. Во-вторых, возникает резонный вопрос, а что будет дальше, если увеличится количество данных в таблице, увеличится нагрузка в виде количества пользователей и создаваемых записей? Взаимоблокировки вернутся? Пути отступления есть, что означает избежать проверки ссылочной целостности. Сделать это можно двумя способами, например, отказаться от хранения иерархий в таблице, т.е. хранить в пределах одной таблицы данные только за месяц, предок у всех записей будет null, но это потребует доработок. Второй вариант отключить все проверки на FILTABLE, но это чревато проблемами с целостностью всей таблицы в будущем.
Возможно, кто-то решал аналогичные проблемы или поделится своим мыслями относительно прочитанного, буду премного благодарен.
Резюмируя, опишу кратко свои вопросы:
1. По каким причинам на ваш взгляд возник deadlock? Действительно ли использовался index scan для верификации ссылочной целостности и почему? Или истинная причина в чем-то другом?
2. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки? Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?
Ограничения целостности — это хорошо, но бесперебойная работа всё же куда важнее. Ограничения обязательно нужны на девелоперской-тестовой базах (чтобы ловить ошибки разработчиков), но на боевой их срабатывание само по себе — инцидент, случающийся не каждый год (если, конечно, никто не пытается на живую отлаживать на ней заливку данных итп).
Поэтому если есть подозрения — имхо лучше отключить FK и не рисковать.
Для того, чтобы не пропустить ошибку, имхо уместно применить другой подход, я применял его однажды, работая с базой вообще без FK: я повесил туда набор ночных job-ов, делавших select-ы на "неправильные записи" и возвращавшие список ошибок. Это позволяло эффективно, без просадок в производительности контролировать целостность, не трогать старые записи (там, где их было уже нереально исправить) и оперативно находить и устранять источники новых проблем.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Softwarer, Вы писали:
... S>Для того, чтобы не пропустить ошибку, имхо уместно применить другой подход, я применял его однажды, работая с базой вообще без FK: я повесил туда набор ночных job-ов, делавших select-ы на "неправильные записи" и возвращавшие список ошибок. Это позволяло эффективно, без просадок в производительности контролировать целостность, не трогать старые записи (там, где их было уже нереально исправить) и оперативно находить и устранять источники новых проблем.
А что происходило в случае, когда "неправильные записи" приводили к проблемам уже днём-вечером, до того как выполнялись эти ночные job-ы? Надеюсь, не было ничего страшнее рассылки порно детям?
Re: [SQL Server] FILETABLE, deadlock и кластерный индекс
Мне кажется, ты не совсем верно интерпретировал происходящее.
O>Вставка данных двумя процессами T1 и T2 выглядит следующим образом: O>1. T1 вставляет одну запись в кучу и накладывает эксклюзивную блокировку (X) на строку не кластерного индекса PK O>2. T2 вставляет одну запись в кучу и накладывает эксклюзивную блокировку (X) на строку не кластерного индекса PK O>3. T1 выполняет «поиск/сканирование» первичного ключа PK для того чтобы проверить ссылочную целостность для FK и запрашивает совмещаемую (S) блокировку на строку, на которой уже лежит эксклюзивная блокировка из T2. O>4. T2 выполняет «поиск/сканирование» первичного ключа PK для того чтобы проверить ссылочную целостность для FK и запрашивает совмещаемую (S) блокировку на строку, на которой уже лежит эксклюзивная блокировка из T1. O>5. Для продолжения работы каждый из процессов ожидает освобождения ресурса другим процессом, как результат взаимоблокировка.
Мне кажется, что сначала должна происходить проверка ссылочной целостности, а уже потом вставка.
У тебя до оператора insert в той же транзакции ничего не происходит случайно? Или явной транзакции нету?
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Tigor, Вы писали:
T>Здравствуйте, Olaf, Вы писали:
T>Мне кажется, ты не совсем верно интерпретировал происходящее. T>... T>Мне кажется, что сначала должна происходить проверка ссылочной целостности, а уже потом вставка. T>У тебя до оператора insert в той же транзакции ничего не происходит случайно? Или явной транзакции нету?
План запроса, который я приложил показывает, что сначала идет вставка в таблицу, потом идет поиск по индексу, который ищет родителя, а далее оператор assert проверяет ограничение, на основании чего принимает решение о продолжении выполнения запроса или генерации ошибки.
Транзакция перед insert присутствует, уровень изоляции по умолчанию, после нее сразу идет insert. Перед самим insert выполняются запросы на select для заполнения переменных.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Tigor, Вы писали:
T>Мне кажется, что сначала должна происходить проверка ссылочной целостности, а уже потом вставка.
Это только кажется. SQL Server всегда вставляет данные, а потом выполняет проверки.
Re: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали:
O>Добрый день, коллеги!
O>Нет ясного понимания, для каких целей СУБД обращается к записи, которая только что вставлена соседним процессом. Ведь для верификации FK достаточно выполнить index seek, по PK индексу, не затрагивая грязные данные. Единственное объяснение, которое у меня возникло – по каким-то неведомым причинам при вставке одной записи(!) оптимизатор для верификации ссылочной целостности вместо поиска предлагает использовать сканирование индекса. Возможно это влияние нагрузки, объема уже существующих данных или еще какие-то внешние факторы. Насколько я прав в своих рассуждениях, возможно истинные причины в чем-то другом и я не правильно интерпретирую взаимоблокировку? Повторить это поведение не удалось на тестовой БД без нагрузки. Даже при открытой транзакции repeatable read два процесса вставляют данные и без проблем читают одного и того же предка для верификации.
А перед insert что выполняется? Особенно интересует как вычисляется path_locator.
Может сам SQL Server выполняет проверку path_locator, обращаясь для этого к соседям?
Ну или совсем странный вариант — выполняется scan из-за устаревшей статистики.
Можно ли получить реальный план запроса при падении? или это даст слишком большую нагрузку?
Re[3]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали:
O>План запроса, который я приложил показывает, что сначала идет вставка в таблицу, потом идет поиск по индексу, который ищет родителя, а далее оператор assert проверяет ограничение, на основании чего принимает решение о продолжении выполнения запроса или генерации ошибки.
Да. Это я затупил. На самом деле с учетом FK ключа на ту же самую таблицу (то есть и на ту же самую строку, в теории), это единственный вариант.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, gandjustas, Вы писали: G>А перед insert что выполняется? Особенно интересует как вычисляется path_locator. G>Может сам SQL Server выполняет проверку path_locator, обращаясь для этого к соседям?
Процедура выглядит так:
SP
create procedure dbo.hsp_PutAndRenameDoubleIssueFiles(@p_filename nvarchar(255), @file varbinary(max)) as begin
set nocount on;
begin try
declare @year varchar(4) = cast(year(getdate()) as varchar(4))
declare @month varchar(2) = cast(month(getdate()) as varchar(2))
declare @month_path_locator hierarchyid
declare @path_locator hierarchyid
-- Возьмем локатор каталога МЕСЯЦselect @month_path_locator = path_locator
from dbo.IssueFiles t1
where name = @month
and exists(
select 1
from dbo.IssueFiles t2
where t2.name = @year and t2.path_locator = t1.parent_path_locator
)
if (@month_path_locator is null)
raiserror('Не найден каталог %s/%s для вставки файла %s', 16, 1, @year, @month, @p_filename)
-- Определяем положение узла в иерархии set @path_locator = @month_path_locator.ToString() +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' +
convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/'
begin tran
begin try
insert into dbo.IssueFiles(name, file_stream, path_locator)
select @p_filename, @file, @path_locator
end try
begin catch
-- Если был дубликат, удаляем и делаем повторную попыткуif (error_number() = 2627)
begin
delete from dbo.IssueFiles where name = @p_filename and parent_path_locator = @month_path_locator
insert into dbo.IssueFiles(name, file_stream, path_locator)
select @p_filename, @file, @path_locator
end
else
throw;
end catch
commit tran
end try
begin catch
if (@@trancount > 0)
rollback tran;
throw;
end catch
end
go
Скажу сразу количество дубликатов, которые встречаются при вставке меньше 1% от общего числа.
Поле parent_path_locator вычисляемое persisted, т.е. определяется на основе вставляемого значения path_locator. Тип hierarchyid представляет из себя CLR тип, и в целом из значения path_locator можно извлечь предка через GetAncestor. Формула для поля — (case when [path_locator].[GetLevel]()=(1) then NULL else [path_locator].[GetAncestor]((1)) end). Именно на нем висит FK. G>Ну или совсем странный вариант — выполняется scan из-за устаревшей статистики. G>Можно ли получить реальный план запроса при падении? или это даст слишком большую нагрузку?
Возможно вы правы, устарели статистики. Сейчас думаю, как безболезненно повторить ситуацию и извлечь реальный план.
Re[3]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали:
O>Здравствуйте, gandjustas, Вы писали:
G>>А перед insert что выполняется? Особенно интересует как вычисляется path_locator. G>>Может сам SQL Server выполняет проверку path_locator, обращаясь для этого к соседям?
O>Скажу сразу количество дубликатов, которые встречаются при вставке меньше 1% от общего числа.
А уровень изоляции какой? Repeatable read? Не висят ли блокировки первого селекта до конца транзацкии?
Re[4]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали: O>Резюмируя, опишу кратко свои вопросы: O>1. По каким причинам на ваш взгляд возник deadlock? Действительно ли использовался index scan для верификации ссылочной целостности и почему? Или истинная причина в чем-то другом? O>2. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки? Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?
Решил пойти от обратного, т.е. выдвинув предположение, что причиной взаимоблокировки ожидания при чтении PK индекса, является операция сканирования индекса, заставил оптимизатор построить план со сканированием индекса. Цель одна – получить deadlock граф, сравнить с предыдущим для подтверждения или опровержения гипотезы. Для этого достаточно добавить опцию option (merge join) в insert — insert into dbo.IssueFiles3(name, file_stream, path_locator) select @p_filename, @file, @path_locator option(merge join).
Несмотря на то, что никакие данные не соединяются при вставке, а используются просто переменные, подсказка применяется ко всему запросу, в том числе и для верификации данных. Замена операции соединения с Nested Loop на Merge, привела к сканированию индекса, что собственно и требовалось. Далее, запустил 4 экземпляра SQLQueryStress, предварительно указав в каждом использовать 50 итераций в 100 потоков. Данная нагрузка является однобокой и полностью не воспроизводит рабочую, т.к. выполняется вставка только в одну таблицу, но задача заключается в гарантированном получении взаимоблокировки. Несмотря на все старания, потребовалось 15 минут для отлова. Реальный план запроса, полученный через sqlhandle выглядит следующим образом, что подтверждает использование скана:
Она превратилась во взаимоблокировку преобразования, причем на уровне страницы. Т.е. при вставке в каждом из процессов создается блокировка с намерением на страницу (IX) и эксклюзивная (X) блокировка на строку. Сканирование индекса приводит к тому, что процесс пытается наложить совмещаемую блокировку (S) на страницу. В целом это нормально иметь на одном ресурсе S и IX блокировки, которые сводятся к блокировке SIX Но проблема в том, что SIX блокировки несовместимы с IX блокировками, которые наложены вторым процессом. По этой же причине второй процесс не может получить SIX блокировку, в результате взаимоблокировка.
В общем, идея с тем, что причина взаимоблокировки заключается в сканировании индекса, в первоначальной постановке проблемы, не подтвердилась. Что это было и как докопаться до истины остается загадкой.
Re[3]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Tigor, Вы писали:
T>А как ты определил, что проблемы происходят на первом операторе инсерт, а не при обработке дубликатов?
В целом количество дубликатов 1% от общего количества данных. Взаимоблокировки сыпались по несколько в одну минуту. Я рассмотрю, конечно такой вариант, но пока повторить не удалось, поэтому нельзя быть уверенным на все 100%. Но вот что касается моих экспериментов по сканированию индекса в соседней ветке, то там всё надежно, т.е. deadlock гарантировано возникает в begin try. Для этого вставляю данные полученные из deadlock в DMV и получаю инструкцию, на которой возникли проблемы.
Здравствуйте, Olaf, Вы писали:
O>В общем, идея с тем, что причина взаимоблокировки заключается в сканировании индекса, в первоначальной постановке проблемы, не подтвердилась. Что это было и как докопаться до истины остается загадкой.
Тогда выскажу еще версию. Раз задействована FILETABLE, не могут ли в данном случае какие-то блокировки storage уровня (то есть NTFS) транслироваться на уровень БД? На первый взгляд не похоже, но мало ли, от MS всего можно ожидать.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
O>Она превратилась во взаимоблокировку преобразования, причем на уровне страницы. Т.е. при вставке в каждом из процессов создается блокировка с намерением на страницу (IX) и эксклюзивная (X) блокировка на строку. Сканирование индекса приводит к тому, что процесс пытается наложить совмещаемую блокировку (S) на страницу. В целом это нормально иметь на одном ресурсе S и IX блокировки, которые сводятся к блокировке SIX Но проблема в том, что SIX блокировки несовместимы с IX блокировками, которые наложены вторым процессом. По этой же причине второй процесс не может получить SIX блокировку, в результате взаимоблокировка.
Это тот же самый дедлок, просто на другом уровне гранулярности, и причина та же. Так что это скорее подтверждение, чем опровержение )
Мы уже победили, просто это еще не так заметно...
Re: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали:
O>2. В таблице отсутствует кластерный индекс
на это есть какие-то особые причины?
O>Нет ясного понимания, для каких целей СУБД обращается к записи, которая только что вставлена соседним процессом. Ведь для верификации FK достаточно выполнить index seek, по PK индексу, не затрагивая грязные данные. Единственное объяснение, которое у меня возникло – по каким-то неведомым причинам при вставке одной записи(!) оптимизатор для верификации ссылочной целостности вместо поиска предлагает использовать сканирование индекса.
Собственно scan скорее всего и есть причина дедлока. Получить дедлок, когда два запроса сканируют одну и ту же таблицу/индекс в одной транзакции — совсем не фокус, это, можно сказать, классика.
Следовательно решение проблемы — заставить оптимизатор выбирать план в котором нет сканов в проблемных местах, а используется подходящий индекс по прямому назначению.
O>Второй момент, на котором хотелось бы остановиться – это способ решения проблемы. В таблице из коробки отсутствует кластерный индекс. Цель кластерного индекса, на мой взгляд – снизить время вставки данных в таблицу по сравнению с кучей, уменьшить время блокирования и использования ресурсов и как результат исключить взаимоблокировки.
Не совсем так. Время блокирования конечно уменьшается, но за счет того, что оптимизатор выбирает совсем другой план, где нет сканирования и, как следствие, в принципе убрана эта причина нарваться на дедлок.
O>1. По каким причинам на ваш взгляд возник deadlock? Действительно ли использовался index scan для верификации ссылочной целостности и почему?
Да, скорее всего в этом.
O>2. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки?
Изменил план запроса таким образом, что отпала необходимость сканировать индекс, а это и было причиной дедлока.
O>Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?
Дело не во времени вставки, а в том как именно выполняется проверка. Так что с увеличением нагрузки все должно быть в порядке.
Мы уже победили, просто это еще не так заметно...
Re[3]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, wildwind, Вы писали:
W>Тогда выскажу еще версию. Раз задействована FILETABLE, не могут ли в данном случае какие-то блокировки storage уровня (то есть NTFS) транслироваться на уровень БД? На первый взгляд не похоже, но мало ли, от MS всего можно ожидать.
С этой стороны я тоже пытался рассматривать ситуацию.
Под «капотом» FILETABLE может быть много чего. Единственное, что удалось вытащить — это таблицу filestream_tombstone% для сборщика мусора, на объектах которой тоже возникают блокировки при вставке записей в FILETABLE. Хотя в любых вариациях плана запроса она нигде не фигурирует.
Re[2]: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, IB, Вы писали:
IB>на это есть какие-то особые причины?
Причина одна — вариант поставки FILETABLE из коробки, т.е. таблица с заранее предопределенной структурой, которая создается через инструкцию create table dbo.DocumentStore as filetable; Наличие кластерного индекса просто не предусмотрено разработчиками Microsoft, а я это упустил из виду при эксплуатации.
IB>Собственно scan скорее всего и есть причина дедлока. Получить дедлок, когда два запроса сканируют одну и ту же таблицу/индекс в одной транзакции — совсем не фокус, это, можно сказать, классика.
Полностью согласен. Но запрос, на котором у меня возникает дедлок, уж очень экзотичный insert into dbo.DocumentStore(name, file_stream, path_locator) select @p_filename, @file, @path_locator – вставка одной записи под нагрузкой из переменных в таблицу с FK на саму себя. Здесь и вариантов плана то немного – вставка записи в таблицу и верификация FK через сканирование (1) или поиск (2) по индексу.
IB>Следовательно решение проблемы — заставить оптимизатор выбирать план в котором нет сканов в проблемных местах, а используется подходящий индекс по прямому назначению.
Да, но использование индекса в моем варианте находится под капотом исполнения запроса на INSERT, т.е. для верификации FK я не могу его поменять на более походящий. Максимум, что можно сделать – отключить верификацию FK совсем, либо создать благоприятные условия «снаружи» для использования поиска по PK индексу (кластерный индекс на другом поле, статистики, железо, архивирование данных и т.п.)
IB>Не совсем так. Время блокирования конечно уменьшается, но за счет того, что оптимизатор выбирает совсем другой план, где нет сканирования и, как следствие, в принципе убрана эта причина нарваться на дедлок.
Оптимизатору конечно виднее, что и когда использовать. С толку сбивали следующие факты, хотя если кто-то еще считает, что это был скан наверно есть повод уже успокоиться
1. Верификация FK осуществляется по PK не кластерному индексу. Вставляется одна запись причем из переменных. Казалось бы при таких благоприятных условиях нет повода использовать сканирование, тем не менее это возможно.
2. Кластерный индекс создан на поле, которое не является PK. Его влияние в плане минимально. Единственное место, где он может быть задействован – вставка записи. Т.е. если смотреть на план запроса, то без кластерного индекса, данные вставляются в кучу (Table Insert оператор). C кластерным индексом вставка идет в него самого. По идее, все на что может повлиять кластерный индекс физическая (логическая) организация таблицы и ее индексов. И предпосылок для смены плана нет, т.е. необходимости перехода от сканирования к поиску и наоборот.
3. В распоряжении имеется сервер и БД на котором были deadlock. Единственное отличие от картины, которая была на момент возникновения взаимоблокировок — отсутствие пользователей, т.е. нагрузка сейчас равна нулю. Так вот в таких условиях я не могу повторить ситуацию с дедлоком, как бы не старался, сколько бы соединений параллельно не открывал — ни разу не удалось его словить. Искусственный вариант с option merge не в счет.
Re: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, Olaf, Вы писали:
O>Возможно, кто-то решал аналогичные проблемы или поделится своим мыслями относительно прочитанного, буду премного благодарен.
O>Резюмируя, опишу кратко свои вопросы: O>1. По каким причинам на ваш взгляд возник deadlock? Действительно ли использовался index scan для верификации ссылочной целостности и почему? Или истинная причина в чем-то другом? O>2. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки? Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?
Удалось поймать взаимоблокировку, причем необязательно было давать высокую нагрузку, достаточно ~20 параллельных выполнений запроса на INSERT. Анализ плана запроса показал, что верификация FK выполняется через поиск по индексу, никакого скана в данном случае нет. Решил включить трассировку и посмотреть на одиночном запросе, а что же происходит в БД кроме моей нагрузки, и вот что обнаружил. Между двумя событиями SQL:StmtStarting и SQL:StmtComplleted возникающими при выполнении запроса на insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl выполняется еще достаточное количество обращений к этой же таблице. Причем реализовано, как я понимаю это на уровне ядра БД, т.к. нигде в планах данная информация не фигурирует.
/*SQL:StmtStarting */insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl
set @path_locator = convert(hierarchyid, @path_locator_bin) /* Parent validation is not necessary for top level rows. */if (@path_locator.GetLevel() = 1)
set @parent_is_directory = 1
/*1*/select @parent_is_directory = is_directory from [dbo].[IssueFiles] where path_locator = @path_locator.GetAncestor(1)if (@parent_is_directory = 0)
if (@is_directory = 1)
/*2*/if exists (select 1 from [dbo].[IssueFiles] where parent_path_locator = @path_locator)set @return_value = 1
goto complete
set @path_locator = convert(hierarchyid, @path_locator_bin)
/*3*/select @dummy1 = sum(convert(bigint, is_archive)) from [dbo].[IssueFiles] with (xlock, rowlock, index([PK__IssueFil__5A5B77D5C0E48F84]), forceseek)
where path_locator.IsDescendantOf(@path_locator) = 1set @return_value = 1
/*SQL:StmtComplleted*/insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl
В целом видно, что кроме вставки в кучу и верификации FK выполняется еще три чтения FILTABLE. Причем у одного из запросов на выборку стоит подсказка к запросу xlock. C определенной долей уверенности можно сказать, что причина дедлока не в верификации FK как таковой, а в совокупности обращений к таблице, где по всей видимости присутствует сканирование.
Более детально наверно сложно проанализировать эту ситуацию, т.к. внутренние запросы, которые выполнятся дополнительно, остаются за бортом и я не вижу пока возможности проанализировать и сравнить планы выполнения в каждом из вариантов. Очевидно, что кластерный индекс кардинально меняет картину ...
Если последовательно запустить три запроса на больших объемах данных в таблице без кластерного индекса, то видно, что второй запрос использует сканирование PK индекса, хотя в таблице присутствует подходящий уникальный индекс, по которому можно выполнить поиск. Добавление кластерного индекса приводит к тому, что оптимизатор правильно разбирается в ситуации и использует поиск по соответствующему индексу, именно это и приводит к исчезновению дедлока. Вот собственно и все ответы на вопросы. Открытым остается только один вопрос, а почему Microsoft изначально не предусмотрел наличие кластерного индекса в таблице ?!
Чтобы «добить» тему, решил описать несколько моментов, которые неожиданно возникли при эксплуатации FILETABLE. Может кому-то пригодится и/или коллеги добавят свои замечания.
1. Отсутствие кластерного индекса. Ну об этом собственно вся тема.
2. Когда созрели требования и файлов в таблице накопилось достаточное количество, решили секционировать таблицу — разделить архивные и актуальные данные. Т.к. версия Standard, единственный вариант распределённые секционированные представления, которые планировали построить по дате создания файла (2015, 2014 и т.д.). Однако схема таблицы изменению не подлежит, т.е. переопределить PK нет возможности. Об этом сказано в документации, просто нужно помнить всегда.
3. Тогда перешли к секционированию ручным способом — создали таблицы по годам, логику написали руками. Для переноса данных по таблицам использовали простой запрос…
insert into dbo.Filetable2015
select *
from dbo.Filetable
join …
Рекомендация единственная – использовать в запросе option (maxdop 1)
4. Т.к. данных в таблицу вставляется много из п.3, а при вставке для каждой записи выполняются проверки (каталог-файл, родитель-потомок и т.п. около 4-5 штук), то процесс переноса данных идет совсем не быстро. Чтобы увеличить производительность можно отключить все системные ограничения пространства имен на уровне таблицы. После переноса данных проверки на таблице необходимо включить обратно, выполнив команду alter table dbo.Filetable enable filetable_namespace. И вот здесь, в отсутствии сторонних активностей у процесса с самим собой возникает deadlock. Зная о проблемах параллелизма из п.3. перед включением пространства имен временно поставил на уровне сервера Max Degree of Parallelism = 1
Мы уже победили, просто это еще не так заметно...
