1  LOCK_TABLE
   Specifies a list of tables to be readied in a given lock mode and
   added to the list of reserved tables for the current transaction.
   If a view is specified, then the base tables referenced by the
   view are locked in the specified lock mode.
 

2  Environment
   You can use the LOCK TABLE statement in a compound statement of a
   multistatement procedure:

   o  In interactive SQL

   o  Embedded in host language programs

   o  As part of a procedure in an SQL module or other compound
      statement

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 LOCK TABLE --+-+--> <table-name> --+-+--> FOR -+-->lock-mode MODE --++
              | +-------- , <-------+ +--> IN --+                    ||
              +--------------------- , <-----------------------------+|
 +------------------------------------ <------------------------------+
 +------------+----------------------------+-------------------------->
              +-----> NOWAIT --------------+
              +--+-------------+--> WAIT --+
                 +--> DEFAULT -+



 lock-mode =
 
 ---+---> SHARED  -----+---+---> DATA DEFINITION -+-->
    +---> PROTECTED ---+   +---> READ ------------+
    +---> EXCLUSIVE ---+   +---> WRITE -----------+

 

2  Arguments
 

3  DATA_DEFINITION



   Syntax options:

   DATA DEFINITION | READ | WRITE

   See the SET TRANSACTION statement for a description of the
   arguments.
 

3  lock_mode

   Syntax options:

   IN lock-mode MODE | FOR lock-mode MODE

   Specifies the lock mode to be used for the specified tables and
   views. The IN and FOR keywords are synonymous. A table lock mode
   can be promoted, but cannot be demoted. For example, you can
   promote a SHARED READ lock to SHARED WRITE, but you cannot demote
   a SHARED WRITE mode to a SHARED READ mode.

   See the SET TRANSACTION statement for a description of these
   arguments.
 

3  table-name

   The names of one or more tables or views currently existing in
   the database that you want to lock and reserve. You can specify
   tables created as GLOBAL or LOCAL TEMPORARY TABLES, but they
   will be ignored because these types of tables do not contain
   shared data and so are never locked. You can specify tables from
   multiple databases by using the alias name as a prefix to the
   table name. If you do not specify an alias, then the default
   alias is used.
 

3  wait-mode

   Syntax options:

   WAIT | NOWAIT | DEFAULT WAIT

   Specifies what the LOCK TABLE statement does when it encounters
   a locked table. If you specify WAIT, the statement waits for
   other transactions to complete and then proceeds. If you specify
   NOWAIT, your transaction returns an error message when it
   encounters a locked table. If you specify DEFAULT WAIT, then
   the lock mode specified for the current transaction is used.
   If you specify a different lock mode than was specified for the
   transaction, the mode you specify with the LOCK TABLE statement
   takes precedence, unless the table is already reserved.

   The WAIT clause is the default.
 

2  Examples

   Example 1: Locking a Table in READ MODE

   SQL> LOCK TABLE EMPLOYEES IN PROTECTED READ MODE NOWAIT;

   Example 2: Locking Two Tables in Different Modes

   SQL> LOCK TABLE DB1.JOB_HISTORY IN SHARED WRITE MODE,
   cont>      DB2.SALARY_HISTORY IN EXCLUSIVE WRITE MODE;