我真的需要在存储过程中使用事务吗？ [MSSQL 2005]
我正在 asp.net 中编写一个非常简单的电子商务应用程序，我是否需要在存储过程中使用事务？
读/写比例约为9:1
I'm writing a pretty straightforward e-commerce app in asp.net, do I need to use transactions in my stored procedures?
Read/Write ratio is about 9:1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
在以下情况下使用它们：
Use them if:
当然，这取决于情况。
这取决于特定存储过程执行的工作，也许不太取决于您建议的“读/写比率”。 一般来说，如果查询可能受到其他同时运行的查询的影响，则应考虑将工作单元包含在事务中。 如果这听起来不确定，确实如此。 通常很难预测在什么情况下特定的工作单元有资格作为候选者。
一个好的起点是查看单元内执行的精确 CRUD工作，在这种情况下是在您的存储过程中，并确定它是否a）可能受到其他一些同时操作的影响，b）其他工作是否对正在执行的这项工作的最终结果很重要（或者甚至反之亦然） ）。 如果这两个问题的答案都是“是”，那么请考虑将工作单元包装在事务中。
这表明您不能总是简单地决定使用或不使用事务，而是应该在有意义时应用它们。 使用 ACID 定义的属性（原子性、一致性、隔离性和持久性）来帮助决定何时情况可能是这样。
另一件需要考虑的事情是，在某些情况下，特别是如果系统必须快速连续执行许多操作（例如，大容量事务处理应用程序），您可能需要权衡事务的相对性能成本。 根据工作单元的大小，事务的提交（或回滚）可能会占用大量资源，可能会对系统性能产生不必要的负面影响，或者至少带来有限的好处。
不幸的是，这不是一个容易准确回答的问题：“这取决于情况。”
Of course, it depends.
It depends upon the work that the particular stored procedure performs and, perhaps, not so much the "read/write ratio" that you suggest. In general, you should consider enclosing a unit of work within a transaction if it is query that could be impacted by some other, simultaneously running query. If this sounds nondeterministic, it is. It is often difficult to predict under what circumstances a particular unit of work qualifies as a candidate for this.
A good place to start is to review the precise CRUD being performed within the unit of work, in this case within your stored procedure, and decide if it a) could be affected by some other, simultaneous operation and b) if that other work matters to the end result of this work being performed (or, even, vice versa). If the answer is "Yes" to both of these then consider wrapping the unit of work within a transaction.
What this is suggesting is that you can't always simply decide to either use or not use transactions, rather you should apply them when it makes sense. Use the properties defined by ACID (Atomicity, Consistency, Isolation, and Durability) to help decide when this might be the case.
One other thing to consider is that in some circumstances, particularly if the system must perform many operations in quick succession, e.g., a high-volume transaction processing application, you might need to weigh the relative performance cost of the transaction. Depending upon the size of the unit of work, a commit (or rollback) of a transaction can be resource expensive, perhaps negatively impacting the performance of your system unnecessarily or, at least, with limited benefit.
Unfortunately, this is not an easy question to precisely answer: "It depends."
请记住，在 SQL Server 中，默认情况下所有单语句 CRUD 操作都在隐式事务中。 如果需要使多个语句充当原子单元，则只需打开显式事务（BEGIN TRAN）即可。
Remember in SQL Server all single statement CRUD operations are in an implicit transaction by default. You just need to turn on explict transactions (BEGIN TRAN) if you need to make multiple statements act as an atomic unit.
答案是，这取决于情况。 您并不总是需要交易安全。 有时这是矫枉过正。 有时并非如此。
我可以看到，例如，当您实现结账流程时，您只想在收集所有数据后完成它，等等。想想支付失败，您可以回滚 - 这是您需要交易时的一个例子。 或者也许在明智的时候使用它们。
创建新用户帐户时需要进行交易吗？ 也许，如果它跨越 10 个表（无论出于何种原因），如果它只是一个表，那么可能不会。
这还取决于你向客户推销什么、他们是谁、他们是否提出要求等等。但如果决定取决于你，那么我会说，明智地选择。
我的底线是，避免过早优化。 构建您的应用程序，请记住，您可能希望稍后在需要时返回并重构/优化。 查看几个开源项目，看看他们如何实现应用程序的不同部分，从中学习。 您会发现他们中的大多数根本不使用交易，但有大量的在线商店使用它们。
The answer is, it depends. You do not always need transaction safety. Sometimes it's overkill. Sometimes it's not.
I can see that, for example, when you implement a checkout process you only want to finalize it once you gathered all data, etc.. Think about a payment f'up, you can rollback - that's an example when you need a transaction. Or maybe when it's wise to use them.
Do you need a transaction when you create a new user account? Maybe, if it's across 10 tables (for whatever reason), if it's just a single table then probably not.
It also depends on what you sold your client on and who they are, and if they requested it, etc.. But if making a decision is up to you, then I'd say, choose wisely.
My bottom line is, avoid premature optimization. Build your application, keep in mind that you may want to go back and refactor/optimize later when you need it. Look at a couple opensource projects and see how they implemented different parts of their app, learn from that. You'll see that most of them don't use transactions at all, yet there are huge online stores that use them.
很多人问——我需要交易吗？ 为什么我需要它们？ 何时使用它们？
答案很简单：一直使用它们，除非您有充分的理由不这样做（例如，不要将原子事务用于企业之间的“长时间运行的活动”）。 默认值应始终为“是”。 你有疑问吗？ - 使用交易。
为什么交易有利？ 它们帮助您处理崩溃、故障、数据一致性、错误处理，它们帮助您编写更简单的代码等。随着时间的推移，这些好处将继续增长。
以下是来自 http://blogs.msdn.com/florinlazar/ 的更多信息
Many people ask - do I need transactions? Why do I need them? When to use them?
The answer is simple: use them all the time, unless you have a very good reason not to (for instance, don't use atomic transactions for "long running activities" between businesses). The default should always be yes. You are in doubt? - use transactions.
Why are transactions beneficial? They help you deal with crashes, failures, data consistency, error handling, they help you write simpler code etc. And the list of benefits will continue to grow with time.
Here is some more info from http://blogs.msdn.com/florinlazar/