Добрый день, коллеги!

Решили на пилотном проекте применить технологию в коробке от 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. Причина возникновения взаимоблокировки

Информация о взаимоблокировках из журнала ошибок рабочей среды:

	DEADLOCK
	deadlock-list deadlock victim=process23df4c188 process-list process id=process23df4c188 taskpriority=0 logused=2136 waitresource=KEY: 8:72057626804813824 (c61cf6ccabf8) waittime=111 ownerId=103380711 transactionname=user_transaction lasttranstarted=2015-01-21T12:39:12.867 XDES=0x272275348 lockMode=S schedulerid=3 kpid=9276 status=suspended spid=67 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-01-21T12:39:12.870 lastbatchcompleted=2015-01-21T12:39:12.870 lastattention=1900-01-01T00:00:00.870 clientapp=HS hostname=HOST hostpid=7708 loginname=user isolationlevel=read committed (2) xactid=103380711 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=109 stmtstart=6522 stmtend=6830 sqlhandle=0x020000005e250e31e4ee32206dff26668bdf95328ff4916f0000000000000000000000000000000000000000 frame procname=DB.dbo.hsp_PutAndRenameDoubleIssueFiles line=109 stmtstart=6522 stmtend=6830 sqlhandle=0x030008008144c2762debc50027a4000001000000000000000000000000000000000000000000000000000000 insert into dbo.IssueFiles(name, file_stream, path_locator) select @p_filename, @file, @path_locator inputbuf Proc [Database Id = 8 Object Id = 1992443009] process id=process2455b1c38 taskpriority=0 logused=2136 waitresource=KEY: 8:72057626804813824 (82b7a1c04c5d) waittime=172 ownerId=103380443 transactionname=user_transaction lasttranstarted=2015-01-21T12:39:12.640 XDES=0x246a803a8 lockMode=S schedulerid=1 kpid=7812 status=suspended spid=113 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-01-21T12:39:12.640 lastbatchcompleted=2015-01-21T12:39:12.640 lastattention=1900-01-01T00:00:00.640 clientapp=HS hostname=HOST hostpid=2644 loginname=user isolationlevel=read committed (2) xactid=103380443 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=109 stmtstart=6522 stmtend=6830 sqlhandle=0x020000005e250e31e4ee32206dff26668bdf95328ff4916f0000000000000000000000000000000000000000 frame procname=DB.dbo.hsp_PutAndRenameDoubleIssueFiles line=109 stmtstart=6522 stmtend=6830 sqlhandle=0x030008008144c2762debc50027a4000001000000000000000000000000000000000000000000000000000000 insert into dbo.IssueFiles(name, file_stream, path_locator) select @p_filename, @file, @path_locator inputbuf Proc [Database Id = 8 Object Id = 1992443009] resource-list keylock hobtid=72057626804813824 dbid=8 objectname=DB.dbo.IssueFiles indexname=PK__IssueFil__5A5B77D55446D258 id=lock2be37f380 mode=X associatedObjectId=72057626804813824 owner-list owner id=process2455b1c38 mode=X waiter-list waiter id=process23df4c188 mode=S requestType=wait keylock hobtid=72057626804813824 dbid=8 objectname=DB.dbo.IssueFiles indexname=PK__IssueFil__5A5B77D55446D258 id=lock22a81ed00 mode=X associatedObjectId=72057626804813824 owner-list owner id=process23df4c188 mode=X waiter-list waiter id=process2455b1c38 mode=S requestType=wait

Из представленной выше информации видно, что взаимоблокировка возникает на простых операторах 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. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки? Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?

Спасибо.