INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

FireDAC and locking

FireDAC and locking

(OP)
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.

CODE

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?

RE: FireDAC and locking

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...

RE: FireDAC and locking

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

RE: FireDAC and locking

(OP)
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.

RE: FireDAC and locking

SQLLite seems to be perfect in these situations.

/Daddy

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

RE: FireDAC and locking

(OP)
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.

RE: FireDAC and locking

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close