title: Table locks in SQLite accessed by fireDAC
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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 ?</p>
  
  <p>Here is the code internal to the threads:</p>
  
  <pre><code>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;
  </code></pre>