Protecting the integrity of your data

Application systems are all about data – reading and writing data. The utility of any system is, at its core, dependent on whether the underlying data is correct. Garbage (data) in, garbage (results) out.

Databases are sophisticated pieces of software designed to protect the correctness, or integrity, of the data they hold. You have to be sure that once you enter data into the system, it is stored and available consistently.

Protecting data integrity becomes much more challenging when multiple users are trying to access the same data – especially when more than one user is trying to update the same record.

Traditional databases protect against this problem by using a system of locks, which prevent anyone from modifying data when others are using it. This solution presents a number of problems in an on-demand environment, since the data interactions can take place over a long period of time. You can’t risk a potential loss of data integrity, regardless of how many users your on-demand application is supporting.

The Summer 07 release of the platform provides an automatic way to address this issue. Whenever any user tries to update any piece of data, the database checks to make sure that the value of the data has not been changed since the data was read. If the value has been changed, the database returns an error and asks the user to refresh the data and try the update again. This feature has the descriptive name of Update Collision Detection.

The great news about this feature is that it comes automatically with the platform. The integrity of your data is protected without any work on your part. The even better news about the feature is the way that it adjusts to situations where the use of the feature may not be appropriate.

One of these situations has to do with the way that people user browsers, the standard client interface to applications. The browser has a Back button, which can recall earlier pages. These pages may include an earlier version of a record, and users frequently will simply go to a previous page to recall earlier data. Since the user is in control of this interaction, you would not want to bring up a Collision Detection error in response to this scenario – the user knows what they are doing.

To account for this, Update Collision Detection will not be used for multiple updates from the same user, so there would never be an error raised for multiple updates from the same user.

When Update Collision Detection detects an issue, the platform informing the user that there is a problem, and asks the user to take some remedial action. You may be thinking that this could create a lot of problems for data updates done using the Web Services API. If an update collision is detected, is the programmer supposed to add code to expose this choice to the user? And what about bulk operations?

To address this situation, Update Collision Detection is not enforced for API-based data interactions. There is not really any need to check for update collisions with API-based requests, since the request is executed in real time and the target of the request is protected during the write operation by locks.

At, the motto is “Make easy things easy and hard things possible”. The Update Collision Detection eliminates potentially serious problem of data integrity – automatically.

- Rick Greenwald