Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

4.2 Transactions

Like most databases, Oracle implements the concept of a
transaction , which is a set of related
statements that either all execute or do not execute at all.
Transactions play an important role in maintaining data integrity.

4.2.1 Protecting Data Integrity

Example 4-17 shows one method for changing a project
number from 1001 to 1006:

Because rows in the project_hours table must
always point to valid project rows, the example begins by creating a
copy of project 1001 but gives that copy the new number of 1006.

With project 1006 in place, it's then possible to
switch the rows in project_hours to point to 1006
instead of 1001.

Finally, when no more rows remain that refer to project 1001, the row
for that project can be deleted.

Example 4-17. Changing a project's ID number

--Create the new project
INSERT INTO project
SELECT 1006, project_name, project_budget FROM project
WHERE project_id = 1001;
--Point the time log rows in project_hours to the new project number
UPDATE project_hours
SET project_id = 1006
WHERE project_id = 1001;
--Delete the original project record
DELETE FROM project
WHERE project_id=1001;

You'll encounter two issues when executing a set of
statements such as those shown in Example 4-16.
First, it's important that all
statements be executed. Imagine the mess if your connection dropped
after only the first INSERT statement was executed. Until you were
able to reconnect and fix the problem, your database would show two
projects, 1001 and 1006, where there should only be one. The second
related issue is that you really don't want other
users to see any of your changes until you've made
all of them. Transactions address both these
issues.

To treat a set of statements as a unit, in which all or none of the
statements are executed, you can wrap those statements using SET
TRANSACTION and COMMIT, as shown in Example 4-18.

Example 4-18. A transaction to change a project's ID number

--Begin the transaction
SET TRANSACTION READ WRITE;
--Create the new project
INSERT INTO project
SELECT 1007, project_name, project_budget FROM project
WHERE project_id = 1002;
--Point the time log rows in project_hours to the new project number
UPDATE project_hours
SET project_id = 1007
WHERE project_id = 1002;
--Delete the original project record
DELETE FROM project
WHERE project_id=1002;
COMMIT;

SET TRANSACTION marks the beginning of a transaction. Any changes you
make to your data following the beginning of a transaction are not
made permanent until you issue a COMMIT. Furthermore, those changes
are not visible to other users until you've issued a
COMMIT. Thus, as you issue the statements shown in Example 4-18, other database users won't
see the results of any intermediate steps. From their perspective,
the project ID number change will be a single operation. All rows
having 2002 as a project ID value will suddenly have 1007 as that
value.

Using SET TRANSACTION to begin a transaction is optional. A new transaction begins implicitly with the first DML statement that you execute after you make a database connection or with the first DML statement that you execute following a COMMIT or a ROLLBACK (or any DDL statement such as TRUNCATE). You need to use SET TRANSACTION only when you want transaction attributes such as READ ONLY that are not the default.

4.2.2 Backing Out of Unwanted Changes

A third issue, and one that I didn't mention
earlier, is that you might change your mind partway through the
process. Perhaps you'll start out to change project
ID 1003 to 2008, issue the INSERT followed by the UPDATE, and then
realize that you need to change your project ID to 1008 and not to
2008. Again, transactions come to your rescue. You can undo every
change you've made in a transaction by issuing the
simple ROLLBACK statement. Example 4-19 demonstrates.

Example 4-19. A transaction to change a project's ID number

--Begin the transaction
SET TRANSACTION READ WRITE;
--Create the new project
INSERT INTO project
SELECT 2008, project_name, project_budget FROM project
WHERE project_id = 1002;
--Point the time log rows in project_hours to the new project number
UPDATE project_hours
SET project_id = 2008
WHERE project_id = 1002;
--Oops! Made a mistake. Undo the changes.
ROLLBACK;

ROLLBACK is handy for backing out of mistakes and when testing new
SQL statements. You can issue an UPDATE or DELETE statement, follow
that statement with a SELECT, and if you see that the results of your
UPDATE or DELETE aren't what you intended, you can
issue ROLLBACK and try again. I used this technique frequently while
testing the examples in this book.

4.2.3 You're Always Using Transactions

You may have just learned about transactions in this section, but
you've been using them all along. You
can't issue a SQL statement and not be in a
transaction. If you omit executing a START TRANSACTION statement,
Oracle will implicitly begin a read/write transaction with the first
SQL statement that you do execute. Oracle will automatically commit
(or roll back) transactions for you, too, under certain
circumstances:

Oracle implicitly commits any pending transaction the moment you
issue a DDL statement such as CREATE TABLE or TRUNCATE TABLE.

Oracle implicitly commits any pending transaction when you exit
SQL*Plus normally, e.g., you issue the EXIT command.

Oracle implicitly rolls back any pending transaction when your
connection terminates abnormally, e.g., when your network connection
is broken or when the server (or your workstation) crashes.

Open transactions consume resources, as the database must maintain
the information needed to roll back and provide other users with
views of data as they were before your transaction began. Unless all
you're doing is querying the database, you should
keep your transactions as short in time as possible.

4.2.4 Understanding Transaction Types

Example 4-19 specified a read/write transaction. Such
a transaction is the default, and it allows you to issue statements
such as UPDATE and DELETE. You can also create read-only
transactions:

SET TRANSACTION READ ONLY;

Read-only transactions are particularly useful when generating
reports because, in a read-only transaction, you see a consistent
snapshot of the database as it was when the transaction began. Think
of freezing the database at a moment in time. You can begin a report
at 8:00 a.m., and even if that report takes the rest of the day to
run, the data on that report will reflect 8:00 a.m. Other users are
free to make their changes, but you won't see those
changes and they won't show up on any report that
you run until you commit (or roll back) your read-only
transaction.