Avoiding Lost Updates: Protecting Data in a Multi-user environment

There is a particular problem with Oracle and other databases where access to data is not serialized (by default), and there are a number of ways this problem is generally dealt with. The problem crops up when you have many users, some of whom may wish to update the same data at around the same time. The process that takes place when a user wishes to update a row is:

Session #1:

  1. Read the data from the database into the client memory.
  2. Make the changes in the client memory.
  3. Write the changes from the client memory to the database.

Between steps 1 and 2 another session (#2) might read the same data (unless you’re in a particularly poorly designed database in which writers block readers; e.g. one database I helped to refactor did just that – it issued exclusive locks on rows every time they were read into a form(!) – this was the first thing we got rid of in the new system), and modify it.

The row isn’t locked, so the other user makes their changes and clicks their “Save” button which commits the changes to the database. Meanwhile, session #1 finally gets to step 3 and saves their changes. If no checks happened, they would overwrite the changes made by session #2.

To illustrate:

  • Session #1 reads the row “Joe Bloggs”.
  • Session #2 reads the row “Joe Bloggs”.
  • Session #2 changes the row to “Jane Bloggs” and saves.
    – now, Session #1 is still looking at “Joe Bloggs” on their screen.
  • Session #1 changes the row to “Joe Jones” and saves.

The problem is that Session #1 has unwittingly undone the change that Session #2 made.

There are a number of ways this problems is approached that I know of:

  1. “Last Update Wins” – just allow the update.
  2. “Column Compare” – all the columns to be updated are compared when updating, and if any are different to the values originally read, an error is returned (e.g. Oracle Forms does this and issues “FRM-40654: Record has been updated by another user. Re-query to see change.”).
  3. “Hash Compare” – this is a variant on the “Column Compare” method used by HTMLDB. It compares a hash of the combined data for the row.
  4. “Timestamp Compare” – the table has a “timestamp” column, which is automatically set to the last date/time when the row was inserted or most recently updated. An update will only succeed if the timestamp of the updated row is the same as the timestamp originally read.
  5. “Version Compare” – the table has a “version” column, which is set to 1 on insert and incremented on update. An update will only succeed if the version of the updated row is the same as the version originally read.

Note: all the “Compare” approaches above do not necessarily require a second query-and-lock on the row prior to the update; the best way to implement these is to add the compare as a predicate for the update – then, if no rows are updated, the caller can tell that the update failed (because the row had been updated or deleted by another session).

Comparison

  1. “Last Update Wins” – This approach may be acceptable for some applications.
  2. “Column Compare” – If only the updated columns are compared, this method means that two sessions can modify different columns in the same row independently.
  3. “Hash Compare” – This method stops two sessions modifying the same row, even if they’re touching different sets of columns.
  4. “Timestamp Compare” – This method requires the timestamp to have sufficient granularity so that a read by one session, followed by an update by another session, will not occur at exactly the same timestamp. Generally milliseconds or microseconds are needed at least, although even this granularity might not be sufficient for some situations. This method also stops two sessions modifying the same row, even if they’re touching different sets of columns.
  5. “Version Compare” – This is a favourite for Java developers and works very well. It is simple to code, computationally cheap, and doesn’t suffer from the timestamp granularity problem. This method also stops two sessions modifying the same row, even if they’re touching different sets of columns.

From the above comments you might think I prefer #5 (Version Compare) but I think it can depend on the requirements of the system you’re working with. Of course, if you’re using Forms or HTMLDB you don’t need to worry about it too much because it’s been largely taken care of for you.

In my next entry I describe how this works when you’re working with updateable views.

Further Reading


SQL problem

Came across this SQL problem in Eddie Awad’s blog, where he gives a solution to a problem posted to the Oracle-l mailing list.

I thought, this would be so much easier to solve if we were allowed to model the complete problem domain in the database, e.g.:

— create the data set

SQL> create table t (id number);
Table created.

SQL> insert into t values (1);
1 row created.

SQL> insert into t values (2);
1 row created.

SQL> select * from t;
ID
———-
1
2

— create the query set

SQL> create table u (id number);
Table created.

SQL> insert into u values (1);
1 row created.

SQL> insert into u values (2);
1 row created.

SQL> insert into u values (3);
1 row created.

SQL> select * from u;
ID
———-
1
2
3

SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
no rows selected

— correct: the complete query set was not found in the data set

SQL> insert into t values (3);
1 row created.

SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
ID
———-
1
2
3

— correct: the complete query set was found in the data set

The query could be further improved by changing the “where id in ()” to a “where exists ()”.

To make the query table usable by multiple sessions simultaneously, it can be created as a global temporary table (and populated whenever a query is needed), or a “group” distinguisher could be added to it.


Instead-of-Delete trigger problem

In software development, quite often the problem you encounter has virtually nothing to do with the thing you’re trying to do.

I’m creating some views which will provide an interface to a database. The views will have Instead-of triggers so that all DML will be passed to my own (packaged) procedures.

I’ve got a particular view which is a join between a base table, filtered by some criteria, and a code/description mapping view. I’ve implemented instead-of-insert, update and delete triggers that do the appropriate DML on the base table.

Inserts and updates work fine, but when deleting from the view I’m getting “TM contention” wait events. When deleting across a database link the session gets “ORA-02049: timeout: distributed transaction waiting for lock” after a long delay. If the delete is issued within the same database as the view, the session waits indefinitely for the lock.

I had a look at the view and noticed that it is a key-preserved view, so I theorised that maybe the session was locking the row before running my instead-of-delete trigger that does the delete; the trigger tries to delete the row that has already been locked. Doesn’t make sense. When I remove the instead-of-delete trigger however, I get “ORA-01752: cannot delete from view without exactly one key-preserved table”. So I need the trigger to handle the delete.

Looked up the Oracle doc “Instance Tuning Using Performance Views” about “TM enqueue”: “The most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.” I saw this ref come up in a search early on but I initially dismissed it because my problem wasn’t related to performance. I’m glad I took a second look.

Righty-o. Looked at the table I was deleting from, and lo and behold there is another table with a fk to my table with no index on the fk column (mind you, there was an index that contained that column along with another column, obviously the database wasn’t going to use that to avoid the table lock).

When I created an index on the fk column, the problem was resolved. Brilliant!

The application never needed to delete from that table before, so this had never been encountered. In this case adding the index solved the problem. It’s on a very small, rarely-used table so the index won’t have a negative impact. Took about half an hour to work out, but I learned a bit more about Oracle, so that’s good.