Caves Travel Diving Graphics Mizar Texts Cuisine Lemkov Contact Map RSS Polski
Trybiks' Dive Texts DBExpress DBExpress and Thread Safety YAC Software












Rhino Mocks



UI Testing




DBExpress and Thread Safety
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.


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:
    TYIKDBTestThread = class(TThread)
      FException: boolean;
      FTerminated: boolean;
      procedure Execute; override;
  procedure TYIKDBTestThread.Execute;
    CCommandCount = 100;
    k: integer;
    LConnection: TSQLConnection;
    LQuery: TSQLQuery;
    FTerminated := FALSE;
        for k := 0 to CCommandCount - 1 do
          LConnection := CreateSQLConnection;
            if Random(2) = 0 then
                'insert into TIMING values (''1'', ''1'', ''1'', ''1'')')
              LQuery := TSQLQuery.Create(NIL);
                LQuery.SQLConnection := LConnection;
                LQuery.SQL.Text := 'select * from TIMING';
        on Exception do
          FException := TRUE;
      FTerminated := TRUE;
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;
    CThreadCount = 20;
    k, LCount: integer;
    LThreadList: TObjectList;
    LThreadList := TObjectList.Create;
      for k := 0 to CThreadCount - 1 do
        LCount := 0;
        for k := 0 to LThreadList.Count - 1 do
          if not (LThreadList[k] as TYIKDBTestThread).FTerminated then
      until LCount = 0;
      for k := 0 to LThreadList.Count - 1 do
        Check(not (LThreadList[k] as TYIKDBTestThread).FException);
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
    FDefaultSchema := 'DEFAULT_SCHEMA';
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...


Trevor Toms wrote on 2015-01-29 17:45:30
Your "BlackfishSQL" fix has resolved a long-running multi-user web service error for us. We suffered random SQL errors in dbExpress (D2007) usually of the form "Missing Driver Name" or segment faults. These would only occur when a second or third thread was started by IIS.

Thanks for this valuable fix!


Add a comment (fields with an asterisk are required)
Name / nick *
Mail (will remain hidden) *
Your website
Comment (no tags) *
Enter the text displayed below *





Related pages

Delphi interfaces... again

Checking "Dangling" Event Handlers in Delphi Forms

Drag-n-drop files onto the application window

Intraweb and MaxConnections

A Case for FreeAndNIL

Intraweb as an Apache DSO module

"Device not supported" in Intraweb

Automated GUI Testing

Rounding and precision on the 8087 FPU

SessionTimeout in Intraweb

Using TChart with Intraweb

Unknown driver: MySQL

TIdMessage's CharSet

Software Guarantees

Automated Testing of Window Forms

TChart - Missing Labels in Axes

Memory Leaks and Connection Explosions in DBExpress

Controlling Conditional Defines and Compilation Switches

Detecting Memory Leaks with DUnit

last_insert_id() and DBExpress

Registering Extensions

Forms as Frames

Checking Dangling Pointers vs. the New Memory Manager

Accessing Protected Members

Objects, interfaces, and memory management in Delphi