Delphi multi-threaded ADO programming
Preface:
Received a task a few months: Change a backend program to ADO from BDE in a way that a rear program is accessed by BDE, because the amount of traffic is written to the database or from the database through BDE It has become unbearable. Everyone knows that ADO is much more faster than BDE in database access (I wrote a test program using ADO, which is more than 100 times faster than using BDE!). This task is not simple, just replace the BDE control to ADO, then modify some code! I really thought about it, and I used it less than an hour to get it. I didn't have any problems in the test. I got it. I think. Who knows that a nightmare begins, my ignorance is ignorant, I have buried a super bomb in the program, and its power is not successful from the two passenger planes of the World Trade Center, and the whole system is ruthlessly ruthless. The program is running for a long time to capture a series of exceptions:
Ole Error 800A0E7F
Access Violation at Address 00135770. Write of Address 005D8B78
Access Violation At Address 00178EC6. Read of Address ffffffffff
Access Violation At Address 1F499BDD in Module 'Msado15.dll'. Read of Address 0000000c
.......
Next our system is like the World Trade Building is tragic.
why?
why? The program is running well in the use of BDE before the change, I didn't change the structure of the program? I confused, of course, I have to solve the problem everything starts with the error code.
Ole Error 800A0E7F: What is it? What does it mean? What causes it? I haven't found a description in my system, I am so developed in my system. Maybe someone will encounter the same problem as me, so I use Ole Error 800A0E7F as keywords, oh, fruit is really I found it:
> 0x800A0E7F Operation Cannot Be Performed While Executing> Asynchronously.
When the asynchronous execution is not executed (completed), or is not a reason why the error is not clear, so I released a post on a website, some people told me that the ADO thread is not safe, I need thread synchronization, in fact my program is synchronized And use multiple adoconnection for different applications, I think I should work hard to study this problem, it is very interesting. Next I should analyze my procedure and do a series of tests to find that bomb.
Find a bomb
All access databases in my program are completed through an interface provided by a DataModule unit TDataModule1 class. A total of three threads use TDATAMODULE1 object dataModule1. DataModule1 is a global variable, the following is a structural model of the access mode of the database. (The actual structure is more complicated)
database:
Adoconnection1
Adoconnection2
Table1
Table2
1AdoQuery1
2ADOQUERY3
1Adoprocedure11
1adoprocedure2
1UPDATEQUERY
3AdoQuery2
figure 1
Description:
1UPDATEQUERY
The AdoQuery control is used to modify the Table2 record, 1 representing thread 1, white representation frequency is very low (the color is deeper, the higher the frequency of use)
3AdoQuery2
Query Table2, 3 Represents All Thread 3, the frequency is high
2ADOQUERY3
Query Table2, 3 represents all, high use frequency
1Adoprocedure11
ADO stored procedure controls into table table2, belongs to thread 1 frequent use
1adoprocedure2
Modify the record of AdoProcedure1 insertion, belongs to thread 1 frequently
Among them, the thread 3 and thread 2 do not lock when using the ADO control, and all access to thread 1 is locked (so no effect)
The structure of the program came out, where is the problem? Next I wrote a small test program, the structure of the program is the same above, it has three threads and a DataMoule unit, thread a record of the Table1 of the database DBTEST by AdoQuery1, and the thread two inserted into Table1 through AdoQuery2. Record, thread three modify a field of the last record in Table1 through AdoQuery3. AdoQuery1, AdoQuery2, and AdoQuery3 are all established through AdoConnection1 and database dbtest1. At the beginning, all threads do not synchronize, run, ok! Errors come out, two errors are what I want, this is my program newspaper Wrong.
Figure II
Next, I will add the three adoqury, and there is no problem, I will connect AdoQuery to connect the database through three different adoConnections, and there is no problem without locking. It seems that I found the hateful bomb, how to remove it?
Exclude bombs
The bomb found, how should I remove it? Is it simple to do thread synchronization or every thread uses an adoConnection? I didn't dare to do it again, I have to take a good look at this area, in the Delphi help documentation, "Using the main vcl thread" I found the following paragraph:
......
Data access components are thread-safe as long as each thread has its own database session component. The one exception to this is when you are using Access drivers. Access drivers are built using the Microsoft ADO library, which is not thread-safe.
... ..
Also give me a clear suggestion in Delphi's help document "Managing Multiple Sessions":
......
If You Create a Single Application That Multiple Threads to Perform Database Operations, You Must Create ONE ADITIONAL SESSION for Each Thread.
... ..
Hey found: ADO controls are unsafe threads, so if your program uses multithreaded access to the database, you should make sure that each thread has its own session.
In fact, in another book "Delphi 4 programming technology insider" is talking about thread safety database access, but Mr. Li Wei, Taiwan in his "Delphi 5.x ADO / MTS / COM advanced programming "But if your program is not connected to multiple databases, it is best to use a connection with a connection, do not use multiple connections. How to do? Who is wrong? Why use a connection? This is mainly from the server, because the database server needs to assign a certain resource for each connection and maintain it, the more resources consumed by the server side, the more poor performance, so you must Minimize the number of clients as possible. Good in my program is that the server program adds some connections to the database server. Now I can reset my database access structure model database:
Adoconnection1
Adoconnection2
Table1
Table2
1AdoQuery1
2ADOQUERY3
1Adoprocedure11
1adoprocedure2
1UPDATEQUERY
3AdoQuery2
Adoconnection2
Figure three
I added an Adoconnection to ensure that each thread has a connection (session), so that there is a resource conflict, is my problem solved? Yes, this problem has been resolved, putting my procedure and database running on the same machine, but a new issue occurs when the program is not running on the same machine as the database server.
[DBNMPNTW] ConnectionWrite (WriteFile ()) error
This error is not a multi-threaded, but a problem with MicrSoft's own, which may be caused by the network abnormality, can be changed to TCP / IP Sockets by the default network protocol of the SQLServer client. For details, please refer to the Microsoft Knowledge Base Article - Q178040 "" Microsoft Knowledge Base Article - Q178040 "
to sum up
Due to the unsafeness of the ADO control (in fact this unsafe is from MicrSoft ADO Library, there is also the same problem in other development tools) Therefore, you should pay attention to the problem when using multithreaded ADO programming:
First: To ensure that each thread has its own session.
Second: As a client program, it should be reduced as much as possible to the number of connections to the database library server.
Third: Ensure all resources before exiting the thread.
references:
1, Li Wei "Delphi 5.x ADO / MTS / COM Advanced Programming Design" Machinery Industry Press 2000.
2, Charlie Calvert "Delphi 4 Programming Technology insider" Xiaoxiang Studio Translation Machinery Industry Press 1999.
