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.
1 – 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
(B)0[m[1;4mLOCK[m[1m [1;4mTABLE[m[1m qqwqwqq> <table-name> qqwqwqq> [1;4mFOR[m[1m qwqq>lock-mode [1;4mMODE[m[1m qqwk [m [1m x mqqqqqqqq , <qqqqqqqj mqq> [1;4mIN[m[1m qqj xx [m [1m mqqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqjx [m [1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqq> [m [1m tqqqqq> [1;4mNOWAIT[m[1m qqqqqqqqqqqqqqu [m [1m mqqwqqqqqqqqqqqqqwqq> [1;4mWAIT[m[1m qqj [m [1m mqq> [1;4mDEFAULT[m[1m qj [m (B)0[m[1mlock-mode = [m [1m [m [1mqqqwqqq> [1;4mSHARED[m[1m qqqqqwqqqwqqq> [1;4mDATA[m[1m [1;4mDEFINITION[m[1m qwqq> [m [1m tqqq> [1;4mPROTECTED[m[1m qqqu tqqq> [1;4mREAD[m[1m qqqqqqqqqqqqu [m [1m mqqq> [1;4mEXCLUSIVE[m[1m qqqj mqqq> [1;4mWRITE[m[1m qqqqqqqqqqqj [m
3 – Arguments
3.1 – DATA_DEFINITION
Syntax options:
DATA DEFINITION | READ | WRITE
See the SET TRANSACTION statement for a description of the
arguments.
3.2 – 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.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.4 – 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.
4 – 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;