Board index » kylix » mySQL multi threaded

mySQL multi threaded


2005-03-03 10:17:30 AM
kylix2
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(ParamStr(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.
 
 

Re:mySQL multi threaded

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
 

Re:mySQL multi threaded

Hi siegs
Quote

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
 

{smallsort}

Re:mySQL multi threaded

Quote
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
 

Re:mySQL multi threaded

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" < XXXX@XXXXX.COM >wrote in message
Quote
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(ParamStr(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.
 

Re:mySQL multi threaded

Quote

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.

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!
 

Re:mySQL multi threaded

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