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

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
День добрый.
Есть таблица, с именем _InfoRg6219 и колонками
[_Fld6220RRef]
[_Fld6221]
[_Fld6222_TYPE]
[_Fld6222_RTRef]
[_Fld6222_RRRef]
[_Fld6223RRef]
[_Fld281]
содержащая 7 млн строк данных и 2 индекса:
Кластерный
[_Fld281]
[_Fld6220RRef]
[_Fld6221]
[_Fld6222_TYPE]
[_Fld6222_RTRef]
[_Fld6222_RRRef]
[_Fld6223RRef]

и некластерный:
[_Fld281]
[_Fld6222_TYPE]
[_Fld6222_RTRef]
[_Fld6222_RRRef]
[_Fld6220RRef]
[_Fld6221]
[_Fld6223RRef]

Из этой таблицы в 2 транзакциях, параллельно удаляются записи запросом вида:
DELETE FROM T1
FROM dbo._InfoRg6219 T1
WHERE (T1._Fld6222_TYPE = 0x08 AND T1._Fld6222_RTRef = 0x000016E5 AND T1._Fld6222_RRRef = @P1) AND (T1._Fld281 = @P2)

План запроса прост, как палка и не менялся ни разу:
StmtText
--------
Clustered Index Delete(OBJECT:([DISTR_DE].[dbo].[_InfoRg6219].[_InfoRg6219_1] AS [T1]), OBJECT:([DISTR_DE].[dbo].[_InfoRg6219].[_InfoRg6219_2] AS [T1]), OBJECT:([DISTR_DE].[dbo].[_InfoRg6219].[_InfoRg6219_3] AS [T1]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([DISTR_DE].[dbo].[_InfoRg6219].[_InfoRg6219_2] AS [T1]), SEEK:([T1].[_Fld281]=[@P2] AND [T1].[_Fld6222_TYPE]=0x08 AND [T1].[_Fld6222_RTRef]=0x000016E5 AND [T1].[_Fld6222_RRRef]=[@P1]) ORDERED FORWARD)


Уровень изоляции транзакции - READ COMMITTED SNAPSHOT

В случае, если удаляется до 500 записей, транзакции выполняются параллельно, если записей больше 5000 - 2 транзакция ждет первую.
Во всех случаях - попадание в индекс есть, сколько строк к удалению - столько строк и прочитано в Index Seek.

Эскалаций блокировки в событиях профайлера не увидел.

В событии timeout lock Блокировка - IX на объекте _InfoRg6219

Что я не понимаю или делаю не так?
27 фев 19, 13:30    [21820485]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Ну так посмотрите на каком ресурсе ждет.

UPD:
Ежов Дмитрий Сергеевич
timeout lock Блокировка - IX на объекте _InfoRg6219
Запрещайте эскалацию, понижайте уровень гранулярности блокировок.

Сообщение было отредактировано: 27 фев 19, 13:36
27 фев 19, 13:35    [21820491]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
msLex
Member

Откуда:
Сообщений: 7729
Ежов Дмитрий Сергеевич
5000


5000 - "магическое" число эскалации блокировок

Запретите эскалацию на уровне таблицы

ALTER TABLE ... SET ( LOCK_ESCALATION = DISABLE)
27 фев 19, 13:36    [21820495]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
msLex,

а почему я не вижу эскалацию в профайлере?
27 фев 19, 13:50    [21820523]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
Пардон, вижу, дико извиняюсь, стоял фильтр по другой БД
27 фев 19, 13:52    [21820529]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
Так.
Управлять эскалацией блокировки я не могу, запрос делает наша прекрасная 1С и вмешаться в его работу я не могу.
Я могу менять только параметр locks и флаги трассировки:
https://docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2005/ms188396(v=sql.90)
DBCC TRACEON (1221,-1);
DBCC TRACEON (1224,-1);

В MSDN говориться, что может не хватить памяти, но у нас на сервере SQL 1 Тб памяти, из которой SQL пользует только 600-700 Гб.

Кто пользовался этой штукой?
27 фев 19, 14:01    [21820537]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ежов Дмитрий Сергеевич,

96 байт на одну... может и не хватит :) Но вроде под блокировки не всё может выделить
27 фев 19, 14:04    [21820543]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
вру, то можно ограничить максимум

sp_configure 'locks'
27 фев 19, 14:06    [21820551]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7392
Ежов Дмитрий Сергеевич,

емнип игры с этой настройкой заканчиваются экспоненциальным ростом времени выполнения запроса. Граница там не от скуки сделана.
27 фев 19, 14:15    [21820566]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Владислав Колосов
Ежов Дмитрий Сергеевич,

емнип игры с этой настройкой заканчиваются экспоненциальным ростом времени выполнения запроса. Граница там не от скуки сделана.

т.е. время которое будет вызывано ожиданием получения блокировки лучше
27 фев 19, 14:23    [21820579]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
а если просто включить флаг DBCC TRACEON (1224,-1); ?
27 фев 19, 14:25    [21820586]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ежов Дмитрий Сергеевич
а если просто включить флаг DBCC TRACEON (1224,-1); ?

Правильно это отключить эскалация на конкретном объекте, а не во всём борделе. Почему вам это запрещает 1С не ясно
27 фев 19, 14:27    [21820592]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
TaPaK,

потому что сам запрос формирует сервер 1С, закрытое приложение.
Но, собственно, вопрос снят, нашел рекомендации самой 1С по флагам трассировки
https://its.1c.ru/db/metod8dev#content:5946:hdoc
27 фев 19, 14:38    [21820609]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ежов Дмитрий Сергеевич
TaPaK,

потому что сам запрос формирует сервер 1С, закрытое приложение.
Но, собственно, вопрос снят, нашел рекомендации самой 1С по флагам трассировки
https://its.1c.ru/db/metod8dev#content:5946:hdoc

я всё равно не могу понять, запрос - запросом, таблица - таблицей
ALTER TABLE ... SET ( LOCK_ESCALATION = DISABLE)
27 фев 19, 14:39    [21820613]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
TaPaK,

стоп.
ALTER TABLE ... SET ( LOCK_ESCALATION = DISABLE)
выключит эскалацию блокировок для таблицы раз и навсегда?
27 фев 19, 14:42    [21820622]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ежов Дмитрий Сергеевич
TaPaK,

стоп.
ALTER TABLE ... SET ( LOCK_ESCALATION = DISABLE)
выключит эскалацию блокировок для таблицы раз и навсегда?

доставка мануала на дом:
автор
DISABLE Prevents lock escalation in most cases. Table-level locks aren't completely disallowed. For example, when you're scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

для вас это "выключит эскалацию блокировок для таблицы раз и навсегда"
27 фев 19, 14:45    [21820630]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Ежов Дмитрий Сергеевич
Member

Откуда: Новокузнецк-Москва, Россия
Сообщений: 94
TaPaK,

спасибо тебе, добрый человек.
27 фев 19, 14:46    [21820634]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7392
Ежов Дмитрий Сергеевич,

рискуете нагрузить журнал так, что сервер чихнуть не сможет. Однако, практика - критерий истины. Расскажете, что получилось, если будет желание.
27 фев 19, 17:30    [21820912]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Владислав Колосов
Ежов Дмитрий Сергеевич,

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

а журнал то при чём?
27 фев 19, 17:31    [21820916]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
Владислав Колосов
Member

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

разве блокировки не пишутся в журнал? Надо освежить...
27 фев 19, 17:35    [21820920]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Владислав Колосов
TaPaK,
разве блокировки не пишутся в журнал? Надо освежить...

я могу и ошибаться, но зачем в принципе это для лога транзакций
27 фев 19, 17:38    [21820923]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
msLex
Member

Откуда:
Сообщений: 7729
TaPaK
Владислав Колосов
TaPaK,
разве блокировки не пишутся в журнал? Надо освежить...

я могу и ошибаться, но зачем в принципе это для лога транзакций


Пишутся, вместе с операцией изменения

Нужно это, для того, чтобы в стадии UNDO база была доступна для изменения.
В стадии REDO накладываются блокировки а затем получается как бы обычный откат транзакции в рабочей базе.
27 фев 19, 17:53    [21820946]     Ответить | Цитировать Сообщить модератору
 Re: Параллеьное удаление из таблицы вызывает ожидание на блокировке.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
msLex
TaPaK
пропущено...

я могу и ошибаться, но зачем в принципе это для лога транзакций


Пишутся, вместе с операцией изменения

Нужно это, для того, чтобы в стадии UNDO база была доступна для изменения.
В стадии REDO накладываются блокировки а затем получается как бы обычный откат транзакции в рабочей базе.

да, информация о блокировках есть. Но "больше данных" её не становится от количества этих самих блокировок. Просто для блокировки будет в строке аля
HoBt 72057594044022784:ACQUIRE_LOCK_X OBJECT: 15:1586104691:0
для ключа
HoBt 72057594044022784:ACQUIRE_LOCK_IX OBJECT: 15:1586104691:0 ;ACQUIRE_LOCK_IX PAGE: 15:1:832 ;ACQUIRE_LOCK_X KEY: 15:72057594044022784 (fb4bcb376b2f)
27 фев 19, 18:19    [21820977]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить