Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Добрый день!

Есть следующая ситуация 2 таблицы, в них информация о данных загруженных пользователем, построчно.

Пользователь грузит разное кол-во данных. Старые данные удаляются. Проблема в том, что сейчас на этих таблицах довольно часты блокировки и вот, чтобы избежать блокировок в процедуре удаления стоит блок удаления 5000 записей (так не включается блокировка таблицы).
Так как иногда удаляется под 1 000 000, то по 5 тысяч данные удаляются небыстро. Хочется увеличить блок, но тогда будут блокировки.

Действия, которые я считаю верными:
- проверить фрагментацию индексов на таблицах, если больше 20% - делать ребилд (сейчас он совсем не делается)
- на всех индексах Allow page locks = False, ALTER TABLE SET ( LOCK_ESCALATION = DISABLE )
- увеличить блок до 50-100 тыс.

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

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

версия MS SQL Server 2012, snapshot isolation - off.

Напишите, пожалуйста, ваше мнение, за и против режима с отключенными постраничными блокировками и блокировками уровня таблица.
5 июл 16, 15:32    [19371460]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Порезать данные на секции и транкейтить их. Блокировок на данные не будет вообще.
5 июл 16, 15:36    [19371495]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Да, таблицы несекционированы, там довольно сложный алгоритм принятия решения что нужно удалять - и он может варьироваться в зависимости от настроек пользователя.

Я думаю, если таблицу секционировать станет получше.

А про отключение блокировки страниц и таблицы что скажете?
5 июл 16, 15:47    [19371585]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Nimua
Да, таблицы несекционированы, там довольно сложный алгоритм принятия решения что нужно удалять - и он может варьироваться в зависимости от настроек пользователя.

а чего просто на помечать запись как неактивную то ? А хаускипинг делать в незагруженное время..если он дествительно так необходим ?
5 июл 16, 15:51    [19371604]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
sti
Member

Откуда:
Сообщений: 769
2 Nimua
Ешё вариант - использовать TIL SNAPSHOT. Возрастёт нагрузка на tempdb, но блокировки уйдут.
5 июл 16, 15:51    [19371608]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
2 Maxx
там 2 части - удаление неактивных записей - оно как раз и делается в менее незагруженное время, но системе работает все время и окно все меньше.

2 sli
про Snapshot в курсе - пока не получается говорить.

А про сам вопрос - вы считаете отключить блокировки более высокого уровня плохим решением?
Объясню почему спрашиваю - на моем прошлом проекте у нас была высоконагруженная OLTP система и там почти на всех таблицах были отключены блокировки уровня страница и таблица + был включен snapshot isolation level. Все хорошо работало, поэтому мне не понятно почему это не может быть решением, мне кажется эти настройки как раз и есть для того, чтобы можно было регулировать под систему и ее нагрузку.
5 июл 16, 17:35    [19372155]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
o-o
Guest
Nimua
там почти на всех таблицах были отключены блокировки уровня страница и таблица + был включен snapshot isolation level. Все хорошо работало

а зачем отменять эскалацию, если работаешь с версиями на уровне снэпшот?
5 июл 16, 17:50    [19372219]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Владислав Колосов
Member

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

блокировки регулируют не нагрузку, а целостность и достоверность данных. В Вашем случае будет более выгодным ставить пометку об удалении и перестроить запросы и индексы с учетом дополнительного поля.
5 июл 16, 17:50    [19372220]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
o-o,

чтобы запись тоже нормально работала. В той системе запись была по 1й строке, но много и быстро, с возможным увелечением нагрузки в разные периоды дня, и иногда с увеличением кол-во insert'ов до 4х - 5ти раз от нормального объема.
5 июл 16, 17:54    [19372234]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Владислав Колосов,

Как раз и дело в том, что в этой системе одновременно есть запись в таблицу, и удаление - которые если попадают на одну страницу возникает проблема.

Выше писала - отметка об удалении и так ставится, единственно может есть смысл часть индексов сделать фильтрованными по активном полям только и часть только по неактивным.
5 июл 16, 17:55    [19372247]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Nimua
Владислав Колосов,

Как раз и дело в том, что в этой системе одновременно есть запись в таблицу, и удаление - которые если попадают на одну страницу возникает проблема.
Делайте удаление и вставку с rowlock. А для выборок пусть сервер сам выбирает гранулярность.
5 июл 16, 18:00    [19372271]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Гавриленко Сергей Алексеевич,

О, спасибо!
5 июл 16, 19:00    [19372460]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
sti
Member

Откуда:
Сообщений: 769
Nimua
2 sli
про Snapshot в курсе - пока не получается говорить.

А почему, интересно?

Вот у нас чем то похожая картина. Всего 130 миллионов записей. В день добавляется 5-10 млн., столько же удаляется и столько же обновляется. Единоразовый максимум - примерно 2 млн. Чтений - огромное количество, лень подсчитывать. Включен snapshot и всё работает, для читателей вообще без проблем.
6 июл 16, 09:07    [19373676]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
sti,

Прерыдущая моя запись должна была выглядеть так "пока не получается уговорить".
Я недавно в проекте, пока тут везде стоит NOLOCK - на всех 100% селектов из таблиц которые мне довелось видеть. Я говорила про snapshot - но аргумент у нас же блокировки на чтение-запись, а эта штука в основном хороша для чтений + возрастет же нагрузка на tempdb. Поживем увидим как будет дальше. Про nolock - уже говорили что это через одно место.
6 июл 16, 09:33    [19373788]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
sti
Member

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

забавно. Здесь тоже примерно всё так и было - сплошные nolock и круглые глаза, когда говоришь про snapshot. Согласились в конце концов, когда совсем припёрло.
6 июл 16, 09:51    [19373873]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
sti,

О! спасибо за это :) а то я как то уже начала думать, что вряд ли что-то получится поменять. В общем упаднические настроения начались. Подсоберу аргументы и буду периодически предлагать.
6 июл 16, 10:03    [19373917]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Владислав Колосов
Member

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

поставите уровень изоляции снапшот - можете повалить сервер в DoS и out of space. Аккуратнее с этим.
6 июл 16, 11:03    [19374193]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Владислав Колосов,

да, надо будет пробовать сначала на небольшом, и увеличить место для tempdb. Плюс был опыт при включенном snapshot если какая то транзакция "подвисла" и существует очень долго, то с этим тоже будут проблемы. Обычно такое было при репликации.
6 июл 16, 11:19    [19374301]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
sti
Member

Откуда:
Сообщений: 769
Nimua
был опыт при включенном snapshot если какая то транзакция "подвисла" и существует очень долго, то с этим тоже будут проблемы.

Случается, подтверждаю. Вот пример:
https://www.sql.ru/forum/1198675/server-ne-vidit-deadlock-pochemu?hl=
6 июл 16, 11:30    [19374361]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Вопрос не однозначный относительно уровней изоляции. Какие риски?
Может быть замедление операций, простейших селектов(планы остаются те же).
Могут меняться планы выполнения запросов(памяти становится меньше) далеко не в лучшую сторону и как следствие тотальная деградация системы.
Есть подходы которыми можно обойти эти проблемы но это целая история. Вообщем вопрос многосторонний.
6 июл 16, 13:11    [19375033]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Гавриленко Сергей Алексеевич,

Вопрос а есть ли возможность что при

DELETE TOP (50000) FROM tbl WITH (ROWLOCK)

MS SQL сервер проигнорирует хинт и эскалирует блокировку на уровень страницы\таблицы.

Меня попросили это проверить. А я стыдно сказать не знаю как посмотреть какого уровня блокировка наложена (только опытным путем пытаться что-нибудь обновить\вставить).

И еще вот тут нашла, что несмотря на хинт ROWLOCK у товарища была эскалация блокировки How to delete many records with minimal locks and IO в комментах.

Пробую например запрос

begin tran 

delete top (10000) 
FROM [dbo].[TblUser] with (ROWLOCK)

WAITFOR DELAY '00:00:05'

rollback tran


и потом запускаю sp_lock и по object_id таблице вижу

spid dbid ObjId IndId Type Resource Mode Status
81 6 0 0 DB S GRANT
81 6 15 0 TAB X GRANT

Читаю BOL - вижу что заблокирована вся таблица.

Еще пробовала вот такой запрос Запрос блокировок по базе
ResType = OBJECT, что тоже так понимаю означает таблицу
Потому что если поменять блок на 10 записей и выполнить тот же запрос, то ResType = Key
7 июл 16, 14:18    [19380959]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
aleks2
Guest
Да, гуру в своем репертуаре.
Нет шоб честно сказать тредстартеру: он борется с ветряными мельницами и нихрена ему не светит.

Хоть усрись - быстро удалять 1 000 000 записей не получится.

Поэтому правильное дао - удалять и дальше по 1000-5000 шт.
Только постоянно - круглосуточно.
7 июл 16, 14:34    [19381081]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
aleks2,

Понимаете, боль в том, что на прошлом проекте с настройками allow_page_locks - false + disable lock escalation + snapshot - удаляли по 10 тыс - 50 тыс и это было очень быстро, намного быстрее чем 1000. Поэтому и решила спросить.
7 июл 16, 14:40    [19381127]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
aleks2
Guest
Nimua
aleks2,

Понимаете, боль в том, что на прошлом проекте с настройками allow_page_locks - false + disable lock escalation + snapshot - удаляли по 10 тыс - 50 тыс и это было очень быстро, намного быстрее чем 1000. Поэтому и решила спросить.


Если ты будешь удалять "фсе сразу" - будет (возможно) ишо быстрее.
delete from aTable with(tablockx) where...


Засада тока в том, что недостаточно быстро, чтобы не мешать другим.
Эта ложка дегтя портит фсю бочку меда.
7 июл 16, 14:48    [19381217]     Ответить | Цитировать Сообщить модератору
 Re: Хорошо ли выключать блокировки уровня страница и таблица  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
aleks2,

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

Возвращаясь к исходной формулировке, а почему не выключить эскалацию на индексах и таблице - тогда точно будут блокироваться только строки, чем плохо это решение? жор памяти?
7 июл 16, 15:32    [19381548]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить