FireDAC behaving indeterministically at a specific customer's environment

I'm encountering intermittent errors in a Delphi application that uses FireDAC with SQLite, specifically configured in Delphi 10 Seattle.

This problem is particularly perplexing as it only occurs for one specific customer, displaying seemingly indeterminate behavior. Typically, the system resumes normal function after a restart, but the problem unpredictably recurs later. We have been unable to reproduce these errors internally. They were only identified through the use of EurekaLog.

Configuration of TFDConnection:

• Normal locking is enabled to support access by multiple processes.
• Write-Ahead Logging is enabled.
• (LockWait := True) Lock waiting is enabled, allowing transactions to wait for locks to be released.
• (lmPessimistic) Pessimistic locking is used as required by LockWait.

Two processes access the database concurrently: typically, one writes while the other reads. There are no heavy loads, and concurrent writing attempts, which are very rare, are managed effectively by the lockwait setting.

The examples shown here pertain exclusively to read operations.

Questions

1. Could the specific configuration settings like Locking Mode, WAL, or others contribute to these errors?
2. Are there any adjustments to the FireDAC or SQLite parameters that might help stabilize the application under high-load conditions or specific environmental factors? (AutoClose, BeforeClose?)

Example 1 - Fetching rows

Goal: Fetch a sorted list of SOP instance UIDs using a prepared statement with one variable.

  Query := FQuerySortImagesByInstanceNoAsc;
  try
    begin
      Query.ParamByName('series_iuid').Value := SeriesInstanceUID;
      Query.Open;
      while not Query.Eof do
      begin
        SopInstUIDs.Add(Query.FieldByName(...).AsString);
        Query.Next;
      end;
    end;
  finally
    Query.Close;
  end;

It failed at Query.Next.

ExceptionClass: EFDException
ExceptionMessage: [FireDAC][Phys][SQLITE]-311. Command must be open for fetching.

----------------------------------------------------------------------------------------------------------------------------------------------------------------
|Methods |Details|Stack           |Address         |Module      |Offset          |Unit                |Class                   |Procedure/Method    |Line      |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|*Exception Thread: ID=136748; Parent=131140; Priority=0                                                                                                       |
|Class=TQueryThread; Name=[Unnamed thread] Kind: TThread. Thread function: ...
|DeadLock=0; Wait Chain=                                                                                                                                       |
|Comment=                                                                                                                                                      |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------|
|7FFFFFFE|04     |0000000000000000|0000000000EC87A7|App.exe |0000000000AC87A7|FireDAC.Stan.Error  |                        |FDException         |189[27]   |
|00000020|04     |000000000D27EF68|000000000107C1B1|App.exe |0000000000C7C1B1|FireDAC.Phys        |TFDPhysCommand          |FetchBase           |8661[17]  |
|00000020|04     |000000000D27F018|000000000107B804|App.exe |0000000000C7B804|FireDAC.Phys        |TFDPhysCommandAsyncFetch|Execute             |8540[1]   |
|00000020|04     |000000000D27F048|00000000010AAACA|App.exe |0000000000CAAACA|FireDAC.Stan.Async  |TFDStanAsyncExecutor    |ExecuteOperation    |175[6]    |
|00000020|04     |000000000D27F0A8|00000000010AAFE5|App.exe |0000000000CAAFE5|FireDAC.Stan.Async  |TFDStanAsyncExecutor    |Run                 |270[12]   |
|00000020|04     |000000000D27F158|0000000001070964|App.exe |0000000000C70964|FireDAC.Phys        |TFDPhysCommand          |ExecuteTask         |6575[21]  |
|00000020|04     |000000000D27F1E8|000000000107C919|App.exe |0000000000C7C919|FireDAC.Phys        |TFDPhysCommand          |Fetch               |8751[2]   |
|00000020|04     |000000000D27F268|00000000010F302E|App.exe |0000000000CF302E|FireDAC.Comp.Client |TFDCustomCommand        |Fetch               |7032[11]  |
|00000020|04     |000000000D27F388|00000000010F54A7|App.exe |0000000000CF54A7|FireDAC.Comp.Client |TFDCustomTableAdapter   |Fetch               |7801[3]   |
|00000020|04     |000000000D27F3C8|00000000010FAB3E|App.exe |0000000000CFAB3E|FireDAC.Comp.Client |TFDAdaptedDataSet       |DoFetch             |9957[3]   |
|00000020|04     |000000000D27F408|00000000010C1CB2|App.exe |0000000000CC1CB2|FireDAC.Comp.DataSet|TFDDataSet              |InternalFetchRows   |4485[25]  |
|00000020|04     |000000000D27F488|00000000010BF0C5|App.exe |0000000000CBF0C5|FireDAC.Comp.DataSet|TFDDataSet              |GetRecord           |3551[16]  |
|00000020|04     |000000000D27F4D8|0000000000EBA653|App.exe |0000000000ABA653|Data.DB             |TDataSet                |GetNextRecord       |13812[9]  |
|00000020|04     |000000000D27F518|0000000000EBB272|App.exe |0000000000ABB272|Data.DB             |TDataSet                |MoveBy              |14183[16] |
|00000020|04     |000000000D27F578|0000000000EBB4CE|App.exe |0000000000ABB4CE|Data.DB             |TDataSet                |Next                |14227[3]  |
|00000020|04     |000000000D27F5A8|0000000001242159|App.exe |0000000000E42159|SQLiteConnection    |TSQLiteConnection       |SortImagesBySortType|577[26]   |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------|

Example 2 - COUNT

Goal: Determine the number of files that are part of the current series using a prepared statement with one variable.

  Query := FQueryGetFileCountSeries;
  try
    Query.ParamByName('series_iuid').Value := SeriesInstanceUID;
    Query.Open;
    Result := Query.Fields[0].AsInteger;
  finally
    Query.Close;
  end;

It failed at Query.Close.

ExceptionClass: EInvalidPointer
ExceptionMessage: Application made attempt to free invalid or unknown memory block: $000000000F6B02B0 DATA [?] 0 bytes.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Methods |Details|Stack           |Address         |Module      |Offset          |Unit                |Class              |Procedure/Method                   |Line       |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*Exception Thread: ID=139204; Parent=0; Priority=0                                                                                                                       |
|Class=; Name=MAIN                                                                                                                                                        |
|DeadLock=0; Wait Chain=                                                                                                                                                  |
|Comment=                                                                                                                                                                 |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|7FFFFFFE|04     |0000000000000000|0000000000412285|App.exe |0000000000012285|System              |                   |_UStrClr                           |24641[8]   |
|00000020|04     |000000000014DC38|0000000000412342|App.exe |0000000000012342|System              |                   |_UStrArrayClr                      |24786[4]   |
|00000020|04     |000000000014DC78|00000000004150DA|App.exe |00000000000150DA|System              |                   |_FinalizeArray                     |31726[24]  |
|00000020|04     |000000000014DCC8|0000000000414FE0|App.exe |0000000000014FE0|System              |                   |_FinalizeRecord                    |31577[18]  |
|00000020|04     |000000000014DD18|000000000040EF1E|App.exe |000000000000EF1E|System              |TObject            |CleanupInstance                    |16368[8]   |
|00000020|04     |000000000014DD58|000000000040ED3B|App.exe |000000000000ED3B|System              |TObject            |FreeInstance                       |16186[1]   |
|00000020|04     |000000000014DD88|000000000040F82E|App.exe |000000000000F82E|System              |                   |_ClassDestroy                      |17536[1]   |
|00000020|04     |000000000014DDB8|0000000000E9D8F8|App.exe |0000000000A9D8F8|Data.DB             |TField             |Destroy                            |4809[9]    |
|00000020|04     |000000000014DDF8|000000000040EE65|App.exe |000000000000EE65|System              |TObject            |Free                               |16255[5]   |
|00000020|04     |000000000014DE28|0000000000E9CB6A|App.exe |0000000000A9CB6A|Data.DB             |TFields            |ClearBase                          |4487[14]   |
|00000020|04     |000000000014DE88|0000000000E9CC46|App.exe |0000000000A9CC46|Data.DB             |TFields            |ClearAutomatic                     |4506[1]    |
|00000020|04     |000000000014DEB8|0000000000EB717E|App.exe |0000000000AB717E|Data.DB             |TDataSet           |DestroyFields                      |12782[1]   |
|00000020|04     |000000000014DEF8|00000000010BD795|App.exe |0000000000CBD795|FireDAC.Comp.DataSet|TFDDataSet         |InternalClose                      |3068[21]   |
|00000020|04     |000000000014DF78|00000000010F9C93|App.exe |0000000000CF9C93|FireDAC.Comp.Client |TFDAdaptedDataSet  |InternalClose                      |9628[2]    |
|00000020|04     |000000000014DFA8|00000000010FD3BD|App.exe |0000000000CFD3BD|FireDAC.Comp.Client |TFDRdbmsDataSet    |InternalClose                      |11018[3]   |
|00000020|04     |000000000014DFF8|0000000000EB631C|App.exe |0000000000AB631C|Data.DB             |TDataSet           |CloseCursor                        |12528[6]   |
|00000020|04     |000000000014E038|00000000010BD03E|App.exe |0000000000CBD03E|FireDAC.Comp.DataSet|TFDDataSet         |CloseCursor                        |2939[3]    |
|00000020|04     |000000000014E078|0000000000EB60BA|App.exe |0000000000AB60BA|Data.DB             |TDataSet           |SetActive                          |12480[22]  |
|00000020|04     |000000000014E0C8|00000000010B881C|App.exe |0000000000CB881C|FireDAC.Comp.DataSet|TFDDataSet         |SetActive                          |1581[7]    |
|00000020|04     |000000000014E0F8|0000000000EB5DB1|App.exe |0000000000AB5DB1|Data.DB             |TDataSet           |Close                              |12431[1]   |
|00000020|04     |000000000014E128|000000000124D247|App.exe |0000000000E4D247|SQLiteConnection    |TSQLiteConnection  |GetFileCountSeries                 |1582[8]    |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

I moved the relevant calls to the main thread using TThread.Synchronize, which resolved the issue.