TransactionScope的帮手耗尽连接池没有失败 - 帮助？ [英] TransactionScope helper that exhausts connection pool without fail - help?
问题描述
前阵子我问了一个有关的TransactionScope升级到MSDTC的时候我没想到它的问题。 （<一href="http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines">$p$pvious问题）
什么是归结为是，在SQL2005，为了使用一个TransactionScope，你只能实例和TransactionScope的生活中开单的SqlConnection。与SQL2008，可以实例的多个SqlConnections，但只有一个人可以在任何特定时间打开的。 SQL2000总是会升级为DTC ......我们不支持SQL2000在我们的应用程序，WinForms应用程序，顺便说一句。
我们的解决方案，以单次连接，唯一的问题是创建一个TransactionScope辅助类，称为LocalTransactionScope（又名LTS）。它包装一个TransactionScope，最重要的是，建立并推广我们的应用程序维护一个单一的SqlConnection实例。好消息是，它的工作原理 - 我们可以用LTS跨不同张code，它们都加入了环境事务。很好。麻烦的是，每次创建将创建并有效杀灭从连接池中的连接的根的LTS实例。通过有效杀死'我的意思是它会实例SqlConnetion，这将打开的新的连接（不管是什么原因，它永远不会重用从池中的连接，），当这根LTS配置，它关闭并处置这是为了释放连接返回到池中，以便它可以重复使用，但是，它清楚地从未被重复使用的SqlConnection。游泳池涨大，直到被刷爆了，然后当建立一个最大池大小+ 1连接的应用程序失败。
下面，我已经把它贴的LTS code一个精简版，并且将展示连接池耗尽一个示例控制台应用程序类。为了看你的连接池膨胀，使用SQL Server枭雄Studio的活动监视器或此查询：
SELECT DB_NAME（DBID）为数据库名称'，
COUNT（DBID）作为连接
从具有sys.sysprocesses（NOLOCK）
WHERE DBID＆GT; 0
GROUP BY DBID
我在安装LTS在这里，你可以用它来证明自己，这会消耗从池中的连接，从来没有一个示例控制台应用程序重复使用，也没有释放他们。您将需要一个引用到System.Transactions.dll的LTS进行编译。
注意事项：这是根级LTS打开和关闭SqlConnection的，它总是会打开池中一个新的连接。有嵌套LTS情况下没有什么区别，因为只有root LTS实例建立一个SqlConnection。正如你所看到的，在连接字符串总是相同的，所以它的应的可重复使用的连接。
有一些神秘的条件，我们不是会导致无法重新使用的连接？有没有解决这个除了转向池完全关闭？
公共密封类LocalTransactionScope：IDisposable的
{
私有静态的SqlConnection _Connection;
私人的TransactionScope _TransactionScope;
私人布尔_IsNested;
公共LocalTransactionScope（字符串的connectionString）
{
//剥离出需要抛出一个异常，少数病例
_TransactionScope =新的TransactionScope（）;
//我们将使用这个后面的Dispose（...），以确定这是否LTS实例应该关闭连接。
_IsNested =（_Connection！= NULL）;
如果（_Connection == NULL）
{
_Connection =新的SqlConnection（的connectionString）;
//这code-奇臭。要打开您的连接尽可能晚并把它们打开尽可能少
//时间尽可能。然而，为了使用的TransactionScope与SQL2005你只能有一个单一的
//连接，并且它只能对整个的TransactionScope的范围内打开一次。如果你有
//多个SqlConnection的，或者你打开一个SqlConnection，关闭，并重新打开它，它不止一次，
//在的TransactionScope将升级到MSDTC。 SQL2008可以让你有一个在多个连接
//单一的TransactionScope，但你只能有一个单一，在任何给定的时间打开。
//最后，让我们不要忘了SQL2000。使用的TransactionScope使用SQL2000会立即始终升级为DTC。
//我们已经放弃支持SQL2000的，所以这不是一个问题，我们有。
_Connection.Open（）;
}
}
///＆LT;总结＆gt;完成对'的＆lt;看到CREF =TransactionScope的/＆GT;该＆lt;见CREF =LocalTransactionScope/＆GT;封装＆LT; /总结＆gt;
公共无效完成（）{_TransactionScope.Complete（）; }
///＆LT;总结＆gt;创建一个全新＆lt;见CREF =的SqlCommand/＆GT;从电流I看到的cref =SqlConnection的/＆GT;该＆lt;见CREF =LocalTransactionScope/＆GT;是管理和LT; /总结＆gt;
公众的SqlCommand CreateCommand（）{返回_Connection.CreateCommand（）; }
无效IDisposable.Dispose（）{this.Dispose（）; }
公共无效的Dispose（）
{
处置（真）; GC.Sup pressFinalize（本）;
}
私人无效的Dispose（BOOL处置）
{
如果（处置）
{
_TransactionScope.Dispose（）;
_TransactionScope = NULL;
如果（！_IsNested）
{
//最后一个出去关闭门，这将是根LTS中，第一个被实例化。
LocalTransactionScope._Connection.Close（）;
LocalTransactionScope._Connection.Dispose（）;
LocalTransactionScope._Connection = NULL;
}
}
}
}
这是一个Program.cs中，将展出连接池耗尽：
类节目
{
静态无效的主要（字串[] args）
{
//填写您的连接字符串，但不要猴子与任何池设置，比如
//池=假;或最大池大小的东西。如果你使用无所谓
//不，如果你使用Windows或SQL身份验证，只要确保你设置一个数据索里和一个初始目录无所谓
字符串的connectionString =此连接字符串;
名单＆LT;字符串＆GT; randomTables =新的名单，其中，串＆GT;（）;
使用（VAR nonLTSConnection =新的SqlConnection（的connectionString））
使用（VAR命令= nonLTSConnection.CreateCommand（））
{
command.CommandType = CommandType.Text;
command.CommandText = @SELECT [TABLE_NAME]，NEWID（）AS [ID]
FROM [INFORMATION_SCHEMA] .TABLES]
WHERE [TABLE_SCHEMA] ='DBO'和[TABLE_TYPE] ='基表
ORDER BY [ID];
nonLTSConnection.Open（）;
使用（VAR读卡器= Command.ExecuteReader却（））
{
而（reader.Read（））
{
字符串表=（字符串）阅读器[表格名];
randomTables.Add（表）;
如果（randomTables.Count＆GT; 200）{打破; } //得到了足够多的测试。
}
}
nonLTSConnection.Close（）;
}
//我们要假设你的数据库有一些表。
对于（INT J = 0; J＆LT; 200; J ++）
{
//在J = 100，你会看到它暂停，你会很快得到与文一个InvalidOperationException：
//超时过期。之前从池中获取连接超时时间已过。
//出现这种情况可能是因为所有池连接使用，最大池大小共识。
字符串tableName值= randomTables [J％randomTables.Count]。
Console.Write（创建根级别LTS+ j.ToString（）+ tableName值的选择）;
使用（VAR范围=新LocalTransactionScope（的connectionString））
使用（VAR命令= scope.CreateCommand（））
{
command.CommandType = CommandType.Text;
command.CommandText =SELECT TOP 20 * FROM [+ tableName值+];
使用（VAR读卡器= Command.ExecuteReader却（））
{
而（reader.Read（））
{
Console.Write（。）;
}
Console.Write（Environment.NewLine）;
}
}
Thread.sleep代码（50）;
scope.Complete（）;
}
Console.ReadKey（）;
}
}
预期的TransactionScope / SqlConnection的模式是，根据的 MSDN ：
使用（TransactionScope的范围= ...）{
使用（SqlConnection的康恩= ...）{
conn.Open（）;
SqlCommand.Execute（...）;
SqlCommand.Execute（...）;
}
scope.Complete（）;
}
因此，在MSDN的例子中conenction布置的在的范围内，的在的范围内就完成了。您的code虽然是不同的，其配置在</ em>的范围内完成连接的。我不在的TransactionScope及其相互作用与SqlConnection的（我知道的部分的事情，但你的问题已pretty的深），我找不到任何的规格有什么问题专家正确的模式。但我建议你重新审视你的code和处置单连接的最外层范围完成之前，类似于MSDN样本。
另外，我希望你实现你的code将土崩瓦解第二个线程来发挥到应用程序的那一刻。
A while back I asked a question about TransactionScope escalating to MSDTC when I wasn't expecting it to. (Previous question)
What it boiled down to was, in SQL2005, in order to use a TransactionScope, you can only instance and open a single SqlConnection within the life of the TransactionScope. With SQL2008, you can instance multiple SqlConnections, but only a single one can be open at any given time. SQL2000 will always escalate to DTC...we don't support SQL2000 in our application, a WinForms app, BTW.
Our solution to single-connection-only problem was to create a TransactionScope helper class, called LocalTransactionScope (aka 'LTS'). It wraps a TransactionScope and, most importantly, creates and maintains a single SqlConnection instance for our application. The good news is, it works - we can use LTS across disparate pieces of code and they all join the ambient transaction. Very nice. The trouble is, every root LTS instance created will create and effectively kill a connection from the connection pool. By 'Effectively Kill' I mean it will instance a SqlConnetion, which will open a new connection (for whatever reason, it never reuses a connection from the pool,) and when that root LTS is disposed, it closes and disposes the SqlConnection which is supposed to release the connection back to the pool so that it can be reused, however, it clearly never is reused. The pool bloats until it's maxed out, and then the application fails when a max-pool-size+1 connection is established.
Below I've attached a stripped down version of the LTS code and a sample console application class that will demonstrate the connection pool exhaustion. In order to watch your connection pool bloat, use SQL Server Managment Studio's 'Activity Monitor' or this query:
SELECT DB_NAME(dbid) as 'DB Name',
COUNT(dbid) as 'Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
I'm attaching LTS here, and a sample console application that you can use to demonstrate for yourself that it will consume connections from the pool and never re-use nor release them. You will need to add a reference to System.Transactions.dll for LTS to compile.
Things to note: It's the root-level LTS that opens and closes the SqlConnection, which always opens a new connection in the pool. Having nested LTS instances makes no difference because only the root LTS instance establishes a SqlConnection. As you can see, the connection string is always the same, so it should be reusing the connections.
Is there some arcane condition we're not meeting that causes the connections not to be re-used? Is there any solution to this other than turning pooling off entirely?
public sealed class LocalTransactionScope : IDisposable
{
private static SqlConnection _Connection;
private TransactionScope _TransactionScope;
private bool _IsNested;
public LocalTransactionScope(string connectionString)
{
// stripped out a few cases that need to throw an exception
_TransactionScope = new TransactionScope();
// we'll use this later in Dispose(...) to determine whether this LTS instance should close the connection.
_IsNested = (_Connection != null);
if (_Connection == null)
{
_Connection = new SqlConnection(connectionString);
// This Has Code-Stink. You want to open your connections as late as possible and hold them open for as little
// time as possible. However, in order to use TransactionScope with SQL2005 you can only have a single
// connection, and it can only be opened once within the scope of the entire TransactionScope. If you have
// more than one SqlConnection, or you open a SqlConnection, close it, and re-open it, it more than once,
// the TransactionScope will escalate to the MSDTC. SQL2008 allows you to have multiple connections within a
// single TransactionScope, however you can only have a single one open at any given time.
// Lastly, let's not forget about SQL2000. Using TransactionScope with SQL2000 will immediately and always escalate to DTC.
// We've dropped support of SQL2000, so that's not a concern we have.
_Connection.Open();
}
}
/// <summary>'Completes' the <see cref="TransactionScope"/> this <see cref="LocalTransactionScope"/> encapsulates.</summary>
public void Complete() { _TransactionScope.Complete(); }
/// <summary>Creates a new <see cref="SqlCommand"/> from the current <see cref="SqlConnection"/> this <see cref="LocalTransactionScope"/> is managing.</summary>
public SqlCommand CreateCommand() { return _Connection.CreateCommand(); }
void IDisposable.Dispose() { this.Dispose(); }
public void Dispose()
{
Dispose(true); GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if (disposing)
{
_TransactionScope.Dispose();
_TransactionScope = null;
if (!_IsNested)
{
// last one out closes the door, this would be the root LTS, the first one to be instanced.
LocalTransactionScope._Connection.Close();
LocalTransactionScope._Connection.Dispose();
LocalTransactionScope._Connection = null;
}
}
}
}
This is a Program.cs that will exhibit the connection pool exhaustion:
class Program
{
static void Main(string[] args)
{
// fill in your connection string, but don't monkey with any pooling settings, like
// "Pooling=false;" or the "Max Pool Size" stuff. Doesn't matter if you use
// Doesn't matter if you use Windows or SQL auth, just make sure you set a Data Soure and an Initial Catalog
string connectionString = "your connection string here";
List<string> randomTables = new List<string>();
using (var nonLTSConnection = new SqlConnection(connectionString))
using (var command = nonLTSConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT [TABLE_NAME], NEWID() AS [ID]
FROM [INFORMATION_SCHEMA].TABLES]
WHERE [TABLE_SCHEMA] = 'dbo' and [TABLE_TYPE] = 'BASE TABLE'
ORDER BY [ID]";
nonLTSConnection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string table = (string)reader["TABLE_NAME"];
randomTables.Add(table);
if (randomTables.Count > 200) { break; } // got more than enough to test.
}
}
nonLTSConnection.Close();
}
// we're going to assume your database had some tables.
for (int j = 0; j < 200; j++)
{
// At j = 100 you'll see it pause, and you'll shortly get an InvalidOperationException with the text of:
// "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
// This may have occurred because all pooled connections were in use and max pool size was reached."
string tableName = randomTables[j % randomTables.Count];
Console.Write("Creating root-level LTS " + j.ToString() + " selecting from " + tableName);
using (var scope = new LocalTransactionScope(connectionString))
using (var command = scope.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP 20 * FROM [" + tableName + "]";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.Write(".");
}
Console.Write(Environment.NewLine);
}
}
Thread.Sleep(50);
scope.Complete();
}
Console.ReadKey();
}
}
The expected TransactionScope/SqlConnection pattern is, according to MSDN:
using(TransactionScope scope = ...) {
using (SqlConnection conn = ...) {
conn.Open();
SqlCommand.Execute(...);
SqlCommand.Execute(...);
}
scope.Complete();
}
So in the MSDN example the conenction is disposed inside the scope, before the scope is complete. Your code though is different, it disposes the connection after the scope is complete. I'm not an expert in matters of TransactionScope and its interaction with the SqlConnection (I know some things, but your question goes pretty deep) and I can't find any specifications what is the correct pattern. But I'd suggest you revisit your code and dispose the singleton connection before the outermost scope is complete, similarly to the MSDN sample.
Also, I hope you do realize your code will fall apart the moment a second thread comes to play into your application.
这篇关于TransactionScope的帮手耗尽连接池没有失败 - 帮助？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
