Knowledge Base

Optimistic saving of data

Whoever saves last has won. That’s how most database applications work, and that’s how Firedac is set up by default.  It doesn’t however check whether the data has changed in the meantime, and this is common with many database applications. Sometimes you come across applications with an elaborate customised system to tackle this problem, but Firedac has an option with which you can easily avoid this, namely UpdateMode in the UpdateOptions.

There are three possible settings that solve this problem via the WHERE clause of the update query.

  • upWhereKeyOnly: only the primary key fields are used in the WHERE. This is the default setting. As a result, the latest commit always overwrites the data of the previous commit with potentially obsolete data.
  • upWhereChanged: the changed fields are added to the query as a condition, together with the primary key fields. This way, the update succeeds only if the changed fields in the database still contain the original value.
  • upWhereAll: all fields of the record are put in the WHERE with their original value. This means the update succeeds only if none of the fields have been modified in the database. This is the most restrictive form.

If an update fails, Firedac raises an EFDException. The handling of this is then at your discretion though, however it easily prevents data from being overwritten – to the surprise of users.

Of course, you can also solve this problem with locking, although there are differences. With locking you depend on what database supports this, and the disadvantage of locking is that no one else can write in the table or record as long as you have locked it. And depending on your settings, not even read it. This often means that users have a poor performance experience, because queries are waiting for records to be released.

With the UpdateOptions.UpdateMode, this can be handled in a relatively simple way in an existing application. If you set the option at connection level, it applies to all commands by default, but you can also start to set this per query or table. This also allows a gradual approach.

Written by Kees de Kraker
Director

Contact

Let us help you to realise your ambitions

GDK Software UK

(+44) 20 3355 4470

GDK Software USA

+1 (575) 733-5744