0671-0674

Previous Table of Contents Next

Page 671

CHAPTER 27

Transaction
Processing

IN THIS CHAPTER

• Sessions Versus Transactions672
• Commits, Rollbacks , and Savepoints674
• Transaction Control Statements676
• Types of Transactions676
• Read-Consistency678
• Steps to Processing a Transaction679
• Processing a Remote or Distributed Transaction687
• The SET TRANSACTION Command Reference690
• Transaction Space691

Page 672

Understanding how a transaction begins, executes, and ends, and knowing what happens along each step of the way are vital parts of making Oracle work for you. This knowledge is helpful not only to system and database administrators, but to Oracle developers as well. Knowing when a transaction is assigned a rollback segment or how locking occurs within the database can drastically change the strategy of creating applications or nightly processing jobs.

This chapter covers the following:

• The difference between a session and a transaction
• What happens when a transaction begins
• How rollback segments are assigned
• How the database handles multiple and concurrent transactions
• Locking techniques used to ensure data integrity
• Execution of SQL and PL/SQL statements
• In-doubt and distributed transactions
• Commits, setpoints, and rollbacks
• Behind-the-scenes processes that comprise a session and transaction
• Database parameters that affect the execution of a transaction

Sessions Versus Transactions

A transaction is directly related to a session, but it is still considered a separate entity. A session, simply stated, is a single connection to a database instance based on a username and, optionally , a password. All sessions in a database instance are unique, which means that they have a unique identifier setting them apart from the other users and processes accessing the database. This unique identifier, called a SID, is assigned by the instance and can be reused by future sessions after the current session has ended. The combination of the SID and a session serial number guarantees that each no session, even if the number is reused, is identical.

NOTE

The serial number is used to uniquely identify the objects being manipulated in a given session, and the combination of the SID and serial number guarantees a session's uniqueness. The serial number is used to ensure that any session-level commands are applied to the correct objects in the event that a session is terminated and the SID is reassigned.

A transaction, also in simplified terms, is a specific task, or set of tasks , to be executed against the database. Transactions start with an executable DML statement and end when the
statement or multiple statements are all either rolled back or committed to the database, or when a DDL (Data Definition Language) statement is issued during the transaction.

Page 673

If COMMIT or ROLLBACK statements are issued from the command line, the transaction is said to have been explicitly ended. However, if you issue a DDL command (DROP TABLE, ALTER TABLE, and so on), the previous statements in your transaction will be committed (or rolled back if unable to commit), the transaction will be implicitly ended, and a new transaction will begin and then end for the DDL statement.

NOTE

A DDL statement constitutes an entire transaction, due to the nature of the statements. When a DDL statement that begins your previous transaction is implicitly ended, a new transaction begins and then the transaction is ended.

To illustrate these rules, assume that you log in to your database to update and modify your customer tables. What you would like to do is enter 100 new customers to your database. You could do this by creating a temporary table to hold that customer information, adding the information to the customer table, searching your customer table for duplicates, and rolling back the transaction if there are duplicates. Though this is unlikely , assume that you must update the customer table before checking for duplicate entries. The sequence would look like the steps listed in the following sequence of session and transaction begins and ends without using savepoints:

1. Connect to SQL*Plus (begin session 1).
2. Create temporary customer table (begin and end transaction 1).
3. Insert new customer information into temporary table (begin transaction 2).
4. Step through entries in temporary table (continue transaction 2).
5. Update customer table (continue transaction 2).
6. Check for duplicate entries (continue transaction 2). If duplicates exist, roll back entire transaction (end transaction 2).
7. Repeat steps 4_7 until complete or duplicates are found.
8. Drop temporary table (end transaction 2, begin and end transaction 3).
9. Exit SQL*Plus (end session 1).

Notice how the create-table and drop-table steps (steps 2 and 8) begin and end a transaction. If you found duplicate entries in your tables, step 8 would actually end transaction 3 and begin and end transaction 4. Also note that the DDL command in step 5 implicitly ended transaction 2 by committing any changes made before beginning transaction 3. Finally, it is important to realize that if you had done another update between steps 5 and 6, the exit from SQL*Plus would have implicitly ended transaction 4 (started by the update) by issuing a commit before exiting.

Page 674

NOTE

The relationship between tables is irrelevant when discussing transaction begins and ends. For example, if you update a set of related tables and then attempt to issue a DDL statement against another set of tables from within the same session, the DDL statement attempts to commit the previous set of changes (your previous transaction), if they have not already been committed or rolled back, and then executes the DDL statement as a separate transaction.

One other form of implicitly ending a transaction includes terminating a session either normally or abnormally. When a session is disconnected normally, the instance automatically attempts to commit the current transaction. If that is not possible, the transaction will be rolled back. If a session is terminated abnormally, the current transaction is rolled back.

Commits, Rollbacks, and Savepoints

Although commits, rollbacks, and savepoints are discussed elsewhere in this book, it is important to note how they affect a given transaction. As mentioned earlier, commits and rollbacks both end a transaction. Commit makes all changes made to the data permanent. Rollback reverses all changes made during the transaction by restoring the previous state of all modified data. With the use of savepoints, the ROLLBACK command can also be used to roll back only a portion of a transaction.

Savepoints were designed to be used as logical stopping points from within a single transaction. They are helpful in splitting up extremely long transactions into smaller portions, and they provide points of recovery along the way. Using savepoints within a transaction enables you to roll back the transaction to any given savepoint as long as a commit has not been issued (which immediately commits all data, erases all savepoints, and ends the transaction). Refer to Chapter 13, "SQL*Plus," to learn more about the SAVEPOINT command as well as how to use ROLLBACK to roll back the current transaction to a specified savepoint.

The following list is an update to the previously shown sequence, with the addition of savepoints. Refer to this example to show how savepoints affect the transaction:

1. Connect to SQL*Plus (begin session 1).
2. Create temporary customer table (begin and end transaction 1).
3. Insert new customer information into temporary table (begin transaction 2).
4. Step through each entry in the temporary table (continue transaction 2).
5. Create unique savepoint (continue transaction 2).
6. Update customer table with information from temporary table (continue trans-
   action 2).

Previous Table of Contents Next