交易应在.NET或SQL Server处理？ [英] Transactions should be handled in .NET or SQL Server?

问题描述

我坐进这是使用.NET / C＃作为前端和SQL Server 2008作为后端的应用程序。我发现总是交易中的C＃代码处理。似乎它的这个项目，我们不应该存储过程中使用事务一个不成文的规定。

I got into an application which is using .NET/C# as front end and SQL Server 2008 as back end. I found that ALWAYS transactions are handled in the c# code. Seems its an unwritten rule for this project that we shouldn't use Transactions within stored procedure.

我个人觉得交易应当存储过程中进行处理，因为这将给予更多的控制代码！我们可能有很多的验证脚本而所有这一切，我们不需要一个打开的事务中发生的事情。我们需要打开之前我们做了插入/更新/删除，并会尽快将其关闭交易。

I personally feel that transactions should be handled within Stored Procedure as it would give more control over the code! We might have lot of validations happening within the script all this while we don't need a open transaction. We need to open a transaction just before we do a Insert/Update/Delete and can close it asap.

为了寻找答案，这将有助于我理解处理事务的最佳实践，当正是我们需要选择存储过程/ C＃中的事务。

Looking for answers which would help me understand the best practice for handling transactions and when exactly do we need to opt for Transactions within Stored Proc / C#.

推荐答案

有没有一个硬性的规定，但我看到几个原因，以控制从业务层的事务：

There isn't a hard and fast rule, but I see several reasons to control transactions from the business tier:

• 在整个数据存储的界限通信。交易数据不必是针对关系数据库;他们可以对各种实体。

• Communication across data store boundaries. Transactions don't have to be against a RDBMS; they can be against a variety of entities.

回滚的能力/提交基于业务逻辑的交易，可能无法提供给特定的存储过程，你是呼叫。

The ability to rollback/commit transactions based on business logic that may not be available to the particular stored procedure you are calling.

要在一个事务中调用查询的任意集的能力。这也无需担心交易计数

The ability to invoke an arbitrary set of queries within a single transaction. This also eliminates the need to worry about transaction count.

个人喜好：C＃有宣布交易更优雅的结构：使用块。相比之下，我总是发现交易在存储过程中被跳回滚/提交时比较麻烦。

Personal preference: c# has a more elegant structure for declaring transactions: a using block. By comparison, I've always found transactions inside stored procedures to be cumbersome when jumping to rollback/commit.

我们可能有很多的验证脚本中发生的这一切
，而我们并不需要一个打开的事务。我们需要打开一个交易
之前我们做了插入/更新/删除，并会尽快将其关闭。

We might have lot of validations happening within the script all this while we don't need a open transaction. We need to open a transaction just before we do a Insert/Update/Delete and can close it asap.

这或者可以根据被打开多少交易可能不会是一个问题（目前还不清楚这是否是一个单一的工作，或与高并发运行的程序）。我建议在看什么锁都被放置在对象，这些锁被关押多长时间。

This may or may not be a problem depending on how many transactions are being opened (it's not clear if this is a single job, or a procedure which is run with high concurrency). I would suggest looking at what locks are being placed on objects, and how long those locks are being held.

请记住，验证可能的应锁;如果你确认它的时间和动作出现？

Keep in mind that validation possibly should lock; what if the data changes between the time you validated it and the time the action occurs?

如果它的是有问题，你可以打破时间之间的数据变化违规的程序变成两个程序，并调用一个来自的TransactionScope 。

If it is a problem, you could break the offending procedure into two procedures, and call one from outside of a TransactionScope.

这篇关于交易应在.NET或SQL Server处理？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！