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

Откуда: Нижний Новгород
Сообщений: 2341
есть несколько SQL серверов разных версий 2012 и 2014
на них одинаковые базы созданные по одному и тому же скрипту.
в каждой есть некая таблица log и хранимка типа
set xact_abort on
begin try
  <что-то делаем>
end try
begin catch
  insert into log 
end catch

при одной и той-же ситуации, когда в try возникает одна и та же (смоделировано) ошибка, в 2012 в таблице log ничего не появляется, в 2014 все нормально.
где грабли? куда копать?
12 фев 19, 16:20    [21807605]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
Раз смоделировали, давайте репро.
12 фев 19, 16:21    [21807609]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
felix_ff
Member

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

если у вас будет такой код выполняться в транзакции и вы словите exception в блоке try, у вас транзакция перейдет в нефиксируемое состояние и никакого insert в блоке catch разрешено не будет.

set xact_abort on
begin try
  throw 50000, 'ашибка', 16
end try
begin catch
  insert into log 
end catch


проверьте, может дело именно в этом.
12 фев 19, 17:04    [21807660]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
felix_ff
Member

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

тьфу забыл
set xact_abort on
begin tran;
begin try
  throw 50000, 'ашибка', 16
end try
begin catch
  insert into log 
end catch
12 фев 19, 17:06    [21807662]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

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

был не прав :(,
версии SQL все 2014 - 12.0.2269.0 кроме трех, один 2012, 2014 SP2 и 2016.
убрал из рассматриваемых эти 3.
на всех остальных все одинаковое, но результат разный. почему - никак не пойму.
репро подготовлю выложу.
13 фев 19, 11:14    [21808172]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
Владислав Колосов
Member

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

set xact_abort on откатит транзакцию и вы действительно поймаете исключение из-за uncommitable state.
13 фев 19, 11:47    [21808230]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
Владислав Колосов,

там все значительно сложнее..

действительно, если бы было так просто как в первом посте, то достаточно было бы в кэтч добавить проверку экзакт_стэйт <> 0 и откатить транзакцию. но получается, что это я думаю что ситуация одна и та же, а на самом деле видимо нет. соответственно, в зависимости от той ситуации, попаду я в кэтч или нет..
похоже я понял почему я не попадаю.

+ repro
-- подготовка
create database [udb_test] 
alter database [udb_test] set change_tracking = on;
alter database [udb_test] set allow_snapshot_isolation on;
alter database [udb_test] set read_committed_snapshot on;
alter database [udb_test] set auto_close on;
go
create table [udb_test].dbo.ut_test (tst int)

use [tempdb]
create table ut_log (err int, msg varchar(255))
go
create procedure usp_inner 
as
  declare @sqlstr nvarchar(1024) = N'
  use [udb_test];
  set transaction isolation level snapshot;
  set xact_abort on;
  begin transaction;
  select top 1 * from changetable(changes ut_test, 0) a_ct;
  if (xact_state() = 1) commit transaction;
  '
  exec sp_executesql @stmt = @sqlstr
go

create procedure usp_outer
as
  set xact_abort on
  set nocount on
  begin try
    exec usp_inner
  end try
  begin catch
    -- чтобы test 2 не вывалился из батча совсем необходимо раскоментарить следующую строку, в моей процедуре ее и не было
    -- if (xact_state() <> 0) rollback transaction
    insert into ut_log select ERROR_NUMBER(), ERROR_MESSAGE()
  end catch
go

-- test 1 - он будет в окне Results
set nocount on
-- эта ситуация нормально логируется 
exec usp_outer
select * from ut_log
truncate table ut_log
go

-- test 2 - oн будет в окне Messages
set nocount on
-- эта в логи не попадает и возникает видимо когда сторонний софт рулит своими табличками
drop table [udb_test].dbo.ut_test
exec usp_outer
select * from ut_log
go

-- подчищаем
set nocount on
drop procedure usp_outer
drop procedure usp_inner
drop table ut_log
drop database [udb_test] 
go


почему проверку экзакт_стэйт на -1 я не могу поставить ни в usp_inner, ни в текстовую строку никакими способами, иначе получу в ut_log пусто в обоих случаях

и почему ошибка в случае отката 208, а в случае вываливания 3930

странно все это..
13 фев 19, 18:07    [21808821]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ShIgor,
и почему ошибка в случае отката 208, а в случае вываливания 3930

потому что вы таблицу удилили?
13 фев 19, 18:17    [21808835]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
Может быть в log запись не появляется потому, что процедура выполняется в транзакции, а транзакция откатывается из-за недопустимого состояния?

Вы же в блоке catch исключение не выбрасываете. Если надо писать в журнал при любых обстоятельствах, то можно использовать CLR процедуру или event notification.
13 фев 19, 18:28    [21808847]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
ShIgor
почему проверку экзакт_стэйт на -1 я не могу поставить ни в usp_inner, ни в текстовую строку никакими способам
Потому что до выполнения DSQL дело не доходит - ваша процедура падает на его компиляции.
13 фев 19, 18:29    [21808848]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
Вернее так:
Ошибки 208 - batch termination, а 22105 - нет

Соответственно, при наличии ut_test дело доходит до select top 1, возникает ошибка и управление уходит в блок catch
при отсутствии ut_test - падает на компиляции и опять же управление уходит в блок catch
13 фев 19, 18:42    [21808859]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
invm,

не совсем.
22105 - не разрешен CT - попадаем в catch
208 - нет таблы с откатом - попадаем в catch
3930 - нет таблы без отката - в catch тоже попадаем, но никаких транзакционных операций сделать не можем пока не откатимся
замените insert на print и увидите вот такую картину:

кэтч
после 1го exec usp_outer
кэтч
Msg 266, Level 16, State 2, Procedure usp_outer, Line 0 [Batch Start Line 49]
Счетчик транзакций после выполнения EXECUTE показывает несовпадение числа инструкций BEGIN и COMMIT. Предыдущее число = 0, текущее число = 1.

после 2го exec usp_outer
Msg 3998, Level 16, State 1, Line 50
Нефиксируемая транзакция обнаружена в конце пакета. Был выполнен откат транзакции.

так вот и вопрос, явная транзакция была начата в DSQL, что она там и не откатилась-то сама по его завершении?
13 фев 19, 22:19    [21808973]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
ShIgor
так вот и вопрос, явная транзакция была начата в DSQL, что она там и не откатилась-то сама по его завершении?
С чего вдруг она должна была откатиться? Потому что xact_abort = on?
Ну так не оборачивайте в try/catch и откатится. А если оборачивать, то в блоке catch эта транзакция будет в нефиксируемом состоянии. felix_ff вам об этом уже писал.

В результате, если заменить insert на print или select будет ошибка 266 при завершении usp_outer при первом вызове.
13 фев 19, 22:49    [21808983]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
и еще инфа к размышлению.

В первом тесте DSQL генерит ошибку на этапе компиляции и к его выполнению не приступает. поэтому и явной транзакции нет, и не надо ничего откатывать.
Во втором - на этапе выполнения. тоже легко проверить добавив принт в DSQL. С запуском явной транзакции.
ПОЧЕМУ!!! Неужели отсутствие объекта для компилятора меньшее зло нежели отсутствие настроек существующего объекта?
+ repro 2
-- подготовка
create database [udb_test] 
alter database [udb_test] set change_tracking = on;
alter database [udb_test] set allow_snapshot_isolation on;
alter database [udb_test] set read_committed_snapshot on;
alter database [udb_test] set auto_close on;
go
create table [udb_test].dbo.ut_test (tst int)

use [tempdb]
create table ut_log (err int, msg varchar(255))
go
create procedure usp_inner (@n varchar(1))
as
  declare @sqlstr nvarchar(2048) = N'
	print ''' + @n + ' DSQL''
  use [udb_test];
  set transaction isolation level snapshot;
  set xact_abort on;
  begin transaction;
	select top 1 * from changetable(changes ut_test, 0) a_ct;
	if (xact_state() = 1) commit transaction;
  '
  exec sp_executesql @stmt = @sqlstr
go

create procedure usp_outer (@n varchar(1))
as
  set xact_abort on
  set nocount on
  begin try
    exec usp_inner @n
  end try
  begin catch
    -- чтобы test 2 не вывалился из батча совсем необходимо раскоментарить следующую строку, в моей процедуре ее и не было
    --if (xact_state() <> 0) rollback transaction
    print 'catch ' + @n + ' err: ' + ERROR_MESSAGE()
		--insert into ut_log select ERROR_NUMBER(), ERROR_MESSAGE()
		
  end catch
go

-- test 1 - он будет в окне Results
set nocount on
-- эта ситуация нормально логируется 
exec usp_outer '1'
print 'после 1го exec usp_outer'
select * from ut_log
truncate table ut_log
go

-- test 2 - oн будет в окне Messages
set nocount on
-- эта в логи не попадает и возникает видимо когда сторонний софт рулит своими табличками
drop table [udb_test].dbo.ut_test
exec usp_outer '2'
print 'после 2го exec usp_outer'
select * from ut_log
go

-- подчищаем
set nocount on
drop procedure usp_outer
drop procedure usp_inner
drop table ut_log
drop database [udb_test] 
go
+ Messages с комментариями
--1 DSQL отсутствует потому что ошибка на этапе компиляции
catch 1 err: Отслеживание изменений не включено для таблицы "ut_test".
после 1го exec usp_outer
2 DSQL
catch 2 err: Недопустимое имя объекта "ut_test".
-- В catch все-таки попадаем и ошибку правильную получаем, но стартованная явная транзакция в DSQL обрывает весь batch
Msg 266, Level 16, State 2, Procedure usp_outer, Line 0 [Batch Start Line 50]
Счетчик транзакций после выполнения EXECUTE показывает несовпадение числа инструкций BEGIN и COMMIT. Предыдущее число = 0, текущее число = 1.

после 2го exec usp_outer
Msg 3998, Level 16, State 1, Line 51
Нефиксируемая транзакция обнаружена в конце пакета. Был выполнен откат транзакции.
13 фев 19, 23:22    [21808997]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
Владислав Колосов
Member

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

просто напишите set xact_abort off, если пользуетесь try catch.
15 фев 19, 12:47    [21810478]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
Владислав Колосов,

еще хуже.
если не откатить, то даже подчистка колом встает.
15 фев 19, 16:11    [21810905]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
Владислав Колосов
Member

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

от каких ошибок Вы хотите защититься, от ошибок компиляции или от ошибок рантайма? Или хотите журналировать ошибки? Не совсем понятны цели этого обсуждения.
15 фев 19, 16:29    [21810931]     Ответить | Цитировать Сообщить модератору
 Re: Поведение try/catch в разных версиях SQL  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
Владислав Колосов,

конечно от ошибок рантайма.
но всем спасибо, я понял, где накосячил я, почему не логируется и в результате чего такие проявления.
поэтому можно закрыть тему.
15 фев 19, 17:37    [21811066]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить