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 issued a rollback segment, or how locking occurs in the database can drastically change the strategy of creating applications or nightly processes.
This chapter covers, from start to finish:
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 upon 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 after a session is ended.
The combination of the SID and a session serial number guarantees that each no session, even if the number is reused, is identical.
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.
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.
To illustrate these rules, assume that you log into your database to update and modify your customer tables. What you would like to do is enter 100 new customers to your database. You do this by creating a temporary table to hold that customer
information, search your customer table for duplicates, and update those records if they do not exist. Though this is unlikely, assume that you must update 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 savepoints.
Notice how the create-table and drop-table 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.
One other form of implicitly ending a transaction includes terminating a session either normally or abnormally. When these situations arise, the instance automatically attempts to commit the current transaction. If that is not possible, the transaction
will be rolled back.
Although these topics are discussed elsewhere in this guide, 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 6, "SQL*Plus," to
learn more about the SAVEPOINT command as well as how to use ROLLBACK to roll the current transaction back 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.
Notice how the savepoint enables you to roll back to a point within your current transaction without affecting the previous updates before the savepoint. Anywhere within your procedure, you can roll back to any savepoint or you can roll back the entire
transaction. By using the savepoints, you are providing a collection of recovery points that are available to you until you end that transaction. Once the transaction is ended, all savepoints are erased.
Transaction control statements are statements that affect the execution or properties of a transaction, whether it is the management of data or characteristics of how the transaction executes. The family of transaction control statements include:
Several names are used to identify transactions and their states. Knowing these terms is helpful in understanding the terms mentioned by Oracle and interpreting Oracle errors returned during a transaction. These terms cover types of transactions as well
as other terms used in identifying them.
Concurrent transactions are transactions that are executed in the same general time. These transactions, because they have started so close to each other, generally do not see the changes made by the other transactions. Any data that has been
updated by a concurrent transaction and requested by another concurrently running transaction must be read from rollback segments until the transaction requesting the data has completed. This has the potential of leading to the error message Snapshot too
old, which is discussed in more detail under the "Assigning Rollback Segments" section of this chapter.
A discreet transaction is used to improve the performance of short transactions. For developers creating custom applications, the procedure BEGIN_DISCREET_TRANSACTION() changes the steps followed during the duration of a session in order to speed
its processing. The main differences are as follows:
Because the overhead associated with redo and rollback segments is bypassed by storing information directly in the SGA, these transactions bypass a goodly amount of processing overhead. At first this sounds more like a flaw than a feature, but on second
thought, most changes can be made in a very short amount of time because these transactions are short in nature.
Distributed transactions are transactions in which one or more statements manipulate data on two or more nodes, or remote systems, of a distributed database. If a transaction manipulates data on only one node, it is considered a remote
transaction. As in a remote transaction, none of the redo information is stored locally.
An in-doubt transaction is actually a state of a transaction instead of a type and refers to transactions within a distributed database environment. One situation that causes this state is if an instance involved in a currently running
transaction fails, that transaction must be either rolled back or committed. It is difficult, however, to do either without knowing the state of the transaction in the affected database. In this case, all other instances in the distributed environment mark
this transaction as in-doubt. Once the instance is restarted, the transaction can be analyzed and all instances can either commit or rollback.
It is possible to force the commit or rollback of a distributed transaction by using either SQL*DBA and doing a Recover In-Doubt Transaction, or the command COMMIT WORK ... FORCE with the local or global transaction ID of the in-doubt transaction. Refer
to Chapter 7, "SQL*DBA," or Chapter 6, "SQL*Plus," for further information on how to roll back or commit this transaction.
Normal transaction is a term used to refer to a local (non-remote) transaction. All redo information is stored in the local database, and all data manipulation is done to the same database. This type of transaction is the focus for the discussion
on transaction processing.
Read-only refers to the type of read consistency that is set or defaulted to for a given transaction. By default, the level of read consistency is statement level, which is also known as read-write. This means that each consecutive statement in
your transaction will see the changes made to the database by any previous statements regardless of whose transaction has committed the changes.
By changing the read consistency from statement level to transaction level, you force the current transaction to ignore any changes made to the database during this transaction and view the data as it existed immediately before the transaction started.
This mode is helpful if you are executing long running reports against tables that might change during the duration of the report.
When you are creating a read-only transaction, two major changes take effect. First, the number of commands available to the read-only transaction is limited. Second, because the process is literally read-only, it does not require additional locks
against tables and does not acquire a rollback segment or redo log. This is helpful because it limits the processing overhead from the database associated with normal transactions.
Please refer to the command reference at the end of this chapter for a list of commands to which read-only transactions are limited.
Remote transactions are transactions containing single or multiple statement(s) to be executed against a non-local database. These statements all reference the same node. If they do not, they are considered separate remote transactions and the
instance will split them up. One of the major differences between remote and normal transactions is that redo and rollback information against a remote transaction is stored on the remote database. None of this information is transferred to your local
database to be used for recovery.
Read-consistency is not a difficult concept to grasp. In short, read-consistency guarantees that the data you are viewing while executing a transaction does not change during that transaction. With read-consistency, if two users are updating the
same table at the same time, user1 will not see the changes made by the other user during their transaction. User2, likewise, cannot see any changes committed by user1 until both transactions are complete. If they happen to be working on the same row in
the table, this becomes a locking issue instead of read-consistency. A later section discusses locking.
Read-consistency is the major building block that enables multiple users to update, select, and delete from the same tables without having to keep a private copy for each user. When combined with locking techniques, read-consistency provides the
foundation for a multi-user database in which users can do similar or identical operations without difficulty.
Take a look at an example of the way read-consistency works in a theoretical telemarketing department. user1 is entering an order for a customer, while user2 is changing customer information. Both users have concurrent transactions (they are executing
at the same time), but user1 began their transaction first. Suppose that user2 makes a mistake and changes the phone number for the same customer whose order is being entered. Because user1 began their transaction first, they will always be looking at the
"before picture" of the customer data and will see the customer's previous phone number when querying the user's data. This is true even if user2 commits their changes. Why? Because it is possible that user1's transaction is solely dependent on
the data that existed when their transaction began. Imagine the confusion that would result if data could be changed while an already executing query were making changes based on that data! It would be nearly impossible to guarantee the coordination and
functioning of all processing within the application.
Read-consistency is also a secondary function of rollback segments. Aside from being able to undo changes from a transaction, they also provide other users with a "before picture" of the data being modified by any process. If a transaction
must review data that has been modified by a concurrent uncommitted transaction, it must look in the rollback segments to find that data. You can find more information in the section on rollback segments within this chapter.
Understanding the steps followed during the execution of a transaction can be quite helpful in planning and implementing custom applications. It is also important for the database administrator to know these steps because they can help in understanding
and tuning the database parameters and processes. This discussion covers normal transactions. Other transactions, such as distributed, remote, and discreet, are treated a bit differently, because these transactions are short in nature, and those
differences are documented throughout this chapter. The processing steps follow.
The following sections examine each step individually.
The issuing of DML or DDL statements can take place through a number of ways, including SQL*Forms, SQL*Plus, and custom C programs. The rules governing the start and end of transactions are the same no matter which way a SQL statement is issued.
Rollback segments are assigned randomly by Oracle at the beginning of each transaction (not session) when the first DML statement is issued, and they are used to roll back the transaction to a specified savepoint or to the beginning of a transaction.
The selection of a rollback segment is based on which rollback segments are currently available and how busy each segment is. By default, DDL statements are not issued rollback segments due to the nature of DDL commands. They are, however, issued redo
entries so that the modifications can be reapplied if the database must be recovered.
Two types of transactions do not acquire rollback segments. These are read-only transactions and remote transactions. Read-only transactions, by their nature, do not modify data, so they do not require a rollback segment. Remote transactions actually do
acquire rollback segments, but these rollback segments are allocated on the remote database that the transaction is executed on. Distributed transactions are really a form of remote transactions and follow the same rule.
There is no limit to the number of rollback segments a user can access throughout a given session, but only one rollback segment will be used at any given time for any transaction. In other words, a transaction will acquire one and only one rollback
segment to be used for the duration of the transaction. Once the transaction is complete, the rollback segment is released. Space used in that rollback segment is dictated by the amount of data that is modified.
Transactions that modify large amounts of data require larger rollback segments. By using the SET TRANSACTION command, you can specify a specific rollback segment to be used by a given transaction. Reference the section on SET TRANSACTION for a further
explanation of how to do this. It is important to note, however, that a SET TRANSACTION command must be the very first command issued in a transaction. If it is not, an error message will be returned.
Once a transaction is completed, the rollback segment is released. This does not mean that the segment's data is overwritten immediately, though. Sometimes other transactions that started before this transaction finished need access to the unmodified
data for read-consistency. In this case, the rollback segment containing the "before picture" will be used. Unfortunately, Oracle does not lock this data into the rollback segment to prevent the data blocks from being reused if needed. If your
rollback segments are too small, you may encounter the error rollback segment too old. If this error occurs, the transaction that received the error is forced to rollback. This error implies two things:
In either situation, a transaction was accessing the before picture of some data that was still in a rollback segment when the system was forced to reclaim that extent to use for a currently executing transaction. Because this before picture is no
longer available, the executing transaction cannot continue. You can use three steps (separately or in conjunction with each other) to alleviate this problem:
Oracle's optimizer is a critical part in the execution of a transaction. The optimizer is responsible for taking a SQL statement, identifying the most efficient way of executing the statement, and then returning the data requested. There is a high
likelihood that a SQL statement can be executed in more than one way. The optimizer is responsible for identifying the most efficient means of executing that statement.
Optimization can take many steps to complete, depending on the SQL statement. The steps used to execute the SQL statement are called an execution plan. Once the execution plan is completed, it is then followed to provide the desired results
(updated or returned data).
Many factors govern how the optimizer creates an execution plan. These factors are based on the type of optimization method the database uses. At the database level, you have two types of optimization: cost-based and rule-based. The database parameter
OPTIMIZER_MODE, located in the init.ora parameter file, determines which type of optimization mode your instance will use. The parameter has two possible values:
Cost-based analysis is a mode of analyzing SQL statements to provide the most efficient way of execution. When the optimizer is running in cost-based mode, it follows these steps to decide which plan is the best way to execute the statement
unless the developer has provided a hint to use in the execution.
Cost-based analysis uses statistics generated by the ANALYZE command for tables, indexes, and clusters to estimate the total I/O, CPU, and memory requirements required to run each execution plan. Because the goal of the cost-based approach is to provide
maximum throughput, the execution plan with the lowest ranking or lowest estimated I/O, CPU, and memory requirements will be used.
The analysis used to provide the final cost of an execution plan is based on the following data dictionary views:
Rule-based analysis rates the execution plans according to the access paths available and the information in Table 19.1. The rule-based approach uses those rankings to provide an overall rating on the execution plan and uses the plan with the
lowest ranking. Generally speaking, the lower the rating, the shorter the execution time, though this is not always the case.
Type of Access
Single row by ROWID
Single row by cluster join
Single row by hash cluster key with unique or primary key
Single row by unique or primary key
Hash cluster key
Indexed cluster key
Bounded range search on indexed columns
Unbounded range search on indexed columns
MAX() or MIN() of indexed column
ORDER BY on indexed columns
Full table scan
Because the developer can sometimes optimize code more efficiently than the optimizer can, various directives, called hints, can be issued from within the SQL statement to force the optimizer to choose a different method of optimization. This
method works at the statement level from within the transaction and affects only the current statement.
To affect all statements at the transaction level, the SQL command ALTER SESSION SET OPTIMIZER_GOAL can be used. This command overrides the OPTIMIZER_MODE initialization parameter and forces all statements within the current transaction to be optimized
according to this value. This parameter has four possible values:
This parameter affects all SQL statements issued from within the transaction, including functions and stored procedures that are called. OPTIMIZER_MODE is still used for any recursive SQL calls issued by Oracle on behalf of the transaction, though.
A parsed statement is not to be confused with an execution plan of a statement. Whereas an execution plan examines the most efficient way to execute a statement, parsing the statement creates the actual executable statements to be used in retrieving the
data. Parsing a statement is a one-step process by the optimizer to do the following:
When checking the syntax and semantics, the instance is verifying that no key words or necessary parameters are missing. If the statement is in correct form, the instance then verifies that the user has the correct privileges required to carry out the
execution of the statement. Once these have been verified, space is allocated in the private SQL area for the user's statement. This statement is saved until either it is needed again or the memory space is required to store another parsed statement.
After allocating space in the private SQL area, the instance searches through the shared SQL area for any duplicate statements. If a duplicate statement is found, the executable version of the statement is retrieved from memory and executed by the
process, and the private SQL area is pointed to the statement in the shared area. If it is not found, an executable version is created and stored in the private SQL area only.
The locking of data rows and/or tables is completely automated and transparent to the user. Once the executable version of the SQL statement is run, Oracle automatically attempts to lock data at the lowest level required. This means that if possible, a
row will be locked instead of the entire table. This is dependent solely on how the SQL statement was written and what types of access are required (full table scan versus single rows).
A form of manual, or explicit, locking can take place by using the LOCK TABLE command. By default, these commands are not necessary in day-to-day processing. Oracle recommends that you allow the database to handle all locking of data whenever possible.
Each transaction that is committed has a corresponding redo log entry generated. This entry records just the changes applied to the database files, as well as rollback segment information. These entries in the redo logs are not traceable to a user
process. Should the database be brought offline by a system or database failure, you can use these redo logs to reconstruct the database files to a usable state.
Redo log entries are written to the redo log buffer in the SGA. These entries are then written to the online redo logs by the LGWR process. If the instance is running in archive log mode, the redo log files, once filled, are then written to
corresponding archived redo log files, which are separate from the data files that hold tables and data. These archived redo logs are the primary recovery method when the online redo logs have been cycled through or corrupted and no longer hold the data
needed for recovery.
From this point, there are several paths that a transaction can take to completion. Most commonly, the transaction is committed. Still, handling must be taken into account for transactions that are rolled back. Following are the steps taken during a
Should any of these steps fail, the transaction cannot be committed. Depending on the nature of the error, the transaction will either wait for the problem to be fixed so it can complete the transaction, or it will be rolled back.
The following steps illustrate what must take place if a transaction is rolled back.
The steps required to process remote and distributed transactions are nearly identical to the way normal transactions are processed. The biggest difference is where the statement is parsed, and the instance whose resources are used for processing. The
following steps add the logic required for remote and distributed transaction processing.
Once again, look at each step individually in the following sections.
All statements for remote and distributed transactions are entered on a local database, or a database where local data resides. It is not necessary to log in to a database where data will be manipulated in order to issue queries against that database,
because that is essentially what a remote or distributed transaction is.
Just as in a normal transaction, if any part of the transaction's statements modify data on the local database, a rollback segment is assigned to track any changes made to the data.
Oracle must break down all statements that query or modify remote data in order to send them as a group to the remote database(s). Once they are grouped according to remote database, the statements are sent, via SQL*Net, to their intended destination.
Just as in a normal transaction, the local statements are optimized, based on either the database parameter OPTIMIZER_MODE or the transaction-level parameter OPTIMIZER_GOAL. Once the desired explain plan is created and executed, data is returned and
held until all data from remote transactions has been received.
All remote commands are forwarded to the intended database before they are optimized or parsed. Once the remote database has received the information, it acts identically as it would on a local database: the statement is parsed, the shared SQL area is
searched in order to find an identical parsed representation of the statement, the statement is optimized if necessary, and then the statement is executed.
At this point, all data is returned to the local database user or application. If data is to be compared with other data from the local or another remote database, that action takes place on the local database. The local database is responsible for
correlating all returned data to provide the final desired output.
All statements that are sent to remote databases to update/manipulate data are assigned a rollback segment on the remote database as they would if manipulating data. The remote database is then responsible for all recovery operations should the database
fail or should the transaction require a rollback. Remote transactions function like normal transactions when a commit or rollback statement is issued.
Statements that are sent to remote databases are not parsed by the local database. This is so that the remote database's shared SQL area can be searched for identical statements. Once the statement is parsed, it is either optimized or the optimized
execution plan for the identical statement is used. Data is then returned to the local database.
As stated earlier, it is the responsibility of the local database, or the database from where the transaction was initiated, to receive data from all remote databases, correlate it, and return only the data that the original statement requested. This
can include joins, WHERE and IN clauses, and GROUP BY statements.
Despite the differences in where the bulk of the transaction processing resides, the steps are much the same. When working in a distributed environment, though, you need to take into account quite a few other steps when dealing with complex updates and
error handling. Should a transaction be abnormally terminated or an instance in the distributed environment go down, there are quite a few extra steps needed to help decide whether a distributed transaction should be committed or rolled back. It is better
to refer to more in-depth documentation to learn more about two-phase commits and exactly how Oracle deals with the problems resulting from a downed instance or terminated session from within a distributed environment.
SET TRANSACTION is used to alter the current transaction's properties. There are three options available, and the command must be the first command in your transaction. If you have already issued commands in the current transaction, you must
either issue a COMMIT or ROLLBACK before the SET TRANSACTION command will be accepted.
SET TRANSACTION has the following options:
READ ONLY refers to a read-only process where no updates can be performed on any tables in the database. It also sets the read-consistency level to transaction level, where all data viewed is a snapshot of the data as it existed when the transaction
first started. This option is helpful for sessions that will only query data because the processing overhead for this type of transaction is smaller than that of a normal transaction. It is also helpful for transactions, such as reports, that require a
snapshot in time of the current data. For these types of queries, though, processing overhead may be higher because other transactions that modify data will force the reporting transaction to search through rollback segments for the original unmodified
In a read-only transaction, the command set is limited to five groups of commands:
This type of transaction is the default, where the user has the ability to update and delete as well as query tables if they have the appropriate database privileges, and the read consistency is set to statement level. No SET TRANSACTION command must
take place for this option to be in effect because it is the default.
This option is used to set the rollback segment for transactions that update large amounts of data and therefore create larger than normal rollback segments. This rollback segment may have been created with larger initial and next extents to prevent the
maximum number of extents being reached in the rollback segment. The OPTIMAL parameter may also have been set higher, or not used, to prevent extents from being reclaimed and causing a read-consistency error for other concurrent transactions.
Three parameters affect how transactions can work with objects. These parameters affect snapshots, clusters, indexes and tables. These parameters are:
The PCTFREE parameter is used to set aside a percentage of a data block for work space in the object. This space is usually used for extending columns (such as VARCHAR2 data types). If this value is set too high, the result is wasted space in your data
file that cannot be reclaimed without extra work by the database administrator. If the value is too low, the result is either relocated or chained rows. Chained rows are rows that span more than one data block. This creates a problem in that the database
must do an additional seek to read the second data block for the row.
INITRANS specifies initial transactions, or the average number of concurrent transactions. For each transaction that will concurrently update a given object, 23 bytes of space are set aside for each data block to keep track of each transaction against
the row(s) located within that block. If this value is set too low, the database must dynamically allocate space from the free space in the object's data blocks to use as temporary storage. Dynamically allocating this space can slow down the execution time
of a transaction. If no free space can be allocated, the transaction will hang until free space can be allocated. The process may time-out if the wait is long enough.
MAXTRANS is the maximum number concurrent processes that can update a data block in use by the object. If this value is reached, further transactions cannot continue until other transactions have completed.
As you can see, knowing the steps that must be taken to process a transaction can greatly affect how a custom application is developed. If a transaction is querying data only, using a read-only transaction can greatly reduce the amount of processing
overhead required to run an application. If many users are running the same read-only query, this savings on overhead can be a considerable amount.
Likewise, knowing more about how statements are optimized can save a great deal of time in reaching the goals set for a new application. Because optimization methods take a critical role in meeting those goals, it is imperative that you take this into