Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Сервер 2008.
Есть программа которая отправляет такой запрос, все это в хранимке.
...
set @regno = ЕщеХранимка (@numberfilial, @date);
-- @numberfilial  параметр этой хранимки, а @date это обычный getdate(), из них формируется уникальный номер 
select @count=Count("Номер")
from table with (tablock, xlock)
where "Номер"=@regno
.....

если @count = 0 то в table вставляется @regno и Getdata(), если нет, то такой номер уже есть, тогда увел. date на одну минуту и пробуем снова.
Как результат, частые Deadlock, два процесса накладывают S блокировку и хотят получить X.
Разрабы программы говорят это архитектурное решение, но такое нас не устраивает. Можно ли как нибудь если не убрать то уменьшить кол-во Deadlock'ов?
6 май 16, 10:32    [19141394]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yayaadmin
Как результат, частые Deadlock, два процесса накладывают S блокировку и хотят получить X.

S при with (tablock, xlock) или когда ?

Yayaadmin
Можно ли как нибудь если не убрать то уменьшить кол-во Deadlock'ов?

Для начала выясняют их причины
6 май 16, 10:35    [19141413]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Код хранимки
        @Code varchar(4) 
AS 
BEGIN 
        SET NOCOUNT ON 

        DECLARE @message varchar(255) 
        DECLARE @regno numeric(13, 0) 
        DECLARE @count int 
        DECLARE @exitFlag bit 
        DECLARE @date datetime 

        SET @exitFlag = 0 
        SET @date = GETDATE() -- точка отсчета для формирования нового регномера 

        WHILE (@exitFlag <> 1) BEGIN 

                SET XACT_ABORT ON 
                BEGIN TRAN 

                /* формируем новый рег.номер */                 
                SET @regno = dbo.Хранимка (@ifnsCode, @date) 
                IF (@regno = 0) BEGIN 
                        SET @message = 'Произошла ошибка при формировании рег.номера. Входные данные: ' +  
                                @Code + ', ' + CONVERT(varchar(10), @date, 104) + ' ' +  
                                CONVERT(varchar(2), DATEPART(HH, @date)) + ':' +  
                CONVERT(varchar(2), DATEPART(MI, @date)) 
                        RAISERROR (@message, 16, -1) 
                        RETURN -1 
                END 

                SELECT 
                        @count = COUNT("Номер") 
                FROM 
                        dbo.table WITH(TABLOCK, XLOCK) 
                WHERE 
                        "Номер" = @regno 

                IF (@count = 0) BEGIN 
            /* нет такого рег.номера, резервируем его */ 
            INSERT INTO dbo.table ( 
                    "Номер",  
                    "Дата" 
            ) VALUES ( 
                    @regno,  
                    GETDATE() 
            ) 
            SET @exitFlag = 1 
                END 
                ELSE 
                        /* есть такой рег.номер, подготовим дату для формирования нового */ 
                        SET @date = DATEADD(MI, 1, @date) -- увеличиваем дату на 1 минуту 

                COMMIT TRAN 
        END 
         
        SELECT @regno -- возвращаем свободный рег.номер 
END 
6 май 16, 10:40    [19141440]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
[quot Glory]
Yayaadmin
Как результат, частые Deadlock, два процесса накладывают S блокировку и хотят получить X.

S при with (tablock, xlock) или когда ?

Да. В Profiler посмотрел Deadlock Graph, два процесса выполняют эту хранимку, оба держат S блокировку и оба пытаются получить X.
6 май 16, 10:52    [19141496]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yayaadmin
оба держат S блокировку

Еще раз вопрос - откуда они берут эту S ?
6 май 16, 10:57    [19141525]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Glory
Yayaadmin
оба держат S блокировку

Еще раз вопрос - откуда они берут эту S ?


Не знаю. Сейчас запущу занаво профайлер и буду вылавливать.
Я думал что select с хинтами сначала накладывает S, а потом увеличивает ее до X.
6 май 16, 11:02    [19141553]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Glory
Member

Откуда:
Сообщений: 104760
Yayaadmin
Я думал что select с хинтами сначала накладывает S, а потом увеличивает ее до X.

Мда уж
6 май 16, 11:26    [19141712]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Glory
Yayaadmin
Я думал что select с хинтами сначала накладывает S, а потом увеличивает ее до X.

Мда уж


Я имел ввиду именно с хинтом xlock. Буду знать что ошибался.
6 май 16, 11:33    [19141743]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Yayaadmin,


Там точно нужна блокировка WITH(TABLOCK, XLOCK) ?

Если её нельзя убрать попробуйте

OPTION (MAXDOP 1) на запросах внутри транзакции
6 май 16, 11:37    [19141769]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
a_voronin
Yayaadmin,


Там точно нужна блокировка WITH(TABLOCK, XLOCK) ?

Если её нельзя убрать попробуйте

OPTION (MAXDOP 1) на запросах внутри транзакции


В том то и дело что эти хранимки создались при установке ПО и изменять их вроде как незя, на тесте попробую конечно, но на реалке если разрабы сказли так надо то очень сложно руководство переубедить, надо прям показать что вот так будет лучше, ну и тогда уже может что то изменится.
Кстати, еще один лок был, и оба процесса за 10 минут ничего кроме этой хранимки и sp_reset_connection не запускали.
Тогда откуда S, как писал Glory, не понимаю...
6 май 16, 11:49    [19141851]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
a_voronin
Yayaadmin,


Там точно нужна блокировка WITH(TABLOCK, XLOCK) ?

Если её нельзя убрать попробуйте

OPTION (MAXDOP 1) на запросах внутри транзакции


И еще, как это должно помочь?
6 май 16, 11:54    [19141888]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Konst_One
Member

Откуда:
Сообщений: 11540
поищи инфу по ms sql sequence
если версия сервера не позволяет, то есть решения по эмуляции последовательностей, как на форуме ,так и в гугле
6 май 16, 12:01    [19141938]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Konst_One
поищи инфу по ms sql sequence
если версия сервера не позволяет, то есть решения по эмуляции последовательностей, как на форуме ,так и в гугле


Бегло ознакомился. Т.е. вы предлагаете как альтернативу последовательность, заместо:
select @count=Count("Номер")
from table with (tablock, xlock)
where "Номер"=@regno
Т.е. чтобы каждый следующий номер процесса просто брался из последовательности? Возможно это и имело бы место быть, но:
1) Версия сервака не та
2) Это вообще логику программы надо менять в плане присвоения номеров процессам
3) Меня разрабы пошлют куда подальше даже если я им и предложу, доп. работу они не любят.
6 май 16, 12:11    [19142019]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Yayaadmin
И еще, как это должно помочь?
В том-то и дело, что никак. Может только усугубить.

ЗЫ: Хотите конструктивных ответов - показывайте граф дедлока в формате xdl.
6 май 16, 12:11    [19142023]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
invm
Yayaadmin
И еще, как это должно помочь?
В том-то и дело, что никак. Может только усугубить.

ЗЫ: Хотите конструктивных ответов - показывайте граф дедлока в формате xdl.


Дедлоки могут возникать внутри одного процесса при распараллеливании.
6 май 16, 12:23    [19142087]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
msLex
Member

Откуда:
Сообщений: 8215
a_voronin
invm
пропущено...
В том-то и дело, что никак. Может только усугубить.

ЗЫ: Хотите конструктивных ответов - показывайте граф дедлока в формате xdl.


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


При (TABLOCK, XLOCK)?

может привести репро?
6 май 16, 12:25    [19142102]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
msLex,

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/26/reproducing-one-more-intermittent-deadlock-on-only-one-table.aspx
6 май 16, 12:38    [19142170]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
o-o
Guest
обе ссылки отклоняются.
пвот такое у вас попросили
(сейчас трудно "не заметить" хинты, надеюсь):
msLex
a_voronin
пропущено...
Дедлоки могут возникать внутри одного процесса при распараллеливании.

При (TABLOCK, XLOCK)?
может привести репро?
6 май 16, 12:42    [19142189]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
o-o,

отклоняется дерево на ветру.
6 май 16, 12:59    [19142270]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
msLex
Member

Откуда:
Сообщений: 8215
a_voronin
o-o,

отклоняется дерево на ветру.

я правильно понял ваш ответ, что репро с дедлоком на одном запросе с (TABLOCK, XLOCK) не будет?
6 май 16, 13:02    [19142282]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
o-o
Guest
про дерево да, в тему.
с ворониным спорить, что против ветра **ать, простите
6 май 16, 13:02    [19142283]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Yayaadmin,

Нужен граф + код функции, которая возвращает @regno, без этого можно только строить догадки.

Догадка 1: ЕщеХранимка (@numberfilial, @date); читает таблицу table и ставит шаред блокировку. В это время другой процесс тоже ставит шаред блокировку, выполняя эту-же функцию (это скалярная функция, а не хранимка). Если так, то это косяк разработчиков софта такое выпустить в продашн, так как невооружённым глазолм видно что будут дедлоки если с сервером будет работать больше одного пользователя. Как пофиксить быстро: ставить (tablock, xlock) в функции ЕщеХранимка (@numberfilial, @date); при чтении таблицы table. Про производительность этого решения умолчу, но исходя из того что таблицу дальше лочат полностью, предплагаю что на это может быть вполне приемлимо.

Догадка 2: Включить для базы уровень изоляции READ COMMITTED SNAPSHOT (код хранимок или приложения менять не придётся). Если логика программы (получения номеров) при этом не пострадает, то проблема с дедлоками решится сама собой (уйдйт шаред блокировки на чтение).
6 май 16, 13:41    [19142488]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
msLex
a_voronin
o-o,

отклоняется дерево на ветру.

я правильно понял ваш ответ, что репро с дедлоком на одном запросе с (TABLOCK, XLOCK) не будет?


нет, не будет. Зачем мне страдать фигнёй. Погуглите.

Я лишь сообщил информацию

"Дедлоки могут возникать внутри одного процесса при распараллеливании."
6 май 16, 13:50    [19142553]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
a_voronin
нет, не будет. Зачем мне страдать фигнёй. Погуглите.
Вам бы не других в гугл отправлять, а матчасть подучить.
Чтобы осознать - при хинтах на таблицу (tablock, xlock) intraquery parallelism deadlock возникнуть не может.
6 май 16, 14:36    [19142829]     Ответить | Цитировать Сообщить модератору
 Re: Постоянные Deadlock'и  [new]
Yayaadmin
Guest
Denis Reznik
Yayaadmin,

Нужен граф + код функции, которая возвращает @regno, без этого можно только строить догадки.

Догадка 1: ЕщеХранимка (@numberfilial, @date); читает таблицу table и ставит шаред блокировку. В это время другой процесс тоже ставит шаред блокировку, выполняя эту-же функцию (это скалярная функция, а не хранимка). Если так, то это косяк разработчиков софта такое выпустить в продашн, так как невооружённым глазолм видно что будут дедлоки если с сервером будет работать больше одного пользователя. Как пофиксить быстро: ставить (tablock, xlock) в функции ЕщеХранимка (@numberfilial, @date); при чтении таблицы table. Про производительность этого решения умолчу, но исходя из того что таблицу дальше лочат полностью, предплагаю что на это может быть вполне приемлимо.

Догадка 2: Включить для базы уровень изоляции READ COMMITTED SNAPSHOT (код хранимок или приложения менять не придётся). Если логика программы (получения номеров) при этом не пострадает, то проблема с дедлоками решится сама собой (уйдйт шаред блокировки на чтение).


Да, все верно, это скалярная функция, и еще во втором посте
SET @regno = dbo.Хранимка (@ifnsCode, @date) - должно быть
SET @regno = dbo.Хранимка (@Code, @date), не то написал.
Код функции к сожалению приложить не смогу, только во Вт. Но я ее смотрел и она вроде бы не работает с table которая блокируется, но сейчас точно не скажу.
Эта хранимка выполняется за тысячные секунды, вызывается она часто, и дедлоки возникают только когда кол-во активных пользователей переваливает за 2000, а такая нагрузка раньше была нечасто, поэтому возникали они редко, сейчас же постоянно. Это я к чему, разрабы попробовали на 10 пользователях видимо, все норм и выпустили в продакшн.
Граф в формате xdl приложил.
7 май 16, 08:38    [19145079]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить