ODC Appreciation Day: Mike Hichwa’s team #ThanksODC

I don’t know Oracle’s internal organization, but I get the impression that people like Joel Kallman, Jeff Smith and Kris Rice work for or with Mike Hichwa. By naming him, I get to show appreciation for a bunch of technologies, and for the people behind them.

SQL Developer

95% of my time spent on Oracle is spent inside SQL Developer, and I probably use only 5% of its features. It’s free, gets terrific free “support” from an ODC forum and from Jeff Smith’s blogumentation.

Database Virtual Box Appliance

As a developer, not a DBA, I gave up rolling my own database VM and now happily use Kris Rice’s prebuilt appliance, with just a few tweaks for a Macbook Pro with a french keyboard.

APEX

Another “freebie” that blows me away. I never got a chance to use it in my job, so last year I volunteered to develop a web application for my daughter’s medical school. With no training, no knowledge of Javascript and a jump-start from Richard Martens (thanks again!), I made every deadline and delivered every requirement including some I added myself!

APEX provides so much functionality that web developers would otherwise have to reinvent, both for users and for the developers themselves. Again, I probably use 5% or less of the features provided.

The Attitude

Having worked for an IT supplier and IT customers, I favor a win / win attitude. Way too often, Oracle Corporation projects an “I win, you lose” mentality: Oracle competes with everybody, including its customers, the DBAs who make its products work and the developers who make its products worthwhile:

  • Base your technical architecture on our non-contractual licensing rules or face an audit.
  • Buy Autonomous and reduce labor costs for operational DBAs.
  • Buy InMemory and get your results so much faster “without changing a single line of code”.

Since I do all my Oracle-related activity for free, it’s frustrating to promote a technology from one of the least-loved IT companies around.

That is why the attitude of all the folks behind these “no extra cost” tools makes me feel so good. They clearly want people who use their stuff to win big and have fun doing it. Communication with them is a two-way street with fast lanes, so their “products” evolve quickly and in useful directions.

To all you folks, whether I’ve met you or read you or just enjoyed the fruits of your labor, my appreciation today and every day…

Advertisements

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! ;)

 

Optimistic Locking 5: “Read-consistent” SCN

Continuing my series on using ORA_ROWSCN to achieve optimistic locking, let’s zoom in on the query API and the “read-consistent” SCN of the data it returns. I’ve been asking myself these questions:

  • How do we get read-consistent data in the query API if it does more than one SELECT?
  • How do we know the exact value of the “read-consistent” SCN?

Previously in this series…

Suppose you and I query the same data at the same time, then you update it, then I update it. If I never know you changed the data, my update will overwrite yours and your update will be “lost”. This is a danger of stateless applications that cannot use SELECT FOR UPDATE to prevent write conflicts. “Optimistic locking” checks the data when I update it to make sure no one else has changed it in the meantime.

My solution implements a two-phase database API:

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

This post concentrates on the query API, making sure the data returned is read-consistent and determining the “read-consistent” SCN of the data.

Read consistency

In Oracle, queries are “read consistent”, which means “The data returned by a query is committed and consistent for a single point in time.” This point in time is defined by an SCN. I’ll call that SCN the “read-consistent SCN”. By default Oracle uses the SCN that was current when the query started, but within read-only or serializable transactions it uses the SCN when the transaction began.

If the query API does only one SELECT, then the data is automatically read-consistent, with one exception: if the SELECT calls a PL/SQL function, then any queries within that function may use a later SCN as a starting point.

To ensure read-consistency, the query API must either:

  • do one and only one SELECT (avoiding any queries within functions), or
  • set the transaction READ ONLY (and of course COMMIT at the end of the API.
  • (I won’t go into flashback queries because they cause extra parsing and fill the shared pool with child cursors.)

Read-consistent SCN

Providing read-consistent data in a query API is not hard if we pay attention to what we are doing. Getting the “read-consistent SCN” is another story.

Let’s start by just getting the current SCN. I know of three methods:

  1. timestamp_to_scn(systimestamp) : this method is not precise and should be avoided.
  2. Accessing V$DATABASE: this method works but advances the SCN. It also requires read access to V$DATABASE.
  3. Using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER: this method works, does not advance the SCN, but requires the EXECUTE privilege on DBMS_FLASHBACK.

I strongly recommend the third method (especially since we are going to need DBMS_FLASHBACK for other reasons).

Let’s suppose our query API has just one SELECT. When do we call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER? If we call it within the query, there is no guarantee that it will be called at the very beginning, so the result may be slightly later than the true “read-consistent” SCN. If on the other hand we get the SCN beforehand, the result may be slightly earlier.

What are the risks in each case? If within :

  1. The query determines the “read-consistent” SCN
  2. Someone else commits an update (which will be invisible to the query), so new SCN.
  3. The query calls DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER and gets the new SCN.
  4. When it comes time to check the data for “lost updates”, the change in step 2. will not be detected.

If beforehand :

  1. We call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
  2. Someone else commits an update, so new SCN.
  3. The query runs with the new SCN and sees the update.
  4. When it comes time to check the data, the change in step 2. will be detected and assumed to be a “lost update”, when in fact the change was visible in the query.

It is much better to call the function beforehand to avoid any undetected update, even though it introduces a very slight risk of announcing a write conflict where none exists.

If we really want to get the precise “read-consistent” SCN – or if the query API does more than one SELECT – we must use transaction-level read consistency. We can start a READ ONLY transaction and then get the START_SCN value from V$TRANSACTION:

create or replace function set_read_only_get_scn
  return number as  
  l_scn_trans number;
  l_local_trans_id varchar2(32);
begin
  set transaction read only;
  l_local_trans_id := dbms_transaction.local_transaction_id;
  select start_scn into l_scn_trans
  from v$transaction
  where l_local_trans_id = 
    to_char(xidusn, 'fm999999') || '.' ||
    to_char(xidslot,'fm999999') || '.' ||
    to_char(xidsqn, 'fm999999');
  return l_scn_trans;
end set_read_only_get_scn;
/

Using a READ ONLY transaction has several effects:

  • There can be no outstanding uncommitted changes. If there are, an exception is raised.
  • No DML or DDL allowed – which is fine in a query API.
  • Unlike with flashback queries, cursors in the shared pool are reused, whereas flashback queries cause multiple child cursors to be generated.
  • The query API must COMMIT before returning the data.

And the answer is…

In the query API, use a READ ONLY transaction to get fully read-consistent data from any number of SQL queries, and to obtain the exact “read-consistent” SCN used by those queries.

If the API does only one SELECT, then calling DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER just before the query is fine in my book, even though theoretically it might return an SCN very slightly lower than the real “read-consistent” SCN.

Next up: the two flavors of ORA_ROWSCN.

Footnote (2017-09-12)

I forgot to credit and thank Jonathan Lewis for the code that gets the “start SCN” of a read-only transaction.

Optimistic Locking 4: the Good

After discussing lost updates, the SCN and my theoretical use case, here is my idea for doing SCN-based optimistic locking. I’m going to simplify and emphasize the advantages here; limitations and gruesome details will come later.

Context

Here is the situation I am thinking of, before adding my solution.

There is an OLTP application. The “client” part is either a generic middleman like ORDS or a program written in java, PHP, node.js, etc. . The “client” accesses the database via SQL*Net. To support updates, the “data” part of the application provides two APIs in the database, one for querying the data in the first place and another for processing the updates. The query API returns read-consistent data, and the update API is atomic: either all the updates occur or none of them occur.

The dialogue between “client” and “data” uses generic connections and stateless sessions, so optimistic locking is the only solution for avoiding lost updates. I’ll start with a simplistic “lost update” requirement: if the “client” wants to update some rows, and someone else has already updated one of them between query time and update time, then the update must not take place.

In order to avoid the “object-relational impedance mismatch”, without frameworks and without being language-specific, I am going to use JSON as my data-interchange format.

The application scenario is:

  1. Client calls the query API to display data to the user.
  2. The query API returns the data for the user.
    • If any additional data is needed to support optimistic locking, the API has to return it here.
  3. Client calls the update API to update the data.
    • Any additional data from step 2. has to be sent back along with the updates.
  4. The update API uses the additional data to check for intervening updates during its processing.

Adding the SCN solution

My idea is based on comparing:

  1. The “read-consistent SCN”:  it determines what read-consistent version of the data is returned from a query.
  2. ORA_ROWSCN : a pseudocolumn that “reflects the system change-number (SCN) of the most recent change to a row.”

As I showed in an earlier post, the “SCN of the most recent change” may be an upper bound, but the query itself will adjust that upper bound downwards so it will not be greater than the “read-consistent SCN”.

Now I’ll add the SCN to my 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.

Advantages

I’m concentrating on the “good” here, since I want you to keep reading. Several things tempt me in this approach:

  • No special “version number” or “change timestamp” columns needed in the table.
  • No CPU spent doing checksums or hashes.
  • Only one number, the SCN, sent back and forth between the “client” and the APIs.
  • The data used for optimistic locking (the SCN) can be kept separate from the user data.
  • The “client” is freed from practically all implementation details.

If you base optimistic locking on row version numbers, row timestamps, or checksums of row columns, then the client code has to keep track of that data and send it back with the updates. With the SCN, the data from each row is just user data, not technical data. It can be pivoted, unpivoted, aggregated or denormalized. With JSON, it is trivial to separate the SCN from all the user data.

All the client code has to do is keep the SCN somewhere and include it in the call to the update API.

Example

My query API is going to get the current SCN, then query the DEPT table.

create or replace function queryAPI return varchar2 is
  l_scn number;
  l_result varchar2(4000);
begin
  l_scn := dbms_flashback.get_system_change_number;
  select json_object(
    'SCN' value l_scn,
    'DEPTS' value json_arrayagg(
      json_object(
        'DEPTNO' value DEPTNO, 
        'DNAME' value DNAME, 
        'LOC' value LOC
      )
    )
  ) into l_result
  from dept;
  return l_result;
end queryAPI;
/

Now my “client” (Oracle SQL Developer) will call the API:

var v_result varchar2(4000);
exec :v_result := queryAPI;
select json_query(:v_result, '$' pretty) from dual;


{
  "SCN" : 22422562,
  "DEPTS" :
  [
    {
      "DEPTNO" : 10,
      "DNAME" : "ACCOUNTING",
      "LOC" : "NEW YORK"
    },
    {
      "DEPTNO" : 20,
      "DNAME" : "RESEARCH",
      "LOC" : "DALLAS"
    },
    {
      "DEPTNO" : 30,
      "DNAME" : "SALES",
      "LOC" : "CHICAGO"
    },
    {
      "DEPTNO" : 40,
      "DNAME" : "OPERATIONS",
      "LOC" : "BOSTON"
    }
  ]
}

After changes to DNAME and LOC in the user interface, the client is going to send this data to the update API:

{
  "Action": "Update",
  "SCN": 22422562,
  "DEPTS": [
    {
      "DEPTNO": 10,
      "DNAME": "Admin",
      "LOC": "Seattle"
    },
    {
      "DEPTNO": 20,
      "DNAME": "Marketing",
      "LOC": "Toronto"
    },
    {
      "DEPTNO": 30,
      "DNAME": "Purchasing",
      "LOC": "Seattle"
    },
    {
      "DEPTNO": 40,
      "DNAME": "HR",
      "LOC": "London"
    }
  ]
}

In the update API, I’ll use JSON_TABLE to convert the JSON data to SQL rows and columns, then use FORALL to do the updates in bulk. For any row, if ORA_ROWSCN is greater than the read-consistent SCN then the update will not take place and I raise an exception. That is enough to roll back all the work done in that call to the API.

create or replace procedure updateAPI(p_json_input varchar2) as
  cursor c_input is select 0 SCN, deptno, dname, loc from dept;
  type tt_dept is table of c_input%rowtype;
  lt_dept tt_dept;
  l_rowcount number;
  c_dummy varchar2(1) := substr(p_json_input,1,1);
begin

  -- get data in SQL format. Every row will have the SCN
  select SCN, deptno, dname, loc bulk collect into lt_dept
  from json_table(p_json_input, '$'
    columns(
      action path '$.Action',
      scn number path '$.SCN',
      nested path '$.DEPTS[*]'
      columns(
        deptno number path '$.DEPTNO',
        dname path '$.DNAME',
        loc path '$.LOC'
      )
    )
  )
  where action = 'Update';

  -- Use FORALL to update in bulk
  forall i in 1..lt_dept.count
    update dept set dname = lt_dept(i).dname, loc = lt_dept(i).loc
    where deptno = lt_dept(i).deptno
    and ora_rowscn <= lt_dept(i).scn
    and coalesce(c_dummy,dname||loc) is not null;

  -- Check for row not updated due to ORA_ROWSCN change
  for i in 1..lt_dept.count loop
    if sql%bulk_rowcount(i) = 0 then raise_application_error(
      -20001,
      'DEPT row with DEPTNO = ' 
      || lt_dept(i).deptno 
      ||' already changed by another user. No updates have been made.'
    );
    end if;
  end loop;
end updateAPI;
/

Now suppose that, between the query and the update, someone else has updated department 10. In that case:

declare
  l_scn varchar2(100);
begin
  select json_value(:v_result, '$.SCN') into l_scn from dual;
  updateAPI('{"Action":"Update","SCN":'||l_scn||',"DEPTS":[
          {"DEPTNO":10,"DNAME":"Admin","LOC":"Seattle"},
          {"DEPTNO":20,"DNAME":"Marketing","LOC":"Toronto"},
          {"DEPTNO":30,"DNAME":"Purchasing","LOC":"Seattle"},
          {"DEPTNO":40,"DNAME":"HR","LOC":"London"}
          ]}');
end;
/

ORA-20001: DEPT row with DEPTNO = 10 already changed by another user. No updates have been made.
ORA-06512: at "STEW.UPDATEAPI", line 28
ORA-06512: at line 5

There: that wasn’t too hard, was it?

Too Good to be True?

I hope I have gotten the basic idea across and aroused your interest. In my next few posts, I’ll talk in more detail about limitations or possible objections and how to deal with them. Here are some of the issues:

  • How do we get read-consistent data in the query API if it does more than one SELECT?
  • How do we know the exact value of the read-consistent SCN? My example code is not perfect in that regard.
  • ORA_ROWSCN can be maintained at the block level or the row level. What is the difference and which one should we use?
  • Tom Kyte gave up on this idea because a change in ORA_ROWSCN does not “trigger a restart” of the update, see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2680538100346782134. How do we get the restart to happen when it should?
  • What happens if an update occurred but the columns we want to update did not change? How can we avoid “false alarms”?
  • How can we provide more information on the intervening update so the user can better decide what to do next?

More later!

Optimistic ORA_ROWSCN 3: the Use Case

My goal is to show how ORA_ROWSCN can help deal with the problem of “lost updates”. Before that, I need to explain the use case I have in mind: an OLTP application that provides a transactional API in the database, using JSON for data interchange, across a stateless connection.

OLTP versus Batch

Technically, OLTP (On Line Transaction Processing) implies lots of small transactions running concurrently, whereas “batch” implies changing lots of data in one process, using bulk or set-based techniques. There is another important difference: with OLTP, someone is there!

To avoid a “lost update”, someone has to be there to “find” it. Suppose you and I query a row at the same time, then we both try to update it. If your update happens first and commits, I need to know it so I can decide whether my update should overwrite yours. In other words, I need to “find” your update so I can think about how to deal with the conflict.

When a batch program runs, there is no one there to “find” an update made by something else while the batch is running.

A prerequisite of avoiding “lost updates” is the presence of a human being who can be notified in case of a conflict and decide what to do next.

Stateless: Request and Response

When you and I write SQL or PL/SQL, we generally use a tool like SQL Developer, Toad or SQL*Plus. These tools use the same client / server architecture that applications used in the 1980’s: each person has a conversation with the database using a session that keeps track of what is going on. If I query 1000 rows in SQL Developer, the tool will execute the query and fetch 100 rows. If I scroll down, the tool will fetch 100 more rows from the same query – because the cursor was left open in my session.

The fact that the session remembers what is going on during these exchanges is called “state”.

In modern OLTP applications, people access servers using HTTP, then the servers access the database using connections from a pool. Each connection has a session that can by used by you, then me, then you again. When you use the session a second time, the session remembers nothing from your previous use – if it does, there is a huge bug in the application. This absence of “state” is what we call “stateless”.

Being “stateless” makes a big difference. A “stateful” interface can do a SELECT FOR UPDATE, then UPDATE the rows that I change. A “stateless” interface cannot do that, since the session I used for the SELECT and the session I use for the UPDATE are not necessarily the same.

A stateless application must use a pure “Request and Response” protocol with the database:

  • I ask the application to query some data for me. The application gets a connection from the pool, requests the data and shows me the response from the database. The database session forgets about me because the next user will likely be somebody else.
  • I ask the application to update some data. The application gets whatever connection and session are free. If the database needs some information from my first query, the application has to pass it back.

The whole idea of “optimistic locking” is based on this scenario: a stateless OLTP application with a real life user who has to be informed of update conflicts.

JSON and the Object-relational impedance mismatch

As stated in the JSON standard, JSON is a data interchange format. Did I emphasize that enough for you? JSON was not meant for storing data, but for exchanging it between programming languages. Starting with Oracle Database 12c, SQL can query JSON (meaning it can map JSON to SQL rows and columns); starting with version 12.2, SQL can also generate JSON from “normal” database data. In other words, SQL all by itself can now use JSON as a data interchange format.

Ever since Java hype got huge mind share in the minds of enterprise IT architects, developers are supposed to do object-oriented programming. Thinking of data as rows and columns in a SQL database is old hat. Instead of data, there are objects with attributes and methods; several EMP objects can point to the same DEPT object, whereas in a JOIN the same DEPT data is repeated across several rows. The conceptual differences between “object” and “relational” are important enough to have a fancy name: the “object-relational impedance mismatch”. People write object-relational mapping frameworks so that application code can ignore SQL as much as possible, leading to all sorts of wasted resources, time and money.

By using JSON intelligently as a data interchange format, we can map relational data to an “object-friendly” form right in our SQL statements. As long as the application developers and the SQL developers understand the mapping and what underlies it, each can work in their own environment with data everyone can access with relative ease.

Note I am doing the JSON-relational mapping in the database, in SQL. The database client (application or generic tool) accesses the database via SQL*Net and receives data in JSON format already. That data may or may not be a payload for a RESTful web service. JSON in the database can be used in browser-based, #ThinJava applications but also in enterprise applications with thick application layers.

Transactional API: read-consistent, write-coherent

I wish I had a better term than “transactional API”, because I don’t mean the same thing by it as others – sorry!  Let me explain.

Getting back to the “lost update” problem, the user needs one API to query some data and another API to update it. Each API has to include one “request” from the application and one “response” from the database (this is all stateless, remember). In my view, every “query” API should return read-consistent data, and every “update” API should be transactional – at least potentially.

As far as querying is concerned, I am not saying that two different responses from the database should be consistent – only that all the data contained within one response should be internally consistent. If data from two queries are combined in one response, then the queries should be “as of” the same SCN.

With respect to “update” APIs (or any API that changes data), the API should provide everything the database needs to make a coherent set of changes that can reasonably be committed together. This is what we used to call a “logical unit of work”. If a change doesn’t make sense by itself, but only as part of a whole, then there should be no API to make just that change.

Unlike the #SmartDB paradigm, I don’t insist that the API commit before returning. It may be convenient for the application to do a complex transaction that involves two APIs which must succeed together or fail together. It may be that the application accesses multiple data sources and does two-phase commits. I do insist that the API is “atomic”: the changes either entirely succeed or entirely fail, and if they fail the database returns to precisely the state it was in before the API was called. For want of a better phrase, I’ll call this API a “write-coherent” API.

When is there a lost update?

Let me add one final touch to my use case. Optimistic locking schemes are often row-based: if you change any column in a row, I get notified of a “lost update” even if I am changing another column. My approach will start with that idea, but then I will make it more granular so that only updates to the same column will cause a conflict.

Next up: an overview of the ORA_ROWSCN solution.

Optimistic ORA_ROWSCN 2: the SCN

In my previous post, I covered lost updates. Before saying how ORA_ROWSCN can help avoid them, I need to talk about the SCN. It’s hard to be simple and correct about such a central element of the Oracle database, but I’ll try.

Aside from the documentation, I am relying on Oracle Core by Jonathan Lewis, chapter 3, along with this blog post and Jonathan’s answer to my question on OTN.

The Commit SCN

The documentation defines the SCN, or “System Change Number”, as a “database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.” It also calls it a “stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.”

In other words:

  • Every change to data in the database is made as part of a transaction.
  • The changes in a transaction become effective when the transaction is committed.
  • Every committed transaction gets a unique SCN.
  • The SCN increments at each commit, which allows it to “order the database” in time.
  • Every “commit SCN” “defines a committed version of the database”, so the SCN is like a version number that applies to the entire database.

The “commit SCN” of a transaction is precisely the SCN that was assigned to a transaction on commit.

[UPDATE 2017-07-24: Jonathan Lewis just pointed me to a blog post by Frits Hoogland where he showed that it is possible for two commits to share the same SCN. To quote Frits: “both commits … have the same SCN. At this point I think it’s fitting to theorise why this is happening. I think this is happening and makes sense because the table is protected for consistency at another level in the database, which is the locking mechanisms (TX locks for rows, TM locks for segments), which guarantees that two transactions at the same time do not compromise data consistency. And because of that, it’s not a problem to batch them with the same SCN.”

Frits seems to have shown an error, or at least a simplification, in the documentation. Either that or “unique” means every transaction has just one “commit SCN”, even if one “commit SCN” can be associated with two simultaneous transactions.

Fortunately for me, this refinement doesn’t invalidate the rest of this post.]

[UPDATE 2017-08-01: thanks to Connor McDonald on asktom for posting this quote from the Concept Guide:

“Several events may share the same SCN, which means that they occurred at the same time in the database. Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN.”

This seems to confirm that two transactions can share the same SCN.]

The SCN and Read Consistency

In Oracle, queries are “read consistent”, which means “The data returned by a query is committed and consistent for a single point in time.” This point in time is defined by – you guessed it – an SCN. I’ll call that SCN the “read-consistent SCN”. By default Oracle uses the SCN that was current when the query started, but within read-only or serializable transactions it uses the SCN when the transaction began.

Now comes the hard part.

When we submit a query, Oracle gets the data blocks it needs to read the rows of interest. The problem is that each data block may contain uncommitted data, or data that was committed after the “read-consistent SCN”. To determine that, Oracle consults the ITL (Interested Transaction List) in the block header, and if necessary the UNDO that the ITL entries point to.

There are three case to consider:

  1. All the ITL entries show committed transactions having “commit SCNs” that are less than or equal to the “read-consistent” SCN.
  2. All the ITL entries show committed transactions, but there is at least one “commit SCN” greater than the “read-consistent” SCN.
  3. At least one ITL entry shows a transaction that has not committed.

Case 1. is the most frequent and the simplest: none of the data is more recent than the read-consistent SCN, so the query may use the block as is.

Case 2. is not as simple as you might think. The “commit SCN” in an ITL entry may be an upper bound: it cannot be less than the real “commit SCN”, but it could be greater. In this case Oracle cross-checks with the “transaction table slot” in the UNDO segment header. If it finds that the transaction was actually committed before the read-consistent SCN, it will use the block in the query, and it will lower the “commit SCN” stored in the ITL entry.

Case 3. is similar to case 2. The ITL entry says “transaction not committed”, but that may be because Oracle did not have time to update the ITL entry before the block was flushed from memory. Oracle must cross-check with the “transaction table slot” in the UNDO segment header. If it says “actually, the transaction has committed”, then the ITL entry is updated with an upper bound “commit SCN”. This is known as “delayed block cleanout”.

We can see case 3. and case 2. at work in the following example, using two independent sessions:

  • Session 1
create table T1 (a number, ts timestamp, val number);
create table T2 (a number, ts timestamp, val number);

begin
    insert into T1 values (1, localtimestamp, 0);
    insert into T1 values (2, localtimestamp, 0);
    commit;
end;
/
begin
    insert into T2 values (3, localtimestamp, 0);
    commit;
end;
/
select dbms_flashback.get_system_change_number current_scn from dual;

CURRENT_SCN
-----------
   20031509

update T1 set ts = localtimestamp, val = 1 where a = 2;
alter system flush buffer_cache; -- (1)
commit;

begin
    for i in 1 .. 5000 loop
        update T2 set val = val + 1;
        commit; -- (2)
    end loop;
end;
/
select dbms_flashback.get_system_change_number current_scn from dual;

CURRENT_SCN
-----------
   20036606

(1) Notice that we update T1, then flush the block from memory before the COMMIT. This prevents Oracle from doing the “commit cleanout” in memory that would normally put the real “commit SCN” in the ITL entry.

(2) After the update to T1, we do 5000 transactions to T2 in order to increment the SCN.

  • Session 2
set transaction read only; -- This simulates a long-running query
  • Session 1
begin
    for i in 1 .. 5000 loop
        update T2 set val = val + 1;
        commit;
    end loop;
end;
/
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

CURRENT_SCN ORA_ROWSCN
----------- ----------
   20041709   20041359
   20041709   20041359

We use the pseudocolumn ORA_ROWSCN to query the “last change SCN” of the block, which corresponds to the highest SCN in the ITL. Notice the value is the same for both rows, even though we updated one row later than the other. Notice also that ORA_ROWSCN shows an SCN value that is clearly less than the current SCN (so we know the data is read consistent with the query) but much greater than the real commit SCN.

What happened here was what I called case 3. above. The data block was flushed from memory before the commit, so Oracle had to go to the UNDO to find out what happened. It discovered the transaction was committed some time ago, and updated the ITL entry to an SCN it could find efficiently and that was “good enough” for the query being executed. This SCN is still almost 10,000 more than the real “commit SCN”.

  • Session 2
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

CURRENT_SCN ORA_ROWSCN
----------- ----------
   20041714   20036604
   20041714   20036604

This is a example of what I called case 2. above. Oracle got the data block, found an SCN that was too recent for the query, and went to the UNDO to find out if the real commit SCN was lower. Once it found an SCN that was not greater than the read-consistent SCN (at the beginning of the transaction), it updated the ITL entry (together with the “last change SCN”) and used the data block in the query.

  • Session 1
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

CURRENT_SCN ORA_ROWSCN
----------- ----------
   20041714   20036604
   20041714   20036604

This is just to show that the ITL entry was well and truly changed for all sessions, not just for Session 2.

To summarize what we have seen: whenever a query accesses a data block, Oracle has to determine whether the block has changed earlier or later than the read-consistent SCN of the query. It may have to go to the UNDO to find that out, but if it does it updates one or more ITL entries so the next query doesn’t have to do the same work over again. At the end of the query, all the data blocks that were read consistent now have ORA_ROWSCN values no greater than the read-consistent SCN.

By the way, flashback queries work the same way. Using AS OF SCN nnnn, I can lower an ORA_ROWSCN several times in a row and get pretty close to the real commit SCN.

ROWDEPENDENCIES

I said the pseudocolumn ORA_ROWSCN tells us what is the most recent SCN in the ITL. That is true if the table was created with NOROWDEPENDENCIES, which is the default. If we create the table using ROWDEPENDENCIES, Oracle makes room for an SCN for each row. For this kind of table, ORA_ROWSCN shows us the SCN stored with each row, not the overall SCN we discussed up until now. I’ll have more to say about this in a later blog post.

(Updated 2017-09-12) If we went through the example above using a table with ROWDEPENDENCIES, we would see different results for case 2. The first time an ITL entry gets a “commit SCN” (whether “upper bound” or not), the affected rows get that same “commit SCN”. In case 2, the ITL entry gets a new, lower “commit SCN” – but it is not copied to the affected row.

As a result, it is conceivable that the ORA_ROWSCN of a row will be greater than the most recent “commit SCN” stored in the data block.

Summary

  • Oracle orders all transactions in time by assigning each one a unique SCN on commit: this is called the “commit SCN”. [UPDATE 2017-07-24: it may be possible for two transactions to share the same “commit SCN” (and therefore be simultaneous), but no transaction will ever have more than one “commit SCN”.]
  • Every query returns results that were consistent as of an SCN that I call the “read-consistent SCN”: it corresponds to the start of the query (or the start of the transaction).
  • During query processing, Oracle compares the two SCNs and returns results where the “commit SCN” is never greater than the “read-consistent SCN”.

Sometimes a data block has incomplete information in its ITL, and Oracle consults the UNDO to determine whether the “commit SCN” is not greater than the “read-consistent SCN”. If it is, Oracle will update the ITL to save work for the next query.

The end result is that, after the query finishes, every ITL entry for eligible blocks will contain an SCN less than or equal to the “read-consistent SCN” of the query. This is crucial for doing optimistic locking with ORA_ROWSCN.

More later…

Optimistic ORA_ROWSCN 1: Lost Updates

I’ve gotten lots of feedback about my proposal to use ORA_ROWSCN for optimistic locking. I’m going to start over in more detail and try to leave less scope for misinterpretation. Thanks to all for your comments, especially Jonathan Lewis who saved me from some technical doubts.

“Optimistic locking” is a type of solution to the problem of “lost updates”. So what do we mean by “lost update”, anyway?

Same row, same column

The Oracle documentation defines a lost update as: “A data integrity problem in which one writer of data overwrites the changes of a different writer modifying the same data.” The Concepts document gives an example where two sessions update the same column in the same row of the same table:

  • Session 1 updates, but does not commit yet
  • Session 2 selects the row, but does not see the change made by Session 1 because it is not committed yet.
  • Session 2 updates the same column in the same row. In fact, it has to wait on Session 1.
  • Session 1 commits.
  • Session 2 is no longer blocked by Session 1, so the Session 2 update overwrites the Session 1 update.
  • Session 2 commits.
  • Session 1 queries the row and discovers its update is “lost”.

Why say this update is “lost”? Simply because Session 2 did not see it when it queried the data. Session 2 overwrote the update without ever seeing it. The documentation also calls this a “conflicting write”.

I think the following scenario is also a “lost update”.

  • Session 2 selects a row.
  • Session 1 updates a column in the row and commits.
  • Session 2 updates the same column in the same row and commits.
  • Session 1 queries the row and discovers its update is “lost”.

In both scenarios, the Session 1 update is “lost” because Session 2 (that is, the user behind Session 2) did not see the change before overwriting it.

Why should we care?

In real life, what we have here is a failure to communicate. Behind the two sessions are two users who want to put different values into the same place in the database. In collaborative software such as Lotus Notes, Session 1 would create a document and Session 2 would create another document marked as a “conflict”: after the fact, human beings would work together to resolve the conflict.

In the database, we can’t store both values simultaneously, so we generally communicate the existence of a conflict by refusing to execute the second update. Whoever gets the error can query the row again, see the change made by the first update, then decide what to do next.

Update vs. Changed Value

An “update” to a column does not necessarily mean that the value of the column changed!

If I say UPDATE EMP SET SAL = SAL, Oracle will perform an update, executing the same triggers and creating UNDO as it does when the SAL value changes. (There is one difference: if the column is indexed and the value does not change, Oracle doesn’t touch the index.)

Updating columns to the same value may mean two different things: either

  1. the application uses generic UPDATE code and always updates the same columns, whether the user wanted to update all of them or just some of them; or
  2. a user deliberately updates a column with the same value it had previously, as a way of confirming that the value is still valid.

I suspect the first case is much more common than the second, but the second cannot be completely ruled out.

Same row, different columns

In both of the scenarios above, suppose Session 1 updates one column and Session 2 updates another column in the same row. As long as Session 2 leaves the first column alone, there is no conflict, no “lost update”.

Multiple rows and columns

I recently learned of a project where the “lost update” scenario has been extended to a group of rows. Since the rows are related functionally, the developers want to avoid applying changes to one row if any of the others in the group have changed. I’ll call this requirement “detecting changed context” instead of “detecting lost update”.

“Lost updates”, and beyond

Optimistic locking basically means detecting updates or changes that were committed between the time the user queried the data and the time (s)he updated it.

But: different applications may want to detect different things. For example:

  • Detect only changed values in columns the user really wants to change.
  • Detect changed values in any updatable column of the row the user wants to change.
  • Detect changed values in the row, even if the user cannot update them.
  • Detect any update to the row, whether values changed or not.
  • Detect changes in the context, meaning all rows related to the target row(s), even those the user does not intend to update.

Mechanisms for detecting changes / updates

In the old “client / server” architectures, each user kept the same session for querying the data and updating it. To avoid conflicts at the row level, it was sufficient to do SELECT FOR UPDATE instead of a simple SELECT. That way, the current user saw all previous changes and the later users would see all the current changes. “Lost updates” were purely and simply avoided. This is known as “pessimistic locking”, since we assume conflicts will occur and take steps to avoid them.

In stateless architectures today, a user may obtain one session for querying the data and another for updating it. No session will remember anything about what happened when the user accessed the database previously. Therefore, SELECT FOR UPDATE is out of the question. This led to various “optimistic locking” strategies, where the data is to some extent queried again and compared to the values that were originally shown to the user. Some methods are:

  • column compare (all columns or only those of interest)
  • hash compare (hashing all or some columns)
  • timestamp compare (a row has been updated if the timestamp is different)
  • version compare (a rows has been updated if the version number has incremented).

Note that for efficiency, conflicts within a row can be detected directly in the UPDATE statement – a prior SELECT FOR UPDATE is not needed.

Where do I fit in?

My proposal is inspired by Tom Kyte (even though he gave up on ORA_ROWSCN himself). I want to use ORA_ROWSCN to detect conflicting updates to a row, whether any values changed or not. We’ll see in the following posts how this works, what requirements it fits and how much we can rely on it.