One of the key features provided by the Oracle relational database engine is support for transactions. As you've seen in earlier chapters, a transaction is a logical unit of work--;a series of database changes that reflect a well-defined event in the real world. This chapter explores the interplay between transactions and concurrency and also looks at the use of rollback segments in supporting transactions.
A customer transfers money from a checking account to a savings account, a patient is transferred from an intensive care unit to a regular bed, a jet engine subassembly passes final inspection--;these are all examples of real transactions or events that must be accurately represented in a database. If a bank increases the funds in a customer's savings account and, for whatever reason, the same amount is not successfully subtracted from the customer's checking account, the transaction has failed and all database changes made on behalf of the transaction must be undone.
A database transaction is a logical unit of work. A number of changes are made to database tables. If any of these changes fails, the entire transaction is undone or rolled back. If all changes are successful, you can commit the transaction by invoking the SQL COMMIT statement.
Oracle defines a transaction as the set of database changes that have been made since the beginning of an Oracle session or since the last COMMIT or ROLLBACK statement was invoked. The changes made by a transaction are not visible to other Oracle users until the transaction is committed.
If an Oracle instance is up and running and the system in which it resides suddenly fails--;for example, due to a power failure--;you have instance failure. The next time that the instance is started, whether via Database Manager or SQL*DBA, Oracle performs instance recovery.
Oracle automatically performs these actions whenever an Oracle instance is started, whether or not the instance had previously failed.
Concurrency is the ability of multiple users to simultaneously read and modify various database tables. Oracle maximizes concurrency by enabling multiple users to query a table even while the contents of the table are being changed. You can observe this process by using SQL*Plus and SQL*DBA to modify and query the same table.
You can demonstrate this concept with the Test_Table table. In SQL*Plus, a SELECT statement is issued to query Test_Table. (In this example, the SQL*Plus variable TIME is set to ON so you can observe the sequence of events.) As you can see in Figure 17.1, Test_Table has seven rows. The first step is to delete the contents of Test_Table.
Next, invoke SQL*DBA by double-clicking its icon and then connect to Oracle as the user FRAYED_WIRES. A query of Test_Table returns the same seven rows as seen with SQL*Plus (see Figure 17.2)--;even though the rows have been deleted in the SQL*Plus session.
Switch back to the SQL*Plus window and commit the transaction. (See Figure 17.3.)
After a COMMIT has been issued in SQL*Plus, the effect of the transaction is visible in SQL*DBA. As you can see in Figure 17.4, no rows are retrieved when Test_Table is queried. Note that the changes to the table performed by one Oracle session aren't visible to another Oracle session--;even if the same Oracle user is connected to both sessions.
To maintain concurrency and read consistency, Oracle uses a variety of locks, depending on the submitted SQL statement. (See Chapter 9, "Using SQL to Modify Data," for more information about read consistency.) These locks are either exclusive or share. Exclusive locks behave as you would expect; when a user places an exclusive lock on a resource, no other user has similar access to that resource until the lock is relinquished. Share locks enable multiple users to read from the locked resource. Oracle's management of these locks is transparent to database users.
TipIn examining the online Oracle documentation, you might stumble upon the LOCK TABLE statement, which places an exclusive lock on a table. Avoid using the LOCK TABLE statement. If you're using this statement so that users can't make any changes while you perform some table maintenance, you might be making things worse; you might forget to place a lock on all the tables that you're working on. Instead, start the database in exclusive mode via SQL*DBA.
The SELECT FOR UPDATE statement is an Oracle SQL statement that locks the selected rows so that another user can't modify them until you use a COMMIT or ROLLBACK statement to release the row locks. For instance, suppose that both Jones and Smith are updating patient records. Jones brings up the record for Patient ID 9191 and begins to change the billing information. Smith also brings up the record for Patient ID 9191 and starts changing the patient's insurance information. If the application software doesn't lock the record with a SELECT FOR UPDATE, both users are able to make changes; however, the last person to issue an UPDATE statement actually changes the row.
To preclude this possibility, you should use the SELECT FOR UPDATE statement. The syntax for SELECT FOR UPDATE is
select-stmt FOR UPDATE [NOWAIT];
where select-stmt is a valid Oracle SELECT statement that identifies the rows to be modified.
You should specify the NOWAIT option if you want Oracle to return an error code indicating that the record is already locked. Without the NOWAIT option, the statement waits until the lock is released from the row through a COMMIT or ROLLBACK issued by the Oracle user who has a lock on the row. By using SELECT FOR UPDATE with the NOWAIT option, an application developer can take some other action when a row is locked--;for example, delay five seconds and try again and notify the user after three tries that the row is currently unavailable.
In Figure 17.5, the record for patient ID R4321 is locked from SQL*Plus by issuing a SELECT FOR UPDATE statement.
From SQL*DBA, an attempt is made to lock the same record. However, because the NOWAIT option is used with SELECT FOR UPDATE, Oracle returns an error message to indicate that the resource--;the row--;is in use. (See Figure 17.6.)
Because a database transaction can be rolled back, Oracle must have a place to write the rows that are changed by a transaction. The rollback segment is the database object that contains all changes that are made by a transaction. The default database installed by Personal Oracle contains four rollback segments: SYSTEM, RB_TEMP, RB1, and RB2.
NoteWith Personal Oracle7 for Windows 95, you probably won't need to create additional rollback segments because this version can automatically extend a tablespace. Therefore, a rollback segment that needed another extent would cause the tablespace to automatically allocate additional space. In addition, the Windows 95 version includes a larger number of rollback segments than existed (by default) in Windows 3.1.
With a large transaction, you might exhaust the available space in the rollback segment tablespace. For example, the following anonymous PL/SQL block attempts to insert one million rows into a table.
SQL> declare
2
2 max_records constant int := 1000000;
3 i int := 1;
4
4
4 begin
5
5 for i in 1..max_records loop
6
6 insert into XYZ
7 (Column1, Column2)
8 values
9 (i, i);
10
10 end loop;
11
11 commit;
12
12 end;
13 /
declare
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment (id = 2)
ORA-01650: unable to extend rollback segment RB1 by 50 in
tablespace ROLLBACK_DATA
ORA-06512: at line 6
If you want to delete all the rows in a table, use the TRUNCATE TABLE statement--;it's faster because it doesn't write the deleted rows to a rollback segment. Don't use the TRUNCATE TABLE statement if you are deleting all rows in a table as part of a transaction.
Whenever a transaction is started, a rollback segment is assigned to store the row changes associated with the transaction. If the transaction is very large--;meaning that a large number of rows are affected by the transaction--;the assigned rollback segment might have many extents allocated for storing the changed rows. If a transaction is exceptionally large, either of two events can occur:
To reduce the likelihood of either of these situations occurring, you should perform the following steps:
The next section explains how to create a new rollback segment.
You can create a new rollback segment with the CREATE ROLLBACK SEGMENT statement. The syntax is
CREATE ROLLBACK SEGMENT rollback-segment-name [TABLESPACE tablespace-name] [STORAGE storage-clause]
where rollback-segment-name is the name of the rollback segment to be created and tablespace-name is the tablespace where rollback-segment-name will be stored (which should usually be set to ROLLBACK DATA for Personal Oracle or the Oracle Workgroup Server). storage-clause is an optional set of storage parameters used for rollback-segment-name. (See Chapter 26 for more information.)
The following creates rollback segment RB_LARGE with larger values for the initial and next extents:
SQL> create rollback segment RB_LARGE 2 tablespace ROLLBACK_DATA 3 storage (initial 200K next 200K); Rollback segment created.
Even though RB_LARGE has been successfully created, it is not yet available for use by transactions, as you can see by inspecting the data dictionary view DBA_ROLLBACK_SEGS. The status of RB_LARGE is shown as OFFLINE.
SQL> select segment_name, status 2 from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE RB_TEMP ONLINE RB1 ONLINE RB2 ONLINE RB_LARGE OFFLINE
The next step in adding the new rollback segment is to modify the initialization parameter rollback segments to include the new rollback segment. You can accomplish this task with Database Manager.
As Database Manager indicated, you have to restart the database to implement a change to an initialization parameter. Using Database Manager, shut down Personal Oracle and restart it. Once again, query the data dictionary view DBA_ROLLBACK_SEGS. This time, you'll see that RB_LARGE is ONLINE.
SQL> select segment_name, status 2 from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE RB_TEMP ONLINE RB1 ONLINE RB2 ONLINE RB_LARGE ONLINE
To ensure that a transaction uses a particular rollback segment, use the SET TRANSACTION statement with the syntax
SET TRANSACTION USE ROLLBACK SEGMENT rollback-segment;
where rollback-segment is the rollback segment to be assigned to the transaction.
For example, if you issue the following statement at the beginning of a transaction, all subsequent row changes are stored in rollback segment RB_LARGE:
SQL> set transaction use rollback segment RB_LARGE;
Transaction set.
If you don't issue the SET TRANSACTION statement at the beginning of a transaction, Oracle returns the following error message:
SQL> set transaction use rollback segment RB_LARGE; set transaction use rollback segment RB_LARGE * ERROR at line 1: ORA-01453: SET TRANSACTION must be first statement of transaction
One of the SQL*Plus system variables, AUTOCOMMIT, controls the transaction behavior of SQL*Plus. By default, AUTOCOMMIT is set to OFF. The effect of this setting is that SQL*Plus will commit changes to a table only in the following two situations:
Here is an example of the effect of setting AUTOCOMMIT to ON:
SQL> select * from test_table;
RECORD_NUMBER CURRENT_D
------------- ---------
1 22-JUN-95
2 22-JUN-95
3 22-JUN-95
4 22-JUN-95
5 22-JUN-95
6 22-JUN-95
7 22-JUN-95
7 rows selected.
SQL> set autocommit on
SQL>
SQL> delete from test_table;
Commit complete.
7 rows deleted.
SQL> rollback;
Rollback complete.
SQL> select * from test_table;
no rows selected
Notice that the ROLLBACK statement had no effect because the previous DELETE statement had already been committed.
This chapter presents essential information regarding database transactions: