Creating A Thread Safe Database Connection Pool for Delphi and Free Pascal based Lazarus

One day I got a software project to create an application that need to be accessed from many computer with one data server. I decided to create a three-tier application, a client application for the client computer and a middle application that connect directly to database server that is a Firebird Database Server for this project. After a few weeks the project is finished and I realized that the response time of every client request is very slow caused by database connection time. Because every time client request arrived the middle application create new thread to handle the request and create new database connection and doing database query and then send the respond data back to the requester client and finally close database connection and free database connection, transaction and queries objects before the thread is destroyed.

Then I begin researching to reduce the database connection time in the request handler thread. I become pesimistic after I read in delphi manual book that every thread must create and use its own database connection. But I'm still not giving up because other programming like PHP and MySQL can do that. Then I try to isolate every parts of the database connection and database query in part by part, and finally I'm able to reuse database connection, transcation and query object with many threads by isolating creation and desctruction of database connection object and transaction object and also commit and rollback process, everything else is no problem with multithread.

1. procedure TSimpleQuery.Commit;
2. begin
3.   if ((FQuery.Connection = nil) or not FQuery.Connection.Connected or
4.     FQuery.Connection.AutoCommit) then exit;
5.   EnterCriticalSection(FConnectionManager.GetCriticalSection^);
6.   try
7.     try
8.       FQuery.Connection.Commit;
9.     except
10.       LogExceptionMessage;
11.       raise;
12.     end;
13.   finally
14.     LeaveCriticalSection(FConnectionManager.GetCriticalSection^);
15.   end;
16.   ConnectionManager.SetAllowFailover(FQuery, True);
17. end;
18. 
    
19. procedure TSimpleQuery.Rollback;
20. begin
21.   if ((FQuery.Connection = nil) or not FQuery.Connection.Connected or
22.     FQuery.Connection.AutoCommit) then exit;
23.   EnterCriticalSection(FConnectionManager.GetCriticalSection^);
24.   try
25.     try
26.       FQuery.Connection.Rollback;
27.     except
28.       LogExceptionMessage;
29.       raise;
30.     end;
31.   finally
32.     LeaveCriticalSection(FConnectionManager.GetCriticalSection^);
33.   end;
34.   ConnectionManager.SetAllowFailover(FQuery, True);
35. end;

After succeeded creating reusable database connection object accross multiple threads then I create wrapper component for the database connection, transaction dan query object like this:

1.   ISimpleQuery = interface
2.     ['{1C1949E4-C472-45BA-8638-5A14545592F8}']
3.     function GetConnection: TZConnection;
4.     function GetConnectionManager: IConnectionManager;
5.     function GetParentQuery: ISimpleQuery;
6.     function GetQueryObject: TZReadOnlyQuery;
7.     function GetPrepared: Boolean;
8.     function GetRecordCount: Integer;
9.     function GetRowsAffected: Integer;
10.     function GetActive: Boolean;
11.     procedure SetActive(AValue: Boolean);
12.     function GetName: String;
13.     procedure SetName(const AValue: String);
14.     function GetFieldDefs: TFieldDefs;
15.     function GetFields: TFields;
16.     function GetSQL: TStrings;
17.     function GetBOF: Boolean;
18.     function GetEOF: Boolean;
19.     function GetParams: TParams;
20.     function GetParamCheck: Boolean;
21.     procedure SetParamCheck(Value: Boolean);
22.     function GetConnected: Boolean;
23.     function GetAutoCommit: Boolean;
24.     procedure SetAutoCommit(const Value: Boolean);
25.     function GetRecordAccess: TRecordAccess;
26.     function GetPost: ITransportVar;
27.     function GetWhere: ITransportVar;
28. 
    
29.     procedure Open;
30.     procedure OpenQuery(const ASQL: String);
31.     procedure ExecSQL;
32.     procedure ExecuteQuery(const ASQL: String);
33.     procedure Close;
34.     procedure First;
35.     procedure Next;
36.     procedure Prior;
37.     procedure Last;
38.     procedure Commit;
39.     procedure Rollback;
40.     procedure Prepare;
41.     procedure Unprepare;
42.     function CreateQuery: ISimpleQuery;
43.     function FieldByName(const AFieldName: String): TField;
44.     function FindField(const AFieldName: String): TField;
45.     function ParamByName(const AParamName: String): TParam;
46.     procedure Select(const SelectedCols: String);
47.     procedure Group(const GroupBy: String);
48.     procedure Order(const OrderBy: String);
49.     procedure Get(const Table: String);
50.     procedure Insert(const Table: String);
51.     procedure Update(const Table: String);
52.     procedure Delete(const Table: String);
53.     function GetSQLWhere(AWhere: ITransportVar): String;
54.     procedure AllowPost(const Columns: String);
55. 
    
56.     property ConnectionManager: IConnectionManager read GetConnectionManager;
57.     property ParentQuery: ISimpleQuery read GetParentQuery;
58.     property Active: Boolean read GetActive write SetActive;
59.     property Connection: TZConnection read GetConnection;
60.     property QueryObject: TZReadOnlyQuery read GetQueryObject;
61.     property FieldDefs: TFieldDefs read GetFieldDefs;
62.     property Fields: TFields read GetFields;
63.     property QueryFields[const AFieldName: String]: TField read FieldByName; default;
64.     property SQL: TStrings read GetSQL;
65.     property BOF: Boolean read GetBOF;
66.     property EOF: Boolean read GetEOF;
67.     property RowsAffected: Integer read GetRowsAffected;
68.     property RecordCount: Integer read GetRecordCount;
69.     property Prepared: Boolean read GetPrepared;
70.     property Params: TParams read GetParams;
71.     property ParamCheck: Boolean read GetParamCheck write SetParamCheck;
72.     property Connected: Boolean read GetConnected;
73.     property AutoCommit: Boolean read GetAutoCommit write SetAutoCommit;
74.     property Name: String read GetName write SetName;
75.     property RecordAccess: TRecordAccess read GetRecordAccess;
76.     property Post: ITransportVar read GetPost;
77.     property Where: ITransportVar read GetWhere;
78.   end;

I'm using reference counter in the Interface type to make coding faster by creating query interface object and after the interface object is unused then it's automatically freed and the database connection object, transaction object, the wrapped query object is deallocated and waiting for next use.

I develop two version one with ZeosDBO component and another one with SQLDb component  and both working and very stable.

After I upgraded my application with this database connection pool the client request handling is faster than before.

I'm sorry about my bad english this is the first time i write article in english because maybe alot programmer outside of my country require this knowledge.