Нулевый SQL-тупик по дизайну - любые шаблоны кодирования?

Ответ 1

Написание кода с блокировкой блокировки очень сложно. Даже когда вы получаете доступ к таблицам в том же порядке, вы все равно можете получить блокировки [1]. Я написал сообщение в своем блоге, в котором вы найдете несколько подходов, которые помогут вам избежать и разрешить тупиковые ситуации.

Если вы хотите, чтобы два оператора/транзакции никогда не заходили в тупик, вы можете достичь этого, наблюдая, какие блокировки каждый оператор потребляет с помощью хранимой процедуры sp_lock. Для этого вам нужно либо очень быстро, либо использовать открытую транзакцию с подсказкой holdlock.

Примечания:

• Любой оператор SELECT, который требует более одного замка сразу, может зайти в тупик против разумно разработанной транзакции, которая захватывает блокировки в обратном порядке.

Ответ 2

Нулевые взаимоблокировки в основном являются чрезвычайно дорогостоящей проблемой в общем случае, потому что вы должны знать все таблицы /obj, которые вы собираетесь читать и изменять для каждой выполняемой транзакции (включая SELECT). Общая философия называется упорядоченной строгой двухфазной блокировкой (не путать с двухфазной фиксацией) (http://en.wikipedia.org/wiki/Two_phase_locking; даже 2PL не гарантирует никаких взаимоблокировок)

Очень немногие СУБД фактически реализуют строгий 2PL из-за огромной производительности, вызванной такой вещью (нет бесплатных обедов), в то время как все ваши транзакции ждут даже простых операторов SELECT, которые будут выполняться.

В любом случае, если это вас действительно интересует, взгляните на SET ISOLATION LEVEL в SQL Server. Вы можете настроить это по мере необходимости. http://en.wikipedia.org/wiki/Isolation_level

Для получения дополнительной информации см. wikipedia о Serializability: http://en.wikipedia.org/wiki/Serializability

Тем не менее, большая аналогия похожа на исправления исходного кода: сначала проверьте и часто. Держите транзакции маленькими (в # из операторов SQL, количество измененных строк) и быстрое (время настенных часов помогает избежать столкновений с другими). Может быть приятно и аккуратно делать много вещей в одной транзакции - и вообще я согласен с этой философией, - но если вы столкнулись с множеством тупиков, вы можете разбить транс в более мелкие, а затем проверьте их статус в приложении по мере продвижения. TRAN 1 - OK Y/N? Если Y, отправьте TRAN 2 - OK Y/N? и т.д.

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

Ответ 3

Нет никакого магического общего решения этой проблемы, которое работает на практике. Вы можете нажать concurrency на приложение, но это может быть очень сложно, особенно если вам нужно координировать работу с другими программами, запущенными в отдельных пространствах памяти.

Общие ответы для уменьшения возможностей взаимоблокировки:

• Оптимизация основных запросов (правильное использование индекса) hotspot avoidanant design, проведение транзакций в кратчайшие возможные сроки... и т.д.

• Когда это возможно, установите разумные тайм-ауты запроса, чтобы при возникновении взаимоблокировки он автоматически очищался после истечения периода ожидания.

• Тупики в MSSQL часто возникают из-за его модели чтения по умолчанию concurrency, поэтому очень важно не зависеть от нее - предположим, что стиль MVCC Oracle во всех проектах. Используйте изоляцию моментальных снимков или, если возможно, уровень изоляции READ UNCOMMATED.

Ответ 4

Я полагаю, что следующий полезный шаблон чтения/записи является мертвым блокировкой, учитывая некоторые ограничения:

Ограничения:

• Одна таблица
• Для чтения/записи используется индекс или PK, поэтому движок не использует блокировки таблиц.
• Пакет записей может быть прочитан с использованием предложения SQL where.
• Использование терминологии SQL Server.

Цикл записи:

• Все записи выполняются в рамках одной транзакции "Read Committed".
• Первое обновление транзакции относится к конкретной, всегда текущей записи  в каждой группе обновлений.
• Несколько записей могут быть записаны в любом порядке. (Они "защищены"  путем записи в первую запись).

Цикл чтения:

• Уровень прочитанной транзакции по умолчанию
• Без транзакции
• Чтение записей как одного оператора select.

Преимущества:

• Циклы вторичной записи блокируются при записи первой записи до тех пор, пока первая транзакция записи полностью не завершится.
• Чтения блокируются/помещаются в очередь/выполняются атомарно между транзакциями записи.
• Достичь согласованности уровня транзакции без использования "Serializable".

Мне нужно, чтобы это работало так, пожалуйста, прокомментируйте/исправьте!!

Ответ 5

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

Еще один классный трюк - объединить 2 sql-заявления в один раз, когда сможете. Одиночные заявления всегда являются транзакционными. Например, используйте "UPDATE... SELECT" или "INSERT... SELECT", используйте "@@ERROR" и "@@ROWCOUNT" вместо "SELECT COUNT" или "IF (EXISTS...)"

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

Ответ 6

Если у вас есть достаточный контроль над вашим приложением, ограничьте свои обновления/вставки конкретными хранимыми процедурами и удалите права на обновление/вставку из ролей базы данных, используемых приложением (только явным образом разрешайте обновления через эти хранимые процедуры).

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

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

Ответ 7

В дополнение к последовательной последовательности захвата замка - в другом пути явно используется привязка и изоляция для сокращения времени/ресурсов, которые теряются в случайном порядке, например, при попытке наведения во время чтения.

Ответ 8

Что-то, о чем никто не упомянул (что удивительно), заключается в том, что, если речь идет о сервере SQL, многие проблемы с блокировкой могут быть устранены с помощью правильного набора индексов покрытия для рабочей нагрузки БД. Зачем? Поскольку он может значительно уменьшить количество поисков по закладкам в таблице с кластеризованным индексом (предполагая, что это не куча), таким образом уменьшая конкуренцию и блокировку.

Ответ 9

Быстрый ответ - нет, нет гарантированной техники.

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

Лучший способ решить проблемы тупика SQL, как и большинство проблем с производительностью и доступностью, - это посмотреть на рабочую нагрузку в профилировщике и понять поведение.

Ответ 10

Не прямой ответ на ваш вопрос, но пища для размышлений:

http://en.wikipedia.org/wiki/Dining_philosophers_problem

Проблема "Обеденные философы" - это старый мысленный эксперимент для изучения проблемы взаимоблокировки. Чтение об этом может помочь вам найти решение ваших конкретных обстоятельств.