FireDAC and locking 1

[Griffyn]

Hi all,

Still getting to grips with FireDAC. My current issue is how quick it is to give up when executing a TFDCommand and raise an exception about the table being locked.

The app in question has a thread that will update a record in a table (MSAccess). It uses a copy of the TFDConnection object to avoid multi-threading issues.

Meanwhile, the main thread has a TFDQuery open on the same table. I've set LockMode := lmPessimistic, and LockWait := True in the TFDConnection object, but I get frequent exceptions from the TFDCommand when it tries to execute. A solution I've tried that seems to mitigate the issue is to wrap the Execute in a loop ie.

procedure TFDCommandEx.SafeExecute(ARetries: Integer);
var
  c : Integer;
begin
  for c := 1 to ARetries do
    try
      Execute;
      Break;
    except
      on E: Exception do
      begin
        Logger.LogErr('TFDCommandEx', E);
        if c >= ARetries then
          raise
      end;
    end;
end;

This seems crazy. Surely I'm overlooking something. Isn't locking in FireDAC supposed to work as critical sections would amongst threads? If locking doesn't work properly, I'll have to manually handle it with critical sections.

Or - is the problem with MS Access, and should I porting to something like SQL Server?

 

[whosrdaddy]

MSAccess has always been a troublesome product when it comes to multiuser environments.
SQL Server/MySQL/PostGreSQL are better fit for this scenario.

/Daddy

-----------------------------------------------------
Helping people is my job...

 

[DjangMan]

Plus you can get SQL Server Express for free and it will out perform MS Access on almost all fronts.

 

[Griffyn]

Thanks guys,

I ended up trying out Firebird - Embedded. But then discovered that it locks the database file so there's no multi-user/application access. Otherwise that would be perfect.

SQL Server Express looks pretty good, and unless I can find a flat file database similar to Microsoft Access without the annoyances like poor locking and filesize bloat that's what I'll probably go with.

 

[whosrdaddy]

SQLLite seems to be perfect in these situations.

/Daddy

-----------------------------------------------------
Helping people is my job...

 

[Griffyn]

Just reading the FAQ on SQLite now. Bit weird that regardless of field data type, you can put in any type of data you like. And the part about SELECT queries not guaranteed to return the actual column names unless specified is bizarre.

Looks interesting though.

 

[whosrdaddy]

Ah forgot about the multiple user requirement.
SQLLite supports it, but it locks the complete db on writes.
So performance can be a problem if you need to write a lot.

/Daddy

-----------------------------------------------------
Helping people is my job...