mySQL multi threaded

Hi

I know there are probably more appropriate groups for this question, but
there is too much traffic over there.. ;-)
Maybe there are some database experts here.
I'm trying to use mySQL in a multithreaded App.
I'd like your comments on the code below, because I'm not an expert in this.
Is this the right way to do it?
It seems to work quite fine this way.
I've found that setting the NUM_SHARED_CONNECTIONS const to more than 1
results in SIGSEGV with multi-threading.
So dbExpress / mySQL is not thread safe, right?
Is there any better/faster way to do it?
Any potential problems?

Thanks
Theo




 ****************************************
**********************

unit uHarvLinDBConn;

interface

uses Classes, Sysutils,
DBXpress, FMTBcd, DB, SqlExpr,
MiniXMLTree, SyncObjs, LibC;

const NUM_SHARED_CONNECTIONS = 1;

const SS_FREE = -1;

type
TConnectionShare = class
private
fConnList: TList;
FSection: TRTLCriticalSection;
fAbort: boolean;
fCountConns: integer;
procedure Acquire;
procedure Release;
protected
procedure SQLConnection1Login(Database: TSQLConnection;
LoginParams: TStrings);
public
constructor Create(ConnName: string; NumConns: integer);
destructor Destroy; override;
function GetConnection: TSQLConnection;
procedure ReleaseConn(Conn: TSQLConnection);
property Abort: boolean read fAbort write fAbort;

end;

type
THarvDBConn = class(TObject)
private
fQuery: TSQLQuery;
public
constructor Create;
destructor Destroy; override;
function ExecSQL(SQL: string): string;
function OpenSQL(SQL: string; var Tree: TMiniXMLTree): string;
published

end;

var CS: TConnectionShare;

implementation


{  THarvDBConn }


constructor THarvDBConn.Create;
begin
fQuery := TSQLQuery.Create(nil);
end;


destructor THarvDBConn.Destroy;
begin
fQuery.free;
inherited;
end;

function THarvDBConn.ExecSQL(SQL: string): string;
begin
Result := '';
fQuery.SQLConnection := CS.GetConnection;
try
if not fQuery.SQLConnection.Connected then
fQuery.SQLConnection.Connected := true;
fQuery.SQL.Text := SQL;
try
fQuery.ExecSQL;
except
on E: Exception do Result := E.message;
end;
finally
fQuery.Close;
CS.ReleaseConn(fQuery.SQLConnection);
end;
end;

function THarvDBConn.OpenSQL(SQL: string; var Tree: TMiniXMLTree): string;
var i, k: integer;
nd: TMiniXMLNode;
begin
Result := '';
fQuery.SQLConnection := CS.GetConnection;
try
if not fQuery.SQLConnection.Connected then
fQuery.SQLConnection.Connected := true;
fQuery.SQL.Text := SQL;
try
fQuery.Open;
fQuery.First;
for i := 0 to fQuery.RecordCount - 1 do
begin
nd := Tree.Addnode(inttostr(i), '');
for k := 0 to fQuery.FieldCount - 1 do
nd.addNode(fQuery.Fields[k].FullName, fQuery.Fields[k].AsString);
fQuery.next;
end;
except
on E: Exception do Result := E.message;
end;
finally
fQuery.close;
CS.ReleaseConn(fQuery.SQLConnection);
end;
end;

{ TConnectionShare }


constructor TConnectionShare.Create(ConnName: string; NumConns: integer);
var i: integer;
tmpConn: TSQLConnection;
begin
fAbort := false;
InitializeCriticalSection(FSection);
fConnList := TList.create;
for i := 0 to NumConns - 1 do
begin
tmpConn := TSQLConnection.create(nil);
tmpConn.Tag := SS_FREE;

tmpConn.ConnectionName := ConnName;
tmpConn.DriverName := 'MySQL';
tmpConn.GetDriverFunc := 'getSQLDriverMYSQL';
tmpConn.LibraryName := 'libsqlmy.so';
tmpConn.VendorLib := 'libmysqlclient.so';

tmpConn.LoginPrompt := False;
tmpConn.LoadParamsOnConnect := false;
tmpConn.KeepConnection := true;

tmpConn.OnLogin := SQLConnection1Login;
tmpConn. LoadParamsFromIniFile(ExtractFilePath(Pa
ramStr(0)) +
'mySQL.conn');

tmpConn.Connected := true;
fConnList.Add(tmpConn);
end;
fCountConns := 0;

end;

procedure TConnectionShare.SQLConnection1Login(Database: TSQLConnection;
LoginParams: TStrings);
begin
LoginParams.Assign(Database.Params);
end;

destructor TConnectionShare.Destroy;
var i: integer;
begin
try
Acquire;
try
for i := 0 to fConnList.count - 1 do
begin
{$MESSAGE Warn'Potential Problem here when freeing conn in use by
another thread'}
TSQLConnection(fConnList[i]).Free;
end;
finally
Release;
end;
finally
fConnList.free;
DeleteCriticalSection(FSection);
end;
inherited;
end;

function TConnectionShare.GetConnection: TSQLConnection;
var i, tim: integer;
begin
Acquire;
try
tim := 0;
Result := nil;
while (tim < 24000) and not (fAbort) do //sleep 24000 x 5 ms = 120 sec
begin
inc(tim);
for i := 0 to fConnList.count - 1 do
if TSQLConnection(fConnList[i]).Tag = SS_FREE then
begin
//writeln('connnum '+inttostr(i));
Result := TSQLConnection(fConnList[i]);
Result.Tag := i; //Just for information
inc(FCountConns);
//writeln(fCountConns);
Exit;
end;
//writeln('sleepin');
sleep(5);
end;
//writeln('GetConn timeout');
finally
Release;
end;
end;


procedure TConnectionShare.Acquire;
begin
EnterCriticalSection(FSection);
end;

procedure TConnectionShare.Release;
begin
LeaveCriticalSection(FSection);
end;

procedure TConnectionShare.ReleaseConn(Conn: TSQLConnection);
begin
if Assigned(Conn) then Conn.Tag := SS_FREE;
end;

initialization
CS := TConnectionShare.Create('MySQLConnection', NUM_SHARED_CONNECTIONS);

finalization
CS.free;

end.

Hi Theo,

I have not studied your code, but..

For my experience and in general running database queries in threads you
need to derive your thread class from TThread and each instance needs a
unique connection to the database.

unless you are trying something else in which case ignore..

siegs

Xref: number1.nntp.dca.giganews.com borland.public.kylix.non-technical:13702

Hi siegs

>
> For my experience and in general running database queries in threads you
> need to derive your thread class from TThread and each instance needs a
> unique connection to the database.
>

Thanks for your answer.
Of course I'm doing this, (although the thread-part is not shown in the
sample-code), but found that multiple connections (from multiple
threads) result in SIGSEGV with dbExpress / mySQL.
So it's not thread safe in my observation.


Thanks
Theo

> Thanks for your answer.
> Of course I'm doing this, (although the thread-part is not shown in the
> sample-code), but found that multiple connections (from multiple
> threads) result in SIGSEGV with dbExpress / mySQL.
> So it's not thread safe in my observation.
>
>
> Thanks
> Theo

theo,

I've had same situation , but with postgres. I'm using zeoslib and it worked
for me, but there is a trick , each query must have it's own connection.My
app work with max. 8 connections at the same time, so don't know about eg.
20 threads.

cheers

If you want i can send you the linux version of dbx4mysql ... as far as i'm
aware it is thread safe but to be honest i've never tested it on linux.

Cristian Nicola

"theo" <nospam@for.me> wrote in message
news:422672ae@newsgroups.borland.com...
> Hi
>
> I know there are probably more appropriate groups for this question, but
> there is too much traffic over there.. ;-)
> Maybe there are some database experts here.
> I'm trying to use mySQL in a multithreaded App.
> I'd like your comments on the code below, because I'm not an expert in
this.
> Is this the right way to do it?
> It seems to work quite fine this way.
> I've found that setting the NUM_SHARED_CONNECTIONS const to more than 1
> results in SIGSEGV with multi-threading.
> So dbExpress / mySQL is not thread safe, right?
> Is there any better/faster way to do it?
> Any potential problems?
>
> Thanks
> Theo
>
>
>
>
>  ****************************************
**********************
>
> unit uHarvLinDBConn;
>
> interface
>
> uses Classes, Sysutils,
>    DBXpress, FMTBcd, DB, SqlExpr,
>    MiniXMLTree, SyncObjs, LibC;
>
> const NUM_SHARED_CONNECTIONS = 1;
>
> const SS_FREE = -1;
>
> type
>    TConnectionShare = class
>    private
>      fConnList: TList;
>      FSection: TRTLCriticalSection;
>      fAbort: boolean;
>      fCountConns: integer;
>      procedure Acquire;
>      procedure Release;
>    protected
>      procedure SQLConnection1Login(Database: TSQLConnection;
>        LoginParams: TStrings);
>    public
>      constructor Create(ConnName: string; NumConns: integer);
>      destructor Destroy; override;
>      function GetConnection: TSQLConnection;
>      procedure ReleaseConn(Conn: TSQLConnection);
>      property Abort: boolean read fAbort write fAbort;
>
>    end;
>
> type
>    THarvDBConn = class(TObject)
>    private
>      fQuery: TSQLQuery;
>    public
>      constructor Create;
>      destructor Destroy; override;
>      function ExecSQL(SQL: string): string;
>      function OpenSQL(SQL: string; var Tree: TMiniXMLTree): string;
>    published
>
>    end;
>
> var CS: TConnectionShare;
>
> implementation
>
>
> {  THarvDBConn }
>
>
> constructor THarvDBConn.Create;
> begin
>    fQuery := TSQLQuery.Create(nil);
> end;
>
>
> destructor THarvDBConn.Destroy;
> begin
>    fQuery.free;
>    inherited;
> end;
>
> function THarvDBConn.ExecSQL(SQL: string): string;
> begin
>    Result := '';
>    fQuery.SQLConnection := CS.GetConnection;
>    try
>      if not fQuery.SQLConnection.Connected then
> fQuery.SQLConnection.Connected := true;
>      fQuery.SQL.Text := SQL;
>      try
>        fQuery.ExecSQL;
>      except
>        on E: Exception do Result := E.message;
>      end;
>    finally
>      fQuery.Close;
>      CS.ReleaseConn(fQuery.SQLConnection);
>    end;
> end;
>
> function THarvDBConn.OpenSQL(SQL: string; var Tree: TMiniXMLTree): string;
> var i, k: integer;
>    nd: TMiniXMLNode;
> begin
>    Result := '';
>    fQuery.SQLConnection := CS.GetConnection;
>    try
>      if not fQuery.SQLConnection.Connected then
> fQuery.SQLConnection.Connected := true;
>      fQuery.SQL.Text := SQL;
>      try
>        fQuery.Open;
>        fQuery.First;
>        for i := 0 to fQuery.RecordCount - 1 do
>        begin
>          nd := Tree.Addnode(inttostr(i), '');
>          for k := 0 to fQuery.FieldCount - 1 do
>            nd.addNode(fQuery.Fields[k].FullName,
fQuery.Fields[k].AsString);
>          fQuery.next;
>        end;
>      except
>        on E: Exception do Result := E.message;
>      end;
>    finally
>      fQuery.close;
>      CS.ReleaseConn(fQuery.SQLConnection);
>    end;
> end;
>
> { TConnectionShare }
>
>
> constructor TConnectionShare.Create(ConnName: string; NumConns: integer);
> var i: integer;
>    tmpConn: TSQLConnection;
> begin
>    fAbort := false;
>    InitializeCriticalSection(FSection);
>    fConnList := TList.create;
>    for i := 0 to NumConns - 1 do
>    begin
>      tmpConn := TSQLConnection.create(nil);
>      tmpConn.Tag := SS_FREE;
>
>      tmpConn.ConnectionName := ConnName;
>      tmpConn.DriverName := 'MySQL';
>      tmpConn.GetDriverFunc := 'getSQLDriverMYSQL';
>      tmpConn.LibraryName := 'libsqlmy.so';
>      tmpConn.VendorLib := 'libmysqlclient.so';
>
>      tmpConn.LoginPrompt := False;
>      tmpConn.LoadParamsOnConnect := false;
>      tmpConn.KeepConnection := true;
>
>      tmpConn.OnLogin := SQLConnection1Login;
>      tmpConn. LoadParamsFromIniFile(ExtractFilePath(Pa
ramStr(0)) +
> 'mySQL.conn');
>
>      tmpConn.Connected := true;
>      fConnList.Add(tmpConn);
>    end;
>    fCountConns := 0;
>
> end;
>
> procedure TConnectionShare.SQLConnection1Login(Database: TSQLConnection;
>    LoginParams: TStrings);
> begin
>    LoginParams.Assign(Database.Params);
> end;
>
> destructor TConnectionShare.Destroy;
> var i: integer;
> begin
>    try
>      Acquire;
>      try
>        for i := 0 to fConnList.count - 1 do
>        begin
> {$MESSAGE Warn'Potential Problem here when freeing conn in use by
> another thread'}
>          TSQLConnection(fConnList[i]).Free;
>        end;
>      finally
>        Release;
>      end;
>    finally
>      fConnList.free;
>      DeleteCriticalSection(FSection);
>    end;
>    inherited;
> end;
>
> function TConnectionShare.GetConnection: TSQLConnection;
> var i, tim: integer;
> begin
>    Acquire;
>    try
>      tim := 0;
>      Result := nil;
>      while (tim < 24000) and not (fAbort) do //sleep 24000 x 5 ms = 120
sec
>      begin
>        inc(tim);
>        for i := 0 to fConnList.count - 1 do
>          if TSQLConnection(fConnList[i]).Tag = SS_FREE then
>          begin
>            //writeln('connnum '+inttostr(i));
>            Result := TSQLConnection(fConnList[i]);
>            Result.Tag := i; //Just for information
>            inc(FCountConns);
>            //writeln(fCountConns);
>            Exit;
>          end;
>        //writeln('sleepin');
>        sleep(5);
>      end;
>      //writeln('GetConn timeout');
>    finally
>      Release;
>    end;
> end;
>
>
> procedure TConnectionShare.Acquire;
> begin
>    EnterCriticalSection(FSection);
> end;
>
> procedure TConnectionShare.Release;
> begin
>    LeaveCriticalSection(FSection);
> end;
>
> procedure TConnectionShare.ReleaseConn(Conn: TSQLConnection);
> begin
>    if Assigned(Conn) then Conn.Tag := SS_FREE;
> end;
>
> initialization
>    CS := TConnectionShare.Create('MySQLConnection',
NUM_SHARED_CONNECTIONS);
>
> finalization
>    CS.free;
>
> end.

>
> I've had same situation , but with postgres. I'm using zeoslib and it work
ed
> for me, but there is a trick , each query must have it's own connection.My
> app work with max. 8 connections at the same time, so don't know about eg.
> 20 threads.
>

Hi Zeljko

As you can see in the demo-code, I'm using a connection-pool.
Each thread requests one of NUM_SHARED_CONNECTIONS.
It works fine, if I only have one shared connection, but SIGSEGV's after
some time, with more than one conn.

So I think having more than one connection, or one per thread, is not a
solution here...

Cheers!

> So I think having more than one connection, or one per thread, is not a
> solution here...

ok