Solve the Problem of incorrect loading of High-concurrency data in the examination system.

Solve the Problem of incorrect loading of High-concurrency data in the examination system.
Background:

Now this edition of the examination system is divided into student examination end and background management end. On the same logon page, students enter the student ID and password to go to the examination page. The Administrator enters the user name and password to go to the background management page. When no student exam is available, the school drop-down box on the background management interface can read and load data from the database correctly. However, when there is a student exam, the data obtained from the school drop-down box on the background management interface is sometimes blank, sometimes it is dirty data.

Our attempt: 1. Reproduce the incorrect data loading scenario

The LoadRunner stress test program is used to simulate the online examination process for 255 people. When the number of people in the examination reaches 50, the background management interface starts to show that the above data loading is incorrect.

2. verify whether the database has a deadlock

We released two identical test systems A, B, A, and B on the 207 server IIS to access the same database. We used LoadRunner to simulate the access of 255 people to system, when system A fails to load data correctly, use system B to log on to the backend for management. data can be correctly loaded every time it is refreshed. Therefore, it is concluded that the database is not a problem. Otherwise, data loading in system B is incorrect.

3. Verify that the virtual memory and dedicated memory allocated to the application by IIS are too small.

We use LoadRunner to simulate access to the examination system by 255 people. When the system fails to load data correctly, the examination system is recycled in the application pool of IIS, and the system data is loaded correctly, refresh all the time, and data cannot be loaded normally after a period of time. We set the virtual memory and dedicated memory of the examination system to the maximum value in the IIS application pool, and the data loading error still occurs when refreshing the page.

4. Optimize the SQLHelper class

Many connections, SqlCommand, SqlDataAdapter, and SqlDataReader are used in SQLHelper. They are originally executed in Using. To ensure that the above operations release the memory after execution, try, catch, and finally are added, disable SqlDataReader, SqlDataAdapter, SqlCommand, and Connection in finally, but the result data is still incorrectly loaded.

5. Add the MultipleActiveResultSets = true attribute to the database connection string.

Because an error message in the Yellow Pages is "There is already an open DataReader associated with this Command which must be closed first", I tried to add the MultipleActiveResultSets = true attribute in the database connection string, multiple database queries or stored procedures can be executed on a single connection, but the result data is still incorrectly loaded.

6. Change the maximum value of the database connection pool from 1200 to 500.

After completing the above five steps, the problem still persists, so I tried to change the idea to whether the database connection pool was too large and changed the maximum value from 1200 to 500. The problem was solved.

We can check the definition of the database connection pool in Baidu Encyclopedia: During database connection pool initialization, a certain number of database connections are created and placed in the connection pool, the number of connections to these databases is set by the minimum number of connections to the database. Whether or not these database connections are used, the connection pool will always have at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

Least connections: it is a database connection that has been maintained by the connection pool. Therefore, if the application uses little data for database connection, a large amount of database connection resources will be wasted;

Maximum number of connections: the maximum number of connections that can be applied by the connection pool. If the number of database connection requests exceeds this limit, the subsequent database connection requests will be added to the waiting queue, which will affect subsequent database operations.

The difference between the minimum number of connections and the maximum number of connections is too large: the first connection request will be profitable. The connection request that exceeds the minimum number of connections is equivalent to creating a new database connection. However, these database connections greater than the minimum number of connections will not be released immediately after use. They will be placed in the connection pool for reuse or released after idle timeout.

The original database connection pool is up to 1200 and the minimum is 300. When the system is initialized, 300 database connections are created. If the number of connections exceeds 300, a new connection is created. However, you have not used so many database connections during actual system operation, this results in a waste of database connections (a key and limited and expensive resource.

Although the problem is solved, there is still a question: "Why is the maximum value of the database connection pool too large, resulting in incorrect data loading "?

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.