存储过程中的事务+客户端代码
我有一个 SQL Server 存储过程，它创建一个如下所示的 TRANSACTION：
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Table1 ...
INSERT INTO Table2 ...
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
该存储过程将插入到两个单独的表中。如果其中之一失败，它将回滚。
我还有以下创建事务范围的 .net 代码：
using( var scope = new TransactionScope() )
{
SqlCommand cmd1 = connection.CreateCommand();
SqlCommand cmd2 = connection.CreateCommand();
// ...
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
scope.Complete();
}
如果我的存储过程和代码都在创建事务，会发生什么情况？这会导致问题/需要成为分布式事务吗？或者只要我只创建一个到数据库的连接就可以吗？
I have a SQL Server Stored Procedure that creates a TRANSACTION like this:
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Table1 ...
INSERT INTO Table2 ...
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
This stored procedure will insert into two separate tables. If one of them fails it will roll back.
I also have the following .net code that creates a transaction scope:
using( var scope = new TransactionScope() )
{
SqlCommand cmd1 = connection.CreateCommand();
SqlCommand cmd2 = connection.CreateCommand();
// ...
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
scope.Complete();
}
What happens if both my stored procedure and my code is creating a transaction? Will this cause problems / need to be a distributed transaction or is this okay as long as I only create one connection to the database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除非你有充分的理由，否则我只会在一处或另一处进行交易。
如果可能的话，我会让那个地方成为数据库。这减少了往返次数，更容易测试，将其与系统的其他组件隔离，减少暴露的数据库表面积，并通过强制操作通过明确定义的接口来保护数据库外围完整性。
Unless you had good reason, I would do the transactions in only one place or the other.
And if possible, I would have that place be the database. That reduces round trips, is more easily testable, isolates it from other components of the system, reduces exposed database surface area, and protects database perimeter integrity by forcing actions to go through a well-defined interface.
在这种情况下，如果您的存储过程之一失败，它将使用内部事务回滚。您的代码将认为它已完成并提交分布式事务。这是您想要的吗，因为分布式事务毫无意义，因为每个事务都将单独运行。
如果您希望两者都运行或都不运行，请从存储过程中删除事务并仅使用分布式事务。
In this scenario, if one of your stored procedure fails, it will roll back using the internal transaction. Your code will think it has completed and commit the distributed transaction. Is this what you want, as the distirbuted transaction is pointless as each will run individually.
If you want both to run or neither to run, remove your transaction from the stored procedue and just use the distributed transaction.