SQLite3 Framework and multi-threading

yurasek · 2011-05-09 22:59:55

First of all, thank you so much for developing the framework. The latest versions of framework I was faced with some errors when developing multithreaded applications. For some unknown reason, when you add or change records in multithreaded applications, there are exceptions to the form

Project Server.exe raised exception class ESQLException with message 'table SampleRecord1 has no column named Count3.'

That is, when adding a record SampleRecord1 with field Count1 or a record SampleRecord3 with field Count3 there is a distortion of data, resulting in an error.
Here is an example of code that causes an error:

unit uServer;

interface

uses
  Windows, Messages, SysUtils, Classes, Controls, Forms,
  SQLite3Commons, SQLite3, StdCtrls, SyncObjs;

type
  TSQLSampleRecord1 = class(TSQLRecord)
  private
    fCount: Integer;
  published
    property Count1: Integer read fCount write fCount;
  end;

  TSQLSampleRecord2 = class(TSQLRecord)
  private
    fCount: Integer;
  published
    property Count2: Integer read fCount write fCount;
  end;

  TSQLSampleRecord3 = class(TSQLRecord)
  private
    fCount: Integer;
  published
    property Count3: Integer read fCount write fCount;
  end;

  TDBThread = class(TThread)
  private
    FDB: TSQLRestServerDB;
    FEvent: THandle;
  public
    constructor Create(DB: TSQLRestServerDB);
    destructor Destroy; override;
    procedure Execute; override;
  end;

  TSampleThread = class(TThread)
  private
    FSQLRecordClass: TSQLRecordClass;
    FDB: TSQLRestServerDB;
    FId: Cardinal;
    FCount: Integer;
  public
    constructor Create(DB: TSQLRestServerDB; SQLRecordClass: TSQLRecordClass);
    destructor Destroy; override;
    procedure Execute; override;
    procedure Info;
  end;

  TSampleForm = class(TForm)
    LogMemo: TMemo;
    StartButton: TButton;
    StopButton: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure StartButtonClick(Sender: TObject);
    procedure StopButtonClick(Sender: TObject);
  private
    function CreateSampleModel: TSQLModel;
  public
    Model: TSQLModel;
    DB: TSQLRestServerDB;
    SampleThread1: TSampleThread;
    SampleThread2: TSampleThread;
    SampleThread3: TSampleThread;
    DBThread: TDBThread;
  end;

var
  SampleForm: TSampleForm;
  CS: TCriticalSection;

implementation

{$R *.dfm}
{.$define CS}

function TSampleForm.CreateSampleModel: TSQLModel;
begin
  Result:= TSQLModel.Create([TSQLSampleRecord1, TSQLSampleRecord2, TSQLSampleRecord3]);
end;

constructor TDBThread.Create(DB: TSQLRestServerDB);
begin
  FDB:= DB;
  FEvent:= CreateEvent(nil, False, False, nil);
  inherited Create(False);
end;

procedure TDBThread.Execute;
begin
  inherited;
  {$ifdef CS}
  CS.Enter;
  {$endif CS}
  try
    FDB.TransactionBegin(TSQLSampleRecord1);
    FDB.TransactionBegin(TSQLSampleRecord2);
    FDB.TransactionBegin(TSQLSampleRecord3);
  finally
    {$ifdef CS}
    CS.Leave;
    {$endif CS}
  end;
  while not Terminated and (WaitForSingleObject(FEvent, 5000) <> WAIT_OBJECT_0) do
    begin
      {$ifdef CS}
      CS.Enter;
      {$endif CS}
      try
        FDB.Commit;
        FDB.TransactionBegin(TSQLSampleRecord1);
        FDB.TransactionBegin(TSQLSampleRecord2);
        FDB.TransactionBegin(TSQLSampleRecord3);
      finally
        {$ifdef CS}
        CS.Leave;
        {$endif CS}
      end;
    end;
  {$ifdef CS}
  CS.Enter;
  {$endif CS}
  try
    FDB.Commit;
  finally
    {$ifdef CS}
    CS.Leave;
    {$endif CS}
    CloseHandle(FEvent);
  end;
end;

destructor TDBThread.Destroy;
begin
  SetEvent(FEvent);
  WaitFor;
  inherited;
end;

procedure TSampleForm.FormCreate(Sender: TObject);
begin
  CS:= TCriticalSection.Create;
  Model:= CreateSampleModel;
  DB:= TSQLRestServerDB.Create(Model, ExtractFilePath(Application.ExeName) + 'sample.db');
  DB.CreateSQLIndex(TSQLSampleRecord1, ['Count1'], False);
  DB.CreateSQLIndex(TSQLSampleRecord2, ['Count2'], False);
  DB.CreateSQLIndex(TSQLSampleRecord3, ['Count3'], False);
  DB.CreateMissingTables(0);
end;

procedure TSampleForm.FormDestroy(Sender: TObject);
begin
  if StopButton.Enabled then
    StopButton.Click;
  DB.Free;
  Model.Free;
  CS.Free;
end;

procedure TSampleThread.Execute;
var
  Rec1: TSQLSampleRecord1;
  Rec2: TSQLSampleRecord2;
  Rec3: TSQLSampleRecord3;
begin
  FId:= GetCurrentThreadId;
  if FSQLRecordClass.ClassName = 'TSQLSampleRecord1' then
    begin
      Rec1:= TSQLSampleRecord1.Create;
      while not Terminated do
        begin
          Randomize;
          Rec1.Count1:= Random(2000);
          {$ifdef CS}
          CS.Enter;
          {$endif CS}
          try
            FDB.Add(Rec1, True);
          finally
            {$ifdef CS}
            CS.Leave;
            {$endif CS}
          end;
          Inc(FCount);
          if FCount mod 1000 = 0 then
            Synchronize(Info);
          Sleep(1);
        end;
      Rec1.Free;
    end
  else
    if FSQLRecordClass.ClassName = 'TSQLSampleRecord2' then
      begin
        Rec2:= TSQLSampleRecord2.Create;
        while not Terminated do
          begin
            Randomize;
            Rec2.Count2:= Random(2000);
            {$ifdef CS}
            CS.Enter;
            {$endif CS}
            try
              FDB.Add(Rec2, True);
            finally
              {$ifdef CS}
              CS.Leave;
              {$endif CS}
            end;
            Inc(FCount);
            if FCount mod 1000 = 0 then
              Synchronize(Info);
            Sleep(1);
          end;
        Rec2.Free;
      end
    else
      begin
        Rec3:= TSQLSampleRecord3.Create;
        while not Terminated do
          begin
            Randomize;
            Rec3.Count3:= Random(2000);
            {$ifdef CS}
            CS.Enter;
            {$endif CS}
            try
              FDB.Add(Rec3, True);
            finally
              {$ifdef CS}
              CS.Leave;
              {$endif CS}
            end;
            Inc(FCount);
            if FCount mod 1000 = 0 then
              Synchronize(Info);
            Sleep(1);
          end;
        Rec3.Free;
      end;
end;

constructor TSampleThread.Create(DB: TSQLRestServerDB; SQLRecordClass: TSQLRecordClass);
begin
  FSQLRecordClass:= SQLRecordClass;
  FCount:= 0;
  FDB:= DB;
  inherited Create(True);
end;

destructor TSampleThread.Destroy;
begin
  Terminate;
  WaitFor;  
  inherited;
end;

procedure TSampleThread.Info;
begin
  SampleForm.LogMemo.Lines.Add(FormatDateTime('hh:mm:ss.zzz', Now) + ' [thread ' + IntToStr(FId) + ']: ' + IntToStr(FCount));
end;

procedure TSampleForm.StartButtonClick(Sender: TObject);
begin
  StartButton.Enabled:= False;
  DBThread:= TDBThread.Create(DB);
  SampleThread1:= TSampleThread.Create(DB, TSQLSampleRecord1);
  SampleThread2:= TSampleThread.Create(DB, TSQLSampleRecord2);
  SampleThread3:= TSampleThread.Create(DB, TSQLSampleRecord3);
  SampleThread1.Resume;
  SampleThread2.Resume;
  SampleThread3.Resume;
  StopButton.Enabled:= True;
end;

procedure TSampleForm.StopButtonClick(Sender: TObject);
begin
  StopButton.Enabled:= False;
  SampleThread3.Free;
  SampleThread2.Free;
  SampleThread1.Free;
  DBThread.Free;
  StartButton.Enabled:= True;
end;

end.

ab · 2011-05-10 07:08:01

Which version did you use?
Please use the latest version from http://synopse.info/fossil

I'll try to reproduce this issue.
Thanks for the feedback and sample code.

BUT the way you're using the transaction is not correct, from the framework point of view.
You should use the transactions within the adding threads, not in a separate thread.

yurasek · 2011-05-10 08:40:11

I'm using the latest version.
In my application into a database saved a lot of small records and without the use of a transaction can not do.
In the examples and documentation I have not found a way how to open the transaction for several tables, but as I understand the use of the following construction is completely correct:

FDB.TransactionBegin(TSQLSampleRecord1);
FDB.TransactionBegin(TSQLSampleRecord2);
FDB.TransactionBegin(TSQLSampleRecord3);
FDB.Commit;

As far as I understand the use of class methods TSQLRestServerDB thread-safe. So why can not I execute a transaction in a separate thread?

Last edited by yurasek (2011-05-10 08:41:10)

ab · 2011-05-10 08:53:31

TransactionBegin will work only once at a time, and can't be nested.

There is a RESULT boolean value, to be checked at call time.

You should write for instance:

if TransactionBegin(TSQLSampleRecord1) then
try
  .... 
  Commit;
except
  RollBack;
end;

The parameter supplied to TransactionBegin is not used in the current implementation.

For the Client DB, you have even a TSQLRestClientURI.TransactionBeginRetry() method.

ab · 2011-05-10 09:19:39

I've updated the documentation in order to be more clear about using transactions in the framework.

See http://synopse.info/fossil/info/c50e494a5b

ab · 2011-05-10 09:21:34

yurasek wrote:

In my application into a database saved a lot of small records and without the use of a transaction can not do.

You can use a counter in the adding loop, then call Commit/TransactionBegin for every 5,000 items for instance:

counter := 0;
for i := 1 to 100000 do // or a repeat/while whatever loop you need
begin
  // adding some record
  inc(counter);
  if counter mod 5000=0 then
  begin
    Commit;
    if not TransactionBegin(...) then error
  end;

yurasek · 2011-05-13 07:24:27

Were you able to reproduce the situation with a simultaneous change in the database in two or more threads, when this error occurs?
Here is an example of the log when my program tries to add data to the wrong table (in my case, the data were to be added to the table Value but not Data:

2011-05-10 18:26:16 TSQLRestServerDB.EngineExecute: unknown error  INSERT INTO Data (Time,TId,Bus,Address,Counter,Value) VALUES (:(40673.7682443171288):,:(123427):,:(1):,:(10):,:(16):,:(531):);

Very often when you use the client part implemented by TSQLite3HttpClient function MultiFieldValues when selecting data sometimes does not return, which probably also related to mistakes in the implementation of thread-safe.

ab · 2011-05-13 14:46:53

I guess you should protect direct call to TSQLRestServerDB via critical sections.
You won't notice any performance problem, IMHO.

mORMot Open Source

#1 2011-05-09 22:59:55

SQLite3 Framework and multi-threading

#2 2011-05-10 07:08:01

Re: SQLite3 Framework and multi-threading

#3 2011-05-10 08:40:11

Re: SQLite3 Framework and multi-threading

#4 2011-05-10 08:53:31

Re: SQLite3 Framework and multi-threading

#5 2011-05-10 09:19:39

Re: SQLite3 Framework and multi-threading

#6 2011-05-10 09:21:34

Re: SQLite3 Framework and multi-threading

#7 2011-05-13 07:24:27

Re: SQLite3 Framework and multi-threading

#8 2011-05-13 14:46:53

Re: SQLite3 Framework and multi-threading

Board footer