- Lab 3.1: DML Statements in PL/SQL
- Lab 3.2: Transaction Control in PL/SQL
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Lab 3.2: Transaction Control in PL/SQL
Using COMMIT, ROLLBACK, and SAVEPOINT
Transactions are a means to break programming code into manageable units. Grouping transactions into smaller elements is a standard practice that ensures an application will save only correct data. Initially, any application will have to connect to the database to access the data. When a user is issuing DML statements in an application, however, these changes are not visible to other users until a COMMIT or ROLLBACK has been issued. The Oracle platform guarantees a read-consistent view of the data. Until that point, all data that have been inserted or updated will be held in memory and will be available only to the current user. The rows that have been changed will be locked by the current user and will not be available for updating to other users until the locks have been released. A COMMIT or ROLLBACK statement will release these locks. Transactions can be controlled more readily by marking points of the transaction with the SAVEPOINT command.
- COMMIT: Makes events within a transaction permanent.
- ROLLBACK: Erases events within a transaction.
Additionally, you can use a SAVEPOINT to control transactions. Transactions are defined in the PL/SQL block from one SAVEPOINT to another. The use of the SAVEPOINT command allows you to break your SQL statements into units so that in a given PL/SQL block, some units can be committed (saved to the database), others can be rolled back (undone), and so forth.
To demonstrate the need for transaction control, we will examine a two-step data manipulation process. Suppose that the fees for all courses in the CTA database that have a prerequisite course need to be increased by 10 percent; at the same time, all courses that do not have a prerequisite need to be decreased by 10 percent. This is a two-step process. If the first step is successful but the second step is not, then the data concerning course cost would be inconsistent in the database. Because this adjustment is based on a change in percentage, there would be no way to track which part of this course adjustment was successful and which part was not.
In the following example, one PL/SQL block performs two updates on the cost item in the course table. In the first step (this code is commented for the purpose of emphasizing each update), the cost is updated with a cost that is 10 percent less whenever the course does not have a prerequisite. In the second step, the cost is increased by 10 percent whenever the course has a prerequisite.
For Example ch03_7a.sql
BEGIN
-- STEP 1
UPDATE course
SET cost = cost - (cost * 0.10)
WHERE prerequisite IS NULL;
-- STEP 2
UPDATE course
SET cost = cost + (cost * 0.10)
WHERE prerequisite IS NOT NULL;
END;
Let’s assume that the first update statement succeeds, but the second update statement fails because the network went down. The data in the course table is now inconsistent because courses with no prerequisite have had their cost reduced but courses with prerequisites have not been adjusted. To prevent this sort of situation, statements must be combined into a transaction. Thus either both statements will succeed or both statements will fail.
A transaction usually combines SQL statements that represent a logical unit of work. The transaction begins with the first SQL statement issued after the previous transaction, or with the first SQL statement issued after connecting to the database. The transaction ends with the COMMIT or ROLLBACK statement.
COMMIT
When a COMMIT statement is issued to the database, the transaction has ended, and the following results are true:
- All work done by the transaction becomes permanent.
- Other users can see changes in data made by the transaction.
- Any locks acquired by the transaction are released.
A COMMIT statement has the following syntax:
COMMIT [WORK];
The word WORK is optional and is used to improve readability. Until a transaction is committed, only the user executing that transaction can see changes in the data made by his or her session.
Suppose User A issues the following command on a student table that exists in another schema but has a public synonym of student:
For Example ch03_8a.sql
BEGIN
INSERT INTO student
(student_id, last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Tashi', 10015,
'01-JAN-99', 'STUDENTA', '01-JAN-99',
'STUDENTA', '01-JAN-99'
);
END;
Then User B enters the following command to query the table known by its public synonym student, while logged on to his session.
SELECT * FROM student WHERE last_name = 'Tashi';
Then User A issues the following command:
COMMIT;
Now if User B enters the same query again, he will not see the same results.
In this example, there are two sessions: User A and User B. User A inserts a record into the student table. User B queries the student table, but does not get the record that was inserted by User A. User B cannot see the information because User A has not committed the work. When User A commits the transaction, User B, upon resubmitting the query, sees the records inserted by User A.
ROLLBACK
When a ROLLBACK statement is issued to the database, the transaction has ended, and the following results are true:
- All work done by the transaction is undone, as if it hadn’t been issued.
- Any locks acquired by the transaction are released.
A ROLLBACK statement has the following syntax:
ROLLBACK [WORK];
The WORK keyword is optional and provides for increased readability.
SAVEPOINT
The ROLLBACK statement undoes all work done by the user in a specific transaction. With the SAVEPOINT command, however, only part of the transaction can be undone. A SAVEPOINT command has the following syntax:
SAVEPOINT name;
The word name is the SAVEPOINT statement’s name. Once a SAVEPOINT is defined, the program can roll back to that SAVEPOINT. A ROLLBACK statement, then, has the following syntax:
ROLLBACK [WORK] to SAVEPOINT name;
When a ROLLBACK to SAVEPOINT statement is issued to the database, the following results are true:
- Any work done since the SAVEPOINT is undone. The SAVEPOINT remains active, however, until a full COMMIT or ROLLBACK is issued. It can be rolled back again, if desired.
- Any locks and resources acquired by the SQL statements since the SAVEPOINT will be released.
- The transaction is not finished, because SQL statements are still pending.
Putting Together DML and Transaction Control
This section combines all the elements of transaction control that have been covered in this chapter. The following piece of code is an example of a PL/SQL block with three SAVEPOINTs.
For Example ch03_9a.sql
BEGIN
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES ( student_id_seq.nextval, 'Tashi', 10015,
'01-JAN-99', 'STUDENTA', '01-JAN-99',
'STUDENTA','01-JAN-99'
);
SAVEPOINT A;
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Sonam', 10015,
'01-JAN-99', 'STUDENTB','01-JAN-99',
'STUDENTB', '01-JAN-99'
);
SAVEPOINT B;
INSERT INTO student
( student_id, Last_name, zip, registration_date,
created_by, created_date, modified_by,
modified_date
)
VALUES (student_id_seq.nextval, 'Norbu', 10015,
'01-JAN-99', 'STUDENTB', '01-JAN-99',
'STUDENTB', '01-JAN-99'
);
SAVEPOINT C;
ROLLBACK TO B;
END;
If you were to run the following SELECT statement immediately after running the preceding example, you would not be able to see any data because the ROLLBACK to (SAVEPOINT) B has undone the last insert statement where the student Norbu was inserted.
SELECT * FROM student WHERE last_name = 'Norbu';
The result would be “no rows selected.”
Three students were inserted in this PL/SQL block: first Tashi in SAVEPOINT A, then Sonam in SAVEPOINT B, and finally Norbu in SAVEPOINT C. When the command to roll back to B was issued, the insert of Norbu was undone.
If the following command was entered after the script ch03_9a.sql, then the insert in SAVEPOINT B would be undone—that is, the insert of Sonam:
ROLLBACK to SAVEPOINT A;
Tashi was the only student that was successfully entered into the database. The ROLLBACK to SAVEPOINT A undid the insert statements for Norbu and Sonam.
Here is an example of a single PL/SQL block with multiple transactions.
For Example ch03_10a.sql
DECLARE
v_Counter NUMBER;
BEGIN
v_counter := 0;
FOR i IN 1..100
LOOP
v_counter := v_counter + 1;
IF v_counter = 10
THEN
COMMIT;
v_counter := 0;
END IF;
END LOOP;
END;
In this example, as soon as the value of v_counter becomes equal to 10, the work is committed. Thus there will be a total of 10 transactions contained in this one PL/SQL block.