В чем преимущество использования "SET XACT_ABORT ON" в хранимой процедуре?
В чем выгода использования SET XACT_ABORT ON в хранимой процедуре?
7 ответов
SET XACT_ABORT ON указывает SQL Server выполнить откат всей транзакции и прервать пакет при возникновении ошибки во время выполнения. Он охватывает вас в таких случаях, как тайм-аут команды, возникающий в клиентском приложении, а не в самом SQL Server (который не рассматривается по умолчанию). XACT_ABORT OFF установка.)
Поскольку тайм-аут запроса оставит транзакцию открытой, SET XACT_ABORT ON Рекомендуется во всех хранимых процедурах с явными транзакциями (если у вас нет особых причин делать это иначе), поскольку последствия выполнения приложением работы над соединением с открытой транзакцией являются катастрофическими.
В блоге Дэна Гузмана есть очень хороший обзор,
По моему мнению, SET XACT_ABORT ON был сделан устаревшим добавлением BEGIN TRY/BEGIN CATCH в SQL 2k5. До блоков исключений в Transact-SQL было действительно трудно обрабатывать ошибки, и несбалансированные процедуры были слишком распространены (процедуры, которые имели различный @@TRANCOUNT на выходе по сравнению с входом).
С добавлением обработки исключений в Transact-SQL стало намного проще писать правильные процедуры, которые гарантированно обеспечивают правильный баланс транзакций. Например, я использую этот шаблон для обработки исключений и вложенных транзакций:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
go
Это позволяет мне писать атомарные процедуры, которые откатывают только свою собственную работу в случае исправимых ошибок.
Одной из основных проблем, с которой сталкиваются процедуры Transact-SQL, является чистота данных: иногда полученные параметры или данные в таблицах просто неверны, что приводит к ошибкам дублирующихся ключей, ошибкам ограничения ссылок, ошибкам проверки ограничений и т. Д. И т. Д. В конце концов, именно в этом и заключается роль этих ограничений: если бы эти ошибки чистоты данных были бы невозможны и все они были пойманы бизнес-логикой, ограничения были бы все устаревшими (резкое преувеличение добавлено для эффекта). Если XACT_ABORT имеет значение ON, то все эти ошибки приводят к потере всей транзакции, в отличие от возможности кодировать блоки исключений, которые корректно обрабатывают исключение. Типичным примером является попытка сделать INSERT и возврат к UPDATE на нарушении PK.
Цитирование MSDN:
Когда SET XACT_ABORT установлен в ON, если инструкция Transact-SQL вызывает ошибку во время выполнения, вся транзакция завершается и откатывается. Когда SET XACT_ABORT имеет значение OFF, в некоторых случаях выполняется только откат оператора Transact-SQL, вызвавший ошибку, и транзакция продолжает обрабатываться.
На практике это означает, что некоторые операторы могут потерпеть неудачу, оставив транзакцию "частично завершенной", и для вызывающей стороны может не быть никаких признаков этого сбоя.
Простой пример:
INSERT INTO t1 VALUES (1/0)
INSERT INTO t2 VALUES (1/1)
SELECT 'Everything is fine'
Этот код будет выполнен "успешно" с выключенным XACT_ABORT и завершится с ошибкой с XACT_ABORT ON ("INSERT INTO t2" не будет выполнен, и клиентское приложение вызовет исключение).
В качестве более гибкого подхода вы можете проверять @@ERROR после каждого оператора (старая школа) или использовать блоки TRY...CATCH (MSSQL2005+). Лично я предпочитаю устанавливать XACT_ABORT ON всякий раз, когда нет причин для какой-либо сложной обработки ошибок.
Что касается тайм-аутов клиентов и использования XACT_ABORT для их обработки, по моему мнению, есть, по крайней мере, одна очень веская причина иметь тайм-ауты в клиентских API, таких как SqlClient, и это защищать код клиентского приложения от тупиков, возникающих в коде SQL-сервера. В этом случае в коде клиента нет ошибок, но он должен защитить себя от блокировки навсегда, ожидая завершения команды на сервере. И наоборот, если для защиты клиентского кода должны существовать тайм-ауты клиента, то XACT_ABORT ON должен защищать код сервера от аварий клиента, если выполнение кода сервера занимает больше времени, чем клиент готов ждать.
XACT_ABORT ON отслеживает это состояние транзакции. Если XACT_STATE =-1, то в транзакции произошла ошибка. Если XACT_STATE=1, то транзакция завершена. Если XACT_State=0, то открытой транзакции нет. XACT_ABORT указывает, будет ли выполняться автоматический откат текущей транзакции при возникновении ошибки.
Он используется в управлении транзакциями, чтобы гарантировать, что любые ошибки приведут к откату транзакции.
Добавление новых обновлений здесь. В последнем обновлении MSDN показано, как использовать блок XACT_ABORT ON и TRY/CATCH. Ссылка MSDN
-- Check to see whether this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
-- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO