FCL-db: Problem with database library load/unload in a multithreading environment.
Original Reporter info from Mantis: zgabrovski
-
Reporter name: Zdravko Gabrovski
Original Reporter info from Mantis: zgabrovski
- Reporter name: Zdravko Gabrovski
Description:
The objectives are:
A have a linux daemon developed with Indy HTTP Server that will provide JSON format data extraction from the firebird database.
To implement this I have a OnCommandGet HTTP Server handler assigned. After a simple common parsing I am calling a method, that dynamically creates a connection to the FB Database, executes some set of SQL Quereis , formats the data as a json and returns it as a HTML context. Till now nothing complex looks like. It is developed in a following way:
- Create TIBConnection and assign database parameters to it.
- Create TIBTransaction and assign it to the already created connection. Set transaction isolation level to "read committed". Set TIBConnection Connected property to "true"
- Create TSQLQuery with database assigned to connection created
- Load SQL file and execute it, format the output as JSON
5.Shutdown the connection
6.Free all components (query, transaction,IBConnection).
But When I start performance tests with Apache benchmark in a multithreading environment, the daemon crashes.
After spending a few hours debugging, I found that it fails on step 5 described above, especially the command MainIBConnection.Close raises AV (200 requests,10 parallel threads in Apache benchmark).
How I fix it:
I construct a global critical section, and put the step #2 and step #5 under it.
Now it works without problem.
But for me there is some problem with this .Connected=False method in TIbConnection. It could not be a problem to close the connection in parallel threads. After this, next time when the thread tries to connect in step 2, I receive an exception "DoInternalConnect : Connection shutdown" and I can not connect to firebird anymore, until I restart a daemon.
I had similar problems with the same "DointernalConnect:Connection shutdown" before when I developed a dynamic link library that uses firebird connection. The library unloads fbclient.dll and after that in a host application I received this. But you told me to load a library manulay and the problem disappeared. But now how to do this - it is multhreading in one and the same daemon.
Here is my code:
procedure TdmInAction.ExecuteAction(DBParams: TStringlist;
VarCS: TCriticalSection; var Output: TStringArray);
var
Port, i, ii, c, stage: Integer;
SQLFiledata : TStringList;
SQLFileText : String;
SQLRequestArray: TStringArray;
MainIBConnection: TIBConnection;
qExecuteSelectCommand: TSQLQuery;
MainIBTransaction: TSQLTransaction;
trnExecuteSQLCommand: TSQLTransaction;
begin
//
try
try
SetLength( Output, 0 );
stage :=1;
SQLFiledata := TStringList.Create;
MainIBConnection := TIBConnection.Create( nil );
MainIBTransaction := TSQLTransaction.Create( nil );
MainIBTransaction.Params.AddCommaText('nowait,read_committed,rec_version,write');
MainIBTransaction.DataBase := MainIBconnection;
MainIBConnection.Transaction := MainIBTransaction;
qExecuteSelectCommand:= TSQLQuery.Create(nil);
trnExecuteSQLCommand := TSQLTransaction.Create( nil );
trnExecuteSQLCommand.Params.AddCommaText('nowait,read_committed,rec_version,write');
trnExecuteSQLCommand.DataBase := MainIBconnection;
qExecuteSelectCommand.DataBase := MainIBconnection;
qExecuteSelectCommand.Transaction := trnExecuteSQLCommand;
qExecuteSelectCommand.Options:=qExecuteSelectCommand.Options+[sqoKeepOpenOnCommit];
MainIBConnection.HostName := DBParams.Values['DBHost'];
MainIBConnection.DatabaseName := DBParams.Values['DBFile'];
MainIBConnection.UserName := DBParams.Values['DBUser'];
MainIBConnection.Password := DBParams.Values['DBPassword'];
Port := StrToIntDef( DBParams.Values['DBPort'], 3050 );
if Port = 0 then
Port := 3050;
stage := 2;
MainIBConnection.Port := Port;
DBCs.Acquire; // step 2 Without this code in red below causes "DoInterneal connect: Connection shutdown " when the code in step 5 below raises with AV
try
try
MainIBConnection.Connected := True;
qExecuteSelectCommand.Transaction := trnExecuteSQLCommand;
except
on e : Exception do
Raise Exception.Create('[MainIBConnection.Connected := True]' + E.Message);
end;
finally
DBCs.Release;
end;
stage :=3;
Varcs.Acquire;
try
SQLFiledata.LoadFromFile( DBParams.Values['SQLFile'] );
SQLFileText := SQLFiledata.Text;
finally
Varcs.Release;
end;
stage :=4;
SQLRequestArray := SQLFileText.Split( '#' );
ii := 1;
for SQLFileText in SQLRequestArray do begin
qExecuteSelectCommand.Close;
qExecuteSelectCommand.SQL.Text := SQLFileText;
for i := 0 to qExecuteSelectCommand.Params.Count - 1 do begin
if DBParams.Values[ qExecuteSelectCommand.Params[ i ].Name ] = '' then
Raise Exception.Create ('Parameter missing in the request #'+ii.ToString+'. Parameter name= ' + qExecuteSelectCommand.Params[ i ].Name );
end;
for i := 0 to qExecuteSelectCommand.Params.Count - 1 do begin
qExecuteSelectCommand.Params[ i ].Value :=
DBParams.Values[ qExecuteSelectCommand.Params[ i ].Name ] ;
end;
stage :=5;
try
if not trnExecuteSQLCommand.Active then
TSQLTransaction(trnExecuteSQLCommand).StartTransaction;
qExecuteSelectCommand.Open;
Output += [SaveCustomDataSetToXML( qExecuteSelectCommand )];
qExecuteSelectCommand.Close;
tsqlTransaction(qExecuteSelectCommand.Transaction).Commit;
except
on E : Exception do begin
Raise Exception.Create('SQL Query #'+ii.ToString +' raises exception '+E.Message);
end;
end;
inc( ii );
end;
stage :=6;
finally
try
if MainIBConnection.Connected then begin
try // step 5
dbcs.Acquire; //without this code in red nelow causes AV after some parallel threads
try
MainIBConnection.CloseDataSets;
except
on e: Exception do
Raise Exception.Create('[MainIBConnection.CloseDataSets] '+E.Message);
end;
try
MainIBConnection.CloseTransactions;
except
on e: Exception do
Raise Exception.Create('[MainIBConnection.CloseTransactions] '+E.Message);
end;
try
MainIBConnection.Close();
except
on e: Exception do
Raise Exception.Create('[MainIBConnection.Close()] '+E.Message);
end;
finally
dbcs.Release;
end;
end
else
Stage := 7;
try
qExecuteSelectCommand.Free;
except
on e: Exception do
Raise Exception.Create('[qExecuteSelectCommand.Free] '+E.Message);
end;
try
trnExecuteSQLCommand.Free;
except
on e: Exception do
Raise Exception.Create('[trnExecuteSQLCommand.Free] '+E.Message);
end;
try
MainIBConnection.Free;
except
on e: Exception do
Raise Exception.Create('[MainIBConnection.Free] '+E.Message);
end;
try
MainIBTransaction.Free;
except
on e: Exception do
Raise Exception.Create('[MainIBTransaction.Free] '+E.Message);
end;
finally
SQLFiledata.Free;
end;
end;
except
On E : Exception do
Raise Exception.Create('[TdmInAction.ExecuteAction ] Stage='+Stage.tostring+'# '+E.Message);
end;
end;
Now my code works, but may be will be better to fix it?
For me the problem becomes from library load/unload logic in Connection.open/Connection.close implementation.
I catch this with firebird, but may be this will affect also other database connections.
Mantis conversion info:
- Mantis ID: 39016
- OS: all
- OS Build: all
- Build: trunk
- Platform: all
- Version: 3.3.1