事务应该在存储过程外部还是内部指定？
我们可以将对存储过程的调用包装在事务中并指定隔离级别。
或者我们可以将事务放入存储过程中并在那里指定隔离级别。
最好做什么？
We can wrap a call to a stored procedure in a transaction and specify an isolation level.
Or we can put the transaction inside the stored procedure specify an isolation level there.
Which is it better to do?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我认为存储过程内部是最合适的位置。
良好事务设计的基本规则之一是使事务的生命周期尽可能短，因此提交应该在事务逻辑完成后立即发生。 在存储过程之外控制事务将导致不必要地延长事务的寿命。
您还应该考虑在过程中定义事务也将使您的代码更加清晰。 否则，如果另一个编码人员需要修改给定的存储过程，他们将不得不依赖调用者确实将该过程包装在事务中这一事实。 将事务包含在过程中明确定义了您的事务处理。
Inside the stored procedure is the most appropriate location in my opinion.
One of the fundamental rules of good transaction design is to keep the life of the transaction as short as possible and so the commit should occur immediately after the transaction logic has been completed. Controlling a transaction outside of the stored procedure will result in unnecessarily extending the life of the transaction.
You should also consider that defining the transaction within the procedure will also provide more clarity to your code. Otherwise, should another coder need to modify a given stored procedure, they would have to rely on the fact that the caller does indeed wrap the procedure in a transaction. Including the transaction within the procedure explicitly defines your transaction handling.
您应该采取一致的方法。 请注意，回滚存储过程中的事务将回滚任何嵌套事务范围，包括任何外部范围。
我建议您将交易保留在程序之外。 这样，您就可以保持完全控制。
You should adopt a consistent approach. Be aware that rolling back a transaction within a stored procedure will roll back any nesting transaction scope, including any outside scope.
I would advise you to keep your transactions outside the procedures. That way, you retain full control.
仅供参考，Oracle 不支持嵌套事务，如果您在外部级别开始事务，然后调用一系列存储过程，则任何发出提交的存储过程都将提交迄今为止的整个事务，而不仅仅是它发起的交易。 因此，当从 C# 等语言调用时，您必须在存储过程之外管理事务，
只是为了比较，您可能会感兴趣。
Just as an FYI, Oracle doesn't supported nested transactions, and if you begin a transaction at an outer level and then call a series of stored procedures, any stored-proc that issues a commit will commit the entire transaction so far, not just the transaction it instigated. Therefore you have to manage the transaction outside the stored-proc when calling from languages like C#
Just thought you might be interested, for comparison.
在数据库 API 外部，或者至少在数据库 API 的外层。
如果您在每个存储过程中提交，那么您最好打开自动提交，想象以下存储过程，
如果您在 create_user/create_email_address 中提交，则 create_user_with_email_address 不能再是事务性的，如果 create_email_address 失败，则 create_user 已提交，并且你的数据已损坏。
根据需要将交易放在尽可能高的位置，以将所有内容保留在其中。
Outside, or at least, in the outer layer of your database API.
If you commit inside every stored procedure, then you might as well have autocommit turned on, image the following stored procedures
if you commit within either create_user/create_email_address then create_user_with_email_address can no longer be transactional, if create_email_address fails, create_user has already been committed, and you have broken data.
Put the transaction as high up as needed to keep everything within it.
这取决于业务逻辑，如果 SP 是原子的，它应该实现自己的事务。 如果您不这样做，您将来将面临出现错误代码而不创建包装事务的风险。 所以在回答你的问题时，我认为交易应该进入 SP 内部。
当然，没有什么可以阻止您同时执行这两项操作，原子 SP 实现自己的事务，并且在该范围之外，其他更广泛的事务可能已经存在。
一般来说，当在 SP 中使用事务创建时，您可能已经在事务范围内，在执行提交/回滚时必须为此实例编写代码。
It depends on the business logic, if the SP is atomic it should implement its own transaction. If you don't do that you run the risk of errant code in the future not creating the wrapping transaction. so in answer to your question I think the transaction should go inside the SP.
Of course there's nothing to stop you doing both, atomic SPs implement their own transactions, and outside of that scope other broader transactions may already exist.
In general when creating using transactions within SPs you may already be within a transaction scope, you have to code for this instance when doing a Commit/Rollback.
我们在存储过程中执行以下操作，因为如果我们只是回滚，它会弄乱外部存储过程中的事务计数，这可能会向应用程序生成一个警告 - 如果它不期望/处理它可能会导致应用程序错误。
但是，此方法仅回滚“本地”事务，因此外部“调用者”必须适当地解释返回值； 或者使用 RAISERROR 或类似的。
We do the following, within the Sproc, because if we just rollback it mucks up the transaction count in the outer SProcs, which can generate a warning back to the application - and if it isn't expecting / handling it can cause an application error.
However, this method only rolls back the "local" transaction, so outer "callers" must interpret the Return Value appropriately; alternatively use a RAISERROR or similar.