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;

13.4 Autonomous Transactions

Before the release of PL/SQL 8.1, each Oracle session could have at most one active transaction at a given time. In other words, any and all changes made in your session had to be either saved or erased in their entirety. This restriction has long been considered a drawback in the PL/SQL world. Developers have requested the ability to execute and save or cancel certain DML statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction.

You can now accomplish this goal with the autonomous transaction feature of PL/SQL 8.1 and above. When you define a PL/SQL block (anonymous block, procedure, function,packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction.

Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and resume the main transaction. This flow of transaction control is illustrated in Figure 13-4.