| Lab Objectives After this Lab, you will be able to: 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.  | For more details on transaction control (such as row locking issues), see the companion volume, Oracle DBA Interactive Workbook, by Douglas Scherer and Melanie Caffrey (Prentice Hall, 2000). |
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. | | Note that there is a distinction between transaction and a PL/SQL block. The start and end of a PL/SQL block do not necessarily mean the start and end of a transaction. |
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.  FOR EXAMPLE 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. COMMIT When a COMMIT statement is issued to the database, the transaction has ended, and the following statements 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 session. FOR EXAMPLE 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. | | Note that this is covered in more depth in the companion volume, Oracle DBA Interactive Workbook, by Douglas Scherer and Melanie Caffrey (Prentice Hall, 2000). |
ROLLBACK When a ROLLBACK statement is issued to the database, the transaction has ended, and the following statements are true: All work done by the user 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 is available 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'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: 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 to 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.
Lab 4.2 Exercises 4.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block in a PL/SQL BlockLog into the CTA schema and enter the following series of commands. (Optionally, you can write the PL/SQL block in a text file and then run the script from the SQL*Plus prompt.) -- ch04_7a.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; | a) | If you issue the following command, what would you expect to see? Why? SELECT * FROM student WHERE last_name = 'Norbu'; | | b) | Try it. What happened? Why? |
Now issue ROLLBACK to SAVEPOINT A; | c) | What happened? | | d) | If you issue the following, what do you expect to see? SELECT last_name FROM student WHERE last_na me = 'Tashi'; | | e) | Issue the command and explain your findings. |
| | SAVEPOINT is often used before a complicated section of the transaction. If this part of the transaction fails, it can be rolled back, allowing the earlier part to continue. |
 | It is important to note the distinction between transactions and PL/SQL blocks. When a block starts, it does not mean that the transaction starts. Likewise, the start of the transaction need not coincide with the start of a block. |
Lab 4.2 Exercise Answers This section gives you some suggested answers to the questions in Lab 4.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 4.2.1 Answers | a) | If you issue the following command, what would you expect to see? Why? SELECT * FROM student WHERE last_name = 'Norbu'; | | A1: | Answer: You will not be able to see any data because the ROLLB ACK to (SAVEPOINT) B has undone the last insert statement where the student 'Norbu' was inserted. | | b) | Try it. What happened? Why? | | A2: | Answer: When you issue this command, you will get the message "no rows selected." Three students were inserted in this PL/SQL block. First, Sonam in SAVEPOINT A, then Tashi in SAVEPOINT B, and finally Norbu was inserted in SAVEPOINT C. Then when the command ROLLBACK to B was issued, the insert of Norbu was undone. |
| c) | What happened? | | A1: | Answer: The insert in SAVEPOINT B was just undone. This deleted the insert of Tashi who was inserted in SAVEPOINT B. | | d) | If you issue the following, what do you expect to see? SELECT last_name FROM student WHERE last_na me = 'Tashi'; | | A2: | Answer: You will see the data for Tashi. | | e) | Issue the command and explain your findings. | | A3: | Answer: You will see one entry for Tashi, as follows: LAST_NAME ------------------------- Tashi Tashi was the only student that was successfully entered into the database. The ROLLBACK to SAVEPOINT A undid the insert statement for Norbu and Sonam. A Single PL/SQL Block Can Contain Multiple Transactions For Example: 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. So, there will be a total of 10 transactions contained in this one PL/SQL block. |
Lab 4.2 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 4.2. | 1) | User A can ROLLBACK User B's insert statement. _____ True _____ False
| | 2) | When a COMMIT has been issued, which of the following are true? (Choose all that apply.) _____ All memory holds on the data have been released. _____ All data inserts are available to other users. _____ You have to get married. _____ The transaction is not finished because SQL statements are still pending.
| | 3) | What defines a logical unit of work? _____ From one SAVEPOINT to the next. _____ From one ROLLBACK to the next. _____ From one COMMIT to the next. _____ All of the above.
| | 4) | Which of the following is an advantage of using SAVEPOINTS in a PL/SQL block? _____ It prevents inconsistent data. _____ It allows one to group code into manageable units. _____ It prevents one from duplicating a primary key. _____ It locks rows and prevents other users from updating the same row.
|
|
