SQL Server 如何处理存储过程中与事务相关的语句? [英] How does SQL Server treat statements inside stored procedures with respect to transactions?

问题描述

假设我有一个由几个单独的 SELECT、INSERT、UPDATE 和 DELETE 语句组成的存储过程.没有明确的 BEGIN TRANS/COMMIT TRANS/ROLLBACK TRANS 逻辑.

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.

SQL Server 将如何处理这个存储过程事务?每个语句都会有隐含的联系吗?还是存储过程只有一个事务?

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?

另外，我怎么能使用 T-SQL 和/或 SQL Server Management Studio 自己发现这一点?

Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio?

谢谢！

推荐答案

只会有一个连接，它是用来运行过程的，无论存储过程中有多少 SQL 命令.

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.

由于您在存储过程中没有明确的 BEGIN TRANSACTION，因此每个语句都将自行运行，如果出现任何错误，则无法回滚任何更改.

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.

在存储过程中，您可以通过检查系统变量 @@TRANCOUNT (Transact-SQL).零表示没有事务，其他任何内容都显示您处于事务的嵌套级别.根据您的 sql server 版本，您可以使用 XACT_STATE (Transact-SQL) 也是.

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.

如果您执行以下操作:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

程序内的一切都被事务覆盖，全部6条语句(EXEC是事务覆盖的语句，1+5=6).如果你这样做:

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:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

两个过程调用中的所有内容都包含在事务中，所有12条语句(2个EXEC都是事务覆盖的语句，1+5+1+5=12).

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).

这篇关于SQL Server 如何处理存储过程中与事务相关的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！