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

Откуда:
Сообщений: 155
Всем привет.
Практически безостановочно циклом загружается следующая таблица.

Структура таблицы:

CREATE TABLE  DATA_LOG (
	[id] [bigint] NOT NULL,
	[obj_id] [int] NULL,
	[parent_obj_id] [int] NULL,
	[obj_name] [varchar](255) NOT NULL,
	[val] [varchar](8000) NOT NULL
)


Индексы:
CREATE CLUSTERED INDEX IX_DATA_LOG ON DATA_LOG
([id] ASC,[obj_id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

CREATE NONCLUSTERED INDEX IX_OBJ ON DATA_LOG
([obj_name] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)



На протяжении X лет, данная таблица заполнялась следующим скриптом, который работает практически не останавливаясь:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @CNT INT = 1, @MAX BIGINT = 0;
SET @CNT = 1
SET @MAX = (SELECT MAX(id) FROM DATA_LOG);

WHILE @CNT > 0
BEGIN

INSERT INTO DATA_LOG 
SELECT TOP 500000 WITH TIES A.[id],A.[obj_id],A.[parent_obj_id],A.[obj_name],A.[val]
FROM SOURCE_TABLE (NOLOCK) AS A
INNER JOIN HIST_TABLE AS B ON A.id = B.id
WHERE (B.STAGE_CODE= 'FINAL_REQUEST') AND (B.id> @MAX)
ORDER BY A.id ASC

SET @CNT = @@ROWCOUNT
END



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



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FINAL_REQUEST (id BIGINT)
CREATE UNIQUE CLUSTERED INDEX i_id ON #FINAL_REQUEST (id)
DECLARE @CNT INT = 1, @MAX BIGINT = 0;

WHILE @CNT > 0
BIGINT

TRUNCATE TABLE #FINAL_REQUEST	
SET @MAX = (SELECT MAX(id) FROM DATA_LOG (NOLOCK));
INSERT INTO #FINAL_REQUEST
SELECT TOP 300 id FROM HIST_TABLE  WHERE id > @MAX AND STAGE_CODE= 'FINAL_REQUEST	' ORDER BY id

INSERT INTO DATA_LOG
SELECT A.[id],A.[obj_id],A.[parent_obj_id],A.[obj_name],A.[val]
FROM #FINAL_REQUEST AS B
INNER JOIN SOURCE_TABLE  (NOLOCK) AS A ON A.id= B.id
ORDER BY A.id ASC

SET @CNT = @@ROWCOUNT
END


Вот тут скорость замечательно выросла и вместо 15 минут один шаг стал выполняться около минуты.


Параллельно этому циклу через сервис брокер заполняются порядка 40 таблиц на основе данных из таблицы DATA_LOG.
Все таблички заполняются по одному принципу:
Логика заполнения у всех одинаковая:

DECLARE @Max bigint = (SELECT MAX(id) FROM A1)

INSERT INTO A1
SELECT A.id, A.obj_name, D.[id] AS [index], B.val
FROM (select id from only_id where id > @Max and id <= @MAX + 500 ) AS E
INNER JOIN DATA_LOG AS C ON (E.id = C.id) AND (C.obj_name = 'WORK')
INNER JOIN DATA_LOG AS A ON A.id = E.id AND (c.obj_id = a.parent_obj_id)
INNER JOIN DATA_LOG AS B ON (A.id = B.id) AND (A.obj_id = B.parent_obj_id)
INNER JOIN DIM_INDEX AS D ON D.obj_name = B.obj_name
WHERE (A.obj_name in ('INN','COMPANY_NAME','ADDRESS'))
AND A.id > @Max 
AND B.id > @Max 
AND C.id > @Max 
OPTION(RECOMPILE)


DECLARE @Max bigint = (SELECT MAX(id) FROM A2)

INSERT INTO A2
SELECT A.id, A.obj_name, D.[id] AS [index], B.val
FROM (select id from only_id where id > @Max and id <= @MAX + 500 ) AS E
INNER JOIN DATA_LOG AS C ON (E.id = C.id) AND (C.obj_name = 'CLIENT')
INNER JOIN DATA_LOG AS A ON A.id = E.id AND (c.obj_id = a.parent_obj_id)
INNER JOIN DATA_LOG AS B ON (A.id = B.id) AND (A.obj_id = B.parent_obj_id)
INNER JOIN DIM_INDEX AS D ON D.obj_name = B.obj_name
WHERE (A.obj_name in ('SURNAME','NAME','BIRTHDAY'))
AND A.id > @Max 
AND B.id > @Max 
AND C.id > @Max 
OPTION(RECOMPILE)


итд 40 таблиц


И проблема в том, что после изменения скрипта цикла который заполняет таблицу DATA_LOG , регулярно запросы по заполнению таблиц а1-а40
стали отваливаться из за дедлока. В вопросе блокировок я прям не силен и сколько не читал не могу осилить. Поэтому прошу помочь.
Интуитивно кажется, что дело именно именно в цикле, потому что есть еще 3 процесса под копирку как этот, там цикл не был переписан и дедлока никогда не возникает и не возникало.

Как можно переписать или добавить какие то хинты чтобы заполнение таблицы DATA_LOG не вызывало дедлоков у процедуры запускаемой через сервис брокер?

Сообщение было отредактировано: 1 ноя 19, 19:52
1 ноя 19, 19:50    [22008451]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с причиной блокировки  [new]
defragmentator
Member

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

хинты (nolock) добавить можно на DATA_LOG, вопрос в том, устроит ли тебя "грязное чтение" ?
1 ноя 19, 21:11    [22008495]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с причиной блокировки  [new]
assmsk
Member

Откуда:
Сообщений: 155
defragmentator, имеется ввиду nolock в запросы которые выполняет сервисе брокер?

DECLARE @Max bigint = (SELECT MAX(id) FROM A2)

INSERT INTO A2
SELECT A.id, A.obj_name, D.[id] AS [index], B.val
FROM (select id from only_id where id > @Max and id <= @MAX + 500 ) AS E
INNER JOIN DATA_LOG  [color=red](NOLOCK)[/color] AS C ON (E.id = C.id) AND (C.obj_name = 'CLIENT')
INNER JOIN DATA_LOG  [color=red](NOLOCK)[/color] AS A ON A.id = E.id AND (c.obj_id = a.parent_obj_id)
INNER JOIN DATA_LOG  [color=red](NOLOCK)[/color] AS B ON (A.id = B.id) AND (A.obj_id = B.parent_obj_id)
INNER JOIN DIM_INDEX AS D ON D.obj_name = B.obj_name
WHERE (A.obj_name in ('SURNAME','NAME','BIRTHDAY'))
AND A.id > @Max 
AND B.id > @Max 
AND C.id > @Max 
OPTION(RECOMPILE)



Проблем с грязным чтением возникнуть не должно, тк в таблице (select id from only_id where id > @Max and id <= @MAX + 500 ) лежат id которые уже закомичены в таблице DATA_LOG.


Но в целом конечно непонятно как так получается, для меня как человека непросвещенного в блокировках кажется,
что это если в какую то таблицу идет долгая вставка а я просто из нее селекчу, то когда вставка закончится я должен получить свой запрос в любом случае, а не дедлок(
1 ноя 19, 21:28    [22008504]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с причиной блокировки  [new]
defragmentator
Member

Откуда:
Сообщений: 20460
assmsk
имеется ввиду nolock в запросы которые выполняет сервисе брокер?


Ну да, сервис брокер их просто не увидит.
1 ноя 19, 21:35    [22008507]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с причиной блокировки  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36501
Только графы дедлоков спасут отца русской демократии.
1 ноя 19, 23:54    [22008543]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить