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

Откуда:
Сообщений: 16
Добрый день уважаемые гуру SQL Server.
Столкнулся вот с такой неприятной проблемой:
Используется SQL Server 2005 SP4.
Есть таблица фактов, допустим T1. Определен кластерный индекс по полю dateTimeBegin, тип datetime. Используется секционирование, размер секции - месяц. Размер строки в таблице T1 - примерно 4 кб. На таблице определены еще 4 индекса типа int.
Если мы выполняем такой запрос

DELETE FROM T1 where dateTimeBegin between '20120101' and '20120102'
--удаляется примерно 16000 записей

то на всю таблицу накладывается сначала блокировка IX, потом X.
К таблице идут постоянные запросы на чтение, в момент удаления они блокируются.
В этом и вся проблема.
Решение:
1) как выход вижу использовать грязное чтение.
2)оптимизировать запрос по удалению строк - удалять меньшие порции. Тогда как определить порог?

Можно ли так делать? Какие еще существуют варианты?
Почему блокируется целая таблица, а не отдельная секция?
17 фев 13, 17:04    [13938869]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
rowlock или paglock, запретить эскалацию и убедиться, что есть индекс по дате.
17 фев 13, 17:22    [13938917]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
aleks2
Guest
4k
Используется секционирование, размер секции - месяц.
Если мы выполняем такой запрос

DELETE FROM T1 where dateTimeBegin between '20120101' and '20120102'


У мя есть дурацкий вопрос: нафега "Используется секционирование"?
18 фев 13, 07:49    [13940603]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
4k
1) как выход вижу использовать грязное чтение.

Это худший выход. Да что уж там, не выход вовсе.

4k
2)оптимизировать запрос по удалению строк - удалять меньшие порции. Тогда как определить порог?

Пороги укрупнения блокировок описаны в статье BOL: Укрупнение блокировки (компонент Database Engine).

4k
Какие еще существуют варианты?

Несколько вариантов описано в статье Microsoft: How to resolve blocking problems that are caused by lock escalation in SQL Server. Дополнительно для MSSQL 2005 подходит включение на базе опции read committed snapshot.

4k
Почему блокируется целая таблица, а не отдельная секция?

Это особенность версии 2005. В ней эскалация всегда происходит до уровня таблицы. Эскалация до уровня секции появилась в версии 2008.

4k
dateTimeBegin between '20120101' and '20120102'

Строго говоря, это удаление данных не за месяц. Более правильное условие такое:
dateTimeBegin >= '20120101' and dateTimeBegin < '20120102'.

Ну и да, непонятно, зачем вам вообще секционирование при таких запросах. Да и при таких объёмах (16000 записей в месяц — это типичное значение? Выкиньте тогда секционирование и не парьте мозги :-)
18 фев 13, 10:09    [13941059]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Может, поможет SWITCH PARTITION?
http://msmvps.com/blogs/gladchenko/archive/2010/03/09/1761298.aspx

Тут есть примеры удаления строк из секции.
18 фев 13, 13:23    [13942352]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
4k
Member

Откуда:
Сообщений: 16
Всем спасибо за советы, буду пробовать.
Гавриленко Сергей Алексеевич
rowlock или paglock, запретить эскалацию и убедиться, что есть индекс по дате.

Rowlock и paglock не помогает. Происходит укрупнение до уровня таблицы.
Если запрещать эскалацию через флаг dbcc traceon(1211), то эскалации не происходит. Но что-то мне подсказывает, что так делать нехорошо. Какие ещё есть способы? Индекс по дате есть точно - кластерный.
18 фев 13, 13:54    [13942618]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
4k
Какие ещё есть способы?

Чем не устраивают способы, описанные по приведённой мной ссылке?
18 фев 13, 14:08    [13942750]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
4k
Member

Откуда:
Сообщений: 16
Гость333, 16000 запросов в день. В месяц примерно 500000. Это немного конечно, однако база транзакционная, должна быть шустрой и отзывчивой. Именно поэтому используется секционирование. Запросы в основном идут к последним двум секциям.
18 фев 13, 14:12    [13942777]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
4k
dateTimeBegin between '20120101' and '20120102'

Строго говоря, это удаление данных не за месяц. Более правильное условие такое:
dateTimeBegin >= '20120101' and dateTimeBegin < '20120102'.

Ё-маё, это ж один день. Я почему-то подумал, что один месяц (густо покраснел).
18 фев 13, 14:14    [13942800]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
aleks2
Guest
4k
Гость333, 16000 запросов в день. В месяц примерно 500000. Это немного конечно, однако база транзакционная, должна быть шустрой и отзывчивой. Именно поэтому используется секционирование. Запросы в основном идут к последним двум секциям.

Да, таинство кластерного индекса осталось вне мозга тредстартера....
18 фев 13, 14:20    [13942845]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 888
Значит у вас секционирование не работает, оно точно работает?
данные проверяли, точно в секциях лежат, может в функции секционирования ошибки с датой сделали?
18 фев 13, 14:49    [13943091]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
4k
Всем спасибо за советы, буду пробовать.
Гавриленко Сергей Алексеевич
rowlock или paglock, запретить эскалацию и убедиться, что есть индекс по дате.

Rowlock и paglock не помогает. Происходит укрупнение до уровня таблицы.
Если запрещать эскалацию через флаг dbcc traceon(1211), то эскалации не происходит. Но что-то мне подсказывает, что так делать нехорошо. Какие ещё есть способы? Индекс по дате есть точно - кластерный.
Насчет 2005го не знаю, а в 2008м запрет эскалации - это опция таблицы.

Сообщение было отредактировано: 18 фев 13, 16:29
18 фев 13, 16:28    [13943873]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

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

Команды ALTER TABLE ... SET LOCK_ESCALATION не было в 2005, она появилась в версии 2008.
18 фев 13, 16:38    [13943935]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
4k
Member

Откуда:
Сообщений: 16
aleks2
4k
Гость333, 16000 запросов в день. В месяц примерно 500000. Это немного конечно, однако база транзакционная, должна быть шустрой и отзывчивой. Именно поэтому используется секционирование. Запросы в основном идут к последним двум секциям.

Да, таинство кластерного индекса осталось вне мозга тредстартера....

Не разделяю вашей иронии. Если я в чем-то неправ, подскажите.

2Slava_Nik
секционирование работает. В функции ошибки нет.

2Гость333
Пока вижу выход вот в этом, как приведено по ссылке.
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Влияет ли опция read committed snapshot влияет на операции вставки?
18 фев 13, 21:08    [13945238]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
4k
aleks2
пропущено...

Да, таинство кластерного индекса осталось вне мозга тредстартера....

Не разделяю вашей иронии. Если я в чем-то неправ, подскажите.

2Slava_Nik
секционирование работает. В функции ошибки нет.

2Гость333
Пока вижу выход вот в этом, как приведено по ссылке.
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Влияет ли опция read committed snapshot влияет на операции вставки?
Только не надо устаревшие фичи то использовать, чем вам TOP(500) не понравился?

Deprecated Database Engine Features
19 фев 13, 02:58    [13946089]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
4k
Влияет ли опция read committed snapshot влияет на операции вставки?

Не совсем понятно, что именно вы хотите узнать :-)
Некоторым образом влияет на внутренний механизм хранения записей. На блокировки, накладываемые операцией вставки, не влияет.
Уточните вопрос, в первом сообщении темы шла речь только о конфликтах операций удаления и чтения.
19 фев 13, 10:51    [13947073]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Гость333,

RCSI конфликты модификации и чтения как раз решает хорошо
но вот вопрос почему эскалация сразу до таблицы
возможно, какие-то приколы 2005, в R2 до секции
хотя, если индексы не выровненные - может и больше прихватываться в итоге
19 фев 13, 11:40    [13947395]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Crimean
RCSI конфликты модификации и чтения как раз решает хорошо

Это понятно, но не очень понятно, что конкретно хочет узнать ТС именно про влияние RCSI на insert'ы :-)

Crimean
но вот вопрос почему эскалация сразу до таблицы
возможно, какие-то приколы 2005

Да, я выше писал об этом:
Гость333
4k
Почему блокируется целая таблица, а не отдельная секция?

Это особенность версии 2005. В ней эскалация всегда происходит до уровня таблицы. Эскалация до уровня секции появилась в версии 2008.

Вообще у меня как-то получалось в 2005 наложить X-блокировку на HoBt (т.е. на секцию), но это была не эскалация, а специальная блокировка для регламентного ковыряния в секции.

Crimean
в R2 до секции

Это если опция таблицы LOCK_ESCALATION равна AUTO. При желании можно сказать "ALTER TABLE <имя> SET (LOCK_ESCALATION = TABLE)", и будет как в 2005.
19 фев 13, 11:55    [13947512]     Ответить | Цитировать Сообщить модератору
 Re: блокировка таблицы при удалении 16000 строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Кстати, в хелпе написано
ALTER TABLE
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

TABLE
Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

То есть, получается, в 2008+ эскалация до уровня секции происходит не сама по себе, а после модификации таблицы.
19 фев 13, 12:02    [13947571]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить