Table locks in SQLite accessed by fireDAC

Question

I'm working on porting a set of paradox tables to SQLite. In order to do so, I created a test application that simulates (somewhat) the current usage scenario: multiple users accessing the same DB file and performing simultaneous read and writes.

The application is very simple: it will start several threads that each create a connection, opens a table and will randomly read, update or insert inside the table.

Almost immediately, The application encounters a "database table locked" error. I have tried several things to attempt to work around it but nothing seems to work. What am I doing wrong ?

Here is the code internal to the threads:

procedure testDB(TargetFolder: string);
var
  Conn: TFDConnection;
  Table: TFDTable;
  i: Integer;
begin
  randomize;
  Conn := TFDConnection.Create(nil);
  try
    Conn.DriverName := 'SQLite';
    Conn.LoginPrompt := false;
    Conn.Params.clear;
    Conn.Params.Database := TPath.Combine(TargetFolder, 'testDB.sdb');
    Conn.Params.Add('DriverID=SQLite');
    // all this is the result of several attemp to fix the table locking error. none worked

    Conn.Params.Add('LockingMode=Normal');
    Conn.Params.Add('Synchronous=Normal');
    Conn.UpdateOptions.UpdateMode := TUpdateMode.upWhereAll;
    Conn.UpdateOptions.LockWait := True;
    Conn.UpdateOptions.LockMode := TFDLockMode.lmPessimistic;
    Conn.UpdateOptions.LockPoint := TFDLockPoint.lpImmediate;
    Conn.UpdateOptions.AssignedValues := [uvLockMode,uvLockPoint,uvLockWait];
    Conn.Open();
    Conn.ExecSQL('CREATE TABLE IF NOT EXISTS ''test'' (''ID''   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,''data1'' TEXT NOT NULL,''data2'' INTEGER NOT NULL)');
    Table := TFDTable.Create(nil);
    try
      table.Connection := Conn;
      while True do
      begin
        case Trunc(Random(10)) of
          0..3:
          begin
            table.Open('test');
            try
              if table.Locate('data1', 'name'+intToStr(Trunc(Random(10))),[TLocateOption.loCaseInsensitive]) then
              begin
                table.Edit;
                table.FieldByName('data2').AsInteger := table.FieldByName('data2').AsInteger + 1;
                table.Post;
              end;
            finally
              table.close;
            end;
          end;
          4..8:
          begin
            table.Open('test');
            try
              i := Trunc(Random(10));
              if not table.Locate('data1', 'name'+ i.ToString,[TLocateOption.loCaseInsensitive]) then
              begin
                table.AppendRecord([null, 'name'+ i.ToString, 0]);
              end;
            finally
              table.close;
            end;
          end
        else
          break;
        end;
      end;
    finally
      FreeAndNil(Table);
    end;
  finally
    FreeAndNil(Conn);
  end;
end;

Tachyphylaxis

In this chapter you'll find all that you need. – Gynaecomastia

Thanks, I managed to find the proper parameters. If you write an answer, I'll accept it. Otherwise, I'll answer the question myself. – Tachyphylaxis

Post an answer, please. I'm quite busy now.. – Gynaecomastia

Answer 1

Thanks to Victoria, I managed to find the right parameters.

  Conn := TFDConnection.Create(nil);
  try
    Conn.DriverName := 'SQLite';
    Conn.LoginPrompt := false;
    Conn.Params.clear;
    Conn.Params.Database := TPath.Combine(TargetFolder, 'testDB.sdb');
    Conn.Params.Add('DriverID=SQLite');
    Conn.Params.Add('SharedCache=False');
    Conn.Params.Add('LockingMode=Normal');
    Conn.Params.Add('Synchronous=Normal');
    Conn.UpdateOptions.LockWait := True;
    Conn.Open();

Thanks again

Tachyphylaxis

According to the documentation, UpdateOptions.LockWait is not used unless UpdateOptions.LockMode=lmPessimistic (and by default it is lmNone). – Dribble