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

Откуда:
Сообщений: 39
Народ в общем у меня такая проблема, с которой раньше не сталкивался:
Есть большая таблица с данными по статистике. В нее постоянно идут insert. Переодически на нее делается update (важно старых записей).

В момент когда это происходит insert зависает и ждет окончания работы update. Кто мне может сказать почему и как это вылечить.

В таблице всего 32 миллиона записей. Обновляем кусочки в 200 000 тыс примерно. Индекс весит всего один как раз что бы update быстро отработал.

Как быть кто подскажет?
13 сен 11, 19:20    [11271781]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
Glory
Member

Откуда:
Сообщений: 104751
wghost
В момент когда это происходит insert зависает и ждет окончания работы update. Кто мне может сказать почему и как это вылечить.

Это называется блокировки
Лечится разными способами.
Лечение начинается с анализа блокировок, которые хотят оба запроса
13 сен 11, 19:22    [11271792]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
Так весь прикол в том, что обновляются старые записи, а добавляются новые. никаких чтений на эти таблицы нет.

Вот я и не понимаю какие тут блокировки возникают? Insert и Update 100% не пересекаются.

Подскажите кто знает что это за хрень.

Единственная идея - это из-за перестройки индекса, но как это тогда лечить?
13 сен 11, 19:26    [11271809]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
Le Peace
Member

Откуда: Москва
Сообщений: 8969
Glory
Лечение начинается с анализа блокировок, которые хотят оба запроса
13 сен 11, 19:26    [11271810]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
На таблице тригерров, внешний ключей и первичных нет. обновляется просто вторичное поле
13 сен 11, 19:26    [11271811]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 852
wghost,

Ну, вообще-то это нормально, таблица блокируется при вставке/изменении/удалении.
Вот такой вопрос - а почему не создать другую таблицу, обозвать ее <current table name>_history и сбрасывать туда все старые данные, ну например, все что старше 30 дней начиная с текущего дня? Данные будут вставляться в текущую таблицу, а апдейт будет себе спокойно обрабатывать данные в history-таблице.
13 сен 11, 19:27    [11271815]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
это один из вариантов. Я понимаю, что при update и insert идет блокировка, но никак не всей таблицы, а записей которые затронуты. Или я не прав?
13 сен 11, 19:28    [11271820]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
Народ ну подскажите кто-нибудь как с этим бороться?
13 сен 11, 19:45    [11271901]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
Crimean
Member

Откуда:
Сообщений: 13147
в момент блокировок делаем
1) sp_lock процесс_который_update
2) sp_lock процесс_который_insert

в результатах для (2) ищем строчки с WAIT, в результатах для (1) ищем их же, но с GRANT
показываем все тут, не забыв превратить ИД объектов в имена через object_name
13 сен 11, 19:57    [11271964]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
aleks2
Guest
wghost
Народ ну подскажите кто-нибудь как с этим бороться?


1. Есть ли у тебя, страдалец, кластерный индекс и какие ишо индексы есть в таблице?
2. Идет ли INSERT в хвост или гриву кластерного индекса?
3. Затрагивает ли UPDATE какой-либо индекс?
13 сен 11, 20:02    [11271983]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
Значит результаты у нас следующие:
Для 1:
spid dbid ObjId IndId Type Resource Mode Status
1259 2 0 0 DB [ENCRYPTION_SCAN] S GRANT
1259 7 ResultM1 0 HBT [BULK_OPERATION] S GRANT
1259 7 0 0 MD 14(10000:0:0) Sch-S GRANT
1259 7 ResultM1 8 PAG 1:263553 U GRANT
1259 7 ResultM1 8 PAG 1:255538 U GRANT
.....
1259 7 ResultM1 0 TAB X GRANT
1259 7 ResultM1 0 TAB X GRANT
1259 7 ResultM1 0 TAB X GRANT
1259 7 ResultM1 0 TAB IX GRANT
1259 7 ResultM1 0 TAB IX GRANT
1259 7 ResultM1 0 TAB IX GRANT

Для 2:
spid dbid ObjId IndId Type Resource Mode Status
319 7 ResultM1 0 TAB IX WAIT


Вот примерно такая тема получается. Подскажите что делать дальше. Какие хинты и уровни изоляций ставить?
13 сен 11, 20:12    [11272025]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
aleks2
wghost
Народ ну подскажите кто-нибудь как с этим бороться?


1. Есть ли у тебя, страдалец, кластерный индекс и какие ишо индексы есть в таблице?
2. Идет ли INSERT в хвост или гриву кластерного индекса?
3. Затрагивает ли UPDATE какой-либо индекс?



Сам update:

  UPDATE reports.ReSultM1
          set AppId = null
      where AppId  = 0
         and createdate = N'2011-08-25'

1. В таблице есть один индекс как раз для условия where использующегося в update. Сам индекс как раз на поля дату и AppId
2. Вот на этот вопрос честно ответа нет. как узнать?
3. Update скорее всего именно этот же индекс и затрагивает
13 сен 11, 20:19    [11272046]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
aleks2
Guest
wghost
  UPDATE reports.ReSultM1
          set AppId = null
      where AppId  = 0
         and createdate = N'2011-08-25'

1. В таблице есть один индекс как раз для условия where использующегося в update. Сам индекс как раз на поля дату и AppId
2. Вот на этот вопрос честно ответа нет. как узнать?
3. Update скорее всего именно этот же индекс и затрагивает


1. Не парься с п2. Хотя кластерный индекс по createdate и удаление "индекса как раз для условия where " могло бы, возможно, улучшить ситуацию.

2. Ничо тебе с таким UPDATE и индексом не светит.

3. Уменьшай размер "кусочка в 200 000 тыс" - шинкуй на мелкие порцайки.
13 сен 11, 20:29    [11272078]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Ну, собственно, вот

автор
1259 7 ResultM1 0 TAB X GRANT


и кирдык.

автор
1. В таблице есть один индекс как раз для условия where использующегося в update. Сам индекс как раз на поля дату и AppId
2. Вот на этот вопрос честно ответа нет. как узнать?
3. Update скорее всего именно этот же индекс и затрагивает


И каков план выполнения у этого запроса?
13 сен 11, 20:30    [11272080]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
Народ так в итоге что делать?

У меня это не одна таблица и данных будет тьма, как вообще бороться с такой ситуацией? я имею ввиду, насколько я понял лочиться индекс при update при перестроении? или я ошибаюсь?


Просто я всегда предполагал что update не лочит таблицу, а лочит только записи затрагиваемые им и никак не должен влиять на insert.

Есть-ли возможность в update указать хинт, что бы он не лочил таблицу?
13 сен 11, 23:57    [11272726]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
iljy
Member

Откуда:
Сообщений: 8711
wghost
Народ так в итоге что делать?

У меня это не одна таблица и данных будет тьма, как вообще бороться с такой ситуацией? я имею ввиду, насколько я понял лочиться индекс при update при перестроении? или я ошибаюсь?


Просто я всегда предполагал что update не лочит таблицу, а лочит только записи затрагиваемые им и никак не должен влиять на insert.

Есть-ли возможность в update указать хинт, что бы он не лочил таблицу?

Можно запретить на таблице эскалацию блокировок, но это крайнее средство. Присоединяюсь к вопросу pkarklin - план у запроса какой?
14 сен 11, 00:03    [11272744]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
UPDATE [reports].[DayResultGNA] set [drgna_app_id] = NULL  WHERE [drgna_app_id]=@1 AND [createdate]=@2
  |--Index Update(OBJECT:([banners].[reports].[DayResultGNA].[IX_DayResultGNA_missing_2083]), SET:([Bmk10051029] = [Bmk1005],[drgna_app_id1030] = [banners].[reports].[DayResultGNA].[drgna_app_id],[CreateDate1031] = [banners].[reports].[DayResultGNA].[CreateDate]) WITH ORDERED PREFETCH ACTION:([Act1028]))
       |--Sort(ORDER BY:([banners].[reports].[DayResultGNA].[drgna_app_id] ASC, [banners].[reports].[DayResultGNA].[CreateDate] ASC, [Bmk1005] ASC, [Act1028] ASC))
            |--Filter(WHERE:(NOT [Expr1026]))
                 |--Split
                      |--Compute Scalar(DEFINE:([banners].[reports].[DayResultGNA].[CreateDate]=CONVERT(date,[banners].[reports].[DayResultGNA].[drgna_create_date],0)))
                           |--Table Update(OBJECT:([banners].[reports].[DayResultGNA]), SET:([banners].[reports].[DayResultGNA].[drgna_app_id] = [Expr1004]) WITH UNORDERED PREFETCH)
                                |--Compute Scalar(DEFINE:([Expr1026]=[Expr1026]))
                                     |--Compute Scalar(DEFINE:([Expr1026]=CASE WHEN [Expr1009] THEN (1) ELSE (0) END))
                                          |--Compute Scalar(DEFINE:([Expr1004]=NULL))
                                               |--Top(ROWCOUNT est 0)
                                                    |--Parallelism(Gather Streams)
                                                         |--Table Spool
                                                              |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [banners].[reports].[DayResultGNA].[drgna_app_id] IS NULL THEN (1) ELSE (0) END))
                                                                   |--Index Seek(OBJECT:([banners].[reports].[DayResultGNA].[IX_DayResultGNA_missing_2083]), SEEK:([banners].[reports].[DayResultGNA].[drgna_app_id]=(0) AND [banners].[reports].[DayResultGNA].[CreateDate]='2011-08-24') ORDERED FORWARD)
14 сен 11, 00:21    [11272781]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

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

14 сен 11, 00:23    [11272790]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
iljy
Member

Откуда:
Сообщений: 8711
wghost
|--Table Update(OBJECT:([banners].[reports].[DayResultGNA]), SET:([banners].[reports].[DayResultGNA].[drgna_app_id] = [Expr1004]) WITH UNORDERED PREFETCH)

И че вы хотели? У вас индекс некластерный, и если данные разбросаны по таблице, то блокировка будет очень быстро укрупнена со страничной до табличной.
14 сен 11, 00:35    [11272813]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
aleks2
Guest
iljy
wghost
|--Table Update(OBJECT:([banners].[reports].[DayResultGNA]), SET:([banners].[reports].[DayResultGNA].[drgna_app_id] = [Expr1004]) WITH UNORDERED PREFETCH)

И че вы хотели? У вас индекс некластерный, и если данные разбросаны по таблице, то блокировка будет очень быстро укрупнена со страничной до табличной.


1. У него ОБНОВЛЕНИЕ поля ИНДЕКСА, что означает перестроение оного и полную блокировку ИНДЕКСА => все отдыхают.
2. Что-то мне не нравятся названия таблиц. Может ну его - пусть страдает? Зато прочим станет легше..
14 сен 11, 07:17    [11273023]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2
iljy
пропущено...

И че вы хотели? У вас индекс некластерный, и если данные разбросаны по таблице, то блокировка будет очень быстро укрупнена со страничной до табличной.


1. У него ОБНОВЛЕНИЕ поля ИНДЕКСА, что означает перестроение оного и полную блокировку ИНДЕКСА => все отдыхают.
2. Что-то мне не нравятся названия таблиц. Может ну его - пусть страдает? Зато прочим станет легше..

1. Не, у него Х висит на нулевом индиде, а это куча.
2. Хе-хе, не обратил внимания
14 сен 11, 09:22    [11273277]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
wghost
Member

Откуда:
Сообщений: 39
Народ все это круто, а можете по существу предложить решение, кроме разделения таблиц на ту в которую пишут и таблицу с историей, в которой все будет складываться?
14 сен 11, 09:41    [11273371]     Ответить | Цитировать Сообщить модератору
 Re: Столкнулся с проблемой зависает insert когда на таблицу идет update  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
wghost,

для начала создать кластерный индекс, тока его надо правильно создать, подумать пару дней перед этим:)
можно еще прочитать внимательно весь топик, там куча ответов и решений написана, тока читать надо не по диагонали, а построчно.
ну или просто, если подойти к серверу сзади, там есть кнопка такая неприметная, на ней написано (если с лупой смотреть) "нажми меня я всё исправлю", но это крайний случай не советую ею пользоваться.
14 сен 11, 10:48    [11273706]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить