Когда я обновляю/вставляю одну строку, если она блокирует всю таблицу?

У меня есть два длинных запроса, которые работают как на транзакциях, так и на доступ к одной и той же таблице, но полностью разделяют строки в этих таблицах. Эти запросы также выполняют некоторые обновления и вставки на основе этих запросов.

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

Два вопроса:

Когда я обновляю/вставляю одну строку, она блокирует всю таблицу?
Что можно сделать для решения этой проблемы?

Ответ 1

Обычно нет, но это зависит (чаще всего используется ответ для SQL Server!)

SQL Server должен будет каким-то образом заблокировать данные, участвующие в транзакции. Он должен блокировать данные в самой таблице, а данные - любые затронутые индексы, в то время как вы выполняете модификацию. Чтобы улучшить concurrency, существует несколько "гранулярностей" блокировки, которые сервер может решить использовать, чтобы разрешить запуск нескольких процессов: обычные блокировки, блокировки страниц и блокировки таблиц являются обычными (их больше). Какая шкала блокировки находится в игре, зависит от того, как сервер решает выполнить данное обновление. Усложняя вещи, есть также классификации блокировок, таких как общие, эксклюзивные и намеренные эксклюзивные, которые контролируют, может ли заблокированный объект быть прочитан и/или изменен.

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

Чтобы узнать, что происходит в вашем конкретном случае, вам нужно взглянуть на логику запроса, и, пока ваш материал запущен, изучите условия блокировки/блокировки в sys.dm_tran_locks, sys.dm_os_waiting_tasks или других DMV. Вы бы хотели узнать, что именно блокируется на каком этапе в каждом из ваших процессов, чтобы узнать, почему один из них блокирует другой.

Ответ 2

Краткая версия:

Нет
Исправьте свой код.

Длинная версия:

LCK_M_IX - блокировка намерения, то есть операция будет помещать блокировку X в подчиненный элемент. Например. При обновлении строки в таблице, таблица операций занимает стопку IX в таблице перед блокировкой X, которая обновляется/вставляется/удаляется строка. Intent locks являются общей стратегией для работы с иерархиями, такими как table/page/row, потому что диспетчер блокировок не может понять физическую структуру ресурсов, запрошенных для блокировки (то есть он не может знать, что X-блокировка на странице P1 несовместима с S-lock в строке R1, потому что R1 содержится в P1). Для получения дополнительной информации см. Режимы блокировки.

Тот факт, что вы видите противоречие с блокировками намерений, означает, что вы пытаетесь получить блокировки объектов высокого уровня, например, блокировки таблиц. Вам необходимо проанализировать исходный код блокируемого запроса (тот, который запрашивает блокировку, несовместимую с LCK_M_IX), и удалить причину запроса блокировки на уровне объекта. То, что это означает, будет зависеть от вашего исходного кода, я не знаю, что вы там делаете. Я предполагаю, что вы используете ошибочный намек на блокировку.

Более общий подход - полагаться на SNAPSHOT ISOLATION. Но это, скорее всего, не решит проблему, которую вы видите, поскольку изоляция моментальных снимков может только выиграть от проблем с конфликтом на уровне строк, а не с приложениями, которые запрашивают блокировки таблиц.

Ответ 3

Частая цель использования транзакций: держите их как можно короче и сладкими. Я понимаю смысл вашей формулировки в вопросе о том, что вы открываете транзакцию, а затем делаете всевозможные вещи, некоторые из которых занимают много времени. Затем ожидаем, что несколько пользователей смогут одновременно запускать этот же код. К сожалению, если вы выполняете вставку в начале этого набора кода, тогда делаете 40 других вещей перед тем, как совершать или откатываться назад, возможно, что эта вставка заблокирует всех остальных от работы с одним и тем же типом вставки, по существу превращая вашу операцию из бесплатно для всех.

Узнайте, что делает каждый запрос, и если вы получаете блокировку, которую вы не ожидаете. Просто потому, что вы говорите, что WITH (ROWLOCK) в запросе не означает, что SQL Server сможет выполнить... если вы затронули несколько индексов, индексированных представлений, постоянных вычисляемых столбцов и т.д., Тогда есть всевозможные причины, почему ваша строка может не содержать воды. Возможно, у вас также есть вещи позже в транзакции, которые занимают больше времени, чем вы думаете, и, может быть, вы не понимаете, что блокировки для всех объектов, участвующих в транзакции (а не только текущая работа) могут быть сохранены для продолжительность транзакции.

Ответ 4

Различные базы данных имеют разные механизмы блокировки, но такие, как SQL Server и Oracle, имеют разные типы блокировки.

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

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