Как я понимаю, проблема проистекает от не очень удачного применения не очень удачного метода разрешения дедлоков. Когда объектов очень много, скрипты сложные, вероятность дедлоков возрастает. Происходят дедлоки обычно потому, что:
а) Транзакции блокируют ресурсы не одномоментно, а последовательно
б) Последовательность блокировок ресурсов в разных транзакциях может привести к блокировке разных ресурсов одновременно несколькими транзакциями и запроса блокировки ресурсов, занятых другими транзакциями, которые в свою очередь запрашивают ресурсы, уже заблокированные в первой транзакции.
Дедлок - это замкнутый круг, бороться с которым можно двумя способами:
1. Откатить одну из транзакций, вошедшую в этот замкнутый круг (что принудительно и делает встроенная в SQL сервер искалка дедлоков).
2. Не допустить замыкания круга и исключить саму вероятность выполнения последовательности блокировок в разных транзакциях, которые могут приветси к взаимным блокировкам.
Способ 1 мало кому нравится по двум причинам. Во-первых, дедлок обнаруживается обычно не сразу. На его выявления может уйти довольно много времени. Все это время некоторая часть пользователей будет с недоумением смотреть на ваше приложение, впавшее в глубюокую задумчивость. Во-вторых, когда дедлок наконец будет выявлен, кто-то из юзеров получит соответствующее сообщение об ошибке, и будет пребывать в состоянии растерянности. С точки зрения его, юзверной логики, он не придпринял никаких предосудительных действий, и периодическое возникновение ошибки буквально на голом месте, да еще такой, с которой невозможно бороться никакими средствами, весьма сильно раздражает.
Обычно разработчики выбирают способ 2 (и правильно делают). Однако, этот способ в свою очередь разбивается на два диаметрально противоположных решения, каждое из которых имеет свои положительные стороны и отрицательные:
Решение 1. Заблокировать все что нужно и все что не нужно в самом начале транзакции, а потом предпринимать какие-то последовательные алгоритмические шевеления внутри транзакции уже на полностью заблокированных транзакцией ресурсах. Именно по этому пути пошли ваши разработчики. Плюсы данного решения: а) высокая степень изолированности транзакций позволяет гарантированно избежать побочных эффектов (вроде "грязного чтения"); б) простота реализации. Минусы: высокая конкуренция транзакций приводит к большим временным задержкам, потому что транзакции вынуждены ждать освобождения всех ресурсов, выстаивая в "очереди транзакций".
Решение 2. Попытаться свести к минимуму блокировки. Это решение предлагает Аджан Абдулин (и мне оно тоже больше по душе). Плюсы этого решения - возможность одновременного выполнения нескольких транзакций приводит к устранению циклов ожидания в "очереди транзакций", что положительно сказывается на быстродействии. Минусы: а) требуется тщательное продумывание всех скриптов (сложность реализации, что может приводить к ошибкам); б) возможность проявления побочных эффектов вроде "грязного чтения" на некоторых комбинациях одновременно выполняемых транзакций из-за низкого уровня изолированности транзакций.
Самый грамотный и одновременно самый трудный путь - это решение 2. Если Вам удастся уговорить ваших разработчиков по нему пойти, значит Вы родились под счастливой звездой. Базовые приемы названы Аджаном Абдулиным. По возможности во всех операциях SELECT, выполняемых по постоянным таблицам, во фразу FROM необходимо добавить хинт with (NoLock). При каждом добавлении этого хинта нужно помнить о том, что его использование может привести к "грязному чтению", и тщательно продумывать возможные влияния на другие транзакции. Во все фразы Insert, Update и Delete лучше всего поставить хинт With (RowLock). Эти операции неизбежно приводят к блокировкам, и наша задача избежать эсклации блокировок, которая может привести к дедлоку (обычно это происходит при повышении статуса блокировки до уровня страничной или табличной, что потенциально может привести к блокировке записей, расположенных по соседству с теми, над которыми выполняется операция - если другая транзакция в это время пытается получить к ним доступ, возникает опасность дедлока). Однако, следует помнить, что данный хинт можно использовать только в том случае, когда вы уверены, что эти операции всегда будут выполняться над небольшим числом записей. Если операции Insert, Update и Delete могут выполняться над большим массивом записей (сотни и тысячи), этот хинт использовать нельзя. При массовых операциях модификации записей с указанным хинтом на каждую модифицируемую запись будет занят отдельный ресурс, называемый "блокировка". Количество таких объектов при достижении критически больших величин может привести к исчерпанию ресурсов памяти SQL-сервера. Следует помнить, что даже повсеместное использование указанных хинтов не приводит гарантированно к устранению опасности возникновения дедлоков. Особое внимание следует обратить на последовательность выдачи команд UPDATE и DELETE по разным таблицам в разных скриптах. Если большая часть скриптов оперирует сразу множеством таблиц, желательно во всех скриптах придерживаться одной и той же последовательности модификации данных по нескольким таблицам.
Следует учесть, что указание табличных хинтов во фразе FROM имеет смысл только для тех таблиц, которые существуют на момент создания скрипта. Хинты по временным таблицам и таблицам Inserted и Deleted в триггерах будут проигнорированы (см. BOL).