Newsgroup Nugget: Row-level locking with FireDAC

Here is a recent newsgroup post asking about Row-Level locking using FireDAC to talk to SQL databases. Enjoy!

Emery asks:

I’m working on a project right now that’s presently using FireDAC to connect to PostgreSql, but we are planning on expanding the possible backends to include MS/SQL, MySQL and Oracle as well (with possibly other, not yet identified, alternatives as well). PostgreSql may or may not be in our long-term plans.

One of the single most important requirements that I have is the ability to perform row-level pessimistic locking as effortlessly as possible within the application. I would like to, if at all possible, have many queries read the database asserting minimal locks and have specific queries assert atomic row-level locks on a single record identified by the primary key that will prohibit other users of the same system from writing to the same record at the same time (and, strictly as a matter of convention, from adding or deleting child records of the locked parent records as well). I do not want to block other users from reading the locked data with the understanding that a user will be possibly seeing stale data at the time of the read).

What specifically must I do to do so with your components? Are any of the mechanisms dependent in any way on the specific backend that I am connecting to (that is, in terms of the SQL that I must create on the front-end in order to connect)? In other words, can I safely have the exact same SQL used to connect to any of the above backend databases or will I have to generate SQL that is dependent on the specific database to which I am connecting? If the SQL has to vary, can you identify for me what the SQL must look like for each of the above backends?

Please provide as much detail as possible. The right database connectivity solution is terribly important. I want to give FireDAC the full consideration it deserves in making the right decision for this project.

Thanks, Emery Galambos

Dmitry replies with the relevant FireDAC DocWiki links:

• http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDUpdateOptions.LockMode
• http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDUpdateOptions.LockPoint
• http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDUpdateOptions.LockWait

All of these properties control only automatic data editing: FDQuery.Edit / Post. They do not have influence on FDQuery.ExecSQL('UPDATE ...'), etc.

With best regards, Dmitry