SQL Server 如何处理存储过程中与事务相关的语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2622069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does SQL Server treat statements inside stored procedures with respect to transactions?
提问by Sleepless
Say I have a stored procedure consisting of several separate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic.
假设我有一个由几个单独的 SELECT、INSERT、UPDATE 和 DELETE 语句组成的存储过程。没有明确的 BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS 逻辑。
How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one transaction for the stored procedure?
SQL Server 将如何在事务方面处理这个存储过程？每个语句都会有隐含的联系吗？或者存储过程会有一个事务？
Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio?
另外，我如何使用 T-SQL 和/或 SQL Server Management Studio 自己发现这一点？
Thanks!
谢谢！
采纳答案by KM.
There will only be one connection, it is what is used to run the procedure, no matter how many SQL commands within the stored procedure.
无论存储过程中有多少 SQL 命令，都只有一个连接，它用于运行过程。
since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is any error.
由于您在存储过程中没有明确的 BEGIN TRANSACTION，因此每个语句都将自行运行，如果出现任何错误，则无法回滚任何更改。
However, if you before you call the stored procedure you issue a BEGIN TRANSACTION, then all statements are grouped within a transaction and can either be COMMITted or ROLLBACKed following stored procedure execution.
但是，如果您在调用存储过程之前发出 BEGIN TRANSACTION，则所有语句都在一个事务中分组，并且可以在存储过程执行后提交或回滚。
From within the stored procedure, you can determine if you are running within a transaction by checking the value of the system variable @@TRANCOUNT (Transact-SQL). A zero means there is no transaction, anything else shows how many nested level of transactions you are in. Depending on your sql server version you could use XACT_STATE (Transact-SQL)too.
在存储过程中，您可以通过检查系统变量@@TRANCOUNT (Transact-SQL)的值来确定您是否在事务中运行。零表示没有事务，其他任何内容都显示您所在的事务的嵌套级别。根据您的 sql server 版本，您也可以使用XACT_STATE (Transact-SQL)。
If you do the following:
如果您执行以下操作：
BEGIN TRANSACTION
EXEC my_stored_procedure_with_5_statements_inside @Parma1
COMMIT
everything within the procedure is covered by the transaction, all 6 statements (the EXEC is a statement covered by the transaction, 1+5=6). If you do this:
程序内的一切都被事务覆盖，全部6条语句（EXEC是事务覆盖的语句，1+5=6）。如果你这样做：
BEGIN TRANSACTION
EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1
COMMIT
everything within the two procedure calls are covered by the transaction, all 12 statements (the 2 EXECs are both statement covered by the transaction, 1+5+1+5=12).
两个过程调用中的所有内容都包含在事务中，所有 12 条语句（2 个 EXEC 都包含在事务中的语句，1+5+1+5=12）。
回答by MJB
You can find out on your own by creating a small stored procedure that does something simple, say insert a record into a test table. Then Begin Tran; run sp_test; rollback; Is the new record there? If so, then the SP ignores the outside transaction. If not, then the SP is just another statement executed inside the transaction (which I am pretty sure is the case).
您可以通过创建一个执行简单操作的小型存储过程（例如将记录插入测试表）来自行查找。然后开始Tran; 运行 sp_test; 回滚；有新记录吗？如果是，则 SP 忽略外部事务。如果不是，那么 SP 只是在事务中执行的另一个语句（我很确定就是这种情况）。
