6.1.1 The COMMIT Statement

When you COMMIT, you make permanent any changes made by your session to the database in the current transaction. Once you commit, your changes will be visible to other Oracle sessions or users. The syntax for the COMMIT statement is:

	COMMIT [WORK] [COMMENT text];

The WORK keyword is optional and can be used to improve readability.

The COMMENT keyword specifies a comment which is then associated with the current transaction. The text must be a quoted literal and can be no more than 50 characters in length. The COMMENT text is usually employed with distributed transactions. This text can be handy for examining and resolving in-doubt transactions within a two-phase commit framework. It is stored in the data dictionary along with the transaction ID.

Note that COMMIT releases any row and table locks issued in your session, such as with a SELECT FOR UPDATE statement. It also erases any savepoints issued since the last COMMIT or ROLLBACK.

Once you COMMIT your changes, you cannot roll them back with a ROLLBACK statement.

The following statements are all valid uses of the COMMIT:

COMMIT; COMMIT WORK; COMMIT COMMENT 'maintaining account balance'.

6.1.2 The ROLLBACK Statement

When you ROLLBACK, you undo some or all changes made by your session to the database in the current transaction. Why would you want to erase changes? From an ad hoc SQL standpoint, the ROLLBACK gives you a way to erase mistakes you might have made, as in:

	DELETE FROM orders;

"No, no! I meant to delete only the orders before May 1995!" No problem, just issue ROLLBACK. From an application coding standpoint, ROLLBACK is important because it allows you to clean up or restart from a "clean state" when a problem occurs.

The syntax for the ROLLBACK statement is:

	ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop.

The parameterless ROLLBACK undoes all outstanding changes in your transaction.

The ROLLBACK TO version allows you to undo all changes and release all acquired locks which were issued since the savepoint identified by savepoint_name was marked (see the next section on the SAVEPOINT statement for more information on how to mark a savepoint in your application).

The savepoint_name is an undeclared Oracle identifier. It cannot be a literal (enclosed in quotes) or variable name.

All of the following uses of ROLLBACK are valid:

ROLLBACK; ROLLBACK WORK; ROLLBACK TO begin_cleanup;

All of the following uses of ROLLBACK are invalid:

ROLLBACK SAVEPOINT;   -- ORA-02181: invalid option to ROLLBACK WORK   -- Must use TO keyword before SAVEPOINT. ROLLBACK WORK TO;   -- ORA-02182: save point name expected   -- Must specify savepoint name. ROLLBACK TO SAVEPOINT 'favorite_movies';   -- ORA-03001: Unimplemented feature   -- Savepoint cannot be in quotes.

When you roll back to a specific savepoint, all savepoints issued after the specified savepoint_name are erased. The savepoint to which you roll back is not, however, erased. This means that you can restart your transaction from that point and, if necessary, roll back to that same savepoint if another error occurs.

Immediately before you execute an INSERT, UPDATE, or DELETE, PL/SQL implicitly generates a savepoint. If your DML statement then fails, a rollback is automatically performed to that implicit savepoint. In this way, only that last DML statement is undone.

6.1.3 The SAVEPOINT Statement

SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks which occurred before you marked the savepoint.

The syntax for the SAVEPOINT statement is:

SAVEPOINT savepoint_name;

where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and #, $, or _ ), but that you do not need to (nor can you) declare that identifier.

Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current transaction, that savepoint is "moved" from its original position to the current point in the transaction, regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently marked savepoint.

6.1.4 The SET TRANSACTION Statement

The SET TRANSACTION statement allows you to begin a read-only or read-write session, establish an isolation level, or assign the current transaction to a specified rollback segment. This statement must be the first SQL statement processed in a transaction and it can appear only once. This statement comes in the following four flavors:

SET TRANSACTION READ ONLY;

This version defines the current transaction as read-only. In a read-only transaction, all subsequent queries only see those changes which were committed before the transaction began (providing a read-consistent view across tables and queries). This statement is useful when you are executing long-running, multiple query reports and you want to make sure that the data used in the report is consistent:

SET TRANSACTION READ WRITE;

This version defines the current transaction as read-write:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ COMMITTED;

This version defines how transactions that modify the database should be handled. You can specify a serializable or read-committed isolation level. When you specify SERIALIZABLE, a data manipulation statement (update, insert, delete) which attempts to modify a table already modified in an uncommitted transaction will fail. To execute this command, you must set the database initialization parameter COMPATIBLE to 7.3.0 or higher.

If you specify READ COMMITTED, a DML which requires row-level locks held by another transaction will wait until those row locks are released:

SET TRANSACTION USE ROLLBACK SEGMENT rollback_segname;

This version assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. This statement cannot be used in conjunction with SET TRANSACTION READ ONLY.

6.1.5 The LOCK TABLE Statement

This statement allows you to lock an entire database table with the specified lock mode. By doing this, you can share or deny access to that table while you perform operations against it. The syntax for this statement is:

LOCK TABLE table_reference_list IN lock_mode MODE [NOWAIT];

where table_reference_list is a list of one or more table references (identifying either a local table/view or a remote entity through a database link), and lock_mode is the mode of the lock, which can be one of the following:

ROW SHARE

ROW EXCLUSIVE

SHARE UPDATE

SHARE

SHARE ROW EXCLUSIVE

EXCLUSIVE

If you specify the NOWAIT keyword, Oracle will not wait for the lock if the table has already been locked by another user. If you leave out the NOWAIT keyword, Oracle waits until the table is available (and there is no set limit on how long Oracle will wait). Locking a table never stops other users from querying or reading the table.

The following LOCK TABLE statements show valid variations:

LOCK TABLE emp IN ROW EXCLUSIVE MODE; LOCK TABLE emp, dept IN SHARE MODE NOWAIT; LOCK TABLE scott.emp@new_york IN SHARE UPDATE MODE;

Now that you know the "macro" commands for managing transactions from within a PL/SQL application, let's move on to cursors; you will use cursors (in one form or another) to create transactions (i.e., specify the SQL statements which make up the transaction).