Lab 4.2 Making Use of 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 in order to access the data. It is important to point out that when a user is issuing DML statements in an application, the changes are not visible to other users until a COMMIT or ROLLBACK has been issued. Oracle 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 only available 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 a ROLLBACK statement will release these locks. Transactions can be controlled more readily by marking points of the transaction with the SAVEPOINT command.
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) and some can be rolled back (undone) and so forth.
In order to demonstrate the need for transaction control, we will examine a two-step data-manipulation process. For example, suppose that the fees for all courses in the CTA database that had a prerequisite course needed to be increased by 10 percent and at the same time all courses that did not have a prerequisite needed to be decreased by 10 percent. This is a two-step process. If one step had been successful but the second step was 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 what part of this course adjustment had been successful and what had not been. In this example, you see one PL/SQL block that 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 when the course has a prerequisite. -- ch04_6a.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. So, 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 the first SQL statement issued after connecting to the database. The transaction ends with the COMMIT or ROLLBACK statement. COMMITWhen a COMMIT statement is issued to the database, the transaction has ended, and the following statements are true:
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 session. Suppose User A issues the following command on a student table that exists in another schema but has a public synonym of student: -- ch04_6a.sql 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' ); Then User B enters the following command to query 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 next 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.
ROLLBACKWhen a ROLLBACK statement is issued to the database, the transaction has ended, and the following statements are true:
A ROLLBACK statement has the following syntax: ROLLBACK [WORK]; The WORK keyword is optional and is available for increased readability. SAVEPOINTThe 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's name. Once a SAVEPOINT is defined, the program can roll back to the 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 statements are true:
 |
Make Use of COMMIT, ROLLBACK and SAVEPOINT in a PL/SQL Block 
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}