How to prevent "updating" unchanged date using Data Mappers?

Question

In some books the implementation of Data Mappers simply update the whole row of a table using the data inside an object, but in a system is possible that two different operations update different fields from a same row in a database, which can make an update simply overwrite another one if both of them retrieve the old version of the row and try to update it. How can I solve this problem? I thought about using an Identity Map inside my Mapper and store the state of the object when they get to the application from the database and just update the fields that changed. Is this a good solution or am I overlooking something?

Answer 1

Normalization

If the system should be able to update different data in one row of your table then your schema may be not in a proper normalization level. What you say with with that is: The data you are manipulating is not in the same conflict domain. The question is: Why is data that does not reside in the the same conflict domain is put together in one table?

Conflict domains

Additionally it seems to me that the system you work with properly defined conflict domains were not considered.

You have to think carefully about what data may be manipulated independently from others so data consistency is possible to be preserved.

Locking mechanisms

If you are sure what kind of so called monitors you have to introduce you have to decide which kind of locking mechanisms you want to use. There are two possibilities: pessimistic and optimistic locking.

Pessimistic locking: You prevent other processes to enter the monitor until the current process leaves it.

To achieve pessimistic locking you have to define a mutex to be aquired before a process wants to enter the monitor.

Optimistic locking: You allow other processes to enter the monitor with an isolated view on the system state. But you prevent other processes to publish their data before leaving the monitor if there are conflicting modifications identified.

To achieve optimistic locking you have to define a version identifier that is selected before a process enters the monitor and compared before the process can publish its data.

Composite or Separation

As I do not know the business requirements there are two possibilities:

If there is only ONE conflict domain then you have to either defining ONE mutex for both data fields or ONE version identifier for both datafields.

If there are TWO conflict domains then you have to either defining ONE mutex for EACH datafield or ONE version identifier for EACH datafield.

Technical implementation

In the context of databases your monitor is the TRANSACTION. The problem is, that the things you have to consider during implementation depend on the concrete possibilities AND current configuration of your database.

Transaction behaviour and view isolation may be different. In MySQL there are for example different table types that give you different assertions on transaction behaviour.

As it it impossible to me to open a discussion for ANY technology and ANY system configuration I'll have a try with the most common one.

Assuming you have DIFFERENT conflict domains (changing both data in a row simultaniously will not cause an inconsistent state) you can either introduce TWO version identifiers you have to select before and use at the end of your transaction to compare and increment the version. Therefore you have to have TWO data mappers.

UPDATE tableX SET version=v + 1 WHERE version = v;

Or you want to have pessimistic locking with ONE or TWO mutexes, depending on the database supported mechanisms. If your database only supports row locking you only need ONE mutex. This is often done by following:

SELECT id_column FROM tableX WHERE id_column=id_to_lock FOR UPDATE

To the core: Lost update problem

Your problem may be solved if you use two Data mappers that will update different parts of a row.

Additionally you can normalize your schema to a higher normalization level.

If you want to keep your data mapper (you implicitly assume ONE conflict domain) AND you want to prevent LOST UPDATES you only have the chance to introduce a version identifier. You then implicitly use optimistic locking.

The point is that optimisitic locking uses OTHER information to identifiy conflicting modifications within a conflict domain. While pessimistic locking says "Do not enter until I am ready!" optimistic locking says "You may enter BUT you cannot publish your data if the data version proceeded.". So you cannot prevent LOST UPDATES with pessimistic locking alone.

Answer 2

The usual procedure for syncing rows in a database would be to initiate a transaction session. Before updating your row you read a second instance of the same row from the database (during the transaction session to avoid changes in the meantime) and compare any column's data for changes. As comparing any column can be a performance intensive task especially when a table consists of a lot of columns use of timestamps is recommended (at first retrieve the timestamp only!) to know in front if it's necessary to retrieve the whole row again. The row with the more recent timestamp overwrites changes to the (same!) local, in-memory row but be aware that there can be scenarios (eg: concurrent data conflicts) in which the user needs to be asked if that is really intended!

For a row three(!) instances with a timestamp column are necessary to know which one is the most recent. Keep in mind to derive your timestamps from a unique time zone (eg: UTC!)

• 1st timestamp is taken from the row itself, it may get refreshed when edited in memory and your row state changes from UNCHANGED to MODIFIED.
• 2nd timestamp is a duplicate of the first (at the time when the row has loaded into memory and is still UNCHANGED)
• 3rd timestamp is taken from the 2nd instance of the row loaded during the transaction session.

Table of conclusions:

1st != 2nd: your local, in-memory row has changed (an update is necessary!)
2nd != 3rd: in the meanwhile the row has changed in database
 1st > 3rd: your local row is more recent than the one from the database
 3rd > 2nd: the row from the database is more recent
 3rd > 1st: the row from the database is even more recent then your modified in-memory row (!)
2nd == 3rd: comparing data not necessary, simply update your local row to your database (the case you were looking for?!)

This way you can determine the destination row where to copy column's data or at least request validation by a human. Once all columns are processed and your row got updated you can finalize your sync by committing your transaction to the database.