的TransactionScope和交易 [英] TransactionScope and Transactions
问题描述
在我使用的TransactionScope，因为有人告诉我，不要依赖我的SQL程序员总是会使用交易，我们是负责任的，内容十分重要我的C＃代码。
In my C# code I am using TransactionScope because I was told not to rely that my sql programmers will always use transactions and we are responsible and yada yada.
有说，
它看起来像TransactionScope的对象滚，之前的SqlTransaction回来吗？这是可能的，如果是这样什么是包裹在一个事务中一个TransactionScope正确的方法。
It looks like TransactionScope object Rolls back before the SqlTransaction? Is that possible and if so what is the correct methodology for wrapping a TransactionScope in a transaction.
下面是sql测试
CREATE PROC ThrowError
AS
BEGIN TRANSACTION --SqlTransaction
SELECT 1/0
IF @@ERROR<> 0
BEGIN
ROLLBACK TRANSACTION --SqlTransaction
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION --SqlTransaction
RETURN 0
END
go
DECLARE @RESULT INT
EXEC @RESULT = ThrowError
SELECT @RESULT
如果我运行此我以0得到公正的鸿沟，并返回-1
And if I run this I get just the divide by 0 and return -1
从C＃代码调用我得到一个额外的错误信息
Call from the C# code I get an extra error message
零错误遇到。结果$ b $鸿沟b 事务计数后EXECUTE表示COMMIT或ROLLBACK TRANSACTION tatement丢失。上一个计数= 1，当前计数= 0。
Divide by zero error encountered.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION tatement is missing. Previous count = 1, current count = 0.
如果我给SQL事务的名称，然后
If I give the sql transaction a name then
不能回退的SqlTransaction。
无交易或该名称的保存点被发现。
事务计数后EXECUTE表示COMMIT或ROLLBACK
TRANSACTION语句丢失。上一个计数= 1，当前计数= 2。
有时似乎次数上升，直到应用程序完全退出
some times it seems the count goes up, until the app completely exits
C＃的就是
using (TransactionScope scope = new TransactionScope())
{
... Execute Sql
scope.Commit()
}
编辑：
SQL代码已在2000年和2005年的工作。
The sql code has to work for 2000 and 2005
推荐答案
有是一个巨大的升级到错误的SQL Server 2005中操作这些文章是相当广泛：错误通过厄兰Sommarskog <后台 - 处理在SQL 2005，后来由厄兰Sommarskog 和错误SQL 2000中处理/ A>
There was a massive upgrade to the error handling within SQL Server 2005. These articles are fairly extensive: Error Handling in SQL 2005 and Later by Erland Sommarskog and Error Handling in SQL 2000 – a Background by Erland Sommarskog
最好的办法是这样的：
创建存储过程，如：
CREATE PROCEDURE YourProcedure
AS
BEGIN TRY
BEGIN TRANSACTION --SqlTransaction
DECLARE @ReturnValue int
SET @ReturnValue=NULL
IF (DAY(GETDATE())=1 --logical error
BEGIN
SET @ReturnValue=5
RAISERROR('Error, first day of the month!',16,1) --send control to the BEGIN CATCH block
END
SELECT 1/0 --actual hard error
COMMIT TRANSACTION --SqlTransaction
RETURN 0
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION --only rollback if a transaction is in progress
END
--will echo back the complete original error message to the caller
--comment out if not needed
DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
RETURN ISNULL(@ReturnValue,1)
END CATCH
GO
不过，这只是为SQL Server 2005及以上。如果没有在SQL Server 2005中使用在try-catch块，你有一个非常困难的时期删除所有的SQL Server发回的消息。在额外信息你指的是通过回滚如何使用@@ TRANCOUNT处理的性质造成的：
however that is only for SQL Server 2005 and up. Without using the TRY-CATCH blocks in SQL Server 2005, you have a very difficult time removing all of the messages that SQL Server sends back. The extra messages you refer to are caused by the nature of how rollbacks are handled using @@trancount:
这 http://www.sommarskog.se/error-handling-I.html#trancount
@@ TRANCOUNT是一个全局变量，
反映出嵌套
交易的水平。每个BEGIN TRANSACTION
增加@@ TRANCOUNT 1，每个
COMMIT TRANSACTION 1。没有减少
@@ TRANCOUNT实际上是
犯，直到@@ TRANCOUNT达到0
ROLLBACK TRANSACTION回滚
一切最外层的BEGIN
交易（除非你已经使用了
相当奇特的SAVE TRANSACTION）和
队@@ TRANCOUNT为0，问候的
之前的值。
@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.
当您退出存储过程，如果
@@ TRANCOUNT不具有相同的
值，因为它有当程序
开始执行，SQL Server将
错误266.不提高这个错误，
不过，如果该过程被称为
从触发器，直接或$ b $间接的b点。无论是B您与组隐
交易运行于
When you exit a stored procedure, if @@trancount does not have the same value as it had when the procedure commenced execution, SQL Server raises error 266. This error is not raised, though, if the procedure is called from a trigger, directly or indirectly. Neither is it raised if you are running with SET IMPLICIT TRANSACTIONS ON
如果你不想获取有关计算不匹配的交易警告时，您只需要在任何一次打开一个事务。您可以通过创建所有的程序是这样做的：
If you don't want to get the warning about the transaction count not matching, you need to only have one transaction open at any one time. You do this by creating all of your procedure like this:
CREATE PROC YourProcedure
AS
DECLARE @SelfTransaction char(1)
SET @SelfTransaction='N'
IF @@trancount=0
BEGIN
SET @SelfTransaction='Y'
BEGIN TRANSACTION --SqlTransaction
END
SELECT 1/0
IF @@ERROR<> 0
BEGIN
IF @SelfTransaction='Y'
BEGIN
ROLLBACK TRANSACTION --SqlTransaction
END
RETURN -1
END
ELSE
BEGIN
IF @SelfTransaction='Y'
BEGIN
COMMIT TRANSACTION --SqlTransaction
END
RETURN 0
END
GO
通过这样做，你只能发出，如果你是交易指令尚未在事务。如果你编写所有的程序这样，只有程序或发出BEGIN TRANSACTION将实际发出COMMIT / ROLLBACK和交易数量将总是匹配（你不会得到一个错误）。
By doing this, you only issue the transaction commands if you are not already in a transaction. If you code all of your procedures this way, only the procedure or the C# code that issues the BEGIN TRANSACTION will actually issue the COMMIT/ROLLBACK and the transaction counts will always match (you won't get an error).
在C＃从 TransactionScope类文档：
static public int CreateTransactionScope(
string connectString1, string connectString2,
string commandText1, string commandText2)
{
// Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
System.IO.StringWriter writer = new System.IO.StringWriter();
try
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open();
// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
// If you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources as connection2 is opened
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(connectString2))
{
// The transaction is escalated to a full distributed
// transaction when connection2 is opened.
connection2.Open();
// Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(commandText2, connection2);
returnValue = command2.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
}
}
// The Complete method commits the transaction. If an exception has been thrown,
// Complete is not called and the transaction is rolled back.
scope.Complete();
}
}
catch (TransactionAbortedException ex)
{
writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}
catch (ApplicationException ex)
{
writer.WriteLine("ApplicationException Message: {0}", ex.Message);
}
// Display messages.
Console.WriteLine(writer.ToString());
return returnValue;
}
只是一个想法，但你也许可以使用 TransactionAbortedException 抓来获得实际的错误，并忽略交易数量不匹配的警告。
Just a thought, but you might be able to use the TransactionAbortedException catch to get the actual error and ignore the transaction count mismatch warning.
这篇关于的TransactionScope和交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
