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

Откуда: Екатеринбург
Сообщений: 9
Node:1

2012-05-10 15:36:03.94 spid6s KEY: 5:72057594061979648 (13008293d9f2) CleanCnt:2 Mode:U Flags: 0x1
2012-05-10 15:36:03.94 spid6s Grant List 0:
2012-05-10 15:36:03.94 spid6s Owner:0x00000000A224C700 Mode: U Flg:0x40 Ref:0 Life:02000000 SPID:113 ECID:0 XactLockInfo: 0x00000001836419B0
2012-05-10 15:36:03.94 spid6s SPID: 113 ECID: 0 Statement Type: DELETE Line #: 128
2012-05-10 15:36:03.94 spid6s Input Buf: Language Event: set xact_abort on

declare @last_run_outcome int
select top 1
@last_run_outcome = st.last_run_outcome
from master.dbo.sysprocesses p with( nolock )
inner join msdb.dbo.sysjobsteps st with( nolock )
on p.program_name like N'%(Job
2012-05-10 15:36:03.94 spid6s Requested by:
2012-05-10 15:36:03.94 spid6s ResType:LockOwner Stype:'OR'Xdes:0x00000003E3D4D970 Mode: U SPID:94 BatchID:0 ECID:0 TaskProxy:(0x00000001FD98E538) Value:0x859a8900 Cost:(0/0)
2012-05-10 15:36:03.94 spid6s
2012-05-10 15:36:03.94 spid6s

Node:2

2012-05-10 15:36:03.94 spid6s KEY: 5:72057594065321984 (2301fe921ec0) CleanCnt:2 Mode:U Flags: 0x1
2012-05-10 15:36:03.94 spid6s Grant List 3:
2012-05-10 15:36:03.94 spid6s Owner:0x00000000A12E9F80 Mode: U Flg:0x40 Ref:0 Life:02000000 SPID:94 ECID:0 XactLockInfo: 0x00000003E3D4D9B0
2012-05-10 15:36:03.94 spid6s SPID: 94 ECID: 0 Statement Type: DELETE Line #: 74
2012-05-10 15:36:03.94 spid6s Input Buf: Language Event: set xact_abort on

declare @last_run_outcome int
select top 1
@last_run_outcome = st.last_run_outcome
from master.dbo.sysprocesses p with( nolock )
inner join msdb.dbo.sysjobsteps st with( nolock )
on p.program_name like N'%(Job
2012-05-10 15:36:03.94 spid6s Requested by:
2012-05-10 15:36:03.94 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0000000183641970 Mode: X SPID:113 BatchID:0 ECID:0 TaskProxy:(0x00000001FA7DA538) Value:0xa2595b80 Cost:(0/54308)
2012-05-10 15:36:03.94 spid6s
2012-05-10 15:36:03.94 spid6s Victim Resource Owner:
2012-05-10 15:36:03.94 spid6s ResType:LockOwner Stype:'OR'Xdes:0x00000003E3D4D970 Mode: U SPID:94 BatchID:0 ECID:0 TaskProxy:(0x00000001FD98E538) Value:0x859a8900 Cost:(0/0)
2012-05-10 15:40:13.35 Backup
10 май 12, 16:49    [12531549]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Что за DELETE там?

katrina_v
2012-05-10 15:36:03.94 spid6s SPID: 113 ECID: 0 Statement Type: DELETE Line #: 128
2012-05-10 15:36:03.94 spid6s SPID: 94 ECID: 0 Statement Type: DELETE Line #: 74
10 май 12, 16:54    [12531601]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
katrina_v
Member

Откуда: Екатеринбург
Сообщений: 9
Первый участник:
ALTER procedure [dbo].[p_smpp_delete_manager]
as
set xact_abort on

-------------------------------------------------------------------------------------------------
-- определяем необходимые настройки
declare @rowcount_limit int
, @actual_rowcount int
, @response_timer int

select @rowcount_limit = cast( dbo.f_get_settings( 'SMPP_DELETE_MANAGER_ROWCOUNT_LIMIT' ) as int )
, @response_timer = cast( dbo.f_get_settings( 'SMPP_RESPONSE_TIMER' ) as int )

if ( @rowcount_limit is null
or @response_timer is null )
goto SETTINGS_ERROR

DELETE_MANAGER_START:
set rowcount @rowcount_limit
-------------------------------------------------------------------------------------------------
-- generic_nack
delete x
from smpp_generic_nack_out x with ( rowlock, readpast, updlock, index( pk_smpp_generic_nack_out ) )
where session_id not in( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = @@rowcount
-------------------------------------------------------------------------------------------------
-- bind_receiver_resp
delete x
from smpp_bind_receiver_resp x with ( rowlock, readpast, updlock, index( pk_smpp_bind_receiver_resp ) )
where session_id not in ( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- bind_transmitter_resp
delete x
from smpp_bind_transmitter_resp x with ( rowlock, readpast, updlock, index( pk_smpp_bind_transmitter_resp ) )
where session_id not in ( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- bind_transceiver_resp
delete x
from smpp_bind_transceiver_resp x with ( rowlock, readpast, updlock, index( pk_smpp_bind_transceiver_resp ) )
where session_id not in ( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- enquire_link_resp
delete x
from smpp_enquire_link_resp_out x with ( rowlock, readpast, updlock, index( pk_smpp_enquire_link_resp_out ) )
where session_id not in ( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- submit_sm_resp
delete x
from smpp_submit_resp x with ( rowlock, readpast, updlock, index( pk_smpp_submit_resp ) )
where session_id not in ( select session_id
from smpp_session
where state_id != 6 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end

delete x
from smpp_submit_resp x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_submit_resp x with ( rowlock, readpast, updlock, index( pk_smpp_submit_resp ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 2 -- PROCESSED
option ( force order )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- submit_sm
delete x
from smpp_submit x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_submit x with ( rowlock, readpast, updlock, index( pk_smpp_submit ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 2 -- PROCESSED
and x_.dialogue_id not in ( select ud.dialogue_id_ from ussd_dialogue ud with (nolock) where ud.dialogue_id_ = x_.dialogue_id )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- deliver_sm_resp
delete x
from smpp_deliver_resp x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_deliver_resp x with ( rowlock, readpast, updlock, index( pk_smpp_deliver_resp ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 2 -- PROCESSED

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end
-------------------------------------------------------------------------------------------------
-- deliver_sm
delete x
from smpp_deliver x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_deliver x with ( rowlock, readpast, updlock, index( pk_smpp_deliver ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 2 -- PROCESSED
and x_.dialogue_id not in ( select ud.dialogue_id_ from ussd_dialogue ud with (nolock) where ud.dialogue_id_ = x_.dialogue_id )
option( force order, loop join )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end

delete x
from smpp_deliver x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_deliver x with ( rowlock, readpast, updlock, index( pk_smpp_deliver ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 1 -- DELIVERY
and x_.change_state_datetime < dateadd( ss, -@response_timer, getdate() )
and x_.dialogue_id not in ( select ud.dialogue_id_ from ussd_dialogue ud with (nolock) where ud.dialogue_id_ = x_.dialogue_id )


set rowcount 0

DELETE_MANAGER_COMPLETE:
if ( @actual_rowcount = @rowcount_limit )
goto DELETE_MANAGER_START

return 0

SETTINGS_ERROR:
raiserror( 'Undefined system settings', 15, 1 )
return 1


Второй участник:
ALTER procedure [dbo].[p_ussd_delete_manager]
as
set xact_abort on

-------------------------------------------------------------------------------------------------
-- определяем необходимые настройки
declare @rowcount_limit int
, @actual_rowcount int

select @rowcount_limit = cast( dbo.f_get_settings( 'USSD_DELETE_MANAGER_ROWCOUNT_LIMIT' ) as int )

declare @d table ( dialogue_id bigint, dialogue_id_ int )

if ( @rowcount_limit is null )
goto SETTINGS_ERROR

DELETE_MANAGER_START:
-------------------------------------------------------------------------------------------------
-- удаляем данные для закрытых диалогов

set rowcount @rowcount_limit
set @actual_rowcount = 0

-- закрываем диалоги с истекшим сроком жизни или периодом неактивности
update d
set dialogue_state_id = 4 -- abort dialogue
from ussd_dialogue d
with ( rowlock, readpast, updlock, index( pk_ussd_dialogue ) )
where dialogue_state_id not in ( 3, 4 )
and ( validity_datetime < getdate()
or activity_datetime < dateadd( ss, -activity_timer, getdate() )
)
option ( maxdop 1 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end

-- удаляем закрытые диалоги и примитивы для них
insert into @d ( dialogue_id, dialogue_id_ )
select dialogue_id, dialogue_id_
from ussd_dialogue
with ( rowlock, readpast, updlock, index( pk_ussd_dialogue ) )
where dialogue_state_id in ( 3, 4 ) -- CLOSED, ABORTED
and transfer_completed = 1
option ( force order, loop join, maxdop 1 )

set @actual_rowcount = case
when @actual_rowcount < @@rowcount then @@rowcount
else @actual_rowcount
end

set rowcount 0

delete d
from @d d_
inner join ussd_dialogue d with ( rowlock )
on d.dialogue_id = d_.dialogue_id
option ( force order, loop join, maxdop 1 )

delete x
from @d d_
inner join ussd_service_primitive_in x with ( rowlock, readpast, updlock, index( nc_idx_ussd_service_primitive_in_dialogue_id ) )
on x.dialogue_id = d_.dialogue_id_
option ( force order, loop join, maxdop 1 )

delete x
from @d d_
inner join ussd_service_primitive_out x with ( rowlock, readpast, updlock, index( nc_idx_ussd_service_primitive_out_dialogue_id ) )
on x.dialogue_id = d_.dialogue_id_
option ( force order, loop join, maxdop 1 )

delete x
from @d d_
inner join smpp_deliver x with ( rowlock, readpast, updlock, index( nc_idx_smpp_deliver_dialogue_id ) )
on x.dialogue_id = d_.dialogue_id_
option ( force order, loop join, maxdop 1 )

delete x
from @d d_
inner join smpp_submit x with ( rowlock, readpast, updlock, index( nc_idx_smpp_submit_dialogue_id ) )
on x.dialogue_id = d_.dialogue_id_
option ( force order, loop join, maxdop 1 )

if ( @actual_rowcount = @rowcount_limit )
goto DELETE_MANAGER_START

--
return 0

SETTINGS_ERROR:
raiserror( 'Undefined system settings', 15, 1 )
return 1
10 май 12, 16:59    [12531656]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
katrina_v
Member

Откуда: Екатеринбург
Сообщений: 9
Дедлок происходит при удалении из таблицы smpp_deliver в обоих процедурах.
10 май 12, 17:02    [12531683]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
katrina_v
Member

Откуда: Екатеринбург
Сообщений: 9
Удаление в первой процедуре:
-- deliver_sm
delete x
from smpp_deliver x_ with ( nolock, index( nc_idx_pdu_state_id_session_id ) )
inner join smpp_deliver x with ( rowlock, readpast, updlock, index( pk_smpp_deliver ) )
on x_.pdu_id = x.pdu_id
and x_.pdu_state_id = 2 -- PROCESSED
and x_.dialogue_id not in ( select ud.dialogue_id_ from ussd_dialogue ud with (nolock) where ud.dialogue_id_ = x_.dialogue_id )
option( force order, loop join )

Удаление во второй:
delete x
from @d d_
inner join smpp_deliver x with ( rowlock, readpast, updlock, index( nc_idx_smpp_deliver_dialogue_id ) )
on x.dialogue_id = d_.dialogue_id_
option ( force order, loop join, maxdop 1 )

В первой процедуре я выбираю строчки, которых нет в таблице ussd_dialogue по условию в джойне.
А во второй я удаляю только те, которые находятся в таблице ussd_dialogue. Не могу понять почему возникает дедлок.
10 май 12, 17:08    [12531733]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
aleks2
Guest
katrina_v
Не могу понять почему возникает дедлок.

Чего тут удивительного? Два delete блокируют страницы в разном порядке - вот и фсе.
10 май 12, 17:33    [12531879]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
katrina_v
Member

Откуда: Екатеринбург
Сообщений: 9
aleks2, т.е. независимо от того пересекаются данные или нет, происходит блокировка целой страницы? А что подразумевается под страницей в дедлоке? В запросах у меня блокируются только строчки в таблице.
10 май 12, 18:47    [12532214]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
aleks2
Guest
Почитайте BOL про Lock Escalation.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/4a24489c-e38f-4d16-9c26-1ab3664cdcb6.htm
10 май 12, 18:54    [12532228]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Бокировка на ключах ( KEY: 5:72057594061979648 ). На страницах была бы PAG.

В любом случае, у вас удаление по двум разным индексам, с разным порядком, скорее всего. Хороший повод для дедлока.

Что делать? Сначала сваливайте ключи (лучше кластерного индекса) во времянку, а потом удаляйте одним стейтментом.
10 май 12, 20:05    [12532548]     Ответить | Цитировать Сообщить модератору
 Re: Не могу разрулить дедлок  [new]
katrina_v
Member

Откуда: Екатеринбург
Сообщений: 9
Коллеги, спасибо за помощь!!!!)))
Благодаря вашим подсказкам смогла разобраться.
Я полагала, что записи, которые я пытаюсь удалить в двух процедурах не пересекаются, а на самом деле пересекаются.
Перед тем как их удалить я их записывала в таблицу и обнаружила, что в процедурах пытаются удалиться одни и теже записи, хотя выборка правильная. Причина такого поведения в том, что во 2ой процедуре удалени происходит не в транзакции. Т.е. сначало идет выборка в переменную таблицу @d, потом удаление из таблицы ussd_dialogue по данным тадлицы @d и только потом удаление из таблицы smpp_deliver. После того как во 2ой процедуре улалилось из таблицы ussd_dialogue, а из таблицы smpp_deliver еще не успело удалиться, первая процедура начинает удалять строчки, которых нет в таблице ussd_dialogue, т.е. забирает такие же строчки.
Для решения такого дедлока я просто переместила во 2ой процедуре удаление из таблицы ussd_dialogue в конец процедуры, чтобы при удалении в первой выбирались только строчки, которых нет в ussd_dialogue.
12 май 12, 08:41    [12540137]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить