The only SET parameter that I have found specific guidance for is SET TRANSACTION ISOLATION LEVEL:
我找到的唯一SET参数是SET TRANSACTION ISOLATION LEVEL:SET TRANSACTION ISOLATION LEVEL:
If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.
如果在存储过程或触发器中发出SET TRANSACTION ISOLATION LEVEL,则当对象返回控件时,隔离级别将重置为调用对象时生效的级别。例如,如果批量设置REPEATABLE READ,然后批处理调用将隔离级别设置为SERIALIZABLE的存储过程,则当存储过程将控制权返回给批处理时,隔离级别设置将恢复为REPEATABLE READ。
What about other SET parameters, like SET XACT_ABORT or SET DEADLOCK_PRIORITY? If you set those during a stored procedure (or a batch submitted from C#), will they go back to their previous values the next time the connection gets used?
那些其他SET参数怎么样,比如SET XACT_ABORT或SET DEADLOCK_PRIORITY?如果您在存储过程(或从C#提交的批处理)中设置它们,它们是否会在下次使用连接时返回到先前的值?
2 个解决方案
#1
By default (that is, unless you include "Connection Reset=false" in the connection string), .NET resets the connection with the sp_reset_connection stored procedure before reusing it; among other things it undoes any SET commands that have been issued since the connection was opened.
默认情况下(即,除非在连接字符串中包含“Connection Reset = false”),.NET在重新使用之前会重置与sp_reset_connection存储过程的连接;除其他外,它撤消自打开连接以来发出的任何SET命令。
See this article for more details.
有关详细信息,请参阅此文章。
#2
I believe Transaction Isolation Level remains set for the rest of the current session. That means that even after such a stored proc returns, if client cide calls another stored proc on the same connection (in the same session) that second proc will be executed under the same isolation level.
我认为事务隔离级别仍然为当前会话的其余部分设置。这意味着即使在这样的存储过程返回之后,如果客户端cide在同一连接上(在同一会话中)调用另一个存储过程,则第二个proc将在相同的隔离级别下执行。
#1
By default (that is, unless you include "Connection Reset=false" in the connection string), .NET resets the connection with the sp_reset_connection stored procedure before reusing it; among other things it undoes any SET commands that have been issued since the connection was opened.
默认情况下(即,除非在连接字符串中包含“Connection Reset = false”),.NET在重新使用之前会重置与sp_reset_connection存储过程的连接;除其他外,它撤消自打开连接以来发出的任何SET命令。
See this article for more details.
有关详细信息,请参阅此文章。
#2
I believe Transaction Isolation Level remains set for the rest of the current session. That means that even after such a stored proc returns, if client cide calls another stored proc on the same connection (in the same session) that second proc will be executed under the same isolation level.
我认为事务隔离级别仍然为当前会话的其余部分设置。这意味着即使在这样的存储过程返回之后,如果客户端cide在同一连接上(在同一会话中)调用另一个存储过程,则第二个proc将在相同的隔离级别下执行。