Optimistic Locking with one Timestamp?

A reader called chris asked me a good question about optimistic locking: why not use a “user-defined” scn (e.g. timestamp) as an extra column to achieve the same goal as ORA_ROWSCN? Well, chris, you’d have to use one per row, not one per transaction.

The Context

In this series of posts, I am assuming a two-phase API:

  1. The first API queries data and sends it to the user.
  2. The second API receives changes from the user to be applied to the database.

The goal is to avoid “lost updates”, by refusing to apply the changes if they would wipe out changes made by others between step 1. and step 2.

Update time and commit time

To really change a row in the database, we have to update it and commit. If we roll back instead, it’s as if the update never happened. What’s more, the update is one statement and the commit is another, so they do not occur at exactly the same moment. In the example I have been using to demonstrate, there is even an overlap: two sessions try to update the same row at about the same time, and the first commit only happens after both UPDATE statements have been issued.

What this means is that “update time” is not the same as “commit time”.

Which time can we control?

Let’s say we add a column called CHANGE_TS to our table, and we set it to SYSTIMESTAMP every time we update a row. That column will contain the “update time”, not the “commit time”. On the other hand, when Oracle updates ORA_ROWSCN the change has been committed, and the SCN is equal to (or possibly greater than) the SCN of the transaction. In other words, ORA_ROWSCN reflects “commit time”.

One SCN vs. one timestamp

The SCN is “a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.” To repeat: the SCN is a point-in-time version number for all committed data in the database.

If we send read-consistent data to the user, one SCN is enough to identify the “version” of that data. If that same SCN comes back with data to be updated, it is enough to determine whether a row has been updated by someone else in the meantime. Of course, we don’t know which column(s) have been updated, or whether the values have really changed – but the SCN is enough to double-check if we want.

  • We have the data the user wants to change.
  • We can get the current committed data with a normal query.
  • We can get the data we originally sent to the user with a flashback query “as of” that same SCN.

By comparing the data of interest, we can determine what values, if any, have actually changed in the meantime. If we want to go this far, we do have to write and test the code, but it will only execute for the rows whose SCN has actually changed. Most of the time, the SCN will not have changed and the update will take place efficiently.

If we use a TIMESTAMP column, we cannot send just one “current” timestamp to the user.

  • At the time of our query, there could be uncommitted data with slightly older timestamps, and our query would not see that data.
  • While our user is pondering his screen, that data gets committed.
  • When our user sends back new data with that timestamp,
    it would not see any of the changes that were updated before our query but committed afterwards.

Here is a scenario to illustrate.

BATCH MILLER
drop table emp_ts purge;
create table emp_ts as select e.*, systimestamp change_ts from emp e;

– E1: SMITH submits a batch job for 10% raises

update emp_ts set sal = sal * 1.1, change_ts = systimestamp;

— E2: MILLER queries SMITH and sees committed data

select empno, sal, deptno, systimestamp current_ts from emp_ts
where ename = ‘SMITH’;

EMPNO SAL DEPTNO CURRENT_TS
7369 800 20 2017-07-02 05:21:02.640271 -04:00
– E3: MILLER reassigns SMITH: update waits for batch job to commit

update emp_ts set sal = 800, deptno = 30, change_ts = systimestamp
where empno = 7369
and change_ts <= ‘2017-07-02 05:21:02.640271 -04:00’

– E4: the batch job commits

commit;

-The application sees “1 row updated.”
– E5: MILLER commits

commit;

– E6: SMITH discovers he was the only one who didn’t get a raise!

select empno, sal, deptno from emp_ts
where ename = ‘SMITH’;

EMPNO SAL DEPTNO
7369 800 30

Conclusion: one timestamp is not enough

We have seen that comparing user-defined timestamps to just one overall “current” timestamp does not always work. If you use timestamps, the “query” API must send a timestamp for every table row involved in the query, and the user must send back a timestamp for every row to be updated. The user-defined timestamp becomes a version number for that row.

The SCN, on the other hand, is a version number for the entire database, so passing just one back and forth is enough to signal any number of “lost updates”. We can provide APIs (using JSON for example) that completely hide the table / row organisation of the data in the database!

Remember, I am assuming a two-phase API:

  • The first API queries data and sends it to the user.
    • With timestamps, every row sent to the user has to include its timestamp.
    • With SCNs, only one SCN needs to be sent.
  • The second API receives changes from the user to be applied to the database.
    • With timestamps, the user sends back the timestamp of each row to be updated, and for each row the API compares the returned timestamp to the current timestamp.
    • With SCNs, the user sends back one SCN, and for each row the API compares its current SCN to that one SCN.
Advertisements

7 thoughts on “Optimistic Locking with one Timestamp?

  1. A great explanation Stew. There are variations, of course, that are of educational value if nothing else:

    – The programmer may maintain a single timestamp for the whole table but then changes to any one row in the table would be considered in conflict to changes made to any other row

    – At the other extreme the programmer may maintain a seperate timestamp for every column in every row (i.e. doubling the number of columns). Now one user could update one column in the row and another could update another column in the same row and these updates would not be in conflict with each other!

    The need for one user timestamp per row is analagous to the need for ROWDEPENDENCIES when using ORA_ROWSCN as I failed to realise in your earlier post!

    One last thing: I think there may be a typo in your post: you reference 5:18 a.m., shouldn’t it be 5:21? Or am I having another “senior” moment?

    • Hi Tony,

      Thanks much for pointing out the typo. The two timestamps should be identical. I have corrected the text.

      You said: “The programmer may maintain a single timestamp for the whole table but then changes to any one row in the table would be considered in conflict to changes made to any other row”.

      There would have to be another table with one row that maintains that single timestamp, and updates would have to serialize on that one row. Bad idea.

      You said: “At the other extreme the programmer may maintain a seperate timestamp for every column in every row (i.e. doubling the number of columns). Now one user could update one column in the row and another could update another column in the same row and these updates would not be in conflict with each other!”

      Sounds complicated!

      You said: “The need for one user timestamp per row is analagous to the need for ROWDEPENDENCIES when using ORA_ROWSCN as I failed to realise in your earlier post!”

      The analogy is imperfect. You really need two timestamps per row: the timestamp from the original query (sent to the user then sent back) and the timestamp in the row during the update. If the two are not identical, the update should not happen.

      With the SCN, you need ONE SCN from the original query, and the ORA_ROWSCN in the row during the update.

      The “one versus many” has to do with the data sent to the user in the first place, not the data stored in each row.

  2. I can see E2 happening in an application with a typical end-user query. But before E3 happens, #1, why isn’t the application doing another select FOR UPDATE before issuing an update or if not, #2 why is it issuing an update on the salary column when the intent is to reassign to a new dept_no (which I don’t see actually changing in the update statement)? Are we making things complicated when the real problem is application design?

    • Hi pnosko and thanks for your reply.

      I made a mistake in E2 : SMITH starts out in deptno 20, not deptno 30. That is why you don’t see the deptno changing. I fixed the text.

      #1 : Doing a SELECT FOR UPDATE doesn’t help avoid lost updates, it just makes more work for the database. How do you see SELECT FOR UPDATE helping?

      #2 : I agree there is a widespread problem of application design. Because of frameworks like Hibernate, or simply to provide generic APIs, applications tend to update more columns than they need to. My scenario is an illustration of this.

      Are you saying that with ideal application design there can be no lost updates? If lost updates are possible, the illustration I use is less important than the solutions I talk about.

  3. #1 Selecting a row for update locks it while getting you the currently committed data so you know it *was* free for update and now is no longer free. Your lost update scenario is not a database problem, it’s an application problem. You don’t update a record, sending back all column values that you retrieved with locking because you won’t know when they became outdated.

    #2 Too many Java folks never grew up understanding databases. Many see the database as a simple data store. They do too much work on the middle tier IMHO.

    • Let me get one thing straight: we are talking about “optimistic locking”. With “pessimistic locking”, you do a SELECT FOR UPDATE to query the data, then an UPDATE to update it. There can be no lost updates.

      In modern web applications, with stateless connection pools to the database, “pessimistic locking” is not possible. The session that queries the data in the first place will almost never be the same as the session that updates the data.

      Now to get around your objection about application design, let’s say both users update the same column in the same row:

      – Smith updates his salary (locking the row)
      – Miller queries the old, committed salary (because writers don’t block readers)
      – Miller updates Smith’s salary (actually waits to lock the row)
      – Smith commits (which releases his lock)
      – Miller’s update replaces Smith’s update.
      – Miller commits.

      Smith’s update is “lost”. Do you agree?

      Now let’s say:
      – Smith updates his salary (locking the row)
      – Miller queries the old salary
      – Miller does a SELECT FOR UPDATE (actually waits to lock the row)
      – Smith commits (which releases his lock)
      – Miller updates Smith’s salary. His update replaces Smith’s update.
      – Miller commits.

      Smith’s update is still “lost”. The SELECT FOR UPDATE did not prevent an update that happened beforehand, but was not committed.

      Now, that is only one possible scenario for lost updates. What happens more often:

      – Miller queries the old salary
      – Smith updates his salary and commits
      – Miller updates Smith’s salary and commits.

      With “optimistic locking”, we prevent lost updates by checking that, between the time the user queried the data and the time (s)he submitted the update, the data the user wants to change was not already changed by someone else.

      Best regards, Stew

      P.S. Just to be clear, I don’t see you arguing for or against any “optimistic locking” method, including mine. I read you as rejecting the need for optimistic locking, no matter what the method.

  4. I will admit having been ignorant of pessimistic vs. optimistic locking and read up on it. I agree with your conclusion. If your app design can’t use pessimistic locking and has different processes and/or different users dedicated to updating different columns in a row, you will need to filter the updates on the original value and handle it when the update affected 0 rows. You may even consider a physical design change to move column(s) frequently updated by batch processes to a separate table to get it out of the way of “online” updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s