C＃与SQL事务 [英] C# vs. SQL transactions
问题描述
您好，
我们有一个MVC应用程序，可以为每个向控制器发出的请求启动交易：
Hello,
We have an MVC application that initiates transactions for every request made to the controllers:
public class TransactionPerRequest : IRunOnEachRequest, IRunOnError, IRunAfterEachRequest
{
...
/// <summary>
/// Runs at the begining of each Post request to setup
/// a Transaction wrapper
/// </summary>
void IRunOnEachRequest.Execute()
{
if (_httpContext.Request.HttpMethod == "POST")
{
_httpContext.Items["_Transaction"] =
_context.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
}
}
...
/// <summary>
/// Commits or rollsback the transaction at the end or each request dependant on the
/// <c>HttpContext</c> <c>_Error</c> item's value
/// This is only run for Post requests
/// </summary>
void IRunAfterEachRequest.Execute()
{
if (_httpContext.Request.HttpMethod == "POST")
{
var transaction = (DbContextTransaction) _httpContext.Items["_Transaction"];
var badRequest = (_httpContext.Response.StatusCode != (int)HttpStatusCode.OK &&
_httpContext.Response.StatusCode != (int)HttpStatusCode.Redirect &&
_httpContext.Response.StatusCode != (int)HttpStatusCode.MovedPermanently);
if (transaction?.UnderlyingTransaction?.Connection == null
|| transaction.UnderlyingTransaction.Connection.State != ConnectionState.Open)
{
Debug.WriteLine("Skipping transaction commit or rollback because the underlying connection is closed");
return;
}
if (_httpContext.Items["_Error"] != null || badRequest)
{
transaction.Rollback();
transaction.Dispose();
}
else
{
transaction.Commit();
transaction.Dispose();
}
}
}
}
我们还有一个包含在交易中的存储过程：
We also have a stored procedure wrapped in a transaction:
ALTER PROCEDURE [dbo].[DeleteProjectData]
@projectId INT,
@deleteType VARCHAR(10),
@username NVARCHAR(255)
AS
BEGIN
-- setup
BEGIN TRY
BEGIN TRANSACTION
-- Changes to database happen here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Exception thrown: ' + ERROR_MESSAGE()
PRINT 'Line number : ' + CAST(ERROR_LINE() AS VARCHAR)
IF(@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION
END
END CATCH
END
所以我的问题是：这是多余的吗？如果发出请求（从而在C＃代码中启动事务），并且处理该请求的一部分涉及调用存储过程，我是否需要这两个事务，或者C＃one是否足够？
我们从这些交易中获得的主要用途是在出现问题时自动回滚。因此，假设我们从存储过程中删除了BEGIN TRANSACTION和COMMIT TRANSACTION，并且在执行
存储过程的中间出现问题，C＃事务是否会回滚所有更改？
我猜测除非C＃代码以某种方式跟踪数据库更改，否则我们仍然需要将存储过程包装在事务中，以便在执行存储时出现任何问题程序，数据库可以处理
回滚。我是对的吗？
$
如果我是对的，那么C＃交易会处理什么样的回滚？我们使用实体框架。我们对实体所做的任何更改首先在实体框架缓存中生效，并且仅当我们调用context.SaveChanges（）时，这些更改才会将
提交到数据库。但是如果在调用context.SaveChanges（）之前出现问题，我猜测这就是C＃事务处理回滚的地方。也就是说，它会回滚对Entity Framework 缓存的更改。我是对的吗？
So my question is: is this redundant? If a request is made (thus initiating a transaction in the C# code), and part of processing that request involves calling the stored procedure, do I need both transactions, or will the C# one suffice?
The main use we're getting out of these transactions is automatic roll back in case something goes wrong. So suppose we removed the BEGIN TRANSACTION and COMMIT TRANSACTION from the stored procedure, and something went wrong in the middle of executing
the stored procedure, will the C# transaction roll back any changes?
I'm guessing that unless the C# code is somehow able to track database changes, we will still need stored procedures to be wrapped in transactions so that if anything goes wrong in the execution of the stored procedure, the database can handle the
roll back. Am I right?
If I'm right, what kind of roll backs will the C# transaction handle? We use Entity Framework. Any changes we make to the entities take effect first in the Entity Framework cache, and only if we call context.SaveChanges() will those changes be committed
to the database. But if something goes wrong before the call to context.SaveChanges(), I'm guessing this is where the C# transaction handles roll backs. That is to say, it rolls back changes to the Entity Framework cache. Am I right?
推荐答案
所以我的问题是：这是多余的吗？如果发出请求（从而在C＃代码中启动事务），并且处理该请求的一部分涉及调用存储过程，我是否需要这两个事务，或者C＃
一个就足够了吗？
这是多余的。事务提交或回滚可以在C＃代码或sproc中发生，但不能同时发生。
主要用途我们'如果出现问题，重新退出这些交易会自动回滚。因此，假设我们从存储过程中删除了BEGIN TRANSACTION和COMMIT TRANSACTION，并且在执行存储过程的中间
中出现问题，C＃事务是否会回滚任何更改？
在C＃代码中启动的事务范围与数据库引擎一起工作，以便在成功或失败时将记录提交到表或从事务范围内的表回滚记录。
The transaction scope initiated in the C# code works in conjunction with the database engine to either commit records to tables or rollback records from tables within the transaction scope, upon success or failure.
不，你不对。在C＃代码中启动或在sproc中启动的一个事务范围涵盖与数据库引擎一起工作的所有记录事务。
No you are not correct. One transaction scope either initiated in the C# code or initiated in the sproc covers all the record transactions with either one working in conjunction with the database engine.
如果我是对的，C＃事务会处理什么样的回滚？
无论事务范围在何处启动都是一样的C＃代码或代码。
It's the same no matter where the transaction scope is initiated C# code or sproc code.
但是如果在调用context.SaveChanges（）之前出现问题，我猜测这个是C＃事务处理回滚的地方。
不，这不正确。它只发生在数据库引擎的数据库级别，因为数据库引擎会持续更改数据库表或带有记录的表。
No, this is not correct. It only happens at the database level with the database engine as the database engine persists changes to database table or tables with records.
也就是说，它会将更改回滚到Entity Framework 缓存。我是对的吗？
不，这不正确。
我们有一个MVC应用程序，可以为每个向控制器发出的请求启动事务：
我也会说上面的内容对MVC UI设计有疑问模式，并且充其量，事务范围应该在模型中启动，模型与数据库一起执行CRUD，模型调用存储库来执行CRUD或
模型调用DAL来执行CRUD。
I would also say that the above is questionable concerning the MVC UI design pattern, and at best, the transactions scope should be initiated in the model with the model doing CRUD with the database, with model calling a repository to do the CRUD or the model calling the DAL to do the CRUD.
理想情况下，它不应该在控制器中徘徊，因为控制器的工作是流量控制。
Ideally, it should not be happing in the controller as the controller's job is flow control.
对我来说，它没有'无论是否使用MVC 5或Core关于MVC UI设计模式的有效使用。
To me, it doesn't matter if one is using MVC 5 or Core concerning the effective usage of the MVC UI design pattern.
https://www.c-sharpcorner.com/UploadFile/56fb14/理解 - 分离关注 - 和 - Asp-Net-mvc /
https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/
https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions -1 / overview / understanding-models-views-and-controllers-cs
https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs
< copied> < span style ="">
<copied>
MVC模型包含视图或控制器中未包含的所有应用程序逻辑。该模型应包含所有应用程序业务逻辑，验证逻辑和数据库访问逻辑。例如，如果您使用Microsoft
实体框架来访问您的数据库，那么您将在Models文件夹中创建您的Entity Framework类（.edmx文件）。
An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.
视图应仅包含与生成用户界面相关的逻辑。控制器应该只包含返回正确视图或将用户重定向到另一个操作所需的最小逻辑（流控制）。其他所有东西都应该包含在模型中的
。
A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.
一般来说，你应该努力寻找胖模型和瘦控制器。您的控制器方法应该只包含几行代码。如果控制器动作太胖，那么你应该考虑将逻辑移到Models文件夹中的新类。
In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.
< end>
<end>
这篇关于C＃与SQL事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
