Solved

How do I write to a database in an Indy TIdTCPServer, TIdCommandHandler event without thread conflicts?

Posted on 2004-04-30
22
1,634 Views
Last Modified: 2012-06-22
Hi,

I've written a client server/database application that works beautifully
with a single client. Unfortunately, when another client connects, the
server freezes after a few requests. Here's how it's set up:

- Server event (Handler) fires when a client sends a command.
- A database connection is obtained from a pool of connections (done within
a critical section)
- The database is queried  <--- This is where the problem occurs!!!
- The database connection is released back to the pool of connections

The application gets stuck in "IdServerIOHandlerSocket", line 50:
  .
  .
    while not AThread.Stopped do begin
      if LIOHandler.Binding.Select(ASocket, 100) then begin  // Wait for 100
ms   <--- Gets stuck here!
        if LIOHandler.Binding.Accept(ASocket) then begin
  .
  .

If I remove the database query, everything works fine. If I wrap the entire
event in a critical section, everything is OK.

I know I must be doing something wrong here. Probably some basic thread
guideline that I'm not adhering to. My question is, how do people normally
write to databases within Indy events? Are there any example projects?
Please bear in mind that I am relatively new to Indy.

I'm using Delphi 7 Arch, Indy 9 and FIBPlus components connecting to a
Firebird 1.5 database.

If anybody thinks it will help, I may be able to send them some of the
source code.

Many Thanks
Paul Thornton
0
Comment
Question by:thornton_paul
  • 12
  • 10
22 Comments
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10957682
so you have multiple datasets in a pool, these datasets are connected to ONE (1) IBDatabase (or any other base db connection)
if that's the case then you must either provide a unique connection component for each thread or synchronise the calls
I've done both and both works just fine
I did the synching with SendMessage - like: SendMessage(Handle, WM_HANDLEDBSTUFF, DB_ACTION, Integer(DBObjectInfo));
don't know if it's a FIBPlus issue - I'm using dbExpress

when I used one connection for all datasets without synching I mostly got an "5 - Access denied" error
0
 

Author Comment

by:thornton_paul
ID: 10957830
I am using a DB Connection pool that uses multiple Connection objects. Each object has it's own database connection and dataset (query) components.

I think the problem lmay lie in the fact that I'm obtaining the connection from within the Indy event. Is this not possible due to the Indy's multiple threads? Do I have to use something like SendMessage? If so, could you give a quick example?
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10957991
something like:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, IdBaseComponent, IdComponent, IdTCPServer;

const
  WM_HANDLEDB = WM_USER + 123;

type
  TForm1 = class(TForm)
    IdTCPServer1: TIdTCPServer;
    procedure IdTCPServer1Execute(AThread: TIdPeerThread);
  private
    { Private declarations }
  protected
    procedure WMHandleDB(var AMsg: TMessage); message WM_HANDLEDB;
  public
    { Public declarations }
  end;

  PDBRequestInfo = ^TDBRequestInfo;
  TDBRequestInfo = record
    Request: Integer;
    Query: TIBQuery;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.IdTCPServer1Execute(AThread: TIdPeerThread);
var dbri: TDBRequestInfo;
begin
     // handle the data
     // now get a query
     dbri.Request:=1; // get query
     case SendMessage(Handle, WM_HANDLEDB, 1, Integer(@dbri)) of
       1: begin // if we got a query
         // use that query - in this connections thread
         dbri.Query.SQL:='select * from table';
       end;
       else
       begin
         // unknown result
       end;
     end;
end;

procedure TForm1.WMHandleDB(var AMsg: TMessage);
var dbri: TDBRequestInfo;
begin
     // forms thread - main thread
     case AMsg.WParam of
       1: begin
          dbri:=PDBRequestInfo(AMsg.LParam)^;
          // get our query from the pool
          dbri.Query:=QueryPool.GetQuery;
          // return message result
          AMsg.Result:=Integer(Assigned(dbri.Query));
       end;
     end;
end;

end.
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10958022
sorry .. missunderstood
when the Query is executed the problems arise
maybe it's a FIBPlus issue .. I didn't experience this with dbExpress
although I'm not using a db pool, I create a connection inside the calling thread (in an Intraweb app - UserSession)
0
 

Author Comment

by:thornton_paul
ID: 10958252
That's right, but I guess it could possibly be something to do with the way in which I retrieve the DB connection.

Can you explain what you actually gain by using a message instead of a simple function call? Sorry it it's a stupid question!
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10958378
if you call a method of an object that was created in another thread it's still executed in the caller thread
SendMessage call is handled by the receiving window in it's thread
the thread that calls SendMessage waits until the receiving window(thread) handles the message and returns
0
 

Author Comment

by:thornton_paul
ID: 10958549
My equivalent of your "WMHandleDB" procedure is this:

function TDBConnectionPool.GetConnection: TBaseDBConnection;
var
  i: Integer;
begin
  // Get an available connection
  // In a critical section so only one thread at a time can claim a connection.
  AcquireConnection.Acquire;
  try
    Result := nil;

    // Find a free connection
    for i := 0 to pred(FDBConnections.Count) do
      if not TBaseDBConnection(FDBConnections[i]).InUse then
      begin
        Result := TBaseDBConnection(FDBConnections[i]);
        Break;
      end;

    // If no free connections exist, create one
    if not Assigned(Result) then
    begin
      Result := CreateConnection;
      Result.FTempName := IntToStr(i);
      FDBConnections.Add(Result);
    end;

    Result.Acquire;

    // if we got a connection, make sure it's connected
    if Assigned(Result) then
      Result.Connect;
  finally
    AcquireConnection.Release;
  end;
end;

function TDBConnectionPool.CreateConnection: TBaseDBConnection;
begin
  Result := nil;

  // Create a new connection
  {$IFDEF FBCONNECTION}
    if FDatabaseType = dtFirebird then
      Result := TFirebirdDBConnection.Create;
  {$ENDIF}

  // Exception raised if no connection created
  if Assigned(Result) then
  begin
    Result.FDatabase := FDatabase;
    Result.FUsername := FUsername;
    Result.FPassword := FPassword;
  end
  else
    raise Exception.Create('Database Type not implemented');
end;

The DBConnectionPool component is created on the main form of the application. Would the critical section not be the equivalent of calling the method via a Message?
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10958804
>>Would the critical section not be the equivalent of calling the method via a Message?
nope, a CS merely locks other threads from executing that part of code
the message handler executes in the other thread and blocks the calling thread - and all others ofcourse

your problem is as I suspected - you have one Database
if you want to keep it like this you'll have to synch/protect all the calls to the DB
another option is to create a T(IB)Database for every pooled connection - I prefer this method - you limit the pool size anyway :-)
0
 

Author Comment

by:thornton_paul
ID: 10958842
I am creating a seperate database for every connection. Here's part of the code of a TBaseDBConnection object:

//----------------------------------------------------------------------------------
{ TFirebirdDBConnection }
//----------------------------------------------------------------------------------

constructor TFirebirdDBConnection.Create;
begin
  inherited;

  // Create the database components
  FDatabaseConnection := TpFIBDatabase.Create(nil);
  FTransaction := TpFIBTransaction.Create(nil);
  FQuery := TpFIBDataSet.Create(nil);
  FStoredProcedure := TpFIBStoredProc.Create(nil);
end;

procedure TFirebirdDBConnection.Connect;
begin
  if not FDatabaseConnection.Connected then
  begin
    // Set up the database component
    FDatabaseConnection.DatabaseName := Database;
    FDatabaseConnection.DBParams.Clear;
    FDatabaseConnection.DBParams.Add(format('user_name=%s', [FUsername]));
    FDatabaseConnection.DBParams.Add(format('password=%s', [FPassword]));
    FDatabaseConnection.SQLDialect := 3;

    // Set up the Transaction component
    FTransaction.DefaultDatabase := FDatabaseConnection;
    FDatabaseConnection.DefaultTransaction := FTransaction;

    // Set up the Query component
    FQuery.Database := FDatabaseConnection;
    FQuery.PrepareOptions := FQuery.PrepareOptions + [psAskRecordCount];

    // Set up the Stored Procedure component
    FStoredProcedure.Database := FDatabaseConnection;

    // Open the database connection
    FDatabaseConnection.Open;
  end;
end;

//----------------------------------------------------------------------------------

I can try rewriting the code to utilise Messages. I'll let you know outcome. Any other ideas before I start?
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10958992
using messages won't help in this case unless you handle the Query execution .. but you can do that with your existing lock and as you said it works
I don't have FIBPlus and can't check .. also don't have the time for that :-/
0
 

Author Comment

by:thornton_paul
ID: 10959125
How do you mean "handle the Query execution"?

Also, if I put the query in a critical section surely only one thread can execute it at a time. This kind of negates the point of using threads. I feel like I'm missing something here! What I'm looking to do is run concurrent database queries. i.e. one thread can be waiting for a long running query to finish while another does a query at the same time.

Also, how would I use the Message code if I'm running it in a TComponent that has no Handle property?

Thanks for your great help so far.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10960582
try that DB pooling with dbExpress or some other DB compos and see if they have the same problems
or .. create/destroy all db stuff inside the servers OnConnect/OnDisconnect methods
personaly I'd create a TIdPeerThread descendant with your TBaseDBConnection as a property
you set the servers ThreadClass:=TMyPeerThread;
then in the events you cast the AThread param to your thread class

if you have a stateless system then this might be a bit of an overhead


can I suggest you check out RemObject - www.remobjects.com
I've been using the V2 for over half a year now and it works great .. a real must have for any serious communication
V3 has lots of new goodies .. which I still have to check out :)


about messages
you create a window and a WndProc method like:

//create
var FHandle: Cardinal = AllocateHWnd(WndProc);
// free
DeallocateHWnd(FHandle);

procedure TMyClass.WndProc(var AMsg: TMessage);
begin
  case AMsg.Msg of
    ...
    else AMsg.Result:=DefWindowProc(FHandle, AMsg.Msg, AMsg.WParam, AMsg.LParam);
  end;
end;
0
 

Author Comment

by:thornton_paul
ID: 10971760
Hi Lee,

I've now done the following:

- Created a new TIdPeerThread descendant that contains a DB Connection and Query.
- Removed FIBPlus and replaced it with DBExpress.

Unfortunately, I am still experiencing exactly the same problem.

In order to try to sort this out, I've written a very small application that demonstrates the problem with all the clutter removed. It's only 500K including the executables. Would it be possible for you (or anybody else that wants the points) to download it and try to figure out where the problem lies. I'm pulling my hair out here!!! :o(

You can download the application source here: http:\\www.helion-studios.com\download\ThreadTest.zip

To compile the application you need Delphi 7 (Indy 9 and DBExpress). You will also need either Firebird or Interbase installed although it would be very simple to convert it to another database as it's now using DBExpress. There is a Firebird database included in the directory. It has one table: "ALERTS". The table contains one field: "ALERT_NAME" String(30). That's it. Just complile and run.

When compiled, start a single server application then start a single client application. In the client application, click "Connect" (it will try to connect to the server on the local [127.0.0.1] machine). Click "Continuous Send". You will see a lot of "Data Sent", "Data Received" messages scrolling up the memo. Now, try opening another client and do exactly the same thing. After a few moments both clients will lock up as they are waiting for a response from the server that never arrives. Why?

Thanks for your help with this very frustrating problem.

/Paul
0
 
LVL 12

Accepted Solution

by:
Lee_Nover earned 500 total points
ID: 10973399
it's probably a CommandHandler issue - I never use them anyway
but I have tried with them and it worked a few minutes until I had enough and finally 'Got it!' :D
note: I am using the _latest_ dev snapshot of Indy 9 .. maybe this is 9.0.14 known bug
get the newest dev snapshot and try it with that .. if it doesn't solve the problem then try with my method .. and if that fails .. then I'm out of ideas
I always create and send a base header (CommandHandlersEnabled = false !)
also the modified example runs just fine .. I had 4 clients running for 10 mins and it worked without a glitch .. for the db I used FB Embeded
here's the modified source

-----------------unit MsgHeader;

interface

type
  TMsgHdr = record // just a basic header example
    Command: Integer;
    Param: Integer;
    DataSize: Cardinal;
  end;

implementation

end.
-------------------------
// ofcourse MsgHeader in the uses clause
procedure TfrmThreadTestSrv.IdTCPServer1Execute(AThread: TIdPeerThread);
var mh: TMsgHdr;
    s: string;
begin
     AThread.Connection.ReadBuffer(mh, SizeOf(mh));
     case mh.Command of
       1: begin
         SetLength(s, mh.DataSize);
         AThread.Connection.ReadBuffer(s[1], mh.DataSize);
         TCCBC_ServerThread(AThread).AddAlert(Format('Alert! %d', [mh.Param]));
         AThread.Connection.WriteLn('Got it!');
       end;
     end;
end;
-----------------------------
procedure TfrmThreadTestClient.Button2Click(Sender: TObject);
var mh: TMsgHdr;
    s: string;
begin
  FCancelled := FALSE;
  While not FCancelled do
  begin
    memLog.Lines.Add('Sending Data...');
    //IdTCPClient1.WriteLn('010:Test');
    s:='Test';
    mh.Command:=1;
    Randomize;
    mh.Param:=Random(1000);
    mh.DataSize:=Length(s);
    IdTCPClient1.WriteBuffer(mh, SizeOf(mh));
    IdTCPClient1.WriteBuffer(s[1], mh.DataSize, true);
    memLog.Lines.Add(IdTCPClient1.ReadLn);
    Application.ProcessMessages;
    Sleep(100);
  end;
end;
-------------------------------


it is a bit more code but it's much more robust, not to mention the less overhead because of no string command handlers
0
 

Author Comment

by:thornton_paul
ID: 10973535
Don't have time to do anything now (23:50 here in Norway), but I'll take a look at work tomorrow.

Thanks for all your effort :o) Hopefully this will fix the problem.
0
 

Author Comment

by:thornton_paul
ID: 10975564
Hi Lee,

I've tried the code above (and turned off CommandHandlersEnabled). It certainly runs better. With two clients it runs for a long time, but with three or four it locks up pretty quick.

I'm still using the same version of Indy however. I'm going to try the latest version, but need the Delphi 7 CD to uninstall the old version. I'm having this delivered to me at 12 today. When I get it I'll try it with the latest version of Indy.

During the meanwhilst, would it be possible to email the executables to me at "norgepaul at hotmail dot com"? This way I can double check that the executable that work on your machine work on mine too.

Cheers
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10975577
sure, when I get home
0
 

Author Comment

by:thornton_paul
ID: 10976058
Here's something a little strange:

I tried running the sample application with Embedded Firebird and got the same results as you i.e. 4 clients running happily for as long as I left them!!!

Looks like this could be a Firebird C/S problem. Can't believe that somebody's not found it before now though.
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10976106
I thought of that as well but didn't test it with FB Server
try with new Indy and see if that makes a difference
maybe even try with Indy10, it's very stable now .. not to mention it performs 4x better (with SuperCore enabled) than Indy 9 ;-)
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10983872
here are the binaries using my method: http://files.delphi-si.com/lee/ThreadTestBin.zip
the db should be in the same dir
0
 

Author Comment

by:thornton_paul
ID: 10987461
Hi Lee,

Looks like the mystery is solved. The problem is to do with the way in which Frirebird connects to the database. If I use the connection string:

"c:\Database.fdb"

then the locking problem occurs. If however the string is:

"127.0.0.1:c:\Database.fdb"

the deadlock goes away. Apparently it is to do with a problem in Firebirds "Local" connectivity.

Thanks for all your help and patience. You certainly pointed me in the right direction and showed me a thing or two about threads.

You get the points. Go crazy :o)

Cheers,
Paul
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 10990007
!! I should've thought of that -- had a similar problem
yes .. using TCP/IP with the server solves that, otherwise it must be singlethreaded - that's even documented :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now