Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

Consistent View

Every database implements some type of consistent view, and how it works is different for each database product.  Here, of course, we are going to discuss Oracle’s implementation of a consistent view. 

In Oracle, a consistent view  means that you will never see other users’ uncommitted data, otherwise known as a dirty read.  If I log onto the database as PUBS and then open a new window and log on again as PUBS, I will have two database sessions, both with user PUBS.  Each session is a separate connection, and neither one will be able to see the others’ uncommitted data. 

Here, I am using the SQL*Plus command SQLPROMPT to change my prompt, so that you can see the two different users, PUBS1 and PUBS2.

SQL> set sqlprompt 'PUBS1 SQL> '
PUBS1 SQL> select author_key from author;

AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

10 rows selected.

The second PUBS log on sees the same data.

PUBS2 SQL> select author_key from author;

AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110 

10 rows selected.

Now, I add ten additional rows to the AUTHOR table as PUBS1 (I used the INSERT  statements from the pubs_db.sql).

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A101', 'jones', 'mark', '303-462-1222', '1401 west fourth st', 'st. louis', 'MO','47301', '5601');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A102', 'hester', 'alvis', '523-882-1987', '2503 backer view st', 'st. louis', 'MO','47301', '5602');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A103', 'weaton', 'erin', '367-980-8622', '6782 hard day dr', 'st. louis', 'MO','47301', '5603');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A104', 'jeckle', 'pierre', '543-333-9241', '3671 old fort st', 'north hollywood', 'CA','91607', '6602');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A105', 'withers', 'lester', '457-882-2642', '1320 leaning tree ln', 'pie town', 'IL','57307', '7896');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A106', 'petty', 'juan', '344-455-6572', '8869 wide creek rd', 'happyville', 'TX','77304', '6547');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A107', 'clark', 'louis', '666-555-8822', '7980 shallow pond st', 'rose garden', 'WI','33301', '3452');

1 row created. 

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A108', 'mee', 'minnie', '321-543-9876', '2356 empty box rd', 'belaire', 'KY','45461', '7954');

1 row created.

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A109', 'shagger', 'dirk', '987-654-3210', '3452 dirt path way', 'cross trax', 'LA','47301', '0001');

1 row created. 

PUBS1 SQL> INSERT INTO AUTHOR
  2  VALUES ('A110', 'smith', 'diego', '564-897-3201', '2567 south north st', 'tweedle', 'MA','47301', '2853');

1 row created.

Now, when PUBS1 checks the AUTHOR table he sees his changes.  When PUBS2 checks the AUTHOR table, he still sees only the original ten rows.  Remember PUBS1 has not committed the inserts.

PUBS1 SQL> select author_key from author;
AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

20 rows selected.

PUBS2 SQL> select author_key from author;
AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

10 rows selected.

Now, PUBS1 commits the changes, and both can now see all 20 rows.

PUBS1 SQL> commit; 

Commit complete. 

PUBS2 SQL> select author_key from author;
AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

20 rows selected. 

So, what happens when more than one person updates the same data?  To get back to the original data, I dropped the AUTHOR table and recreated it from the pubs_db.sql script.

First PUBS1 changes the data in the AUTHOR table for key A101.

PUBS1 SQL> update author
  2  set author_state = 'CO'
  3  where author_key = 'A101'; 

1 row updated. 

PUBS1 SQL> select author_key, author_state from author; 

AUTHOR_KEY  AU
----------- --
A101        CO
A102        MO
A103        MO
A104        CA
A105        IL
A106        TX
A107        WI
A108        KY
A109        LA
A110        MA

10 rows selected.

Now it is PUBS2’s turn.

PUBS2 SQL> update author
  2  set author_state = 'FL'
  3  where author_key = 'A101';
 

The prompt does not come back.  That is because PUBS1 has a lock on that data.  PUBS2 is waiting behind that lock to change the data.  Let’s commit PUBS1, so that he releases the lock.

PUBS1 SQL> commit;

Commit complete.

PUBS2 SQL> update author
  2  set author_state = 'FL'
  3  where author_key = 'A101';

1 row updated. 

Once PUBS1 committed, the lock was released and PUBS2 updated the row.  Now let’s check that consistent view.

PUBS1 SQL> select author_key, author_state from author;

AUTHOR_KEY  AU
----------- --
A101        CO
A102        MO
A103        MO
A104        CA
A105        IL
A106        TX
A107        WI
A108        KY
A109        LA
A110        MA

10 rows selected.

PUBS2 SQL> select author_key, author_state from author;

AUTHOR_KEY  AU
----------- --
A101        FL
A102        MO
A103        MO
A104        CA
A105        IL
A106        TX
A107        WI
A108        KY
A109        LA
A110        MA 

10 rows selected.

PUBS1 sees his committed state of CO, while PUBS2 sees his uncommitted FL.  Once PUBS2 commits, both will see FL.  PUBS2 overwrote PUBS1.  He who commits last wins!

Why did PUBS2’s UPDATE end up waiting for the lock held by PUBS1, but the SELECT by PUBS1 did not wait for the lock held by PUBS2?  The Oracle database has very loose locking in that it only locks what it has to in order to protect transactions.  Locks never block reads.  So PUBS1’s lock held up PUBS2’s UPDATE of the same piece of data, but it never blocked the SELECT statement that only reads the data.

You can sum up Oracle’s consistent view  in two statements:  You will see only committed data and your changes.  No user will ever see anyone else’s uncommitted data.


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.