Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
dab2
Member

Откуда: Воронеж
Сообщений: 762
Читаю на уважаемом ресурсе askit.ru:
askit.ru
... а) при выполнении операций, которые должны быть выполнены с большим количеством записей в таблице, SQL Server пытается вначале использовать блокировки уровня записи. В результате на установку и последующее снятие таких блокировок расходуется значительное количество системных ресурсов. Этого можно было бы избежать, если сразу применить для выполнения операции нужный уровень блокировок (PAG или TAB);
… б) вторая, более важная проблема, заключается в том, что SQL Server применяет эскалацию блокировок, в том числе и на мощных серверах, с которыми одновременно работает большое число пользователей. Типичная ситуация выглядит таким образом: в базе данных есть большая таблица, с которой постоянно работают пользователи (назовем ее главной таблицей). За счет эскалации блокировок количество записей, которые одновременно блокируют пользователи, автоматически увеличивается, в результате чего другие пользователи не могут получить к ним доступ. Таким образом, при достижении определенного количества пользователей работа с этой таблицей резко затрудняется. Особенно неприятно то, что заблокированными оказываются те записи, с которыми пользователи на самом деле не работают: просто они попали на одну страницу с другими записями, открытыми в данный момент.

Собственно, вопрос о более поздних версиях SQL Server - там тоже самое, - эскалация перехода блокировок с уровня строк на таблицы, а не на страницы или что-то всё-таки улучшено по данной теме? Или везде надо руками постоянно настраивать уровни блокировок и расставлять хинты?
============================================================================================================
"О, сколько нам открытий чудных готовит просвещения дух, и опыт - сын ошибок трудных, и гений - парадоксов друг, и случай - бог изобретатель" (Пушкин, однако).
12 фев 20, 18:08    [22078662]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36633
Уже много версий эскалацию можно отключать на конкретных таблицах.
12 фев 20, 18:18    [22078673]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7319
dab2,

Едим слона по частям. Проблема возникает при несовместимых типах блокировки, например, если происходит одновременная запись и чтение. Для чего требуется эскалация. Каждая блокировка потребляет какой-то объем памяти сервера. Для установки большого количества строковых блокировок вы рискуете получить ошибку out of memory. Кроме того, потребуется просмотреть большое количество блокировок, чтобы определить возможность установки очередной блокировки. Если количество блокировок превышает определенное количество, сервер принимает решение укрупнить блокировки, чтобы снизить их общее количество. Укрупнение никак не мешает совместимым типам блокировки.
В случае, если на таблице имеются несовместимые типы блокировки, эскалация не происходит. В этом случае также есть риск получить out of memory при определенных уровнях изоляции.

В более поздних версия появилась возможность версионирования страниц таблиц, при включении этого режима блокировки друг другу не мешают, но версионированные страницы сохраняются в tempdb. понятно, что tempsb не резиновая и имеет конечную пропускную способность.

Рекомендации автора относятся к мерам "застолбить" для себя таблицу в случае критичности выполнения запроса относительно других запросов к этой же таблице. Однако, такой подход может привести к ожиданием освобождения таблицы.
12 фев 20, 18:26    [22078685]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
invm
Member

Откуда: Москва
Сообщений: 9074
Владислав Колосов
Кроме того, потребуется просмотреть большое количество блокировок, чтобы определить возможность установки очередной блокировки.
Как думаете, для чего придуманы блокировки намерения?
12 фев 20, 18:42    [22078697]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
aleks222
Member

Откуда:
Сообщений: 817
Гавриленко Сергей Алексеевич
Уже много версий эскалацию можно отключать на конкретных таблицах.


Вопросик:
Вот есть у меня "большая таблица".
И сыпются в нее обновления/вставки/удаления через простой merge из временной таблицы.
Пока размер батча был < 10000 строк - усе было тип-топ.
Как появились батчи > 10000 строк - начало блокировать таблицу. Т.е. все батчи стоят и ждут выполнения батча > 10000 строк.
Ну отключил я на "большой таблице" эскалацию.
А ничо не изменилось.
Что я сделал не так?

ЗЫ. Пришлось, ведь, тупо понижать размер батча.
13 фев 20, 05:49    [22078939]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
msLex
Member

Откуда:
Сообщений: 7600
aleks222
Гавриленко Сергей Алексеевич
Уже много версий эскалацию можно отключать на конкретных таблицах.


Вопросик:
Вот есть у меня "большая таблица".
И сыпются в нее обновления/вставки/удаления через простой merge из временной таблицы.
Пока размер батча был < 10000 строк - усе было тип-топ.
Как появились батчи > 10000 строк - начало блокировать таблицу. Т.е. все батчи стоят и ждут выполнения батча > 10000 строк.
Ну отключил я на "большой таблице" эскалацию.
А ничо не изменилось.
Что я сделал не так?

ЗЫ. Пришлось, ведь, тупо понижать размер батча.

По-мимо отключения эскалации нужно ещё и начальный уровень блокировок до уровня строк явно указывать хинтом rowlock.
13 фев 20, 09:46    [22078990]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
Yasha123
Member

Откуда:
Сообщений: 1823
msLex

По-мимо отключения эскалации нужно ещё и начальный уровень блокировок до уровня строк явно указывать хинтом rowlock.

пардон, а зачем?
у нас тоже есть табличищe(вернее, уже 4 таких), в которую вечно вставляют по 1,5 млн за раз.
и апдэйты такие же.
отключивши эскалацию на этих таблицах получили на апдэйтах (на всех четырех таблицах) миллионы построчных локов.
пришлось насильно паглоки прописать.
на инсертах конечно роулоки устраивают, но на апдэйтах нет.
апдэйтим старое, и оно никак не на последней странице.

Microsoft SQL Server 2014 (SP3-CU3) (KB4491539) - 12.0.6259.0 (X64)
Apr 1 2019 22:19:54
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
13 фев 20, 11:21    [22079065]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
msLex
Member

Откуда:
Сообщений: 7600
Yasha123
msLex

По-мимо отключения эскалации нужно ещё и начальный уровень блокировок до уровня строк явно указывать хинтом rowlock.

пардон, а зачем?
у нас тоже есть табличищe(вернее, уже 4 таких), в которую вечно вставляют по 1,5 млн за раз.
и апдэйты такие же.
отключивши эскалацию на этих таблицах получили на апдэйтах (на всех четырех таблицах) миллионы построчных локов.
пришлось насильно паглоки прописать.
на инсертах конечно роулоки устраивают, но на апдэйтах нет.
апдэйтим старое, и оно никак не на последней странице.

Microsoft SQL Server 2014 (SP3-CU3) (KB4491539) - 12.0.6259.0 (X64)
Apr 1 2019 22:19:54
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


Как зачем? Чтобы иметь возможность параллельно мёржить по 10000 из разных коннектов.
Тут вопрос баланса между гранулярностью блокировок и возможностью параллельно модифицировать данные в одной таблице
13 фев 20, 11:32    [22079086]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
Yasha123
Member

Откуда:
Сообщений: 1823
msLex

Как зачем? Чтобы иметь возможность параллельно мёржить по 10000 из разных коннектов.
Тут вопрос баланса между гранулярностью блокировок и возможностью параллельно модифицировать данные в одной таблице

так сервер сам, как только отключаешь эскалацию,
выбирает именно роулоки.
мне казалось, должен бы наоборот, паглоки выбирать,
чтобы не хранить такую тучу локов, но пришлось заставлять...
13 фев 20, 12:04    [22079131]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
msLex
Member

Откуда:
Сообщений: 7600
Yasha123
так сервер сам, как только отключаешь эскалацию,
выбирает именно роулоки.


Это точно?
Насколько я помню, отключение эскалации не влияет на начальный выбор уровня гранулярности и sql engine вполне может выбрать блокировку уровня таблицы.
13 фев 20, 12:11    [22079139]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение эскалации блокировок SQL Server 2005 и более поздних версий  [new]
Yasha123
Member

Откуда:
Сообщений: 1823
msLex
Yasha123
так сервер сам, как только отключаешь эскалацию,
выбирает именно роулоки.


Это точно?
Насколько я помню, отключение эскалации не влияет на начальный выбор уровня гранулярности и sql engine вполне может выбрать блокировку уровня таблицы.

это нигде не написано,
просто стабильно воспроизводится на всех 4х таблицах.
и вроде это против всякой логики,
ведь когда так много локов, казалось бы, почему не страницами лочить?
у нас не 10.000 за раз, стабильно 1,5млн...

это не говоря об исключительных случаях,
когда начальнику приходят гениальные идеи типа апдэйтить 17млн строк за раз.
ведь все равно сервер не выбрал страницы, лочил построчно и вывалил в результате дамп памяти в еррор лог и ошибку начальнику.
13 фев 20, 12:33    [22079158]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить