ADO.NET的SQLServer：如何prevent关闭持有S-DB锁连接？ [英] ADO.NET SQLServer: How to prevent closed connection from holding S-DB lock?

问题描述

我Dispose一个SqlConnection对象的，但当然它是不是真的闭 。我需要关闭的连接到坚持不住了数据库对象的锁。我怎样才能prevent关闭连接持有的锁？

---

以上森泰斯的说明对于那些谁不知道：

• 当您关闭ADO或ADO.NET连接，你实际上并没有切断连接到SQL Server。在ADO / ADO.NET的基础设施让你想再次使用它周围的情况下连接。该连接保持周围萦绕在什么是所谓的连接池。

• 不使用几分钟后，该连接会的实际上的关闭。虽然不是真的。 TCP / IP有它自己的方法保持TCP连接打开了几分钟（在 CLOSE_WAIT 的状态）。这样做的情况下，你应要求开一个TCP连接到同一个IP：再次端口。如果是这样，它可以使用一个已经打开的TCP连接。

• 使用连接池和SQL Server的连接仍然建立到SQL Server。每个连接有一个数据库上下文，它是坐在只要连接正坐在该数据库：它保持在该数据库共享数据库（S-DB）锁。

• 一个共享的数据库锁只是表示，不要删除此数据库，而我在里面请。

如何从上持有共享锁prevent它的我的数据库，同时保持连接池的好处是什么？

---

我的临时解决方案，现在的问题是每次叫处置开发商：

  connection.Dispose（）
 

它变成一个电话到全局辅助函数：

  Database.DisposeConnection（连接）;
 

这会改变数据库上下文为主

 公共静态无效DisposeConnection（SqlConnection的连接）
{
    至少在我的数据库 -  //停止拿着一个数据库锁
    ADOHelper.ExecuteNonQuery（连接，使用母）;

    connection.Dispose（）;
}
 

它解决了我的眼前的问题;关闭连接不上持有锁的我的数据库。

但我现在担心的是连接池将有它的大脑炒 - 因为我换数据库环境中落后于它的背

---

在万一有人不知道，或者不以为然：

从的SDK ：

  

关闭和废弃在功能   等价的。

解决方案

您可以将单用户模式的数据库，以恢复它。 IIRC，这样的事情...

 通过滚动即时ALTER DATABASE TheDatabaseToRestore SET SINGLE_USER;
RESTORE DATABASE TheDatabaseToRestore
FROM DISK = N'Z：\路径\到\备份\ BackupFile';
ALTER DATABASE TheDatabaseToRestore SET MULTI_USER;
 

请参见：<一href="http://www.sql-server-performance.com/articles/dba/Obtain_Exclusive_Access_to_Restore_SQL_Server_p2.aspx">Obtain独家访问还原SQL Server的和/或还原数据库备份的一些更多的背景。

编辑：的单用户模式用于备份和恢复（其中，在非前preSS版，是我用它定期）。用它踢了其他所有的连接和新的连接无法进行。我没玩过的各种WITH选项，如以ROLLBACK立竿见影，但它们的使用看起来非常简单。

i Dispose an SqlConnection object, but of of course it isn't really closed. i need closed connection to not hold locks on database objects. How can i prevent closed connections from holding locks?

---

Explanation of the above sentance for those who don't know:

• When you close an ADO or ADO.NET connection, you aren't actually severing the connection to SQL Server. The ADO/ADO.NET infrastructure keeps the connection around in case you want to use it again. The connections are kept lingering around in what's called "The Connection Pool".

• After a few minutes of not being used, the connection will be actually closed. Although, not really. TCP/IP has it's own method for keeping TCP connections open for a few more minutes (in the "CLOSE_WAIT" state). This is done in case you ask to open a TCP connection to the same IP:Port again. If so, it can use that already open TCP connection.

• With connection pooling and SQL Server, the connection is still established to SQL Server. Each connection has a database context that it is sitting in. As long as a connection is sitting in that database: it holds a shared database (S-DB) lock on that database.

• A Shared-Database lock simply means, "Don't delete this database while i'm in it please."

How can i prevent it from holding a shared lock on my database, while keeping the benefits of connection pooling?

---

My ad-hoc solution right now is every time a developer called Dispose:

connection.Dispose()

change it into a call to a global helper function:

Database.DisposeConnection(connection);

which changes the database context to master:

public static void DisposeConnection(SqlConnection connection)
{
    //Stop holding a database lock - in my database at least
    ADOHelper.ExecuteNonQuery(connection, "USE master");

    connection.Dispose();
}

It solves my immediate problem; closed connections aren't holding a lock on my database.

But now i'm worried that connection pooling will have its brain scrambled - because i switched database contexts behind its back.

---

In case anyone didn't know, or thought otherwise:

From the SDK:

Close and Dispose are functionally equivalent.

解决方案

You can place the database in single user mode in order to restore it. IIRC, something like this...

ALTER DATABASE TheDatabaseToRestore SET SINGLE_USER WITH  ROLLBACK IMMEDIATE;
RESTORE DATABASE TheDatabaseToRestore 
FROM DISK =N'Z:\Path\To\Backup\BackupFile';
ALTER DATABASE TheDatabaseToRestore SET MULTI_USER;

see: Obtain Exclusive Access to Restore SQL Server and/or Restore Database Backup using SQL for some more background.

Edit: Single user mode is intended for backups and restores (which, on the non-express edition, is what I use it for regularly). Using it kicks out all other connections, and new connections cannot be made. I haven't played with the various "WITH" options such as with "ROLLBACK IMMEDIATE", but their usage seems straightforward.

这篇关于ADO.NET的SQLServer：如何prevent关闭持有S-DB锁连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！