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

Откуда: СПб
Сообщений: 196
Добрый день!
Стоит такая задача - изменять данные (например удалять) внутри транзакции. Причем состояние xact_abort неизвестно. Предлагаемое решение:
CREATE PROCEDURE procName
AS
BEGIN
...
  declare @xact_abort varchar(3) = 'off'
  if ((16384 & @@OPTIONS) = 16384)
     set @xact_abort = 'on'

  begin try
    begin tran
      set xact_abort on

      delete tableName
....
    commit tran
  end try
  begin catch
    if (@xact_abort = 'off')
      set xact_abort off
  end catch

END


Вопрос: может есть более интересное решение?
7 ноя 17, 10:38    [20931636]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
AndyMandy
Вопрос: может есть более интересное решение?
Есть.
При выходе из процедуры состояние опций восстанавливается автоматически.
7 ноя 17, 10:49    [20931701]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
TaPaK
Member

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

нифига не понятно зачем этот балет... Зачем его менять туда-сюда...
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
7 ноя 17, 10:52    [20931717]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
AndyMandy
Member

Откуда: СПб
Сообщений: 196
invm
...Есть.При выходе из процедуры состояние опций восстанавливается автоматически.

TaPaK
...нифига не понятно зачем этот балет... Зачем его менять туда-сюда...

Большое спасибо. Получается так:
CREATE PROCEDURE procName
AS
BEGIN
...
  set xact_abort on
  begin try
    begin tran

      delete tableName
....
    commit tran
  end try
  begin catch
    if XACT_STATE()) = -1 
      rollback tran
    if XACT_STATE()) = 1
      commit tran
  end catch

END

Так красивее конечно.
7 ноя 17, 11:10    [20931817]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
AndyMandy
invm
...Есть.При выходе из процедуры состояние опций восстанавливается автоматически.

TaPaK
...нифига не понятно зачем этот балет... Зачем его менять туда-сюда...

Большое спасибо. Получается так:
CREATE PROCEDURE procName
AS
BEGIN
...
  set xact_abort on
  begin try
    begin tran

      delete tableName
....
    commit tran
  end try
  begin catch
    if XACT_STATE()) = -1 
      rollback tran
    if XACT_STATE()) = 1
      commit tran
  end catch

END


Так красивее конечно.
При XACT_ABORT ON оно само всё откатится при первой же ошибке.
Достаточно делать COMMIT при @@TRANCOUNT>0, и всё.
7 ноя 17, 11:12    [20931830]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
AndyMandy
Member

Откуда: СПб
Сообщений: 196
iap
При XACT_ABORT ON оно само всё откатится при первой же ошибке.
Достаточно делать COMMIT при @@TRANCOUNT>0, и всё.

Тогда еще короче:
CREATE PROCEDURE procName
AS
BEGIN
...
  set xact_abort on
  begin tran

      delete tableName
....
  if @@TRANCOUNT>0
    commit tran


END
7 ноя 17, 11:31    [20931950]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
AndyMandy,

на рассчитывайте на xact_abort + case..endcase. Может сыграть плохую шутку.

Case 1, ожидаемое поведение, прерывание выполнения пакета:
declare @t table (f1 int not null);
set xact_abort on

insert @t (f1) values (null);
print 'non-stop'


Case 2, аномальное поведение, прерывание выполнения пакета не происходит:
declare @t table (f1 int not null);
set xact_abort on

begin try
		insert @t (f1) values (null);
end try
begin catch;
	print 'write2log'
end catch;

print 'endpack';


Т.е. то, что происходит внутри CASE, находится вне юрисдикции xact_abort.
7 ноя 17, 11:37    [20931988]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов,

какой case? и оба поведения очень дажи понятны.
7 ноя 17, 11:44    [20932036]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
AndyMandy
Member

Откуда: СПб
Сообщений: 196
Владислав Колосов
...
на рассчитывайте на xact_abort + case..endcase. Может сыграть плохую шутку.
...

Уже отказался. Распишу подробнее:
CREATE PROCEDURE procName
AS
BEGIN
...
  set xact_abort on -- Включаем по-любому
  begin tran  -- Погнали!

      delete tableName -- Если тут хапнем error не важно какого уровня, то за счет  "set xact_abort on" прерывается процедура и откатывается транзакция
....
  if @@TRANCOUNT>0 
    commit tran -- Сюда попадем только если все будет хорошо, никаких error

END  -- В НЕ зависимости от результата (был или не был error не важно) xact_abort возвернется к тому значению которые было до вызова процедуры
7 ноя 17, 14:01    [20932619]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
AndyMandy
...
      delete tableName -- Если тут хапнем error не важно какого уровня, то за счет  "set xact_abort on" прерывается процедура и откатывается транзакция
...
Если тут "хапнете" ошибку, сгенерированную в триггере через raiserror, то ничего не прервется и не откатится.
7 ноя 17, 14:19    [20932734]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
dies irae
Member

Откуда:
Сообщений: 78
invm
AndyMandy
...
      delete tableName -- Если тут хапнем error не важно какого уровня, то за счет  "set xact_abort on" прерывается процедура и откатывается транзакция
...
Если тут "хапнете" ошибку, сгенерированную в триггере через raiserror, то ничего не прервется и не откатится.


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

и ещё, в коде не должно быть ничего лишнего. поэтуму проверку на @@trancount - убрать, она лишняя.
И begin - end в обрамлении процедуры - тоже в топку

CREATE PROCEDURE procName
AS
...
set xact_abort on
begin tran

    delete tableName 
commit tran
7 ноя 17, 14:40    [20932848]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
dies irae
если триггер делает raiserror - он же и должен сделать rollback
Попробуйте в этом убедить идейных противников rollback'а в триггерах.
7 ноя 17, 14:46    [20932878]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
AndyMandy
Member

Откуда: СПб
Сообщений: 196
dies irae
Если тут "хапнете" ошибку, сгенерированную в триггере через raiserror, то ничего не прервется и не откатится.

Это безусловно так, поэтому добавил catch в код ниже.
dies irae
и ещё, в коде не должно быть ничего лишнего. поэтуму проверку на @@trancount - убрать, она лишняя.

А ежели транзакция в том же триггере уже commit?
begin-end Это уже дело вкуса.
CREATE PROCEDURE procName
AS
BEGIN
...
  set xact_abort on
  begin try
    begin tran

      delete tableName
....
  if @@TRANCOUNT>0
    commit tran
  end try
  begin catch
   if @@TRANCOUNT>0
      rollback tran
  end catch

END
7 ноя 17, 17:28    [20933800]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
dies irae
Member

Откуда:
Сообщений: 78
AndyMandy,

триггеров, как правило, гораздо меньше, чем хп.
если в триггере есть raiserror, но нет rollback - это плохой триггер. Потому, что этот raiserror придётся обрабатывать во всех хранимых процедурах и просто батчах.

Также не должно быть в триггерах никаких коммитов без begin tran. begin tran - commit обязательно на одном уровне, иначе бардак.

Разумеется, это всё актуально для нормально спроектированной системы. Если в базу пишут как попало и/или стоит задача перестраховаться и предусмотреть все случаи жизни - пожалуйста, усложняйте читаемость, нарушайте консистентность и увеличивайте связность кода
7 ноя 17, 18:18    [20934071]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
AndyMandy
Member

Откуда: СПб
Сообщений: 196
dies irae
...
Разумеется, это всё актуально для нормально спроектированной системы. Если в базу пишут как попало ...

В точку! Именно как попало и приходится страховаться.
7 ноя 17, 18:27    [20934095]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Попробуйте в этом убедить идейных противников rollback'а в триггерах.

Справедливости ради, стоит отметить, что в иных конторах использование райзэррора и роллбэка в триггере -- принципиальное архитектурное решение. Притом, весьма толковое.
8 ноя 17, 10:51    [20935591]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
лолл
Member

Откуда:
Сообщений: 450
set xact_abort on, имхо, хорош в простых скриптах. Если образована вложенность процедур, при которой транзакцию начинает внешняя, а из-за ошибки она завершится во внутренней, то будет дополнительно создано исключение типа "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements"
8 ноя 17, 11:34    [20935733]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
invm
dies irae
если триггер делает raiserror - он же и должен сделать rollback
Попробуйте в этом убедить идейных противников rollback'а в триггерах.


Собственно, дело не в идее, а в архитектуре обработки ошибок и получения их стека. Если триггер во внешней транзакции, то откатывать должен внешний обработчик ошибки, если не в транзакции, то откат произойдет автоматически. Т.е. сам откат в триггере избыточен. Другое дело, если архитектуры обработки ошибки в процедурах нет в принципе и исправить это невозможно.
8 ноя 17, 11:34    [20935736]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Владислав Колосов
invm
пропущено...
Попробуйте в этом убедить идейных противников rollback'а в триггерах.


Собственно, дело не в идее, а в архитектуре обработки ошибок и получения их стека. Если триггер во внешней транзакции, то откатывать должен внешний обработчик ошибки, если не в транзакции, то откат произойдет автоматически. Т.е. сам откат в триггере избыточен. Другое дело, если архитектуры обработки ошибки в процедурах нет в принципе и исправить это невозможно.
Триггер всегда стартует в транзакции.
8 ноя 17, 11:40    [20935759]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
iap,

да, вопрос лишь в счетчике транзакций. Если вы не протолкнете ошибку наверх, то получите попытку uncommitable фиксации, а это значит, что придется писать обвязку по счетчику для каждого begin tran ... end tran в каждой процедуре. Нерационально.
8 ноя 17, 11:47    [20935777]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
iap,

вы поймали меня на пропущенном слове "внешняя". Очевидно, что "внешняя". Или будете ловить на определении "внешняя" теперь?
8 ноя 17, 11:49    [20935782]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7663
TaPaK
Владислав Колосов,

какой case? и оба поведения очень дажи понятны.


Для Вас понятны, потому, что Вы "в теме". В документации я такого не нашел (плохо искал?) и сам попался один раз.
8 ноя 17, 11:56    [20935805]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
TaPaK,
автор
и оба поведения очень дажи понятны.

Вообще-то как раз второй вариант контринтуитивен. В справке экзакт аборт написано, что транзакция откатывается АВТОМАТИЧЕСКИ если возникает эррор. Более там никаких спецэффектов не описано. Совершенно неочевидно, что ТрайкКэтч в данном случае имеет приоритет.


Это если начать рассуждать, то да. Трайкэтч нужен для ручного разруливания эрроров. Экзакт аборт нужен для автоматического разруливания эрроров. Какбе очевидно, что ручная обработка приоритетнее автоматической. Но это все на уровне "здравого смысла".

А дока к скульсерверу, как обычно, подкачала.
8 ноя 17, 12:23    [20935944]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
TaPaK
Member

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

автор
Вообще-то как раз второй вариант контринтуитивен. В справке экзакт аборт написано, что транзакция откатывается АВТОМАТИЧЕСКИ если возникает эррор. Более там никаких спецэффектов не описано. Совершенно неочевидно, что ТрайкКэтч в данном случае имеет приоритет.

что? по вашему транзакция не откатилась? вообще набор слов какой-то
8 ноя 17, 12:40    [20936024]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте смешивание xact_abort , try и tran в одной процедуре  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
DROP TABLE #t

CREATE TABLE #t (f1 INT NOT NULL);
 SET XACT_ABORT ON

BEGIN TRY
	BEGIN TRAN
	SELECT @@trancount
  INSERT INTO #t VALUES (42) 
	INSERT #t (f1)
	VALUES (NULL);

	COMMIT TRAN
END TRY

BEGIN CATCH 
    SELECT * FROM #t /*Охты что это?! Экзакт Аборт не работает! */
   	SELECT @@trancount
END CATCH

  
8 ноя 17, 14:08    [20936455]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить