Oracle Tips and Tricks — David Fitzjarrell

June 24, 2008

Consistency Is Good

Filed under: General — dfitzjarrell @ 16:10

On-line transaction processing (OLTP) systems are designed so that multiple users can do multiple things without severely impacting each other (for the most part). Fifty users can process data at essentially the same time and not ‘step’ on each other’s toes. This doesn’t mean, however, that a user (or a developer) can’t step on his or her own toes when given the opportunity. Misunderstanding the read-consistency mechanism Oracle employs is one reason (and it’s a pretty big reason) users (and developers) shoot themselves in the foot. Let’s find out how this works.

Oracle, in its infinite wisdom, operates on the basic premise that the end user wants to see repeatable, reliable query results. Because of this Oracle uses areas of the database the garden-variety user may not expect when returning query results. Why? Because, for the most part, these databases are built to process transactions and to ensure Oracle meets the ACID test [Atomicity, Consistency, Isolation, and Durability] it uses UNDO segments to preserve the initial state of the data prior to any changes (this is not the entire picture, but it’s the portion we need to explain read-consistency). Oracle can use these UNDO segments to satisfy queries, too, so that an image of the data as of the time the query began can be returned, regardless of the number of uncommitted changes which may have occurred since the query started. Of course, as expected, this behaviour can also create problems when it is not understood. Let’s look at a small two session example.

Magdeline Whiffenpoof, manager of HR, is working on the EMP table and she’s added a new employee with an EMPNO of 8010:

SQL> INSERT
  2  INTO EMP VALUES
  3          (8010, 'SMITH',  'CLERK',     7902,
  4          TO_DATE('17-DEC-1999', 'DD-MON-YYYY'),  900, NULL, 20);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>

Everything looks good, but, wait! Euphemia Snertle, assistant HR manager, has just been informed that this new employee changed her mind and took a job with a rival company so she’s been told to delete this person from the database:

SQL> select max(empno)
  2  from emp;

MAX(EMPNO)
----------
      8010

SQL>
SQL> delete
  2  from emp
  3  where empno = 8010;

1 row deleted.

SQL>

Of course Euphemia hasn’t committed the change yet as she’s been called into a meeting. Meanwhile Magdeline decides to check her work and, lo and behold, it still looks like that new employee is in the database:

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      8010

SQL>

This is because Euphemia’s change hasn’t been committed, and Oracle provides an image of the data as it was when Magdeline’s query started, ignoring any and all uncommitted transactions. Oracle visits the UNDO segments and ‘reconstructs’ the data as it was when Magdeline asked to ‘see’ it, so she’s not able to view what Euphemia just started and has yet to finish. Of course at Euphemia’s terminal she’s seeing a different MAX(EMPNO):

SQL> select max(empno)
  2  from emp;

MAX(EMPNO)
----------
      7934

SQL>

Euphemia returns, and commits the change she made:

SQL> commit;

Commit complete.

SQL>

And now Magdeline, again checking her work, receives a different result:

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      7934

SQL>

Hot potato pancakes, what happened?!?!? Magdeline calls Euphemia to report this ‘missing’ employee, only to be told that she took another offer and is no longer on the payroll. Since both transactions have been committed everyone will return the same maximum EMPNO from the table. And all is, again, right with the world.

Since Oracle provides a consistent view of data as of the moment a query starts it can be confusing when the exact same query is run more than once and returns differing results because of a delay in committing changes as the end user believes the data to be exactly as he or she sees it, blissfully unaware that other users could be changing the picture in the same moment their results are displayed. And, with long running queries sometimes the UNDO images are overwritten by commits, causing the query to end unsuccessfully with an ORA-01555 error. Re-running the query usually succeeds and returns the now-committed changes.

When all is said and done, I’d rather have Oracle do what it does (meaning return a consistent set of data) rather than try to return an image of uncommitted changes. Sure, a commit will set those changes in stone (so to speak) but a rollback will restore the data to the state it was before the changes were enacted. And that could be confusing.

Consistency. It’s a virtue.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: