 |
|
ss
Oracle Tips by Burleson |
Transactions
A transaction is a
logically grouped set of INSERTs, UPDATEs and DELETEs that should all
succeed or fail as a group. We just found a new author that has
already written a book. We want to enter the data into our PUBS
database. Because we normalized our schema, we have to enter the data
into three different tables; author,
book_author and book. We want all three inserts to either
succeed or fail as a group; otherwise, we will have some data in some
tables but not a complete record of the new author and his book. We
log onto the database and insert the three rows of data.
SQL> INSERT
INTO AUTHOR
2 VALUES ('A11l', 'john',
3 'garmany', '123-345-4567',
4 '1234 here st', 'denver',
5 'CO','90204', '9999');
1 row
created.
SQL> INSERT
INTO BOOK_AUTHOR VALUES ('A111', 'B130', .20);
1 row
created.
SQL> INSERT
INTO BOOK
2 VALUES ('B130', 'P002', 'easy oracle sql',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2005','MM-DD-YYYY'));
1 row
created.
SQL> commit;
Commit
complete.
All three of my SQL statements succeeded, so I
committed the changes. Once I commit the changes, they are
permanently changed in the database and the change cannot be undone.
Let’s say that one of the three inserts failed. Since I don’t want
half the information in the database, I can issue a ROLLBACK command
and the changes since the last commit (or since log on) are removed
from the database and the original state is recreated.
A database transaction is all
the changes that take place between a commit or rollback; beginning
with the first SQL statement and ending with the subsequent commit or
rollback. A commit can be issued as a command or happen because of
another statement. Anytime we execute a data definition language
(DDL) command, we issue an implicit commit. In the example
below, I INSERT a duplicate row with the author key A111. I then create
a table (DDL) and rollback.
SQL> insert
into book_author values ('A111', 'B130', .20);
1 row
created.
SQL> create
table t1 as
2 select
3 store_name,
4 avg(quantity) qty
from
5 6 store join sales using (store_key)
7 group by store_name;
Table
created.
SQL>
rollback;
Rollback
complete.
SQL> select
author_key from book_author;
AUTHOR_KEY
-----------
A111
A111
A101
A102
A103
A104
…
A101
A109
27 rows
selected.
Notice that the duplicate row remains after the
rollback. That is because I created a table that issued an implicit
commit. The duplicate row was committed by the CREATE TABLE command.
Before a commit or rollback is executed, I have
the ability to recover any data changes in the transaction. If
I select data that I have changed but not committed, I will get back
the changed data. If another user selects data that I have changed
but not committed, they will see the original data (a consistent view
will be discussed next).
A commit is issued when the user commits, when any
DDL statement is executed, or when the user logs off
normally. If you make a number of changes and you do not want the
changes to be permanent, you must issue a rollback before you log
off. Logging off commits the changes.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
