Day 16

Managing Transactions
and Locks

by Tom Luers

In today's lesson you will learn about transactions and the benefits you can gain by controlling them. Managing transactions provides the user of the Oracle server, application developer, or database administrator the capability of guaranteeing data consistency and data concurrency. Data consistency provides the user a consistent view of data, which consists of data committed by other users as well as changes made by the user. Data concurrency provides the user access to data concurrently used by many other users. Without transactions coordinating data concurrency and data consistency, the user of the server would experience inconsistent data reads, lost updates, and nonrepeatable reads.

Types of Transactions

A transaction is a logical unit of work that is composed of one or more Data Manipulation Language (DML) or Data Definition Language (DDL) statements. For every transaction in Oracle, one of two situations occurs. If the statements in a transaction complete normally, then the effects of the transaction are made permanent in the database. This is called committing the transaction. The other situation occurs when any one of the statements is unable to complete for whatever reason. In this case, the effects of the transaction are removed from the database and the transaction ends. This removal of the effects of a transaction is called rolling back the transaction.

Oracle provides two general types of transactions: read-only and read-write transactions. The read-only transaction specifies that the queried data and all queries within the same transaction will not be affected by any other transactions that take place in the database. In other words, any subsequent query can only read changes committed prior to the beginning of the current transaction. The read-write transaction guarantees that data returned by a query is consistent with respect to the time the query began.

The read-only transaction enforces transaction-level read consistency. This type of transaction can only contain queries and cannot contain any DML statements. In this situation, only data committed prior to the start of the transaction is available to the query. Thus, a query can be executed multiple times and return the same results each time.

The read-write transaction provides for statement-level read consistency. This type of transaction will never see any of the changes made by transactions that commit during the course of a query execution.

Starting a Transaction

The transaction begins with the first SQL statement being executed and ends when the effects of the transaction are saved or backed out. The SET TRANSACTION command also will initiate a transaction.

The SET TRANSACTION command is an integral part of transaction management. This command performs one of these operations on the current transaction:

• Establishes the transaction as either a read-only or a read-write transaction.
• Assigns the current read-write transaction to a specified rollback segment.

The Syntax for the SET TRANSACTION Command

SET TRANSACTION parameter

In this syntax, parameter will be one of the following values:

• READ ONLY—Establishes transaction-level read consistency.
• READ WRITE—Establishes statement-level read consistency.
• USE ROLLBACK SEGMENT—Defines the appropriate rollback segment to be used.

The read-only transaction is the default mode of all transactions. With this mode, you will not have a rollback segment assigned. Additionally, you cannot perform an INSERT, a DELETE, an UPDATE, or a SELECT FOR UPDATE clause command during this transaction. The read-write transaction mode provides no restrictions on the DML statements allowed in the transaction.

The SET TRANSACTION command allows you to explicitly assign a particular rollback segment to the read-write transaction. This rollback segment is used to undo any changes made by the current transaction should a rollback be executed. If you do not specify a rollback segment, Oracle will assign one to the transaction.

The following example of the SET TRANSACTION command would allow the user to run this script every weekend without worrying about any other users who might be modifying data:

COMMIT;
SET TRANSACTION read only;
execute_weekend_packages;
COMMIT;

Ending a Transaction

Ending a transaction will either save the changes made by the transaction or will back out all changes. Saving all pending changes to the database is known as committing the transaction. Backing out is accomplished through the ROLLBACK statement or when there is abnormal termination in the transaction. The ROLLBACK statement is discussed further in the next section, "Canceling a Transaction."

Committing occurs when the user either explicitly or implicitly saves the transaction changes to the database permanently. Until you perform a commit, the following principles characterize the state of your transaction:

• Data Manipulation Language operations only affect the database buffer. Because the changes have only affected the buffer, these changes can be backed out.
• A rollback segment buffer is created in the server.
• The owner of the transaction can view the effects of the transaction with the SELECT statement.

• Other users of the database cannot see the effects of the transaction.
• The affected rows are locked and other users cannot change the data within the affected rows.

After the commit is executed, the following occurs:

1. Locks held on the affected rows are released.
2. The transaction is marked as complete.
3. The internal transaction table of the server generates a system change number, assigns this number to the transaction, and saves them both in the table.

Use the COMMIT statement to explicitly make permanent the changes from a transaction. The following example shows a simple transaction being executed with a commit being issued after the transaction is executed:

SQL>INSERT INTO TABLE raw_material VALUES
SQL>(part_id, part_name)
SQL>VALUES (s_raw_mat.nextval, "18g Copper Wire")
1 row created
SQL> COMMIT;
Commit completed

You can use the COMMENT clause with the COMMIT statement to place a text string in the data dictionary along with the transaction ID. You can view this information in the dba_2pc_pending data dictionary view. Usually you will use this view to obtain additional information about a transaction that has a questionable status in a distributed environment.

To make an explicit commit, you must have the force transaction system privilege. To manually commit a distributed transaction that was originated by another user, you must have the force any transaction system privilege.

Oracle performs an implicit commit before and after every Data Definition Language command.

Canceling a Transaction

Rolling back a transaction means to undo any change that the current transaction has made. The ROLLBACK command will undo the entire transaction. To execute a rollback of the entire transaction, issue the ROLLBACK command. The following example illustrates the use of the ROLLBACK command to undo the effects of the UPDATE command:

UPDATE TABLE employee
set pay_rate = pay_rate * 1.25
WHERE pay_type = `S';
ROLLBACK;