 |
|
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 |
