Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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.

Protecting Data Integrity

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

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

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

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

Tip

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.

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.

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.

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.