Explore the problem of an ASP that is suddenly inaccessible due to deadlock or process blocking

Many times, this can happen whenever I install a count system or a reverse link system on a dc9.cn. The phenomenon is their own space suddenly inaccessible, careful observation is only ASP can not access the static page but access, FTP access is also normal, looks like IIS suspended animation.

inquiry:

First I call the unlimited customer service, they are very good attitude, said that there may be a process blocking the server can not access. Sure enough, they helped me recycle the server resources to access it.

Research:

What is causing my server to suddenly deadlock or process blocking? I studied for a long time, and finally came to the conclusion that because of the large number of access to the statistical system, as well as the query to update the database too many people, and caused the ASP suddenly inaccessible.

So now the reason may be this:
Because each query updates the database to the resources requested by the system, the requested resource is not shut down or released in time to cause the process to block.

User Q accesses Table A (lock table a) and then accesses table B, at this point, User w Access table B (lock table B), and then attempt to access table A, then the user Q because the user W has been locked table b,q must wait for user W release table B to continue, the same user W to wait for user Q release Table A to continue, resulting in deadlock.

It's like I locked my house and ran to your house with my keys, and then you locked me in your house and tried to get into my house. So I can't get out, you can not go in, so I wait indefinitely.
or user A reads a record E, then modifies the record E, but there is no lock, and User B modifies the record e when it is easy to cause a deadlock. This is mainly because the database lock type improper setting, resulting in serious resource competition, resulting in deadlock.

Opinion

How to solve it?

1, I think is to be in the program to pay attention to habitual use xxx.close:set xxx=nothing such statements, timely release of server resources. At the beginning of my that SXNA1.3 did not release the server resources in time and caused a lot of servers appear suspended animation. Later the basic revision of the said.

2, I think it is not on the lock type of the database to try to use the adLockPessimistic pessimistic locking method, Because the default locking method for Conn.execute is adLockReadOnly, this can easily cause deadlocks when a large number of users access the query to update the database.

3, using MSMQ to control the ASP process, resolve the process deadlock problem, this network has a lot of tutorials, I will not repeat.
In short, this problem should be caused by bugs in the program.

My solution is the most enjoyable solution, do not use the station statistics, the use of outside the station statistics. Now the free flow statistics outside the station is also good, the reverse link can also see very clearly, but need to point to see it.