Using transactions in Oracle 10g

Learning objective

After completing this topic, you should be able to identify the functions of different SQL statements and recognize the function of data locking.

1. Transactions in Oracle 10g

A transaction begins when an executable SQL statement runs.

Oracle assigns the transaction to an available undo segment in an undo tablespace.

This segment records the undo entries for the new transaction.

The entries in the undo tablespace are used when a ROLLBACK command is issued.

These are used to return the data to its last committed state before the current transaction.

At certain points during your session, when you have confirmed that the changes you have made are correct, you execute a COMMIT command to make the changes specified in the current transaction permanent.

This ends the current transaction and fixes the state of the database, preventing the transactions carried out since the previous commit - or since the start of the session - from being undone.

After the transaction ends, the next SQL statement you write begins a new transaction.

Even if you don't execute a COMMIT command, the transaction can still end if

• you execute a data definition language (DDL) command
• you disconnect from the database
• the session terminates abnormally

you execute a data definition language (DDL) command

When you execute a data definition language (DDL) command - such as CREATE, ALTER, or DROP - Oracle commits all data manipulation language (DML) statements - such as ADD and DELETE - in the transaction. It then runs and commits the DDL statement.

you disconnect from the database

If you disconnect from the database, the current transaction is committed automatically.

the session terminates abnormally

If your session terminates abnormally, Oracle uses the relevant undo segment to roll the database back to its state before the current transaction began.

You can divide a transaction into smaller parts using what are called savepoints.

You use the SAVEPOINT command to create a savepoint. Each savepoint has a name so that you can specify the one to which you wish to roll back.

Rather than rolling back to the previous commit state, you can instead roll back to a particular savepoint.

Question

Match each transaction-control command to its function.

Options:

1. COMMIT
2. ROLLBACK
3. SAVEPOINT

Targets:

1. Divides a transaction into smaller parts
2. Makes the changes specified in the current transaction permanent
3. Undoes changes you have made

Answer

COMMIT makes the changes specified in the current transaction permanent. ROLLBACK undoes changes you have made. SAVEPOINT divides a transaction into smaller parts.

COMMIT ends the current transaction. It fixes the state of the database so that the commands in the transaction just ended can't be undone.

ROLLBACK ends the current transaction. The relevant undo data is retrieved, and the commands in the transaction just ended are undone.

Because you need to refer to a savepoint specifically, you need to assign it a name.

Oracle Database 10g's fine-grained locking mechanism provides data concurrency and data integrity in transactions.

This means that a single user can update a specific data row at a particular time, and no other user can update that row while the first user is working on it.

However, all other rows in the relevant table are available for modification.

Question

Identify the benefits of data locking.

Options:

1. It enables multiple users to work on the same row of data at the same time
2. It locks all users out of a database while you are working on it
3. It makes the remainder of a table available to other users while you work on a particular row
4. It prevents multiple users from changing a row at the same time

Answer

Data locking makes the remainder of a table available to other users while you work on a particular row, and it also prevents multiple users from changing a row at the same time.

Option 1 is incorrect. Oracle Database 10g's data-locking mechanism is designed to prevent this from occurring.

Option 2 is incorrect. The data-locking mechanism is designed to make as much of the database as possible available to multiple concurrent users.

Option 3 is correct. Oracle Database 10g's data-locking mechanism is fine-grained.

Option 4 is correct. This ensures data integrity.

2. COMMIT, ROLLBACK, and SAVEPOINT

Suppose that you have created a table that has yet to be populated with data.

When you query its contents, no results are generated.

SELECT * FROM local_temp

You add a row of data to the table.

INSERT INTO local_temp
VALUES (SYSDATE, 76, 58, 67)

Then you query the table again. This time, the command returns the contents of the new row.

SELECT * from local_temp

You decide to roll back the current transaction.

ROLLBACK

To do this, you type ROLLBACK and then click Execute.

The transaction has been rolled back and the table is empty again.

This means that no results are generated when you query the table contents again.

SELECT * FROM local_temp

You add further data to the table, and then you decide to create a savepoint called before_insert.

SAVEPOINT before_insert

You type SAVEPOINT before_insert and click Execute.

You have created a savepoint called before_insert.

Suppose now that you insert a row into the LOCAL_TEMP table, and then decide that you want to roll the code back to the before_insert savepoint.

To roll back to the savepoint, you need to add a TO clause to the rollback statement. This clause specifies the name of the savepoint.

ROLLBACK TO before_insert

You type ROLLBACK TO before_insert and then you click Execute.

The statement rolls back to the before_insert savepoint so that the latest insertion is rolled back.

But no commands that were executed before the savepoint in the current transaction are rolled back.

You decide to commit any changes you have made in the current transaction.

COMMIT

You type COMMIT and then you click Execute.

The changes made in the current transaction are committed.

Question

You have initiated a transaction by adding a row to the locations table.

Type the command that undoes this change to the database.

Answer

You use the ROLLBACK command.

3. Other statement categories

Besides ROLLBACK, COMMIT, and SAVEPOINT, a number of other SQL*Plus commands can be used to work with transactions. They fall into three categories.

• Session control statements
• System control statement
• Embedded SQL statements

Session control statements

Session control statements enable you to implement dynamic management of your session. They are

• ALTER SESSION
• SET ROLE

The ALTER SESSION command can

• modify National Language Support (NLS) parameter values
• set the server logging level
• set the schema emulation mode

You can use ALTER SESSION to change the ISO currency symbol to the ISO currency symbol for the territory America, for example.

Session control statements don't implicitly commit the transaction. For this reason, to commit the transaction you can add the committed keyword to the end of the statement.

The code to do this is:

ALTER SESSION SET nls_iso_currency = America committed

You use the SET ROLE command to enable and disable roles in the current session.

In this example you enable all roles granted to you in the current session.

The code is as follows:

SET ROLE all

System control statement

In SQL*Plus, there is a single system-control statement: ALTER SYSTEM.

You use ALTER SYSTEM to dynamically manage the properties of an instance.

In this example, you dynamically change the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54.

The code to do this is:
ALTER SYSTEM
SET license_max_sessions = 64
license_sessions_warning = 54

Embedded SQL statements

Embedded SQL statements are statements supported by Oracle precompilers that can be embedded DL, DML, and transaction-control statements in procedural code, such as a C++ application.

For example, in C++ you use an EXEC SQL expression to execute a SQL statement.

Question

Match each of these statement categories with the corresponding feature.

Options:

1. Embedded SQL statements
2. Session control statements
3. System control statements

Targets:

1. These include ALTER SESSION and SET ROLE
2. In SQL*Plus, this category includes only ALTER SYSTEM
3. These are statements that can run in certain procedural languages

Answer

Session control statements include ALTER SESSION and SET ROLE. The only SQL*Plus system control statement is ALTER SYSTEM. Embedded SQL statements can run in certain procedural languages.

Embedded SQL statements must be supported by the relevant Oracle precompiler.

Session control statements manage sessions dynamically.

A system control statement manages the properties of an instance dynamically.

Summary

You can undo the SQL commands executed during a transaction before it is committed. When the transaction is committed, it ends and the current state of the database is fixed. From that point, the statements that made up the transaction can no longer be undone. You can divide up a transaction using savepoints. Oracle Database 10g's data-locking mechanism provides data-concurrency and data-integrity functionality.

You undo SQL statements using the ROLLBACK command. You commit a transaction using the COMMIT command. You create a savepoint using the SAVEPOINT command, specifying a name for the savepoint. To roll the database back to a savepoint, you use the ROLLBACK command, appending a TO clause specifying the relevant savepoint name.

Besides COMMIT, ROLLBACK, and SAVEPOINT, there are three other categories of transaction-related SQL commands: session control statements, system control statements, and embedded SQL statements.