Well, another serious problem with using DBExpress drivers with MySQL and Delphi 2007 -
during heavier loads (wow, I never thought that several connections at a time I would have to call "heavy")
I would get strange Access Violation errors reported for WideStrings.TWideStrings.GetValue.
WTF?
From Delphi's renowned
documentation:
Absolute thread safety is left to applications using dbExpress.
However, some thread safety issues are best handled by the dbExpress framework.
dbExpress thread safe operations include loading and unloading drivers,
and connection creation As mentioned earlier,
a delegate driver can be created to make the entire public interface of dbExpress thread safe if needed.
Hmm... I wonder how "absolute thread safety" compares to plain old standard thread safety...
Anyway, QualityCentral is your friend here: see
QC#57326.
The suggested solution there is to add a critical section for creating SQL connections.
The report is marked as a "Test Case Error" - I guess that since DBExpress doesn't ensure
thread safety (as per the cited piece above), it's not CodeGear's problem
that their libs barf in standard usage scenarios...
Before using the suggested workaround I wanted to have a test case that would reproduce the problem with high probability.
I was thinking of about 20 threads each issuing randomly select and insert statements to the DB:
type
TYIKDBTestThread = class(TThread)
private
FException: boolean;
FTerminated: boolean;
protected
procedure Execute; override;
end;
procedure TYIKDBTestThread.Execute;
const
CCommandCount = 100;
var
k: integer;
LConnection: TSQLConnection;
LQuery: TSQLQuery;
begin
FTerminated := FALSE;
try
try
for k := 0 to CCommandCount - 1 do
begin
LConnection := CreateSQLConnection;
try
if Random(2) = 0 then
LConnection.ExecuteDirect(
'insert into TIMING values (''1'', ''1'', ''1'', ''1'')')
else
begin
LQuery := TSQLQuery.Create(NIL);
try
LQuery.SQLConnection := LConnection;
LQuery.SQL.Text := 'select * from TIMING';
LQuery.Open;
finally
FreeAndNIL(LQuery);
end;
end;
finally
FreeAndNIL(LConnection);
end;
end;
except
on Exception do
FException := TRUE;
end;
finally
FTerminated := TRUE;
end;
end;
The FTerminated and FException flags were added to see what's happening in the thread
(and fail the test case with FException is TRUE for any of the threads).
CreateSQLConnection creates a connection and sets KeepConnection to FALSE and Connected to TRUE.
Next, the test case code:
procedure TYIKDBTests.TestSqlExprThreads;
const
CThreadCount = 20;
var
k, LCount: integer;
LThreadList: TObjectList;
begin
Randomize;
LThreadList := TObjectList.Create;
try
for k := 0 to CThreadCount - 1 do
LThreadList.Add(TYIKDBTestThread.Create(FALSE));
repeat
LCount := 0;
for k := 0 to LThreadList.Count - 1 do
if not (LThreadList[k] as TYIKDBTestThread).FTerminated then
Inc(LCount);
until LCount = 0;
for k := 0 to LThreadList.Count - 1 do
Check(not (LThreadList[k] as TYIKDBTestThread).FException);
finally
FreeAndNIL(LThreadList);
end;
end;
Now, the nice thing is that this test case demonstrates the error almost every time.
That will surely help in checking / finding a good solution / workaround.
Ok, so now the time for the suggested workaround in the QC report -
add a critical section for creating SQL connections.
At first, it seemed to work fine, but unfortunately, once in a while I started to get
AV errors on System.TObject.Free... So, that probably meant that closing SQL connections
should also be under a critical section (and the same critical section as the one for creating connections).
Unfortunately, although this solves the AV, it introduces a deadlock...
After trying various scenarios such as locking read and writes to the DB (that is, adding critical sections
around ExecuteDirect and TQuery.Open) I would get different behaviors, but none of these solutions
would produce really stable results...
So I started digging deeper into SqlExpr.pas - not a fun job, but I had to get my program working.
Since critical sections seem to fix the problem (except for the deadlocks), I tried to narrow down
the code that needs to be in those sections - in TSQLConnection.DoConnect and .DoDisconnect.
After much experimentation, I finally narrowed this down to the following code in .DoConnect:
if (FDBXConnection is TDBXConnectionEx)
and (TDBXConnectionEx(FDBXConnection).ProductName = 'BlackfishSQL') then
begin
FDefaultSchema := 'DEFAULT_SCHEMA';
end;
Since I'm not using BlackfishSQL :-) I just commented this out (the problem is with referencing ProductName).
Now, without any critical sections (that slow down execution, BTW, up to 5 times for the test code) I get an almost stable unit.
I write "almost stable" because very rarely other errors appear
(and these errors sometimes appear when using critical sections too, so that's probably a different matter).
Usually, connection parameters look like being messed up, but frankly, I have no more time to spend on this.
Especially since the change described above seems to be enough to stop the problems
with my application - no data losses anymore...
Last thing: it seems that Delphi 2009 fixes this problem (and the previous one).
However, the problems mentioned in the paragraph above (with connection parameter corruption) are still present...
Top
|
Texts
