ExecSQL Access violation with multi-threaded process

[amarildolacerda]

Using version 7.0.4, or 7.2 - has the same behavior:
I have a RESTful server (using indy httpserver). For each client I create a connection to the database (Firebird 2.5.3 - 64bit) to isolate the database access for each client.

Everything works fine when I asked to "select" command (TzQuery.open), but when I need to insert a row in the table with "insert" using "TzQuery.ExecSQL" occurs error "access violation ..." inside code ZeosLib.
If I have only one client, it's work well, but when I have 10 clients, some times, occurs "access violation...".

For now I'm using TCriticalSection to isolate the thread of each client - this work OK, but limit speeds:
    
...
      LLockExecSql.Enter;
       try
         inherited ExecSQL ;
       finally
           LLockExecSql.Leave;
       end;
...

Does anyone know if this problem is a bug of Firebird or any changes to be made in ZeosLib?

[marsupilami]

Hello amarildolacerda,

I seem to remember that Firebird needs a separate connection per thread. Also only one thread is allowed to connect / disconnect at the same time. Have a look at this thread: http://groups.yahoo.com/neo/groups/fire ... ics/122401
For Zeos I would say the same is true: Have every thread create its own TZConnection and the other components. Also I would keep ownership of the created objects strictly to one thread and not switch between them.
Best regards,

Jan

[amarildolacerda]

I create a connection for each thread and free at the end.
It work ok with select, but crash with execSql.

Crash occurs in this point (ZAbstractRODataset -line 1660)
FRowsAffected := Statement.ExecuteUpdatePrepared;

[marsupilami]

Hello amarildolacerda,

which version of Zeos and Delphi do you use? Is it possible for you to provide a test case to reproduce the problem?
Best regards,

Jan

[amarildolacerda]

Delphi XE3
Zeos - 7.0.4

[amarildolacerda]

Hello,
thaks for your help, first of all.


I created this test case. For my surprise this test case work fine.
Now I will rework my job to find where is the problem.
Thanks for your attention.

-----------------------------xxxxxxxxxxxxx--------------------------------

uses zConnection, zDataset;

{
TABLE:

create table test (id integer, descr varchar(50), nValue numeric(15,4), dt date )

}


Type
TDBThread = class(TThread)

private
zConn:TZConnection;
zQry:TZQuery;
public

procedure CreateConn;
procedure Clear;
procedure execute;override;
destructor Destroy;override;

end;

procedure TForm19.RunThread();
var th:TDBThread;
begin
th:=TDBThread.Create(true);
try
th.FreeOnTerminate := true;
th.Resume;
finally
end;
end;


procedure TForm19.Button1Click(Sender: TObject);
var i:integer;
const n = 10;
begin

with TDBThread.Create(true) do
begin
Synchronize(Clear);
end;

Sleep(100);

i := n;
while i>0 do
begin
RunThread;
dec(i);
caption := IntToStr(n-i);
end;

end;

{ TDBThread }

var nCount:integer=0;

procedure TDBThread.Clear;
begin
createConn;
zQry.sql.text := 'delete from test';
zConn.StartTransaction;
zQry.ExecSql;
zConn.Commit ;
end;

procedure TDBThread.CreateConn;
begin
zConn := TZConnection.Create(nil);
zConn.Protocol := 'firebird-2.5';
zConn.HostName := 'localhost';
zConn.Database := 'c:\dados\store_paf.fdb';
zConn.User := 'SYSDBA';
zConn.Password := 'masterkey';
zConn.Connect;

zQry := TZQuery.Create(nil);
ZQry.SQL.Text := 'insert into test (id,descr,nValue, dt) values (:id,:descr,:nValue, ''now'' )';
zQry.Connection := zConn;


end;

destructor TDBThread.Destroy;
begin
zConn.Free;
zQry.Free;
//InterlockedDecrement(nCount);
inherited;
end;


procedure TDBThread.execute;
var x:integer;
begin
inherited;
CreateConn;
InterlockedIncrement(nCount);
zQry.ParamByName('id').AsInteger := nCount;
zQry.ParamByName('descr').AsString := 'teste ';

x := 1000;
while (x>0) and (not Terminated) do
begin

zQry.ParamByName('nValue').AsFloat := x;

zConn.StartTransaction;
zQry.ExecSQL ;
zConn.Commit;
dec(x);

end;

end;

[amarildolacerda]

Solved.

A basic mistake. One thread was not commiting record.
When another thread enter a lock conflict at record .

Thanks.

[amarildolacerda]

Inspecting my code I notice some Memory Leaks:

Delphi: XE3/32 or Delphi 2007 (the same)
FastMM4 4.991;
Zeos: 7.0.4;

Its simple to test:

with TZConnection.Create(Self) do
try
Protocol := 'firebird-2.5';
HostName := 'localhost';
User := 'sysdba';
Password := 'masterkey';
Database := 'c:\dados\store_paf.fdb';
Connect;
Disconnect; // with this or not - no matter.
finally
Free;
end;



1 - 4 bytes: Unknown x 2
5 - 12 bytes: TZCppCommentState x 10, TZQuoteState x 10, TZNumberState x 10, TZSymbolState x 10, TZEscapeState x 11, TZInterbaseCommentState x 1, TZInterbaseQuoteState x 1, TZInterbaseNumberState x 1, TZInterbaseSymbolState x 1, Unknown x 5
13 - 20 bytes: TList x 3, UnicodeString x 6, Unknown x 9
21 - 36 bytes: TZInterbase6Driver x 1, TZNativeLibraryLoader x 10, TZInterbase6DatabaseInfo x 1, TZSymbolRootNode x 11, TZCollection x 6, TZDefaultIdentifierConvertor x 2, TZUnmodifiableCollection x 6, TObjectList x 1, TZSymbolNode x 2852, UnicodeString x 454, AnsiString x 3, Unknown x 10
37 - 52 bytes: TZAbstractResultSetMetadata x 1, TZClientVariantManager x 1, TZHashMap x 3, TZURL x 1, TZInterbase6DatabaseMetadata x 1, UnicodeString x 51, Unknown x 25
53 - 68 bytes: TZUnicodeRowAccessor x 3, UnicodeString x 4
69 - 84 bytes: TZColumnInfo x 8, TZAnyValue x 11
85 - 100 bytes: TZURLStringList x 1
101 - 116 bytes: Unknown x 1
133 - 148 bytes: TZVirtualResultSet x 1, Unknown x 10
165 - 180 bytes: TZInterbase6Connection x 1
245 - 276 bytes: TZFirebirdD25PlainDriver x 1, TZFirebirdD21PlainDriver x 1, TZFirebirdD20PlainDriver x 1, TZFirebirdD15PlainDriver x 1, TZFirebird25PlainDriver x 1, TZFirebird21PlainDriver x 1, TZFirebird20PlainDriver x 1, TZFirebird15PlainDriver x 1, TZFirebird10PlainDriver x 1, TZInterbase6PlainDriver x 1
661 - 740 bytes: Unknown x 2
1013 - 1124 bytes: Unknown x 2863
1237 - 1364 bytes: Unknown x 4
1365 - 1508 bytes: Unknown x 4


Because of memory leaks, after 10.000 calls it gone to -> EOutOfMemory ( start with 23mb and hits 1480 mb of memory)
The same 10.000 threads calls with Zeos 7.0.3 -> 35 mb

[amarildolacerda]

With Zeos 7.0.5 (today release)

--------------------------------2013/9/8 22:40:10--------------------------------
This application has leaked memory. The small block leaks are (excluding expected leaks registered by pointer):

5 - 12 bytes: TZCppCommentState x 10, TZQuoteState x 10, TZNumberState x 10, TZSymbolState x 10, TZEscapeState x 11, TZInterbaseCommentState x 1, TZInterbaseQuoteState x 1, TZInterbaseNumberState x 1, TZInterbaseSymbolState x 1, Unknown x 3
13 - 20 bytes: TList x 3, UnicodeString x 5, Unknown x 10
21 - 36 bytes: TZInterbase6Driver x 1, TZNativeLibraryLoader x 10, TZInterbase6DatabaseInfo x 1, TZSymbolRootNode x 11, TZCollection x 6, TZDefaultIdentifierConvertor x 2, TZUnmodifiableCollection x 6, TObjectList x 1, TZSymbolNode x 2852, UnicodeString x 474, Unknown x 7
37 - 52 bytes: TZAbstractResultSetMetadata x 1, TZHashMap x 3, TZURL x 1, TZInterbase6DatabaseMetadata x 1, UnicodeString x 51, Unknown x 20
53 - 68 bytes: TZRowAccessor x 3, TZAnyValue x 11, UnicodeString x 4
69 - 84 bytes: TZColumnInfo x 8
85 - 100 bytes: TZURLStringList x 1
133 - 148 bytes: TZVirtualResultSet x 1
149 - 164 bytes: TZInterbase6Connection x 1
245 - 276 bytes: TZFirebirdD25PlainDriver x 1, TZFirebirdD21PlainDriver x 1, TZFirebirdD20PlainDriver x 1, TZFirebirdD15PlainDriver x 1, TZFirebird25PlainDriver x 1, TZFirebird21PlainDriver x 1, TZFirebird20PlainDriver x 1, TZFirebird15PlainDriver x 1, TZFirebird10PlainDriver x 1, TZInterbase6PlainDriver x 1
437 - 484 bytes: Unknown x 3
597 - 660 bytes: Unknown x 2
1013 - 1124 bytes: Unknown x 2865
1125 - 1236 bytes: Unknown x 4
1237 - 1364 bytes: Unknown x 2

[EgonHugeist]

See http://zeoslib.sourceforge.net/viewtopi ... =39&t=4091

Therefore a little 7.0.5a will released in some days. We're sorry. The team is to small to do ALL things..

Switch to SVN and check again. If your issue remains: post here.

[amarildolacerda]

Solved.

good work.

Thanks...


Today I UPDATED to 7.1.2-stable (Solved too)