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

Откуда:
Сообщений: 1056
Коллеги, помогите снизить количество блокировок между пользователями в такой ситуации:
Сервер 2005.
Имеется БД. Уровень изоляции транзакций НЕ snapshot (и, видимо, менять нельзя. По умолчанию стоит "повторяемое чтение")
Имеется довольно большая, ~ 2млн записей таблица.
Ключ - автоикрементное последовательное int поле, оно же кластерный индекс.
По таблице построено еще несколько индексов, типа поле include список_полей для ускорения запросов.

В таблицу одновременно пишут и читают несколько пользователей (~10+)
Основной способ, которым они пишут - это insert into большая_таблица select from довольно_сложный_запрос_хз_откуда.
Этот запрос возвращает ~1 - 10 000+ записей.
Таблица - довольно широкая, вставка большого количества записей занимает иногда десятки секунд - минуты.
Пользователи также читают из этой таблицы. Но читают то, что вставили (поэтому запросы на чтение With (nolock) слегка подправили ситуацию).
Помогите поправить ситуацию с lock-ами при вставке!

Что в этом смысле можно сделать? Посоветуйте!
7 окт 11, 19:02    [11404762]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
komrad
Member

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

сам селект то сколько по времени выполняется?
7 окт 11, 19:15    [11404817]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
aleks2
Guest
uaggster
По таблице построено еще несколько индексов, типа поле include список_полей для ускорения запросов.
Что в этом смысле можно сделать? Посоветуйте!


1. Или удалить индексы.
2. Или ничего.
7 окт 11, 19:20    [11404833]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
uaggster
Основной способ, которым они пишут - это insert into большая_таблица select from довольно_сложный_запрос_хз_откуда.
Этот запрос возвращает ~1 - 10 000+ записей.
Таблица - довольно широкая, вставка большого количества записей занимает иногда десятки секунд - минуты.
Именно вставка "занимает иногда десятки секунд - минуты" или "довольно_сложный_запрос_хз_откуда"?

Сомнительно, что вставка 1 - 10 000 записей занимает столько времени... Посмотрите на плане, что ли...

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

Ну и ещё вариант, если не поможет - вставлять данные в таблицу-переменную, потом вставлять в таблицу маленькими порциями (допустим, по 100-1000 строк).
7 окт 11, 20:31    [11405101]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
uaggster
Member

Откуда:
Сообщений: 1056
komrad
uaggster,
сам селект то сколько по времени выполняется?

< ~15 секунд.

alexeyvg
Именно вставка "занимает иногда десятки секунд - минуты" или "довольно_сложный_запрос_хз_откуда"?
Сомнительно, что вставка 1 - 10 000 записей занимает столько времени... Посмотрите на плане, что ли...

В комплексе :-)
Сам запрос на выборку целевую таблицу лочить не может (т.е. выборка идет откуда угодно, но не из целевой таблицы).
Лочит конструкция insert into ... select from ...

aleks2
uaggster
По таблице построено еще несколько индексов, типа поле include список_полей для ускорения запросов.
Что в этом смысле можно сделать? Посоветуйте!

1. Или удалить индексы.
2. Или ничего.

Всё так плохо?

А так думаю, избыточные локи могут наблюдаться:
1. Из-за того, что конкурирующая запись от разных пользователей ломится на одну и ту же физическую страницу (там же кластерный индекс по автоинкременту, значит все вставки будут наблюдаться в последние страницы). Соответственно lock нескольких "нижних" страниц будет эквивалентен lock-у всей таблицы.
2. Может быть вставок "много", и sql решает залочить всю таблицу (т.е. эскалация блокировок)
3. Из-за перестроения индексов. Само по себе добавляет времени на вставку, но ведь они при перестроении тоже лочатся...

Вопрос... Если 1) Удалить кластерный индекс по автоинкременту 2) Заменить автоикремент с последовательного на "хаотический" 3) задать AllowPageLocks=false для имеющихся индексов - это даст какой - нибудь эффект?

Видел где то мануал по этому поводу (правда, еще для 7-ки), но где видел - забыл :-(
7 окт 11, 22:32    [11405634]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
uaggster
Member

Откуда:
Сообщений: 1056
От блин, MSDN говорит, что insert loчит соответствующие строки/страницы на все время выполнения запроса, а не только на время физической вставки данных!
7 окт 11, 22:50    [11405708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
iljy
Member

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

а вы смотрели блокировки или так, погадать решили? Есть ли эти самые табличные блокировки?
Вообще можно все-таки попробовать съэкономить через предварительное сохранение данных во временную таблицу. Тогда блокировки целевой таблицы не будут зависеть от времени чтения.
7 окт 11, 22:50    [11405710]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
iljy
Member

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

не табличные, а страничные.
7 окт 11, 22:51    [11405715]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster
От блин, MSDN говорит, что insert loчит соответствующие строки/страницы на все время выполнения запроса, а не только на время физической вставки данных!

И кстати, не на время вставки, а на время выполнения транзакции.
7 окт 11, 22:57    [11405752]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
uaggster
Member

Откуда:
Сообщений: 1056
iljy
uaggster,

а вы смотрели блокировки или так, погадать решили? Есть ли эти самые табличные блокировки?

Да, смотрел. Обычно там pagelock, иногда при вставке >25000 записей - лочится именно что вся таблица.

iljy
uaggster,

И кстати, не на время вставки, а на время выполнения транзакции.


Ё... В этом свете совет
iljy
Вообще можно все-таки попробовать съэкономить через предварительное сохранение данных во временную таблицу. Тогда блокировки целевой таблицы не будут зависеть от времени чтения.
еще нужно обдумать.
Дело в том, что данные пишутся из хранимой процедуры, а там еще несколько вставок и апдейтов в рамках одной транзакции.
Они короткие (вставка или апдейт одной строки), но должны проходить строго целостно с больным запросом.
В таком разрезе вставка сначала в темповую таблицу - поможет?

Т.е. открываем транзакцию, делаем несколько мелких вставок, запускаем больной запрос, грузим результат в темпдб, грузим результат из темпдб в целевую таблицу, закрываем транзакцию - снизит нам количество блокировок?
8 окт 11, 00:15    [11406072]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster
Т.е. открываем транзакцию, делаем несколько мелких вставок, запускаем больной запрос, грузим результат в темпдб, грузим результат из темпдб в целевую таблицу, закрываем транзакцию - снизит нам количество блокировок?

Лучше сначала загрузить все данные во временные таблицы, а потом уже делать модификации. Причем возможно есть смысл сначала сделать большую вставку. Потому что ваши мелкие вставки тоже накладывают блокировки, которые будут висеть до конца транзакции, и чем позже вы их повесите, тем меньше они будут висеть.
8 окт 11, 00:19    [11406108]     Ответить | Цитировать Сообщить модератору
 Re: Помогите снизить количество блокировок!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
uaggster
От блин, MSDN говорит, что insert loчит соответствующие строки/страницы на все время выполнения запроса, а не только на время физической вставки данных!
Я про это и говорю - поэтому и может помочь времянка.

Ну и с индексами поаккуратнее.
uaggster
Т.е. открываем транзакцию, делаем несколько мелких вставок, запускаем больной запрос, грузим результат в темпдб, грузим результат из темпдб в целевую таблицу, закрываем транзакцию - снизит нам количество блокировок?
Лучьше классический подход (как iljy сказал) - подготавливаем всё во временных таблицах, потом всё грузим.
8 окт 11, 00:28    [11406154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить