非常老的話題 SQLSERVER連接池

寫這篇文章不是說(shuō)要炒冷飯，因?yàn)閳@子里有非常非常多關(guān)于SQLSERVER連接池的文章，但是他們說(shuō)的都是引用MSDN里的解釋

或者自己做一些測(cè)試試驗(yàn)一下連接池的性能。但是對(duì)于SQLSERVER連接池的內(nèi)部機(jī)制，好像都沒(méi)有非常清晰地說(shuō)清楚。

連接池的作用就不說(shuō)了，在園子里隨便搜一大堆

還有使用連接池有利也有弊，大部分文章都說(shuō)連接池的好處沒(méi)有說(shuō)連接池的不好的地方

連接池不好的地方在于事務(wù)的提交方面，如果上一個(gè)連接沒(méi)有提交事務(wù)，有可能下一個(gè)

連接會(huì)遇到因?yàn)樯弦粋€(gè)連接的事務(wù)沒(méi)有提交從而遇到意想不到的后果

詳細(xì)的可以自行GOOGLE

先說(shuō)一下帶連接池功能的編程接口

微軟的SQLSERVER客戶端編程接口：

ADO和ADO.NET 都支持連接池這種機(jī)制

JAVA的JDBC也支持連接池這種機(jī)制

注意：連接池機(jī)制是客戶端數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序提供的，功能都在驅(qū)動(dòng)程序里，所以JDBC跟ADO,ADO.NET的連接池功能會(huì)有區(qū)別

因?yàn)榭蛻舳藨?yīng)用程序都是通過(guò)加載SQLSERVER的數(shù)據(jù)驅(qū)動(dòng)控件做SQLSERVER連接。目前客戶端數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序主要有3種：

1、MDAC(微軟數(shù)據(jù)訪問(wèn)組件) SQL2000的時(shí)候開始有的

2、SQLSERVER Native Client SQL2005開始引入的

3、Microsoft JDBC Provider ?使用機(jī)制與MDAC和SQLSERVER Native Client不同

下面再說(shuō)一下在論壇里經(jīng)常有人問(wèn)到的問(wèn)題：

問(wèn)題1：SQLServer連接池被創(chuàng)建在SQLClient端還是SQLServer端？

答案：在客戶端

當(dāng)應(yīng)用程序運(yùn)行的時(shí)候，會(huì)有一個(gè)連接池的管理控件運(yùn)行在應(yīng)用程序的進(jìn)程里，統(tǒng)一管理應(yīng)用程序和SQLSERVER建立的所有連接，

并且維護(hù)這些連接一直處于活動(dòng)狀態(tài)。當(dāng)有用戶發(fā)出一個(gè)connection open指令時(shí)連接池會(huì)在自己維護(hù)的連接池中找一個(gè)處于空閑狀態(tài)

的連接放回自己管理的連接池里，給這個(gè)用戶使用。當(dāng)用戶使用完畢后，發(fā)出connection close指令，連接池會(huì)把這個(gè)連接放回自己

管理的連接池里，讓他重新處于空閑狀態(tài)，而不是真的從SQL里登出。這樣如果下次有用戶需要相同連接，就可以重用這個(gè)連接，

而無(wú)須再去做物理連接了。就是說(shuō)連接池是放在客戶端的，是客戶端機(jī)制

問(wèn)題2：如果在一個(gè)應(yīng)用程序里設(shè)置連接池的大小為40000個(gè)，第二個(gè)應(yīng)用程序里設(shè)置連接池的大小也為40000個(gè)，

程序跟SQLSERVER會(huì)不會(huì)報(bào)錯(cuò)？

答案：不會(huì)

當(dāng)應(yīng)用程序運(yùn)行的時(shí)候，會(huì)有一個(gè)連接池的管理控件運(yùn)行在應(yīng)用程序的進(jìn)程里，統(tǒng)一管理應(yīng)用程序和SQLSERVER建立的所有連接，

并且維護(hù)這些連接一直處于活動(dòng)狀態(tài)。當(dāng)有用戶發(fā)出一個(gè)connection open指令時(shí)連接池會(huì)在自己維護(hù)的連接池中找一個(gè)處于空閑狀態(tài)

的連接放回自己管理的連接池里，給這個(gè)用戶使用。當(dāng)用戶使用完畢后，發(fā)出connection close指令，連接池會(huì)把這個(gè)連接放回自己

管理的連接池里，讓他重新處于空閑狀態(tài)，而不是真的從SQL里登出。這樣如果下次有用戶需要相同連接，就可以重用這個(gè)連接，

而無(wú)須再去做物理連接了

這個(gè)問(wèn)題是針對(duì)上面這段話的，如果一個(gè)應(yīng)用程序指定了連接池的大小為40000個(gè)那么跟SQLSERVER的連接已經(jīng)有40000個(gè)了，

SQLSERVER的最大連接數(shù)是32767，那么第二個(gè)應(yīng)用程序再打開40000個(gè)連接會(huì)不會(huì)報(bào)錯(cuò)呢？

本人根據(jù)園子里的這篇文章做了一個(gè)實(shí)驗(yàn)測(cè)試會(huì)不會(huì)報(bào)錯(cuò)，修改了他里面的一些代碼,下面是我自己寫的代碼跟報(bào)錯(cuò)的內(nèi)容跟文章鏈接

做實(shí)驗(yàn)之前記得重啟一下SQLSERVER

親測(cè)SQLServer的最大連接數(shù)

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Data;6 usingSystem.Data.SqlClient;7

8 namespaceSQLServerMaxConnectionTest9 {10 classProgram11 {12 static void Main(string[] args)13 {14 int maxCount = 40000;15 string connstr="Server=joe;Database=AdventureWorks;User ID=sa;Password=test;pooling=true;connection lifetime=0;min pool size = 1;max pool size=40000";16 List collection = new List();17 for (int i = 0; i < maxCount; i++)18 {19 Console.WriteLine(string.Format("成功創(chuàng)建連接對(duì)象{0}",i));20 try

21 {22 var db = newSqlConnection(connstr);23 db.Open();24 collection.Add(db);25 }26 catch(Exception ex)27 {28 WriteErrLog.AppendErrLog(ex.ToString());29 }30

31 }32 }33 }34 }

1 2012-12-01 17:04:22System.Data.SqlClient.SqlException: 當(dāng)前命令發(fā)生了嚴(yán)重錯(cuò)誤。應(yīng)放棄任何可能產(chǎn)生的結(jié)果。2 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)3 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)4 在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)5 在 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)6 在 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

由于本人的筆記本性能不給力，在cmd里面打開的對(duì)象數(shù)達(dá)到16531 另一個(gè)是16124的時(shí)候差不多死機(jī)了，所以未能截圖。

16531+16124 差不多達(dá)到32767，測(cè)試結(jié)果表明，如果有程序一早打開了一定數(shù)目的連接，那么第二個(gè)應(yīng)用程序就算

再打開40000個(gè)連接是沒(méi)有用的并會(huì)報(bào)錯(cuò)，第二個(gè)應(yīng)用程序能打開的連接數(shù)目等于SQLSERVER最大連接數(shù)減去第一個(gè)應(yīng)用程序

打開的連接數(shù)

有一個(gè)問(wèn)題非常奇怪：兩個(gè)應(yīng)用程序都可以指定連接池的最大連接數(shù)為40000，如果是這樣的話應(yīng)該SQLSERVER應(yīng)該預(yù)先

保持了80000個(gè)活動(dòng)連接，讓應(yīng)用程序連接進(jìn)來(lái)，這樣SQLSERVER應(yīng)該會(huì)報(bào)錯(cuò)，但是我用下面代碼測(cè)試了，發(fā)現(xiàn)沒(méi)有報(bào)錯(cuò)

測(cè)試方法跟上面的那個(gè)測(cè)試一樣，做下面實(shí)驗(yàn)之前請(qǐng)重啟一下SQLSERVER

這個(gè)問(wèn)題已經(jīng)有答案了，答案在文章的結(jié)尾o(∩_∩)o

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Data;6 usingSystem.Data.SqlClient;7

8 namespaceSQLServerMaxConnectionTest9 {10 classProgram11 {12 static void Main(string[] args)13 {14 //改成500個(gè)連接

15 int maxCount = 500;16 string connstr="Server=joe;Database=AdventureWorks;User ID=sa;Password=test;pooling=true;connection lifetime=0;min pool size = 1;max pool size=40000";17 List collection = new List();18 for (int i = 0; i < maxCount; i++)19 {20 Console.WriteLine(string.Format("成功創(chuàng)建連接對(duì)象{0}",i));21 try

22 {23 var db = newSqlConnection(connstr);24 db.Open();25 collection.Add(db);26 }27 catch(Exception ex)28 {29 WriteErrLog.AppendErrLog(ex.ToString());30 }31

32 }33 }34 }35 }

希望高人可以解答一下

問(wèn)題3：連接池字符串里要不要寫pooling=true;connection lifetime=0;min pool size = 1;？

答案：個(gè)人覺(jué)得沒(méi)有必要寫，連接池默認(rèn)是開啟的，連接的生命周期沒(méi)有必要指定，因?yàn)槟愣疾恢?/p>

自己的SQL語(yǔ)句要運(yùn)行多長(zhǎng)時(shí)間，連接池的min pool size不需要指定了，只需要指定max pool size=32767

就可以了

問(wèn)題4：sp_reset_connection 經(jīng)常有人在論壇里問(wèn)，使用SQL Trace的時(shí)候會(huì)有大量的sp_reset_connection

這個(gè)存儲(chǔ)過(guò)程的執(zhí)行，這個(gè)存儲(chǔ)過(guò)程到底是什么來(lái)的？

網(wǎng)上對(duì)這個(gè)存儲(chǔ)過(guò)程的資料很少，MSDN也找不到資料

答案：我們知道，每個(gè)連接都會(huì)維護(hù)自己的一些獨(dú)有資源，比如臨時(shí)表，變量，游標(biāo)等，

他也有可能會(huì)修改一些默認(rèn)的設(shè)置，例如事務(wù)隔離級(jí)別等。如果不同用戶先后使用同一個(gè)連接，

會(huì)不會(huì)前一個(gè)用戶設(shè)置的狀態(tài)或申請(qǐng)的資源影響后一個(gè)用戶正常使用呢？如果連接池的管理者

不做特殊處理，的確會(huì)有這種情況發(fā)生。所以微軟的連接池技術(shù)里包括ADO.NET和ADO，

引入了一個(gè)特殊的指令：sp_reset_connection，來(lái)清除前一個(gè)用戶做的絕大多數(shù)設(shè)置，避免這種問(wèn)題。

sp_reset_connection會(huì)在SQLSERVER里做些什么？

1、清除連接現(xiàn)有所有內(nèi)部數(shù)據(jù)結(jié)構(gòu)。包括：

(1)清除所有openxml打開的document句柄

(2)關(guān)閉所有的游標(biāo)(cursor)

(3)釋放所有SQL語(yǔ)句句柄

(4)清除所有臨時(shí)對(duì)象(臨時(shí)表等)

(5)釋放連接持有的所有鎖

(6)清除緩存的所有安全上下文信息(security context)

2、重置連接設(shè)置。包括：

(1)重置連接的SQL Trace標(biāo)志值(例如1204，1222，3604等)

(2)重置所有"SET" 選項(xiàng)值(SET IMPLICIT_TRANSACTIONS ON 等)

(3)重置連接的統(tǒng)計(jì)信息值

3、回滾所有SQLSERVER事務(wù)

需要說(shuō)明的是，如果連接當(dāng)前參與了一個(gè)由客戶端發(fā)起的分布式事務(wù)，這個(gè)分布式事務(wù)不會(huì)受到影響。

在SQL里的事務(wù)還會(huì)被保留

4、把當(dāng)前數(shù)據(jù)庫(kù)切換到用戶默認(rèn)數(shù)據(jù)庫(kù)

5、SQLSERVER會(huì)再次檢查當(dāng)前用戶是否有權(quán)做數(shù)據(jù)庫(kù)連接。如果這個(gè)權(quán)力已被移除

SQLSERVER會(huì)中斷這個(gè)物理連接(這樣防止一個(gè)已經(jīng)被取消訪問(wèn)權(quán)的用戶還能長(zhǎng)時(shí)間

使用數(shù)據(jù)庫(kù)的問(wèn)題)

完成這些事情以后，一個(gè)連接基本上已經(jīng)和他先前做的事情不再有任何關(guān)系(分布式事務(wù)除外)

可以說(shuō)，這個(gè)連接和一個(gè)新的連接已經(jīng)沒(méi)有什么大的區(qū)別了。通過(guò)這些，應(yīng)用程序的用戶

可以盡快地拿到和釋放連接，而SQLSERVER這邊也不會(huì)因?yàn)檫B接的重復(fù)使用而產(chǎn)生相互影響的問(wèn)題

準(zhǔn)備下班了 有問(wèn)題的童鞋可以發(fā)評(píng)論，希望大家拍磚o(∩_∩)o

剛才測(cè)試了一下，在SSMS里運(yùn)行下面語(yǔ)句

1 SELECT [program_name] ,[spid] FROM [sys].[sysprocesses] WHERE [spid]>50

然后運(yùn)行上面我給出的示例代碼程序跟SQLSERVER都在同一臺(tái)電腦，改為500個(gè)連接那個(gè)，我雙擊打開了很多個(gè)cmd程序，

發(fā)現(xiàn)在cmd程序運(yùn)行的時(shí)候會(huì)看到大量的連接在SSMS里面

當(dāng)那些cmd程序全部運(yùn)行完畢之后，這些進(jìn)程就在SQLSERVER里消失

那么上面的問(wèn)題：

兩個(gè)應(yīng)用程序都可以指定連接池的最大連接數(shù)為40000，如果是這樣的話應(yīng)該SQLSERVER應(yīng)該預(yù)先

保持了80000個(gè)活動(dòng)連接，讓應(yīng)用程序連接進(jìn)來(lái)，這樣SQLSERVER應(yīng)該會(huì)報(bào)錯(cuò)

答案：應(yīng)該是連接池保持了80000個(gè)活動(dòng)連接，SQLSERVER并沒(méi)有保持這些活動(dòng)連接

想一想這也是正確的，如果客戶端指定了80000個(gè)連接，SQLSERVER就要一次打開

80000個(gè)連接(實(shí)際數(shù)量是32767個(gè))并對(duì)這些連接進(jìn)行維護(hù)，那么SQLSERVER肯定

慢得要命。因?yàn)榭蛻舳说倪B接并沒(méi)有達(dá)到80000個(gè)那么多，你在連接字符串里指定80000個(gè)

連接，實(shí)際上只是指定連接池的連接數(shù)

客戶端的連接池和服務(wù)器端的線程池的區(qū)別

客戶端連接池即使是40000個(gè)，也只是一個(gè)設(shè)定值，并不是說(shuō)當(dāng)前程序打開了40000個(gè)長(zhǎng)連接

即使真的有40000個(gè)連接到SQL Server，SQL Server同一時(shí)刻也只能保持32767個(gè)長(zhǎng)連接

java：java語(yǔ)言制定了連接數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)JDBC，各種數(shù)據(jù)庫(kù)只需要按JDBC規(guī)范實(shí)現(xiàn)相應(yīng)的驅(qū)動(dòng)程序，JAVA程序通過(guò)JDBC API訪問(wèn)不同類型數(shù)據(jù)庫(kù)

pgsql的JDBC驅(qū)動(dòng)完全由java實(shí)現(xiàn)，沒(méi)有其他語(yǔ)言依賴庫(kù)，類似于python標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)接口為python DB-API

總結(jié)

以上是生活随笔為你收集整理的python sqlserver api连接池_非常老的话题 SQLSERVER连接池的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。