Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Дано:
transaction isolation level = Read commited (2).

Две транзакции, обе изменяют запись с одним и тем же идентификатором:

update b
set 
  value = b.value - 10
from dbo.b b 
where b.id = 12



Первая транзакция накладывает U блокировку на ключ pk, затем x.
Вторая транзакция накладывает U блокировку на тот же ключ pk, и ix блокировку.

В каких случаях может произойти deadlock?
10 ноя 14, 12:26    [16821634]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
msLex
Member

Откуда:
Сообщений: 8730
вопросdeadlock
Первая транзакция накладывает U блокировку на ключ pk, затем x.
Вторая транзакция накладывает U блокировку на тот же ключ pk, и ix блокировку.

В каких случаях может произойти deadlock?

Описанный вами вариант никогда.
U блокировки несовместимы.
10 ноя 14, 12:30    [16821652]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Граф есть?

вопросdeadlock
Первая транзакция накладывает U блокировку на ключ pk, затем x.
X на запись без IX на страницу/таблицу не бывает.

вопросdeadlock
Вторая транзакция накладывает U блокировку на тот же ключ pk, и ix блокировку
IX на ключ не бывает.

Сообщение было отредактировано: 10 ноя 14, 12:35
10 ноя 14, 12:34    [16821681]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Гавриленко Сергей Алексеевич,

граф во вложении

К сообщению приложен файл. Размер - 58Kb
10 ноя 14, 12:40    [16821726]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
msLex
Member

Откуда:
Сообщений: 8730
msLex
вопросdeadlock
Первая транзакция накладывает U блокировку на ключ pk, затем x.
Вторая транзакция накладывает U блокировку на тот же ключ pk, и ix блокировку.

В каких случаях может произойти deadlock?

Описанный вами вариант никогда.
U блокировки несовместимы.

Более того, при поиске записей по ключу (без доп. условий), U на ключ не накладывается, сразу X.
10 ноя 14, 12:41    [16821737]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Структуру таблицы b покажите.

И да, ключи-то в графе, поди, разные?
10 ноя 14, 12:42    [16821747]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
msLex
Member

Откуда:
Сообщений: 8730
вопросdeadlock
Гавриленко Сергей Алексеевич,

граф во вложении

Картинка с другого сайта.

картинка так себе, лучше в текстовом виде, но даже тут видно U + U vs U + X (и не каких IX)
как вариант, разнонаправленный скан индекса в update.
10 ноя 14, 12:45    [16821766]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Гавриленко Сергей Алексеевич
Структуру таблицы b покажите.

И да, ключи-то в графе, поди, разные?


Нет, ключи как раз одинаковые и это Primary Key.

Запросы, вызывающие блокировку тоже одинаковые:
update
   b
set
   r = b.r - t.value
from
   dbo.b b
   inner join (
			   select id, val from dbo.t
			  ) t
	  on t.id = b.id


Таблица в упрощенном варианте:

CREATE TABLE dbo.b(
	id bigint IDENTITY(-4611686018427387904,1) NOT NULL,
	[v] [decimal](19, 2) NOT NULL,
	[r] [decimal](19, 2) NOT NULL,
 CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 
10 ноя 14, 12:49    [16821794]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
msLex
Member

Откуда:
Сообщений: 8730
вопросdeadlock
Запросы, вызывающие блокировку тоже одинаковые:
update
   b
set
   r = b.r - t.value
from
   dbo.b b
   inner join (
			   select id, val from dbo.t
			  ) t
	  on t.id = b.id


во-первых, это совсем не то же, что update по одному ключу
во-вторых, смотрите планы, скорее всего у вас hash-join

сколько данных в таблицах dbo.t и dbo.b?
10 ноя 14, 13:04    [16821961]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
вопросdeadlock -- вы партизан, однако. Структуру таблицы dbo.t покжите. Вместе с индексами.
На dbo.b других индексов нет?

Ну совсем было бы шоколадно, если бы был план запроса.

Сообщение было отредактировано: 10 ноя 14, 13:09
10 ноя 14, 13:09    [16822004]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Гавриленко Сергей Алексеевич,

я не партизан, просто не хочу нагружать чтением лишнего кода.


+ dbo.b


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO



CREATE TABLE [dbo].[b](
	[id] bigint IDENTITY(-4611686018427387904,1) NOT NULL,
	[created_on] [datetime] NOT NULL,
	[v] [decimal](19, 2) NOT NULL,
	[start_date] [datetime] NOT NULL,
	[finish_date] [datetime] NOT NULL,
	[r] [decimal](19, 2) NOT NULL,
	[cq_id] bigint NULL,
	[ci_id] [dbo].[uID] NULL,
	[r_id] [dbo].[uID_guid] NULL,
	[pti] [int] NULL,
	[pd] [datetime] NULL,
	[d_date] [datetime] NULL,
	[sb_id] [dbo].[uID] NULL,
	[cm_id] [dbo].[uID_guid] NOT NULL,
	[cr_id] [dbo].[uID_guid] NOT NULL,
	[p_id] [dbo].[uID_guid] NULL,
	[oti] [char](1) NOT NULL,
 CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
CREATE NONCLUSTERED INDEX [idx_cm_id] ON dbo.b
(
	[cm_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)
GO
CREATE NONCLUSTERED INDEX [idx_cr_id] ON dbo.b
(
	[cr_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) 
GO
CREATE NONCLUSTERED INDEX [idx_cq_id] ON dbo.b
(
	[cq_id] ASC
)
INCLUDE ( 	[created_on],
	[v]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO
CREATE NONCLUSTERED INDEX [idx_ci_id] ON dbo.b
(
	[ci_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) 
GO
CREATE NONCLUSTERED INDEX [idx_finish_date_d_date] ON dbo.b
(
	[finish_date] ASC,
	[d_date] ASC
)
INCLUDE ( 	[cr_id],
	[oti],
	[r]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO
CREATE NONCLUSTERED INDEX [idx_oti_r_id] ON dbo.b
(
	[oti] ASC,
	[r_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) 
GO
CREATE NONCLUSTERED INDEX [idx_pti_p_id] ON dbo.b
(
	[pti] ASC,
	[p_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) 
GO
CREATE NONCLUSTERED INDEX [idx_r] ON dbo.b
(
	[r] 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) 
GO
CREATE NONCLUSTERED INDEX [idx_sb_id] ON dbo.b
(
	[sb_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) 
GO
CREATE NONCLUSTERED INDEX [idx_start_date_pd] ON dbo.b
(
	[start_date] ASC,
	[pd] ASC
)
INCLUDE ( 	[cr_id],
	[v],
	[oti]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO





Вот таблица dbo.t_internal и вьюха dbo.t:
+ dbo.t

set ANSI_NULLS on
GO

set QUOTED_IDENTIFIER on
GO

create table dbo.t_internal (
	[id] [bigint] null
   ,[val] [decimal](19, 2) not null
   ,[spid] [smallint] not null,
   )
on [data]

GO


create clustered index [idx_t_internal_spid] on dbo.t_internal
(
[spid] asc
)with (pad_index = off, statistics_norecompute = on, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = off)
go


create view dbo.t
as
(
 select
   id
  ,val
 from
   dbo.t_internal t with (nolock)
 where
   t.spid = @@spid
)




В запросе, который вызывает дедлок еще есть group by (не думаю, что это важно):

update
   b
set
   r = b.r - t.value
from
   dbo.b b
   inner join (
			   select id, sum(isnull(val, 0)) as val from dbo.t t
                           group by t.id
			  ) t
	  on t.id = b.id


Вызывает дедлок именно два таких запроса.

Больше никаких индексов нет. Есть еще триггеры, констрейнты и внешние ключи.


Гавриленко Сергей Алексеевич
Ну совсем было бы шоколадно, если бы был план запроса.

Хорошо бы, но к сожалению мне прислали только пятничный дедлок граф.
Меня интересует как повторить дедлок в другом окружении с такой же структурой.
10 ноя 14, 14:12    [16822399]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
msLex
во-вторых, смотрите планы, скорее всего у вас hash-join


Как мне повторить дедлок?

msLex
сколько данных в таблицах dbo.t и dbo.b?


Могу сказать порядок, в dbo.b - миллионы (миллионов 8 на тестовой среде), в dbo.t по-разному, но в основном десятки записей.
10 ноя 14, 14:14    [16822404]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Я бы переделал в таблице t_internal кластерный индекс, и с делал бы его по (spid, id), чтобы зафиксировать порядок апдейта.
Дальше нужно смотреть уже в план, чтобы убедиться, что апдейт идет всегда в одном и том же порядке id.

Сообщение было отредактировано: 10 ноя 14, 14:17
10 ноя 14, 14:16    [16822415]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
msLex
Member

Откуда:
Сообщений: 8730
вопросdeadlock
Как мне повторить дедлок?

если это действительно дедлок на параллельном хеш джойне, то сымитировать (сделать воспроизводимое репро) непросто.

вопросdeadlock
Могу сказать порядок, в dbo.b - миллионы (миллионов 8 на тестовой среде), в dbo.t по-разному, но в основном десятки записей.

попробуйте явно указать оптимизатору необходимость поиска по индексу

update
   b
set
   r = b.r - t.value
from
   dbo.b b with(forceseek)
   inner join (
			   select id, val from dbo.t
			  ) t
	  on t.id = b.id
10 ноя 14, 14:20    [16822435]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
Crimean
Member

Откуда:
Сообщений: 13147
вот вам похожий дедлок

if @@trancount <> 0 rollback
go

-- drop table a
-- create table a ( id int primary key , flag int )
-- create index i_a_flag on a ( flag )
-- insert into a ( id , flag ) values (10,10), (20,20), (30,30)

set nocount on
while 1=1 begin

begin tran
update a set id = id where flag = 20
update a set id = id where flag = 20
commit

end
10 ноя 14, 14:26    [16822481]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Crimean,

Вы в вашем примере намеренно изменяете id. У меня в запросе id не меняется (по нему происходит поиск). Так что это не совсем то
10 ноя 14, 14:40    [16822574]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock - U+X - U+IX как воспроизвести  [new]
вопросdeadlock
Guest
Crimean,

при этом в вашем примере дедлок происходит при обновлении pk и i_a_flag:
10 ноя 14, 14:53    [16822669]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить