Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
 select @@version


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )

Выдаваемая ошибка:
Transaction (Process ID 51) was deadlocked on lock | generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Примитивная инструкция на которой процесс задидлочил сам себя:
UPDATE [tbCompositions] SET [CompositionName] = @CompositionName WHERE [CompositionGuid] = @CompositionGuid

Файл с графом дидлока во вложении.

Читал по форуму про проблему самодидлочения, - только сервиспаки советуют ставить, да писать письма в Микрософт.
Дидлок этот у нас первый раз возник без установленых сервиспаков.
Поставили SP, он все равно гарантировано воспроизводится.

Вопрос:
Кроме написания писем в MS, есть еще какието направления как можно попытаться решить эту проблему?
В какую сторону смотреть?

К сообщению приложен файл (Deadlock_2_SP2.xdl - 6Kb) cкачать
9 ноя 17, 11:54    [20939502]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Зарубежная пресса предлагает попробовать

UPDATE [tbCompositions] SET [CompositionName] = @CompositionName WHERE [CompositionGuid] = @CompositionGuid OPTION (MAXDOP 1) 
9 ноя 17, 12:21    [20939598]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Молодой,

я так понимаю что вы в SSMS это не воспроизводите. И если рыть в сторону ожидания transaction mutex то у вас скорее всего mars исполбзуется, что делать вопрос :)
9 ноя 17, 12:41    [20939679]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Молодой
Member

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

От сюда:
https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars

вычитал про MARS:
"MARS enables the interleaved execution of multiple requests within a single connection"

Правильно я понял, что есть возможность того, что внешняя программа, имея на SQL сервере коннектион (SPID=51) не дождавшись
выполнения инструкции
UPDATE [tbCompositions] SET [CompositionName] = @CompositionName WHERE [CompositionGuid] = @CompositionGuid 

попыталась еще какой то запрос произвести под тем же SPID=51?
9 ноя 17, 13:03    [20939786]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Молодой,
вот описания как получить, в том числе ваш orwell.net ожетжет юзать MultipleActiveResultSets=true
https://sqlperformance.com/2012/12/t-sql-queries/transaction-mutex
9 ноя 17, 13:06    [20939793]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Молодой
Member

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

Спасибо за подсказку. Запрос пока менять не хотелось бы.
Пока сможем проверить вариант:

USE DB  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  


По идее, тоже самое должен дать, что и
 OPTION (MAXDOP 1) 
9 ноя 17, 13:09    [20939805]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Молодой
Cammomile,

Спасибо за подсказку. Запрос пока менять не хотелось бы.
Пока сможем проверить вариант:

USE DB  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  


По идее, тоже самое должен дать, что и
 OPTION (MAXDOP 1) 

но так вы рубите параллелизм для всех вообще. Можте через plan guide прибить MAXDOP
9 ноя 17, 13:10    [20939811]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Молодой,

Чтобы избежать эксалации S в X внутри пула MARS, рекомендую сразу вешать WITH(TABLOCKX) на Update.
Проблема эта древняя как мамонт, еще в 2009 году обсуждали, что делать.
Поль Уайтт в 2014 проверял, что до сих пор все так же.

Приведите скрипт таблицы и ее индексов :)
9 ноя 17, 13:11    [20939816]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Andy_OLAP,
WITH(TABLOCKX)
странно что писарь не предлагает сразу застрелится
9 ноя 17, 13:14    [20939829]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Andy_OLAP,
wow!
кстати, как там поживает Paul White?
у него-то спросили про флаг 666?
это самый правильный персонаж в плане исследования недокументированных флагов.
---
а поведайте, плиз, какими такими рассуждениями вы пришли к выводу,
что если имя Paul идет с фамилией White, то это Полль.
а вот если Paul Randal, то он уже Пол
9 ноя 17, 13:31    [20939891]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Yasha123,

Прошу Вас, коллега, не отвлекайтесь на оффтоп, это же нарушение правил - мы должны подавать молодежи на форуме исключительно положительный пример. Расскажите лучше, как Вы относитесь к наложению блокировки на всю таблицу для этой маленькой и короткой операции update - станет ли сильно хуже, или дедлоки уйдут без явного использования option (maxdop 1).
9 ноя 17, 13:34    [20939910]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Молодой,

вообще если смотреть в дедлок, то там у вас 2 транзакции, наш писарь отчасти двигается в верном направлениии, но tablockx вы поставите раком всех. Я бы прибил SERIALIZABLE
9 ноя 17, 13:36    [20939920]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
TaPaK
Andy_OLAP,
WITH(TABLOCKX)
странно что писарь не предлагает сразу застрелится

То есть Вы конкретно считаете использование данного хинта аналогом смертного греха самоубийства? Хм. А чем тогда руководствовались разработчики MSSQL, когда включали такой хинт в список допустимых к использованию?
Ведь есть же ситуации, когда TABLOCKX использовать можно и нужно. Почему не в данной конкретной ситуации?
9 ноя 17, 13:36    [20939926]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Andy_OLAP
TaPaK
Andy_OLAP,
WITH(TABLOCKX)
странно что писарь не предлагает сразу застрелится

То есть Вы конкретно считаете использование данного хинта аналогом смертного греха самоубийства? Хм. А чем тогда руководствовались разработчики MSSQL, когда включали такой хинт в список допустимых к использованию?
Ведь есть же ситуации, когда TABLOCKX использовать можно и нужно. Почему не в данной конкретной ситуации?

я считаю вас пустозвоном, но по сабжу если дали пистолет, то не стреляйте себе сразу в ногу
9 ноя 17, 13:38    [20939935]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Молодой
Member

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


Скрипт таблицы

CREATE TABLE [dbo].[tbCompositions](
	[CompositionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[ServerID] [int] NOT NULL,
	[CompositionType] [int] NOT NULL,
	[CompositionName] [nvarchar](128) NOT NULL,
	[CompositionConf] [xml](CONTENT [dbo].[CompositionCompositionConf]) NULL,
	[CompositionGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ServerGUID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Composition] PRIMARY KEY CLUSTERED 
([CompositionGUID] DESC) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[tbCompositions] ADD  DEFAULT (newsequentialid()) FOR [CompositionGUID]

ALTER TABLE [dbo].[tbCompositions]  WITH CHECK ADD  CONSTRAINT [FK_Composition_Servers] 
FOREIGN KEY([ServerGUID]) REFERENCES [dbo].[tbServers] ([ServerGUID])

ALTER TABLE [dbo].[tbCompositions] CHECK CONSTRAINT [FK_Composition_Servers]

ALTER TABLE [dbo].[tbCompositions] ADD  CONSTRAINT [PK_Composition] PRIMARY KEY CLUSTERED ([CompositionGUID] DESC)


Триггер на ней еще есть, не срабатывающий при описаном UPDATE
CREATE TRIGGER [dbo].[FillCompositionsFkGUIDs] ON [dbo].[tbCompositions]
FOR INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON

IF NOT EXISTS(SELECT * FROM DELETED) OR UPDATE(ServerID)
UPDATE c SET c.ServerGUID = srv.ServerGUID
FROM [dbo].[tbCompositions] c INNER JOIN
INSERTED i ON c.CompositionGUID = i.CompositionGUID INNER JOIN
[dbo].[tbServers] srv ON c.ServerID = srv.ServerID AND srv.SegmentGUID = dbo.SegmentGUIDDefault()

END
9 ноя 17, 13:39    [20939939]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Молодой

Триггер на ней еще есть, не срабатывающий при описаном UPDATE
CREATE TRIGGER [dbo].[FillCompositionsFkGUIDs] ON [dbo].[tbCompositions]
FOR INSERT, UPDATE

С этого нужно было начинать. Что есть триггер на UPDATE. Сейчас коллеги обсудят его содержимое и примут экспертное взвешенное решение, нужно ли триггер переписать.
9 ноя 17, 13:46    [20939970]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Andy_OLAP
С этого нужно было начинать. Что есть триггер на UPDATE. Сейчас коллеги обсудят его содержимое и примут экспертное взвешенное решение, нужно ли триггер переписать.

писарь решил не открывать deadlock, сразу транзать клавиатуру полез
9 ноя 17, 13:49    [20939992]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
TaPaK
Молодой,
но tablockx вы поставите раком всех. Я бы прибил SERIALIZABLE

Кого раком? Триггер? Нужно ведь вытягивать клещами информацию из каждого автора темы, прежде чем пускаться в размышления, что ему почитать и что поменять. И что прибить.
+

С годами придет понимание, что и блокировки явные полезны, чтобы наличие триггеров обнаружить, и флагами нужно пользоваться чаще, и молодых, которые тут последние 4 года задавали глупые вопросы, не нужно было козловыми к стене пригвождать. Корона у кое-кого к голове намертво приросла. Ну ничего.
9 ноя 17, 13:54    [20940018]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Andy_OLAP
Молодой
Триггер на ней еще есть, не срабатывающий при описаном UPDATE
CREATE TRIGGER [dbo].[FillCompositionsFkGUIDs] ON [dbo].[tbCompositions]
FOR INSERT, UPDATE

С этого нужно было начинать. Что есть триггер на UPDATE. Сейчас коллеги обсудят его содержимое и примут экспертное взвешенное решение, нужно ли триггер переписать.



Пока попробуем триггер в тестах удалить м.б. и правда, - из за него проблема...
9 ноя 17, 13:58    [20940040]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Andy_OLAP
Ведь есть же ситуации, когда TABLOCKX использовать можно и нужно. Почему не в данной конкретной ситуации?
Может быть уважаемый эксперт пояснит, каким образом этот хинт поможет в данном конкретном случае, когда конфликтующие ресурсы это ключ и transaction mutex?
9 ноя 17, 14:18    [20940122]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
invm
Andy_OLAP
Ведь есть же ситуации, когда TABLOCKX использовать можно и нужно. Почему не в данной конкретной ситуации?
Может быть уважаемый эксперт пояснит, каким образом этот хинт поможет в данном конкретном случае, когда конфликтующие ресурсы это ключ и transaction mutex?

У меня не хватает знаний, чтобы четко и внятно сформулировать ответ на Ваш вопрос. Но наверняка другие коллеги сейчас явно пояснят, в чем именно я не прав, и сделают это быстро и максимально эмоционально.
9 ноя 17, 14:20    [20940134]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Andy_OLAP
У меня не хватает знаний, чтобы четко и внятно сформулировать ответ на Ваш вопрос.
Тогда зачем давать советы в области, по которой у вас нет необходимых знаний? Вы любитель потыкать пальцем в небо? Или вам просто скучно?
Зачем давать ссылки на дискуссии, которые к обсуждаемому вопросу не относятся?
9 ноя 17, 14:28    [20940182]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
invm
Andy_OLAP
У меня не хватает знаний, чтобы четко и внятно сформулировать ответ на Ваш вопрос.
Тогда зачем давать советы в области, по которой у вас нет необходимых знаний? Вы любитель потыкать пальцем в небо? Или вам просто скучно?
Зачем давать ссылки на дискуссии, которые к обсуждаемому вопросу не относятся?

Я даю экспертные советы в области, где что-то знаю, что-то не знаю. Я не любитель потыкать куда-то пальцем. Мне не скучно. Ссылки я даю на те дискуссии, в которых обсуждается или проблема напрямую, или косвенно.
9 ноя 17, 14:48    [20940282]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Andy_OLAP
Я даю экспертные советы в области, где что-то знаю, что-то не знаю.
Эксперты не могут давать экспертные советы в области, где они не эксперты. Если эксперт что-то не знает, - он уже не эксперт.
Andy_OLAP
Я не любитель потыкать куда-то пальцем. Мне не скучно.
А мне вот почему-то кажется, что наоборот.
Andy_OLAP
Ссылки я даю на те дискуссии, в которых обсуждается или проблема напрямую, или косвенно.
Ок. Поясните тогда, где в той дискуссии обсуждается проблема ТС.
9 ноя 17, 14:57    [20940344]     Ответить | Цитировать Сообщить модератору
 Re: Процесс создал Deadlock сам на себя. Как дальше жить?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Молодой
Кроме написания писем в MS, есть еще какието направления как можно попытаться решить эту проблему?

такое даже не гуглится.
попробуйте на https://dba.stackexchange.com запостить.
там, кстати, сегодня появлялся один из MS.
вообще он на этой неделе там пасется,
хотя отвечает весьма выборочно.
заголовок только получше придумайте,
TransactionMutex как ресурс чтобы там фигурировал.
пускай повисит без ответа, а потом уже можно и в MS обратиться,
сославшись заодно и на тот сайт.
9 ноя 17, 16:29    [20940706]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить