SqlConnection与Sql Session。他们的一生重合吗？ [英] SqlConnection vs Sql Session. Do their lifetimes coincide?
问题描述
我想在我的 c＃应用程序中为某些进程应用一些sql-session级设置。
I want to apply some sql-session level settings for certain processes in my c# app.
例如，我想将某些后台进程的 DEADLOCK_PRIORITY 设置为 LOW 。
For instance, I want to set DEADLOCK_PRIORITY for some background processes to LOW.
问题是：
-
如果我打开新的sql连接，是否会启动新的sql-session ？
If I open a new sql connection, does that start a new sql-session?
在关闭连接之前，sql-session是否有效？如果我在打开 SqlConnection 之后立即应用设置，这些设置对在同一 SqlConnection ？
Will the sql-session live until the connection is closed? If I apply my settings right after the SqlConnection is opened, will they be valid for all queries executed in context of that same SqlConnection?
连接池如何？我的 SET DEADLOCK_PRIORITY LOW 设置是否可能被系统中的其他进程重用（我不想这样做），因为 SqlConnection 实际上并未关闭（ asp.net连接池决定重用它）。
What about connection pooling? Is this possible that my SET DEADLOCK_PRIORITY LOW setting will be reused by other processes in my system (which I don't want to) because the SqlConnection is not actually closed ( asp.net connection pooling decides to reuse it).
谢谢！
推荐答案
ADO.NET执行 sp_reset_connection 从池中获取 SqlConnection 时（关闭它以便将其返回到池中之后）。根据 exec sp_reset_connection是什么意思？ 重置所有 SET 选项意味着什么？这将包括 DEADLOCK_PRIORITY 。
ADO.NET executes sp_reset_connection when you take a SqlConnection from the pool (after having closed it so that it gets returned to the pool). According to What does "exec sp_reset_connection" mean in Sql Server Profiler? all SET options are being reset. That would include DEADLOCK_PRIORITY.
我仍然建议您编写一个微型测试程序来确认这一点。 ADO.NET会话池并不完美，例如，它不会重置 ISOLATION LEVEL ，并且在关闭时不会回滚事务。
I would still suggest that you write a tiny test program to confirm this. ADO.NET session pooling is not perfect, for example it does not reset the ISOLATION LEVEL and does not rollback transactions when closing.
这篇关于SqlConnection与Sql Session。他们的一生重合吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
