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

Откуда:
Сообщений: 1196
Два оператора MERGE лочатся на индексированном представлении.
Вот граф дедлока.

К сообщению приложен файл (deadlock1.xdl - 4Kb) cкачать
7 дек 12, 19:19    [13596851]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
Приведите пожалуйста полный текст процедур WC.Amazon.UpdateDWRatingHistory, WC.OLTP.RatingHistoryAdd.
Также нужна структура DW.RatingHistory со всеми индексами
И структура view DW.mvRatingHistoryCrawler.
7 дек 12, 19:36    [13596969]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Пожалуйста.

К сообщению приложен файл (Sources.sql - 7Kb) cкачать
7 дек 12, 19:50    [13597050]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а вы хинты не перепутали? может все же TABLOCKX?
7 дек 12, 21:07    [13597416]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
Я так понимаю Snapshot транзакция в UpdateDWRatingHistory используется, чтобы получать соглассованные данные из Amazon.ProductReviews и Amazon.ProductRatingByReview.
Если действительно большой процент строк обновлются, то лучше использовать TABLOCKX.

Что касается OLTP.RatingHistoryAdd, то там 100 лет не нужна SNAPSHOT изоляця.
2) вынесите заполнение табличной переменной до цикла. Потому что табличные переменные нетранзакционные. У вас pk violation будет в случае уже 2ого цикла.
Пример :

+
declare @a table (id int not null primary key) 

begin tran 

insert @a (id) values(1) 
rollback tran 
select * 
from @a ;


3) Я думаю для обновления рейтингов по одному продукту не нужно блокировать всю таблицу.
Поэтому я бы заменил зачитку внутри merge на обычную серилизацию по продукту :

WITH RH AS (
                   SELECT ProductId, ReviewCode, ReviewDate, Stars, UpdateDate
	       FROM DW.RatingHistory WITH WITH(XLOCK, HOLDLOCK)
                  WHERE (ProductId = @ProductId)
                )
8 дек 12, 15:13    [13599958]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
Еще можно упростить view для человеческой аггрегации :
CREATE VIEW [DW].[mvRatingHistoryCrawler]
WITH SCHEMABINDING 
AS
SELECT 
         P.CrawlerId,
         COUNT_BIG(*) AS [RowCount]

FROM DW.RatingHistory AS RH

INNER JOIN DW.Product AS P 
        ON RH.ProductId = P.ProductId 
                      
GROUP BY P.CrawlerId


К этой view можно легко присоединять таблицу Crawler для получения имени.
8 дек 12, 15:33    [13600018]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
нязнайка
Я так понимаю Snapshot транзакция в UpdateDWRatingHistory используется, чтобы получать соглассованные данные из Amazon.ProductReviews и Amazon.ProductRatingByReview.
Если действительно большой процент строк обновлются, то лучше использовать TABLOCKX.

Ага. Поставил TABLOCKX.

нязнайка
Что касается OLTP.RatingHistoryAdd, то там 100 лет не нужна SNAPSHOT изоляця.

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

нязнайка
2) вынесите заполнение табличной переменной до цикла. Потому что табличные переменные нетранзакционные. У вас pk violation будет в случае уже 2ого цикла.

Спасибо за замечание. Не доглядел как-то. Сделано.

нязнайка
3) Я думаю для обновления рейтингов по одному продукту не нужно блокировать всю таблицу.
Поэтому я бы заменил зачитку внутри merge на обычную серилизацию по продукту :

WITH RH AS (
                   SELECT ProductId, ReviewCode, ReviewDate, Stars, UpdateDate
	       FROM DW.RatingHistory WITH (XLOCK, HOLDLOCK)
                  WHERE (ProductId = @ProductId)
                )


Думаю, HOLDLOCK тут лишний. XLOCK и так накладывает монопольную блокировку до конца транзакции.
Но вот по поводу гранулярности блокировок - дедлоки часто возникают из-за блокировок на уровне строки или страницы. Но тем не менее, поставил (XLOCK, HOLDLOCK).
И в итоге:

К сообщению приложен файл (deadlock2.xdl - 4Kb) cкачать
10 дек 12, 12:26    [13605617]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
нязнайка
Еще можно упростить view для человеческой аггрегации :
CREATE VIEW [DW].[mvRatingHistoryCrawler]
WITH SCHEMABINDING 
AS
SELECT 
         P.CrawlerId,
         COUNT_BIG(*) AS [RowCount]

FROM DW.RatingHistory AS RH

INNER JOIN DW.Product AS P 
        ON RH.ProductId = P.ProductId 
                      
GROUP BY P.CrawlerId


К этой view можно легко присоединять таблицу Crawler для получения имени.


В этой вьюхе всего 18 строк и план запроса нормальный. Используется в нескольких репортах,
не хотелось бы менять. Не думаю, что это критично.
10 дек 12, 12:32    [13605675]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
В этой вьюхе всего 18 строк и план запроса нормальный. Используется в нескольких репортах,
не хотелось бы менять. Не думаю, что это критично.


Записей всего 18, потому что мало различных crawler, но вот count_big просто может быть достаточно большим.
Очень важно чтобы был хороший план, обновления вью. Сейчас он наверное ужасный. Приведите, может, план обновления по продукту.

И все-таки попробуйте создать другую индексированную вью вместо этой, с моим кодом. Эту переделать на соединение с новой, для получения имени.
И всего лишь осталось убрать NOEXPAND из всех мест.
10 дек 12, 12:54    [13605896]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
нязнайка,

Ок, сделал вьюху без ссылки на таблицу Crawler. Вдобавок ко всему на индексах и таблицы, и вьюхи поставил
ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, чтобы не было блокировок на уровне строк и страниц.

Результат всё тот же.
10 дек 12, 13:16    [13606101]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF
Зачем их убирать ?
Оставьте 2 уровня.

У меня подозрение, доступ производится в разном порядке в случае tablaockx и нескольких строк. Т.е в одном из случаев сначала обновляется базовая таблица потом только вью во втором - наоборот.
Чтобы это подтвердить или опровергнуть нужны планы 2ух обновлений.
+ попробуйте пожалуйста (просто для интереса) поставить tablockx в обоих случаях.
10 дек 12, 13:23    [13606155]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
С ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON для таблицы и для вьюхи и с TABLOCKX в обеих процедурах пока работает нормально.

Вот такие блокировки накладываются.
request_session_idSchemaObjectrequest_moderesource_typeLock Number request_status sp
277 DW mvRatingHistoryCrawlerIXOBJECT 1 GRANT OLTP.RatingHistoryAdd
277 DW RatingHistory X OBJECT 1 WAIT OLTP.RatingHistoryAdd
355 DW mvRatingHistoryCrawler IX OBJECT 1 GRANT Amazon.UpdateDWRatingHistory
355 DW RatingHistory X OBJECT 24 GRANT Amazon.UpdateDWRatingHistory
10 дек 12, 13:57    [13606431]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
А с XLOCK при обновлении по продукту ?

А что если в массовом обновлении открыть Snapshot транзакцию, заполнить временную таблицу ( с индексом по product + ReviewCode), закрыть эту транзакцию.

А уже в новой простой READ COMMITTED делать MERGE.
Интересна природа этого дедлока. А TABLOCKX естественно полностью серилизует доступ к таблице.
И еще приведите пожалуйста планы с XLOCK по продукту и TABLOCKX массовый.
10 дек 12, 14:11    [13606541]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
нязнайка,

Реальные планы взятые из sys.dm_exec_requests.

К сообщению приложен файл (ExecutionPlans.zip - 39Kb) cкачать
10 дек 12, 15:06    [13607046]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
И, по-моему, они одинаковы.
10 дек 12, 15:08    [13607055]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Блокировки для XLOCK

request_session_id Schema Object request_mode resource_type Lock Number request_status sp
198 DW mvRatingHistoryCrawler IX OBJECT 1 GRANT OLTP.RatingHistoryAdd
198 DW RatingHistory IX OBJECT 1 WAIT OLTP.RatingHistoryAdd
355 DW mvRatingHistoryCrawler IX OBJECT 1 GRANT Amazon.UpdateDWRatingHistory
355 DW RatingHistory X OBJECT 24 GRANTAmazon.UpdateDWRatingHistory


Тут на DW.RatingHistory накладывется блокировка намерения вместо монопольной.
10 дек 12, 15:30    [13607278]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Мне кажется, что весь прикол был в ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF для вьюхи.
Так стояло изначально.
10 дек 12, 15:33    [13607293]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
нязнайка
Guest
Понимаете, запрещение блокировок на уровне строк и страниц одновременно делает XLOCK = TABLOCKX .
А тут проблема разного порядка обращения к объектам.
+ как-то очень странно, в приведенных скриптах объекты не секционированы (ни таблица, ни представление).
А граф содержит :
 <objectlock lockPartition="3" objid="776493945"


Какая-то непонятная секция номер 3. Что это может быть ? Объекты секционированы ?
Иначе было бы 0.
10 дек 12, 16:13    [13607736]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock на индексированном представлении  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
нязнайка
Какая-то непонятная секция номер 3. Что это может быть ? Объекты секционированы ?
Иначе было бы 0.

Нет, ни таблица, ни вьюха не секционированы, хотя в базе много секционированных объектов.
10 дек 12, 16:46    [13608107]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить