Optimistic Locking 6: which ORA_ROWSCN?

Continuing my series on using ORA_ROWSCN to achieve optimistic locking, let’s zoom in on ORA_ROWSCN itself. I’ve been asking myself this question:

  • ORA_ROWSCN can be maintained at the block level or the row level. What is the difference and which one should we use?

Previously in this series

I want to implement optimistic locking in a two-phase database API:

  • The “query API” queries data and sends it to the user.
  • The “update API” receives changes from the user to be applied to the database.

Here is how ORA_ROWSCN fits into the overall scenario:

  1. Client calls the query API to display data to the user.
  2. The query API returns the data for the user.
    1. All the rows that might be updated are SELECTed here.
    2. The SELECT ensures that ORA_ROWSCN will be less than or equal to the “read-consistent SCN”.
    3. The data is read-consistent, and the “read-consistent SCN” is returned along with the data.
  3. Client calls the update API to update the data.
    1. The “read-consistent SCN” from step 2. is sent back along with the updates.
  4. The update API compares each ORA_ROWSCN to the”read-consistent SCN”.
    1. If any ORA_ROWSCN is greater, then some intervening update has occurred
      and the update cannot continue. All lost updates have been avoided.

I’m going to zoom in on the statements in bold red (2B and 4). How they work depends on whether ORA_ROWSCN tracks the SCN at the block level or the row level.

Block Level or Row Level?

By default, ORA_ROWSCN reflects the “commit SCN” of the most recent change to any row in a block. If a table is created with ROWDEPENDENCIES, the size of each row is increased in order to store an SCN with each row. We can see the difference with this simple illustration:

SQL> create table t as select level n from dual connect by level <= 2;

Table T created.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949719
 2 7949719

SQL> update t set n = 3 where n = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949731
 3 7949731

SQL> drop table t purge;

Table T dropped.

SQL> create table t rowdependencies as select level n from dual connect by level <= 2;

Table T created.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949749
 2 7949749

SQL> update t set n = 3 where n = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949749
 3 7949760

As you can see, with block-level tracking every row in a block has the same ORA_ROWSCN, even for rows that were not changed in the most recent transaction.

Optimistic Locking with block-level SCN

In my previous post on the SCN, I was working with block-level SCNs. We saw that after a SELECT, all the data blocks that were read consistent now have ORA_ROWSCN values no greater than the read-consistent SCN. This is exactly what statement 2B above is saying.

Unfortunately, for statement 4 things are not so simple. If one row in a block has just been changed, the ORA_ROWSCN will increase for the other rows as well. When my “update API” checks one of the other rows, it will say the row has been updated when in fact it has not.

As a result, the SCN comparison will always detect updated rows, but it will sometimes say a row was updated when it was not. We probably need some confirmation before saying the row was truly changed.

Optimistic Locking with row-level SCN

If we really want to depend on the SCN comparison alone, we should create the table with ROWDEPENDENCIES. There are some limitations when we do this: for example, table compression is not possible. The advantage is that our “statement 4” is now true: the ORA_ROWSCN of a row will only increase when that row was updated in a more recent transaction.

As I mentioned in my previous post on the SCN, it is possible that a row-level ORA_ROWSCN will be greater than the block-level SCN used to get read-consistent data. However, it is hard to imagine a real-life scenario where the row-level ORA_ROWSCN would be greater than the “read-consistent SCN” used in my query API.

Which ORA_ROWSCN should we use?

It depends, of course.

  • If we are free to create our tables with ROWDEPENDENCIES, and if it is enough to detect conflicting updates at the row level, then comparing ORA_ROWSCN to the “read-consistent” SCN should be sufficient for our needs.
  • If we are updating tables without ROWDEPENDENCIES, or if we want to detect real changes to values in the row,
    • then ORA_ROWSCN will tell us which rows have certainly not changed, so we can update them directly.
    • For the small number of rows that might have changed, we need to write code to check the values.
      Even then, our “read-consistent SCN” will help us do that – as I’ll explain in a later post.

Next up: Tom Kyte was wrong – about being wrong! ;)

 

Advertisements

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