在T-SQL中的catch块中回滚一个之前是否有计数事务? [英] Have I to count transactions before rollback one in catch block in T-SQL?
本文介绍了在T-SQL中的catch块中回滚一个之前是否有计数事务?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
BEGIN TRY
开始我的存储过程的最后一个块TRAN
- 我的代码
COMMIT
END TRY
BEGIN CATCH
IF(@@ trancount> 0)
BEGIN
ROLLBACK
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(),@state = ERROR_STATE()
RAISERROR(@message,11,@state)
END
END CATCH
是否可以切换 CATCH
-block to
BEGIN CATCH
ROLLBACK
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(),@state = ERROR_STATE()
RAISERROR(@message,11,@state)
END CATCH
或只是
code> BEGIN CATCH
ROLLBACK
END CATCH
? p>
解决方案
其实我从来没有开始一个新的交易,如果我已经在一个。
存储过程,分布式TXN和TransactionScope
请记住,有一个不存在SQL Server中的嵌套事务。
DECLARE @StartTranCount int
BEGIN TRY
SET @StartTranCount = @@ TRANCOUNT
IF @StartTranCount = 0 BEGIN TRAN
- 我的代码
如果@StartTranCount = 0 COMMIT TRAN
END TRY
BEGIN CATCH
IF @StartTranCount = 0 AND @@ trancount> 0
BEGIN
ROLLBACK TRAN
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(),@state = ERROR_STATE()
RAISERROR(@message,11,@state)
END
/ *
或
IF @StartTranCount = 0 AND @@ trancount
ROLLBACK TRAN
* /
END CATCH
I have next block in the end of each my stored procedure for SQL Server 2008
BEGIN TRY
BEGIN TRAN
-- my code
COMMIT
END TRY
BEGIN CATCH
IF (@@trancount > 0)
BEGIN
ROLLBACK
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
RAISERROR (@message, 11, @state)
END
END CATCH
Is it possible to switch CATCH
-block to
BEGIN CATCH
ROLLBACK
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
RAISERROR (@message, 11, @state)
END CATCH
or just
BEGIN CATCH
ROLLBACK
END CATCH
?
解决方案
Actually, I never start a new transaction if I'm already in one.
This deals with nested stored procs, distributed TXNs and TransactionScope
Remember, there is no such thing as a nested transaction in SQL Server anyway.
DECLARE @StartTranCount int
BEGIN TRY
SET @StartTranCount = @@TRANCOUNT
IF @StartTranCount = 0 BEGIN TRAN
-- my code
IF @StartTranCount = 0 COMMIT TRAN
END TRY
BEGIN CATCH
IF @StartTranCount = 0 AND @@trancount > 0
BEGIN
ROLLBACK TRAN
DECLARE @message NVARCHAR(MAX)
DECLARE @state INT
SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE()
RAISERROR (@message, 11, @state)
END
/*
or just
IF @StartTranCount = 0 AND @@trancount
ROLLBACK TRAN
*/
END CATCH
这篇关于在T-SQL中的catch块中回滚一个之前是否有计数事务?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文