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

Откуда:
Сообщений: 80
Доброго дня!

На форуме уже не один вопрос по данной теме, но ни в одной теме (как и нигде в офф документации) не могу найти четкого ответа - какие ошибки могут стать причиной перехода транзакции в состояние нефиксируемой, почему это состояние возникает, где можно об этом подробно почитать? Ошибки DDL и XACT_STATE ON не принимаю во внимание - с ними более менее понятно, но что с остальными случаями?

Набросал небольшой пример на основе реальных хранимок, которые сваливаются с ошибкой:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Вот текст:
create procedure test_outer as
begin
	begin transaction;
	begin try
		exec dbo.test_inner;
	end try
	begin catch
		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		set @ErrorMessage = 'STATE=' + cast(XACT_STATE() as varchar);
	end catch;
	commit;
end;

create procedure test_inner as
begin
	declare @i int;
	declare @c varchar(10);
	set @c = 'asd';
	set @i = cast(@c as integer);
end;


Во внешей хранимке открывается транзакция, внутренняя хранимка генерит исключение преобразования типов (которое никоим образом на самой транзакции не отражается). Однако XACT_STATE() в блоке CATCH уже показывает на "-1". Почему так?

Сразу вброшу более практически обоснованный пример (с которым, собственно, и мучаюсь сейчас) - есть внешняя хранимка, которая перебирает в цикле определенные записи и коммитит обработки блоками по N штук. Саму обработку отдельной записи выполняет внутренняя хранимка (вызываемая из внешней) и для нее ошибка во входных данных должна быть обычным делом, не влияющим на результаты обработки остальных записей и, соответственно - на возможность закоммитить корректно обработанные записи.
26 дек 17, 17:20    [21064192]     Ответить | Цитировать Сообщить модератору
 Re: uncommittable transaction  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
Ceib, Использование конструкции TRY...CATCH в языке Transact-SQL см Нефиксируемые транзакции
26 дек 17, 17:31    [21064228]     Ответить | Цитировать Сообщить модератору
 Re: uncommittable transaction  [new]
Ceib
Member

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

Это внимательно изучил, причем неоднократно - так сказать для лучшего усвоения) Здесь нет ответа на вопрос. Формулировки расплывчатые и без конкретики:
  • "транзакции могут переходить в состояние, в котором транзакция остается открытой, но не может быть зафиксирована" - это мы и так понимаем, раз ошибку получили, нам интересно почему и в каких случаях это происходит, чтобы предусмотреть это в своем коде;
  • "транзакция переходит в нефиксируемое состояние, если внутри блока TRY происходит ошибка, которая в других обстоятельствах завершила бы эту транзакцию" - какие именно ошибки могут спровоцировать переход в это состояние? - без этого знания мы не сможем предусмотреть корректное поведение
  • "...в результате большинства ошибок, вызываемых инструкциями языка DDL...", "когда значение параметра SET XACT_ABORT установлено в ON" - об этом писал, что более менее понятно. Интересуют остальные (совсем не очевидные) случаи

    Про проверку XACT_STATE - тоже понятно, что она даст нам информацию о текущем состоянии, но если в конкретной задаче крайне важно этого состояния избежать, то что делать?


    Все таки вопрос про конкретику у меня возник. Хотя бы и на том примере, что привел - ПОЧЕМУ там возникает эта ошибка? Как влияет ошибка преобразования типов нейтральных локальных переменных на всю транзакцию и как не потерять изменения транзакции при возникновении таких "левых" ошибок?
  • 26 дек 17, 17:42    [21064263]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9301
    Ceib
    Во внешей хранимке открывается транзакция, внутренняя хранимка генерит исключение преобразования типов (которое никоим образом на самой транзакции не отражается). Однако XACT_STATE() в блоке CATCH уже показывает на "-1". Почему так?
    Потому что у вас XACT_ABORT = ON.
    26 дек 17, 17:47    [21064289]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

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

    Нет, у меня XACT_ABORT = OFF. По умолчанию XACT_ABORT = OFF, поэтому в коде явно не прописал установку. Повторяю код с установкой параметра:

    create procedure test_outer as
    begin
    	set XACT_ABORT OFF
    	begin transaction;
    	begin try
    		exec dbo.test_inner;
    	end try
    	begin catch
    		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    		set @ErrorMessage = 'STATE=' + cast(XACT_STATE() as varchar);
    	end catch;
    	commit;
    end;
    
    create procedure test_inner as
    begin
    	declare @i int;
    	declare @c varchar(10);
    	set @c = 'asd';
    	set @i = cast(@c as integer);
    end;
    



    Ошибка никуда не делась.
    26 дек 17, 17:51    [21064312]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    buser
    Member

    Откуда: Санкт-Петербург
    Сообщений: 4537
    Ceib, не нашел в мсдн... где-то на форуме уже было... Errors Raised with Severity/Level 16 May Cause Transactions into Doomed State
    26 дек 17, 18:15    [21064404]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    felix_ff
    Member

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

    ну так а что вы хотели, у вас ошибка откатывающая транзакцию возникает во внутренней процедуре, но там у вас блока try_catch нет.

    соответственно у вас откатывается транзакция в процедуре test_inner, и вы выходите из нее с @@trancount уже не тем с которым в нее зашли.
    26 дек 17, 18:20    [21064422]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    felix_ff
    Member

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

    что бы не было ошибки у вас должно быть что то типа такого:
    alter procedure test_outer as
    begin
    	set XACT_ABORT OFF
    	begin transaction;
    	begin try
    		exec dbo.test_inner;
    	end try
    	begin catch
    		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    		set @ErrorMessage = 'STATE=' + cast(XACT_STATE() as varchar);
    	end catch;
    	if xact_state() = 1 commit;
    end;
    
    alter procedure test_inner as
    begin
    begin try
    	declare @i int;
    	declare @c varchar(10);
    	set @c = 'asd';
    	set @i = cast(@c as integer);
    end try
    begin catch
         if xact_state() = -1 rollback;
    end catch
    end;
    


    не беря в расчет что по хорошему вам еще на входе внешней процедуры необходимо анализировать @@trancount, иначе вся эта красота развалится при входе во внешнюю хп с TC>0
    26 дек 17, 18:30    [21064457]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    во общем случае никак.
    Потому что точного списка ошибок нет.
    27 дек 17, 10:06    [21065639]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

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

    Как писал выше - есть необходимость обходиться без откатов и обязательно фиксировать уже внесенные ранее в транзации изменения. Вопрос в том, что нет понимания логики - почему ошибки никоим образом не влияющие на саму транзакцию (в данном случае - операции надо локальными переменными) фактически убивают эту транзакцию и обработать это (продолжить работу транзакции без отката) невозможно. Поправьте меня, если не прав.



    buser,

    Спасибо за ссылку - интересная заметка. Исходя из последнего абзаца:

    "I have written this blog as it is not possible to write every example and scenario in the official documentation. I have tested the above example in-house and reached to this conclusion. There could be lot more errors that may get the transactions into doomed state! The user could refer to this blog as a reference to identify whether the error raised with severity\level 16 would roll back the transaction or not. If you wish you can also test errors with other severities or levels."

    - предполагаю, что в официальной документации действительно не описано, как однозначно классифицировать ошибки, приводящие к нефиксируемому состоянию и догадываться нужно самим. Печально)
    27 дек 17, 10:08    [21065645]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    а вот тут есть рекомендация ))
    если таки у вас есть блоки "важного кода" то оборачивать в try cath только их, а не ставить try cath в верхней процедуре и надеяться что всё "заработает" само )). И таки надо понимать что к чему в транзакциях. Это к тому что а может "важный код" вынести из транзакции - и целостность проверять ручками?))
    27 дек 17, 10:22    [21065689]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

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

    Честно говоря, не совсем Вас понял. Что понимается под "важным кодом" и о какой именно целостности идет речь?

    Признаюсь - с MSSQL работаю не так давно и мое представление о внутренностях транзакций в ней может быть неполным. И задача сейчас стоит по переносу одной разработки из другой большой СУБД в MSSQL. Конкретно - есть табличка с CSV-строчками, нужно эти CSV распарсить и положить в таблицу сущности. Если парсинг неудачен (данные на входе могут быть некорректны), то просто игнорировать эту строчку исходной таблицы с продолжением работы над остальными. Сделать все это надо с минимальным количеством транзакций (если строк исходной таблицы относительно немного - можно и одной обойтись). Приведу код (постараюсь его сократить максимально для более простого восприятия):

    create procedure PROCESS_DATA as
    begin
    	SET XACT_ABORT OFF;
    	
    	declare @data_string nvarchar(4000);
    	declare cur cursor local for select CSV_STRING from INPUT_DATA_TABLE;
    	
    	begin transaction;
    	open cur;
            begin try
    		fetch next from cur into @data_string;
    		while @@FETCH_STATUS = 0
    			begin
    				begin try
    					/* В хранимке, вызываемой ниже, сам парсинг и сохранение. Ошибка преобразования типов является нормальным поведением для нее */
    					exec PARSE_AND_SAVE_DATA @data_string;	
    				end try
    				begin catch
    					/* Здесь запись в лог возникшей ошибки обработки одной отдельно взятой записи. 
    					   Если попадаем сюда, то просто пишем лог и транзакцию нужно продолжить для других записей.  */  
    				end catch;
    
    				fetch next from cur into @data_string;
    			end;
    		commit;
    	end try
    	begin catch
    		if xact_state() = -1 rollback;
    		/*Здесь запись в лог информации об ошибке, но нужно как то добиться того, чтобы сюда никогда не попадало - это критическое нарушение процесса */
    	end catch
    	
    	close cur;
    	deallocate cur;
    	
    end;
    


    Какую часть здесь можно вынести из транзакции?
    27 дек 17, 11:05    [21065824]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Cammomile
    Member

    Откуда:
    Сообщений: 1214
    Ceib
    не могу найти четкого ответа - какие ошибки могут стать причиной перехода транзакции в состояние нефиксируемой

    Например вот такого рода.
     
    SET XACT_ABORT OFF
    SET ARITHABORT OFF
    
    BEGIN TRANSACTION
    BEGIN TRY
      DECLARE @zero float= 0
      SELECT LOG(@zero)
    END TRY
    BEGIN CATCH
    END CATCH
      SELECT  'xact_state = '+CAST(XACT_STATE() AS varchar)
    COMMIT
    
    27 дек 17, 11:22    [21065900]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Cammomile
    Member

    Откуда:
    Сообщений: 1214
    CREATE PROC #PPOC1
    AS
    BEGIN
    
    	BEGIN TRAN
    	BEGIN TRY
    	EXEC #PROC2
    	END TRY
    	BEGIN CATCH
    
    	SELECT 'xact_state = '+CAST(XACT_STATE() AS varchar)
    	END CATCH
    	COMMIT TRAN
    END
    
    GO
    
    CREATE PROC #PROC2
    AS
    BEGIN
    
    	BEGIN TRAN
    END
    
    GO
    
    EXEC #PPOC1
    
    27 дек 17, 11:33    [21065955]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Cammomile
    Member

    Откуда:
    Сообщений: 1214
    DECLARE 
       @date DATE
     , @string VARCHAR(5)= 'test'
          
    BEGIN TRAN
       BEGIN TRY
         SET @date = CAST(@String AS DATE)
       END TRY
       BEGIN CATCH
         SELECT 'xact_state = ' + CAST(XACT_STATE() AS varchar)
       END CATCH
    COMMIT 
    
    27 дек 17, 11:45    [21066036]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Cammomile
    Member

    Откуда:
    Сообщений: 1214
    Также нарушение констрейнта, и, кажется, отсутствие объекта во вложенной процедуре.
    Всё, память и фантазия иссякла.
    27 дек 17, 11:47    [21066045]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9301
    Ceib
    Если парсинг неудачен (данные на входе могут быть некорректны), то просто игнорировать эту строчку исходной таблицы с продолжением работы над остальными.
    Версия SQL Server какая?
    Если >= 2012. то см. функции try_parse, try_cast, try_convert.
    27 дек 17, 12:06    [21066136]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    Ceib,

    ну так вы и сами себе ответили ))
    А зачем в верхней проце запускаете транзакцию? ))
    try catch вообще то не связан с транзакцией и ничего не стоит запускать его вне транзакции ))
    а с учетом
    автор
    Ошибка преобразования типов является нормальным поведением для нее

    try catch надо ставить в нижней процедуре. Не зная полных условий задачи , не могу вам советовать с 100% достоверностью - но в вашей задаче транзакция вообще не нужна, а может вообще противопоказана )).
    27 дек 17, 12:07    [21066141]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

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

    Спасибо за подсказку, поставил try_cast'ы - для данной задачи это то, что нужно.


    dao,

    Транзакция нужна здесь не потому что логически нужно обрабатывать блок как единое целое, не для сохранения консистентности даже, а для оптимизации производительности (минимизации операций с диском). Вариант без оборачивания всего этого добра транзакцией дает достаточно большую нагрузку на диск, не говоря уже о, как следствие, значительно большем времени выполнения скрипта.
    27 дек 17, 12:57    [21066268]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    Ceib,

    Если таки вы знаете как может транзакция "минимизации операций с диском" может поделитесь знаниями? )
    27 дек 17, 13:37    [21066406]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9301
    dao
    Если таки вы знаете как может транзакция "минимизации операций с диском" может поделитесь знаниями? )
    Видимо имелся в виду режим autocommit с соответствующим сбросом буфера журнала на диск после каждой инструкции.
    27 дек 17, 14:06    [21066509]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

    Откуда:
    Сообщений: 80
    invm
    dao
    Если таки вы знаете как может транзакция "минимизации операций с диском" может поделитесь знаниями? )
    Видимо имелся в виду режим autocommit с соответствующим сбросом буфера журнала на диск после каждой инструкции.


    Режим автокоммита подразумевает неявный вызов commit'а после каждой операции изменения данных, если я правильно понимаю. Это равно явному открытию транзакции перед выполнением операции и после выполнения, то есть явному открытию транзакции на каждое изменение с явным подтверждением фиксации. Так что да, имел ввиду это. Ну а непосредственно в момент фиксации происходит запись на диск журналов. Описано здесь в секции Write-Ahead Transaction Log.
    27 дек 17, 14:22    [21066603]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    Ceib,

    уточню - т.е. у вас под
    автор
    exec PARSE_AND_SAVE_DATA @data_string;
    находится набор процедур которые парсят @data_string в разные таблицы ? или таки там парсится одна строка?
    27 дек 17, 14:38    [21066683]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    dao
    Member

    Откуда: Москва
    Сообщений: 771
    dao
    Ceib,

    уточню - т.е. у вас под
    автор
    exec PARSE_AND_SAVE_DATA @data_string;
    находится набор процедур которые парсят @data_string в разные таблицы ? или таки там парсится одна строка?
    и вставляется в одну строку одной таблицы?
    27 дек 17, 14:38    [21066690]     Ответить | Цитировать Сообщить модератору
     Re: uncommittable transaction  [new]
    Ceib
    Member

    Откуда:
    Сообщений: 80
    dao
    dao
    Ceib,

    уточню - т.е. у вас под пропущено...
    находится набор процедур которые парсят @data_string в разные таблицы ? или таки там парсится одна строка?
    и вставляется в одну строку одной таблицы?


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

    Примерно так:
    /*
    Здесь парсится строка в переменные:
    @var1 = 1-е вхождение
    @var2 = 2-е вхождение 
    @var3 = 3-е вхождение 
    и т.д.
    Для строки вида '1,2,asd' будет:
    @var1 = 1
    @var2 = 2
    @var3 = 'asd'
    */
    if @var1 = 1
    	insert into TABLE1(COL1, COL2) values(@var2, @var3)
    else if @var1 = 2
    	insert into TABLE2(COL1, COL2) values(@var2, @var3)
    
    27 дек 17, 14:50    [21066743]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить