Your application Program Will it sink or swim "? "Net connection pool lifeguard", the famous one on msdn Article I had read the program n times before, but I was glad that this was not the case in my program. In the system Code The design is coming to an end, and the customer training is coming to an end. When some departments are running, all the users and programmers will crash, and the program will be connected to a full error, and it happened very quickly.
Now I think of this Article. After troubleshooting, the problem finally came to an end.
The situation is as follows:
The first time a program designer developed a system using. net, he had different understandings of the system.
Various. The designed functions basically met the requirements and have already been used on site. At that time, the operating system Win2k and sqlserver databases were running normally. I think that the system can be basically stable, and then the server is upgraded, most departments start to use, the operating system is changed to win2003, an exception occurred. The connection is full. An error occurred while referencing null. According to the analysis, there are several possible problems: incorrect system code updates, incorrect operating system upgrade settings, and modules that were not used by Department users. Now code problems are exposed.
First, the code is restored to the previous update, and the result is still the same exception.
Then there is a server problem. Setting up the IIS connection pool, recycling time, and Web circles does not work.
Then I analyzed the code that was recently used by the system. As a result, there were many unreleased connections. This piece of code was written by a programmer and he was not there at the time. After troubleshooting, the following problems are found: Open the connection
SQL statement assignment
Sqlcommand cmd = new sqlcommand (cnstring, CN );
Sqldatareader DR = cmd. executereader (commandbehavior. closeconnection );
..
No close connection or datareader is found. However, according to Microsoft's description on msdn: The commandbehavior. closeconnection parameter is used as follows:
When the command is executed, the associated connection object is closed when the associated datareader object is closed.
That is to say, the commandbehavior. closeconnection parameter is used to automatically close the opened database connection after datareader is disabled.
William Vaughn said:
Loop the entire datareader result set to the end of its row set (that is, when dr. Read-datareader's read method-returns false) is not enough to trigger the connection to automatically close. However, if you bind to a complex binding control (for example, DataGrid), the control will disable datareader and connection-provided that you have set the commandbehavior. closeconnection option. We can see that if you do not close the connection or datareader,
Disable datareader. If commandbehavior. closeconnection is selected, even if datareader is disabled,
The connection will not be closed soon.
If commandbehavior. closeconnection is selected as the data source of the DataGrid, The DataGrid can automatically close the datareader and connection. After some modifications, the program was released, and the information was full waiting for the message. The result was no more than 10 minutes. The program encountered a long-standing problem and the connection pool was full. Fainted! In a single focus on the program, it seems that the program is no longer a big problem, look at other programs. Elsewhere, it was written by another programmer who accessed the database and used the data access application block. After the discovery just now, I began to wonder if the usage here was incorrect. Many of them were found to be
Sqldatareader DR = sqlherper. executereader (...);
But none of them are closed. It seems that they can be closed automatically. Let's check it out,
So in the while (dr. reder ()){}
Then, I will judge dr. isclosed, the returned value is false, indicating that it cannot be closed. Open the source code and he uses the commandbehavior. closeconnection, it seems that he also hopes to automatically close the connection after closing datareader. If the connection is not closed, it will not be leaked. If it is attached to the DataGrid, you don't have to close it. Why ??
The answer was found on csdn.
Http://blog.csdn.net/levin9/archive/2006/02/14/599115.aspx
The analysis is clear!
When datareader is used as the datasource of the DataGrid, datareader is treated as ienumerable. ienumerator can be obtained from ienumerable, and then every record can be obtained using its movenext/current, does movenext call the close method of datareader because commandbehavior is set. closeconnection. The connection is also closed when datareader is closed. Finally, I found this big bug! At the same time, I also used a few tools that I was too lazy to handle:
Application Center test, which is a stress test provided by Microsoft. It is quite refreshing, but it is not available on the XP system,
It may be a problem with system settings. I haven't found it yet. Then they are all people else. The control is not monitored. Where to monitor the connection pool counters, you must monitor the systems in which ADO. Net creates and adds these counters. If you connect from a remote system, ADO. Net does not always create a pool on Microsoft IIS server or SQL Server; it creates a pool on the system where ADO. Net code runs. This system can be a remote Windows or intermediate layer system that runs IIS, web applications, or web services. On the contrary, the SQL server performance counter is located on the SQL server system, rather than on the client. Use Performance Monitor to monitor the pool. If you use the Microsoft Management Console (MMC) Windows 2000 system monitor management unit, you can select ". net CLR data "to graphically represent sqlclient counters, as shown in figure 1. Note that you can select the _ global _ counter instance to monitor all processes, or you can view a specific instance-each pool generates its own set of monitors. Performance Monitor can list these counters and provide them as instances of the selected performance object. However, the performance monitor does not disclose these counters unless there are instances that require them to be monitored. For example, Figure 1 shows the. net clr data performance object, but does not list specific instances. This means that you must create at least one connection so that the _ global _ instance appears together with the specific instance of each process. This behavior is a problem for your code; you will not be able to use the performancecounter control to return any counters until ADO. Net creates these counters when a connection is opened. Therefore, this provision is a bit difficult. When you use this method, an exception is thrown because a valid counter instance is missing-capture exceptions must be prepared. In short, these errors seem to be a little weak, but there are a lot of problems he explained. If the preliminary design is good and the data access design is complete, it is a problem and it will soon be discovered. If multiple tests are performed, the attack will not occur in a centralized manner. Too many, if the boss can let you program according to the software project, that would be good, unfortunately!
