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

Откуда:
Сообщений: 48
Добрый день.

Пытаюсь устранить дедлоки в БД. При просмотре профайлера получил странные данные. Помогите разобраться.
Прилагаю граф дедлока. Если судить по нему, то процесс 63 удерживает U-блокировку на таблицу CARD_PURSES. Процесс 63 последовательно делает одни и те же операции в цикле:
1. Запрос информации
2. Изменение данных
Предыдущие транзакции на изменение данных закрыты. На момент времени дедлока выполняется шаг 1. Откуда U-блокировка понять не могу. Воспроизвести данную ситуацию у меня не получается.

Еще одна странность с процессом 117. Он выполняет ряд вложенных ХП и в один "прекрасный" момент происходит ошибка, при которой он пропускает все последующие инструкции, включая TRY-CATCH блоки и инструкцию ROLLBACK, что приводит к подвисшей транзакции. Скриншот с текстом профайлера прилагаю. После ошибки идет просто "вываливание" в верхнюю ХП. Т. к. не могу повторить эту ситуацию искусственно, не могу понять как с ней бороться. Поможет ли установка set xact_abort on, чтобы произошел откат транзакции и она завершилась?

К сообщению приложен файл (deadlock.zip - 137Kb) cкачать
21 мар 16, 17:51    [18960272]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8725
Leonid K.,

2. Изменение данных (другим процессом)

U блокировка

TRY-CATCH ловит не все ошибки.
21 мар 16, 18:00    [18960315]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Leonid K.,

Внимательно читать про ограничения TRY-CATCH
21 мар 16, 19:04    [18960590]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Leonid K.,

автор
Поможет ли установка set xact_abort on, чтобы произошел откат транзакции и она завершилась?

Поможет.
21 мар 16, 19:05    [18960596]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Leonid K.
Откуда U-блокировка понять не могу.
Возникает в
UPDATE CARD_PURSES SET Rest = Rest - @writeoff WHERE Id = @idpurs

Потому что Id или не входит в PK, или не является в нем первым столбцом.
21 мар 16, 19:17    [18960626]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
msLex
Member

Откуда:
Сообщений: 9077
invm
Потому что Id или не входит в PK, или не является в нем первым столбцом.

а какое отношение PK имеет к U блокировкам?
21 мар 16, 20:28    [18960808]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
invm
Возникает в

UPDATE CARD_PURSES SET Rest = Rest - @writeoff WHERE Id = @idpurs


Потому что Id или не входит в PK, или не является в нем первым столбцом.


1. Id является первичным ключом и первым столбцом по совместительству. Только не понимаю какое это имеет отношение.
2. Эту инструкцию выполняет процесс 117, а не 63. Откуда у 63 эта блокировка, он UPDATE-ов таблицы CARD_PURSES в текущей транзакции не делал. Могу допустить, что осталась блокировка от предыдущего прохода, но не могу понять почему она осталась висеть.

Про TRY-CATCH вроде понял.
21 мар 16, 20:41    [18960842]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
WarAnt
Leonid K.,
автор
Поможет ли установка set xact_abort on, чтобы произошел откат транзакции и она завершилась?

Поможет.

давайте пример, когда поможет, а то я не очень в такое верю.
у ТС ведь не просто ошибка, а такая, что не ловится в TRY..CATCH.
я таких знаю немного, это фатальные (не его случай),
DBCC CHECKDB (вообще экзотика) и ошибки компиляции.
ну так ошибкам компиляции никак xact_abort не поможет.
их надо отлавливать во внешней процедуре и там же делать роллбэк:
create proc inner_sp as
set xact_abort on;
   begin try
      begin transaction
      print 'inner_sp starting';
      select * from xyz;
      commit transaction;
   end try
   begin catch
       if @@trancount > 0 rollback transaction;
       print 'inner catch';
   end catch
go

create procedure outer_sp as
set xact_abort on;
   begin try
      print 'outer_sp begin try: @@trancount is ' + ltrim(str(@@trancount));
      exec inner_sp;
      print 'outer_sp end try: @@trancount is ' + ltrim(str(@@trancount));
   end try
   begin catch
      print 'outer_sp catch: ' + error_message();
      print 'outer_sp catch: @@trancount is ' + ltrim(str(@@trancount));
      if @@trancount > 0 rollback transaction;
      print 'outer_sp rollback done: @@trancount is ' + ltrim(str(@@trancount));
   end catch
go
set xact_abort on;
exec outer_sp
go

outer_sp begin try: @@trancount is 0
inner_sp starting
outer_sp catch: Invalid object name 'xyz'.
outer_sp catch: @@trancount is 1
outer_sp rollback done: @@trancount is 0
22 мар 16, 00:45    [18961501]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Leonid K.
Откуда у 63 эта блокировка
Нет у 63 U. U есть у 117.

Вы стали жертвой неверного отображения графа в графическом виде.
Реальная картина видна в xml-виде графа. Если посмотреть на resource-list:
  <resource-list>
   <keylock hobtid="72057595035385856" dbid="5" objectname="OnlinePC.dbo.FA_PURSES" indexname="PK_FA_PURSES" id="lock4534bbc00" mode="X" associatedObjectId="72057595035385856">
    <owner-list>
     <owner id="117 - process21892cca8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="113 - process1562b5c28" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057595035385856" dbid="5" objectname="OnlinePC.dbo.FA_PURSES" indexname="PK_FA_PURSES" id="lock4534bbc00" mode="X" associatedObjectId="72057595035385856">
    <owner-list>
     <owner id="113 - process1562b5c28" mode="S" requestType="wait"/>
    </owner-list>
    <waiter-list>
     <waiter id="63 - process3fb7ce4e8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  <keylock hobtid="72057595032371200" dbid="5" objectname="OnlinePC.dbo.CARD_PURSES" indexname="PK_CARDPURSES" id="lock455757580" mode="U" associatedObjectId="72057595032371200">
    <owner-list>
     <owner id="process3fb7ce4e8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process21892cca8" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

Видно, что process3fb7ce4e8 (который 63) удерживает S, а process21892cca8 удерживает U и хочет сконвертировать ее в X.

Индекс PK_CARDPURSES - кластерный и реализует PK. Иначе бы не было попытки наложить X на ключ.
Другие экзотические варианты по системе "путай-путай" рассматривать не будем.

Так вот, что бы при этом получить конвертацию U -> X, в вышепоказанном запросе UPDATE должно быть Clustered Index Scan, либо Clustered Index Seek, в котором кроме Seek Predicates имееются еще и просто Predicates.
Тогда на ключ строки будет накладываться U. Если строка удовлетворяет условиям поиска, U конвертируется в X.

Вы пишите, что Id и есть PK. В этом случае никакого сканирования нет и на ключ должна накладываться сразу X.
Тогда остается единственный вариант, когда U накладывается где-то ранее по ходу транзакции.

Вообще, дедлок несколько странный.
Не понятно, что там делает process1562b5c28, который только ждет S и никому не мешает. Так он еще и выбран жертвой.
Хотя, если судить по resource-list, жертвой должен быть process3fb7ce4e8.
22 мар 16, 01:33    [18961562]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
Попробовал повторить ситуацию с вылетом 117 искусственно (с помощью kill) и проверить xact_abort. Картина в профайлере похожа: то же вылет в ХП более высокого уровня с пропуском блоков try-catch. Но при этом транзакция завершается и откатывается независимо от установки xact_abort.

CREATE PROCEDURE [dbo].[_sp_test]
AS
BEGIN
	SET NOCOUNT ON;

	set xact_abort on;

	BEGIN TRANSACTION
	BEGIN TRY
		print 'test 1'
		EXEC _sp_test2
	END TRY
	BEGIN CATCH
		print 'test 2'
	END CATCH

	if(@@TRANCOUNT > 0) begin
		print 'test 3'
		ROLLBACK
	end

	print 'test 4'
END
GO

CREATE PROCEDURE [dbo].[_sp_test2]
AS
BEGIN
	set xact_abort on;

	BEGIN TRY
		print 'test2 1'
		UPDATE CARD_PURSES SET Rest = 10000 WHERE Id = 57971
		EXEC _sp_test3
		print 'test2 2'
	END TRY
	BEGIN CATCH
		print 'test2 3'
	END CATCH

	print 'test2 4'
END
GO

ALTER PROCEDURE [dbo].[_sp_test3]
AS
BEGIN
	SET NOCOUNT ON;

	set xact_abort on;
	begin try
		print 'test3 1'

		UPDATE CARD_PURSES SET Rest = 20000 WHERE Id = 57971

		WAITFOR DELAY '1:00:00'
			
		print 'test3 2'
	end try
	begin catch
		print 'test3 3'
	end catch
	
	print 'test3 4'
END
GO

EXEC [dbo].[_sp_test]
GO


Результат при убивании процесса один и тот же:

test 1
test2 1
test3 1
Msg 0, Level 11, State 0, Line 0
При выполнении текущей команды возникла серьезная ошибка.. При наличии результатов они должны быть аннулированы.
Msg 0, Level 20, State 0, Line 0
При выполнении текущей команды возникла серьезная ошибка.. При наличии результатов они должны быть аннулированы.

Транзакция откатывается в любом случае. В случае же со 117 процессом она осталась висеть открытой. Мне бы хотелось добиться такого же результата, чтобы убедиться что установка xact_abort поможет.

o-o
ну так ошибкам компиляции никак xact_abort не поможет.
их надо отлавливать во внешней процедуре и там же делать роллбэк:

Каждая ХП обрамлена блоком try-catch. Более того клиентское ПО вызывает ХП след. образом:
begin try
     EXEC _sp_...
end try
begin catch
end catch

Судя по данным профайлера, ни один из блоков не сработал

Вообще есть ли какие-то рекомендации, где включать xact_abort? Или можно во всех ХП устанавливать? Почему он тогда по-умолчанию выключен?
22 мар 16, 12:11    [18962958]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8725
kill не поможет, т.к. он откатит транзакцию.
Вы где-то по ходу пьесы должны поиграться метаданными или вообще прибить таблицу, к которой идет дальше обращение.
22 мар 16, 12:28    [18963092]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
Владислав Колосов
Вы где-то по ходу пьесы должны поиграться метаданными или вообще прибить таблицу, к которой идет дальше обращение.

Попробовал проапдейтить несуществующую таблицу. TRY-CATCH отработал как положено.
Написал клиентскую программу и сделал дисконнект во время выполнения ХП. TRY-CATCH не отработал, но транзакция откатилась без установки xact_abort.
22 мар 16, 14:45    [18964091]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
Спасибо invm. Действительно, если сэмулировать ситуацию, которую вы описали (и которая представлена в resource-list), возникает дедлок. Правда для него достаточно 2-х процессов. Буду думать, как устранить.

Остается непонятным вопрос по поводу xact_abort.
22 мар 16, 15:53    [18964697]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
Leonid K.
Попробовал проапдейтить несуществующую таблицу. TRY-CATCH отработал как положено.

какой у вас сервер особенный,
у других ошибки компиляции в том же бэтче не ловятся ну никак.
вот новый вариант процедуры и ее output.
не печатает из inner catch, не откатывает открытую транзакцию
create proc inner_sp as
set xact_abort on;
   begin try
      begin transaction
      print 'inner_sp starting';
      update xyz
      set col = 10;
      commit transaction;
   end try
   begin catch
       if @@trancount > 0 rollback transaction;
       print N'TRY-CATCH отработал как положено';
   end catch
go

create procedure outer_sp as
set xact_abort on;
   begin try
      print 'outer_sp begin try: @@trancount is ' + ltrim(str(@@trancount));
      exec inner_sp;
      print 'outer_sp end try: @@trancount is ' + ltrim(str(@@trancount));
   end try
   begin catch
      print 'outer_sp catch: ' + error_message();
      print 'outer_sp catch: @@trancount is ' + ltrim(str(@@trancount));
      if @@trancount > 0 rollback transaction;
      print 'outer_sp rollback done: @@trancount is ' + ltrim(str(@@trancount));
   end catch
go
set xact_abort on;
exec outer_sp;
go

outer_sp begin try: @@trancount is 0
inner_sp starting
outer_sp catch: Invalid object name 'xyz'.
outer_sp catch: @@trancount is 1
outer_sp rollback done: @@trancount is 0
22 мар 16, 15:57    [18964719]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
Leonid K.
Вообще есть ли какие-то рекомендации, где включать xact_abort? Или можно во всех ХП устанавливать? Почему он тогда по-умолчанию выключен?

да, всегда включать xact_abort on.
а по умолчанию выключен, т.к. legacy.
MS очень печется о совместимости с динозавро-версиями
22 мар 16, 16:01    [18964737]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Владислав Колосов
Member

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

xact_abort on вроде бы может выбить пробки мимо try-catch.
Я на что-то такое натыкался.
22 мар 16, 17:13    [18965149]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
o-o
какой у вас сервер особенный,
у других ошибки компиляции в том же бэтче не ловятся ну никак.

Тут недопонимание. В том же конечно не ловится. Выше я обратил внимание, что сам вызов ХП обрамлен try-catch. И не понятно, как возникла эта подвисшая транзакция.
Хотя да, для проверки самой работы xact_abort потестирую без внешнего блока.
22 мар 16, 17:34    [18965275]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
Владислав Колосов
o-o,
xact_abort on вроде бы может выбить пробки мимо try-catch.
Я на что-то такое натыкался.

а я нет.
поэтому или пример, или ссылку давайте.
xact_abort влияет на поведение в смысле роллбэка, сделают или нет его автоматически.
а не на отлавливаемость в try/catch
22 мар 16, 17:53    [18965351]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Leonid K.,

http://www.sommarskog.se/error-handling-I.html
22 мар 16, 17:59    [18965378]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
Прочитал статью, поэкспериментировал с xact_abort и в итоге так и не понял как бороться с вылетами, приводящими к подвисшей транзакции.
1. Все блоки TRY-CATCH (как и др. инструкции) в моем случае проигнорировались. Т. е. поймать эксепшен и вызвать ROLLBACK я не смогу.
2. set xact_abort on помогает не во всех случаях. В частности, в примере, приведенном o-o, он транзакцию не откатил. Поэтому у меня нет уверенности, что он откатит транзакцию и в моем случае.
23 мар 16, 17:07    [18969413]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Leonid K.
Все блоки TRY-CATCH (как и др. инструкции) в моем случае проигнорировались.
А в чем заключается "ваш случай"?
23 мар 16, 17:35    [18969549]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
Leonid K.
Member

Откуда:
Сообщений: 48
invm
А в чем заключается "ваш случай"?

В первом же сообщении описал ситуацию с процессом 117. Заключается он в том, что при определенных обстоятельствах (которые я не могу воспроизвести искусственно) SQL Server завершает выполнение ХП, игнорируя все последующие инструкции.
23 мар 16, 17:42    [18969584]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
Leonid K.
2. set xact_abort on помогает не во всех случаях.
В частности, в примере, приведенном o-o, он транзакцию не откатил.
Поэтому у меня нет уверенности, что он откатит транзакцию и в моем случае.

SET XACT_ABORT (Transact-SQL)
BOL
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT

в моем ответе товарищу WarAnt уже было сказано по этому поводу.
ошибки компиляции не ловятся в TRY CATCH в том же scope.
но они ловятся в том же TRY CATCH уровнем выше.
и как раз этим же ошибкам фиолетово на xact_abort
мой пример именно на ошибку компиляции.
23 мар 16, 18:03    [18969699]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
o-o
Guest
Leonid K.
Попробовал повторить ситуацию с вылетом 117 искусственно (с помощью kill) и проверить xact_abort. Картина в профайлере похожа: то же вылет в ХП более высокого уровня с пропуском блоков try-catch. Но при этом транзакция завершается и откатывается независимо от установки xact_abort.
Msg 0, Level 11, State 0, Line 0
При выполнении текущей команды возникла серьезная ошибка.. При наличии результатов они должны быть аннулированы.
Msg 0, Level 20, State 0, Line 0
При выполнении текущей команды возникла серьезная ошибка.. При наличии результатов они должны быть аннулированы.

Транзакция откатывается в любом случае. В случае же со 117 процессом она осталась висеть открытой. Мне бы хотелось добиться такого же результата, чтобы убедиться что установка xact_abort поможет.

вы же тут устроили "фатальную" ошибку,
разумеется, такое никто не отловит,
после фстальной ошибки уже вообще ничего не выполнится,
не только TRY..CATCH.
это же просто капец сессии.
а вы утверждаете, что после загадочной ошибки
"идет просто "вываливание" в верхнюю ХП"
ну так еще раз: по симптомам, это ошибка компиляции.
во внешней процедуре в TRY..CATCH проверяйте открытые транзакции и откатывайте,
ели они есть. а заодно логируйте ошибку или RAISERROR ей делайте
23 мар 16, 18:30    [18969846]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Leonid K.
Заключается он в том, что при определенных обстоятельствах (которые я не могу воспроизвести искусственно) SQL Server завершает выполнение ХП, игнорируя все последующие инструкции.
У вас там сообщение "The statement has been terminated".
Это ошибка 3621 с severity 10. В try/catch отлавливаются ошибки с severity > 10.

3621 обычно возникает как сопровождение другой, более серьезной ошибки.
Если кроме 3621 других ошибок нет, значит ваша инструкция UPDATE, предшествующая появлению ошибки, завершилась по клиентскому таймауту. Транзакция при этом автоматом не откатывается.
23 мар 16, 18:51    [18969946]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить