Nested RecordSet and the port/socket in TIME_WAIT problem by example.
This is basically a continuation of the post on ports ending up in TIME_WAIT, here
In short, what happens is that when you are not using connection pooling, a connection that is closed indeed closed.
However, the port that the connection used is put into what is called TIME_WAIT in order to wait for potentially late or misrouted packages etc.
The default for the port to be in TIME_WAIT is 240 seconds (4 minutes), and during that time, the port is not available.
This means that a new connection can’t use this port during the TIME_WAIT state, and a new one will be opened.
If you are running your application on a Windows 2003 install, the ports that can be used are 1024 to 5000, which means that roughly 4000 ports available.
"TCP TIME-WAIT Delay"
https://msdn2.microsoft.com/en-us/library/ms819739.aspx
So what is the deal about this then you ask, well simply that if you, for example, open 4000 connections and close them, the code may complete in 15 seconds.
However, you will not be able to open another connection until all these ports have been released from TIME_WAIT, and this takes 4 minutes by default.
As mentioned in the other post, this typically does not happen on standalone clients (very uncommon for one client to need 4000 connection) but it happens on web applications.
For example, if you have a web site that connects to a database and you have 4000 employees, then potentially you will get errors when all employees access this site
at the same time, when arriving in the morning or coming back after lunch.
Now, in the last post I mentioned that enabling connection pooling will solve this, so why am I bringing this up again?
Well, if you are running, for example ASP/VB and ADO, then if you have nested Recordsets you may create a lot of connections without knowing it!
What is happening is that the provider is creating and opening a new connection ‘under the cover’.
This means that even if you open only one connection, then the nested query may open more connections.
In short, an outer RecordSet is using the connection, then when trying to use the connection for the inner RecordSet the connection is busy and
a new connection will be opened ‘under the cover’. This connection will not be sent to the connection pool and will therefore put a port in TIME_WAIT.
What this means is that you may think that you open only one connection but in reality you have opened a new connection for each iteration off the
outer loop. All this may seem more complicated than it is, so I have created a simple sample here. All you need is access to the Northwind database.
This as an .asp file, so just create a new .asp file, paste the code into it and run.
<html>
<body>
This will put a lot of ports in time-wait
<BR>
<%
Dim conn
Dim outerRs
Dim innerRs
set conn = server.createobject("ADODB.Connection")
set outerRs = server.createobject("ADODB.Recordset")
set innerRs = server.createobject("ADODB.Recordset")
conn.open "Provider=SQLOLEDB.1;Data Source=tcp:<your server>;Integrated Security=SSPI;Initial Catalog=Northwind"
outerSQL = "select top 10 * from Customers"
innerSQL = "select * from Orders where CustomerID = "
' Create the outer RecordSet
set outerRs = conn.execute(outerSQL)
do Until outerRs.EOF
response.write(outerRs.fields(0)) & " - " & response.write(outerRs.fields(1)) & "<BR>"
' Create the inner RecordSet, this will force the opening of a new, 'hidden/internal' connection
set innerRs = conn.execute(innerSQL & "'" & outerRs.fields(0) & "'")
do Until innerRs.EOF
response.write(innerRs.fields(0)) & " - " & response.write(innerRs.fields(1)) & "<BR>"
innerRs.MoveNext()
loop
outerRs.MoveNext()
loop
%>
</body>
</html>
How many connections are used here?
Well, you may get the impression that only one is used, but in fact, one connection is used by you, and 10 connections are used in the background.
And as mentioned, these connections do not get pooled and therefore are put into TIME_WAIT.
Open a command prompt and type "netstat -aon" (no quotes) and you will see a lot of the following:
…
TCP localip:port remoteip:1234 TIME_WAIT 0
TCP localip:port remoteip:1235 TIME_WAIT 0
TCP localip:port remoteip:1236 TIME_WAIT 0
…
Run the asp page, rerun “netstat -aon” and notice that you now have 10 more connections in TIME_WAIT.
So to conclude, imagine that you do not use the TOP 10 argument in the select above, this would return 100 customers, which in turns means 100 ports in TIME_WAIT.
Then imagine that 40 persons views the page at the same time and you have 4000 (40 persons * 100 connections) ports in TIME_WAIT and you will get time out errors.
There is no real solution to this but a recommendation is to narrow down the size of the outer recordset.
Or you could create a new connection object in the inside the loop that is sent to the pool.