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

помогите пожалуйста разобраться в deadlock-е.

Сам Deadlock граф:
<deadlock>
<victim-list>
<victimProcess id="process155de668c8" />
</victim-list>
<process-list>
<process id="process155de668c8" taskpriority="0" logused="15288" waitresource="KEY: 6:72057594296926208 (0481f868e78c)" waittime="5020" ownerId="10544219494" transactionname="user_transaction" lasttranstarted="2016-09-08T11:25:41.290" XDES="0x2de28233c0" lockMode="U" schedulerid="17" kpid="11340" status="suspended" spid="187" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-09-08T11:25:41.337" lastbatchcompleted="2016-09-08T11:25:41.333" lastattention="1900-01-01T00:00:00.333" clientapp=".Net SqlClient Data Provider" hostname="SRV-CRM-CRM01.w3wp.80" hostpid="2328" loginname="VCT\CRM" isolationlevel="read committed (2)" xactid="10544219494" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="116" stmtend="378" sqlhandle="0x02000000e781881ea368ddc8330e6b8fa9043bd25be5e2a50000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@ModifiedOn datetime,@RegId uniqueidentifier,@RegOTC int)UPDATE [table] SET LatestAutoPostModifiedOn=@ModifiedOn WHERE RegardingObjectId=@RegId AND TypeCode=@RegOTC </inputbuf>
</process>
<process id="process157f919848" taskpriority="0" logused="18600" waitresource="KEY: 6:72057594312589312 (45c2c43d670b)" waittime="7170" ownerId="10544219369" transactionname="user_transaction" lasttranstarted="2016-09-08T11:25:41.260" XDES="0x338fde3670" lockMode="U" schedulerid="17" kpid="1464" status="suspended" spid="173" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-09-08T11:25:41.340" lastbatchcompleted="2016-09-08T11:25:41.340" lastattention="1900-01-01T00:00:00.340" clientapp=".Net SqlClient Data Provider" hostname="SRV-CRM-CRM01.w3wp.80" hostpid="2328" loginname="VCT\CRM" isolationlevel="read committed (2)" xactid="10544219369" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="116" stmtend="378" sqlhandle="0x02000000e781881ea368ddc8330e6b8fa9043bd25be5e2a50000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@ModifiedOn datetime,@RegId uniqueidentifier,@RegOTC int)UPDATE [table] SET LatestAutoPostModifiedOn=@ModifiedOn WHERE RegardingObjectId=@RegId AND TypeCode=@RegOTC </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594296926208" dbid="6" objectname="Database.dbo.table" indexname="cndx_PrimaryKey_table" id="lock121ed71380" mode="X" associatedObjectId="72057594296926208">
<owner-list>
<owner id="process157f919848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process155de668c8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594312589312" dbid="6" objectname="Database.dbo.table" indexname="ndx_TypeCode" id="lock104746bd80" mode="U" associatedObjectId="72057594312589312">
<owner-list>
<owner id="process155de668c8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process157f919848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>


Таблица на которой это все происходит:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table](
	[Id] [uniqueidentifier] NOT NULL,
	[LatestManualPostModifiedOn] [datetime] NULL,
	[LatestAutoPostModifiedOn] [datetime] NULL,
	[TypeCode] [int] NOT NULL,
 CONSTRAINT [cndx_PrimaryKey_table] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Index [ndx_RegardingObjectForSharing]    Script Date: 08.09.2016 15:12:37 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ndx_RegardingObjectForSharing] ON [dbo].[table]
(
	[RegardingObjectId] ASC,
	[TypeCodeForSharing] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

/****** Object:  Index [ndx_TypeCode]    Script Date: 08.09.2016 15:12:37 ******/
CREATE NONCLUSTERED INDEX [ndx_TypeCode] ON [dbo].[table]
(
	[TypeCode] ASC,
	[RegardingObjectId] ASC
)
INCLUDE ( 	[LatestManualPostModifiedOn],
	[LatestAutoPostModifiedOn]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80) ON [PRIMARY]
GO



Запрос генерируется автоматически. Как можно решить проблему без переписывания запроса?
8 сен 16, 15:18    [19642990]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
deadlock123456
Guest
И вообще, интересует вопрос - почему происходит Deadlock.
8 сен 16, 15:18    [19642998]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
deadlock123456,

что то вы не договариваете, не вижу в дефинишне таблицы колонки: [RegardingObjectId]
8 сен 16, 15:44    [19643219]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
вообще запросы одинаковые, судя по логике у вас такое сможет произойти приблизительно так

--spid1
begin tran
UPDATE [table]
 SET LatestAutoPostModifiedOn =GETDATE()
WHERE RegardingObjectId=1
 AND TypeCode=1


--spid2
begin tran
UPDATE [table]
 SET LatestAutoPostModifiedOn =GETDATE()
WHERE RegardingObjectId=2
 AND TypeCode=1


|
v

--spid1
UPDATE [table]
 SET LatestAutoPostModifiedOn =GETDATE()
WHERE RegardingObjectId=2
 AND TypeCode=1


--spid2
UPDATE [table]
 SET LatestAutoPostModifiedOn =GETDATE()
WHERE RegardingObjectId=1
 AND TypeCode=1


Запросы у вас в каком порядке приходят на сервер?
8 сен 16, 16:09    [19643452]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
o-o
Guest
не делают они по 2 апдэйта.
2 сессии, в каждой 1 апдэйт.
но проапдэйтить надо и кластерный cndx_PrimaryKey_table, и ndx_TypeCode,
потому что там это самое LatestAutoPostModifiedOn в инклуде некластерного.

апдэйтят по RegardingObjectId,TypeCode,
эта комбинация не уникальна.

у первого уже есть Х на нужный ключ кластерного,
ждет U на некластерный.
а у второго уже там свое U, поэтому первый его не получит, пока второй не закончит.
а он не может, потому что ему надо U на кластерный, а там уже Х первого
8 сен 16, 16:35    [19643612]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Вообще за такое нужно наказывать

[Id] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED
(
[Id] ASC

Но, я вижу, оно уже....
9 сен 16, 09:24    [19645857]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
AlphaDog,

и таки за что ж надо наказывать, каратель?
9 сен 16, 09:31    [19645874]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
TaPaK
AlphaDog,

и таки за что ж надо наказывать, каратель?


За нечтение документации и как результат - непонимание как работают индексы.

http://sqlmag.com/database-performance-tuning/clustered-indexes-based-upon-guids
9 сен 16, 09:47    [19645920]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
deadlock123456
Guest
AlphaDog
Вообще за такое нужно наказывать

[Id] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED
(
[Id] ASC

Но, я вижу, оно уже....


Да, только это поделки Microsoft, это не переделать, увы.

Как я понимаю решение одно - убрать поле из include?
9 сен 16, 09:51    [19645942]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
deadlock123456
AlphaDog
Вообще за такое нужно наказывать

[Id] [uniqueidentifier] NOT NULL,

PRIMARY KEY CLUSTERED
(
[Id] ASC

Но, я вижу, оно уже....


Да, только это поделки Microsoft, это не переделать, увы.

Как я понимаю решение одно - убрать поле из include?


Это не имеет ни малейшего отношения к Микрософту. Когда же вы читать начнете-то, а? Все RDBMS строят кластерный индекс по одному и тому же принципу. ВСЕ!
9 сен 16, 09:54    [19645950]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
deadlock123456
Да, только это поделки Microsoft, это не переделать, увы.
Поговрка про зеркало и рожу.
9 сен 16, 09:56    [19645957]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Нельзя в кластерный индекс класть широкое поле. И использовать uniqueidentifier. Особенно, когда по нему идут UPDATE.

Точнее все это можно делать. Только потом начинаются подобные чудеса и подобные вопросы типа - а шо теперь делать.
9 сен 16, 09:58    [19645964]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Гавриленко Сергей Алексеевич
deadlock123456
Да, только это поделки Microsoft, это не переделать, увы.
Поговрка про зеркало и рожу.


Мне больше понравился вопрос - "убрать поле из include?" Он показывает уровень погружения в тему.
9 сен 16, 10:01    [19645982]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
deadlock123456
Guest
AlphaDog,Гавриленко Сергей Алексеевич.

нет коллеги, вы не поняли. Базу проектировал великий Microsoft, называется Dynamics CRM и ключ в ней менять не получится, так же как и переписать запрос.
9 сен 16, 10:08    [19646015]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
deadlock123456
AlphaDog,Гавриленко Сергей Алексеевич.

нет коллеги, вы не поняли. Базу проектировал великий Microsoft, называется Dynamics CRM и ключ в ней менять не получится, так же как и переписать запрос.
Ну вот...

А что менять-то можно?
9 сен 16, 10:09    [19646020]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
deadlock123456
Guest
AlphaDog
Нельзя в кластерный индекс класть широкое поле. И использовать uniqueidentifier. Особенно, когда по нему идут UPDATE.

Точнее все это можно делать. Только потом начинаются подобные чудеса и подобные вопросы типа - а шо теперь делать.


Увы, этого не поменять, вся база с кластерными ключами по guid-ам.
Как проблему то решить (кроме того, что назвать автора всего этого т.е. индусов из Microsoft идиотами) ?
9 сен 16, 10:10    [19646028]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
deadlock123456
AlphaDog,Гавриленко Сергей Алексеевич.

нет коллеги, вы не поняли. Базу проектировал великий Microsoft, называется Dynamics CRM и ключ в ней менять не получится, так же как и переписать запрос.


Зато получится поменять индексы. Но для начала нужно разобраться как они устроены и как они работают. А дальше, скорее всего, даже в таком печальном случае, может получиться ништяк.
9 сен 16, 10:11    [19646030]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
deadlock123456
Guest
Гавриленко Сергей Алексеевич
deadlock123456
AlphaDog,Гавриленко Сергей Алексеевич.

нет коллеги, вы не поняли. Базу проектировал великий Microsoft, называется Dynamics CRM и ключ в ней менять не получится, так же как и переписать запрос.
Ну вот...

А что менять-то можно?


Индексы (кроме кластерного) можно.
Сами запросы как и уровень изоляции тоже не поменять. увы.
9 сен 16, 10:11    [19646031]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
deadlock123456
AlphaDog
Нельзя в кластерный индекс класть широкое поле. И использовать uniqueidentifier. Особенно, когда по нему идут UPDATE.

Точнее все это можно делать. Только потом начинаются подобные чудеса и подобные вопросы типа - а шо теперь делать.


Увы, этого не поменять, вся база с кластерными ключами по guid-ам.
Как проблему то решить (кроме того, что назвать автора всего этого т.е. индусов из Microsoft идиотами) ?


Смените индекс для PK с кластерного на некластерный.
9 сен 16, 10:12    [19646037]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Кстати еще... Если в базе нет FK и PK там только для красоты, то рекомендую попробовать добавить поле IDENTITY, по нему построить кластерный индекс. Функциональность таблицы при этом пострадать не должна никак. Это чаще всего плохо не иметь кластерного индекса в таблице.
9 сен 16, 10:24    [19646104]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
AlphaDog
deadlock123456
пропущено...


Увы, этого не поменять, вся база с кластерными ключами по guid-ам.
Как проблему то решить (кроме того, что назвать автора всего этого т.е. индусов из Microsoft идиотами) ?


Смените индекс для PK с кластерного на некластерный.

в копилку полезных советов )))))) вы про индексы вычитали про сплиты и теперь везде суете сие знание? там у человека дедлоки помните?
9 сен 16, 10:24    [19646107]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
TaPaK
AlphaDog
пропущено...


Смените индекс для PK с кластерного на некластерный.

в копилку полезных советов )))))) вы про индексы вычитали про сплиты и теперь везде суете сие знание? там у человека дедлоки помните?


Я помню про дедлоки. И даже знаю почему они возникают. И, более того, даже знаю технические причины почему возникают дедлоки и как вообще SQL Server работает с блокировками и как он их эскалирует и почему. А так же я знаю к чему в результате этот букет приводит.

Это если отвлечься от того, что транзакции в чудо-приложении написаны криво. Но зная CRMподобные чуда, у меня есть уверенность, что транзакции в нем уровня одного запроса.
9 сен 16, 10:28    [19646124]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
прям кладезь...
автор
Если в базе нет FK и PK там только для красоты

автор
Это чаще всего плохо не иметь кластерного индекса в таблице.

вы может пропустили часть когда изучали индексы
9 сен 16, 10:28    [19646125]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
AlphaDog
TaPaK
пропущено...

в копилку полезных советов )))))) вы про индексы вычитали про сплиты и теперь везде суете сие знание? там у человека дедлоки помните?


Я помню про дедлоки. И даже знаю почему они возникают. И, более того, даже знаю технические причины почему возникают дедлоки и как вообще SQL Server работает с блокировками и как он их эскалирует и почему. А так же я знаю к чему в результате этот букет приводит.

Это если отвлечься от того, что транзакции в чудо-приложении написаны криво. Но зная CRMподобные чуда, у меня есть уверенность, что транзакции в нем уровня одного запроса.

в любой непонятной ситуации меняй кластерный на не кластерный, главное уверенно рассказывай что знаешь о чём речь
9 сен 16, 10:29    [19646133]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - помогите пожалуйста разобраться  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
TaPaK
прям кладезь...
автор
Если в базе нет FK и PK там только для красоты

автор
Это чаще всего плохо не иметь кластерного индекса в таблице.

вы может пропустили часть когда изучали индексы


Я? Сколько вы готовы поставить на это свое утверждение? :)
9 сен 16, 10:30    [19646135]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить