Progress
SQL-92
Guide and Reference


LOCK TABLE Statement

Explicitly locks one or more specified tables for shared or exclusive access.

SYNTAX

LOCK TABLE table_name [ , table_name ] , ...  
  IN { SHARE | EXCLUSIVE } MODE ; 

table_name

The table in the database that you want to lock explicitly. You can specify one table or a comma-separated list of tables.

SHARE MODE

Allows all transactions to read the table(s). Prohibits all other transactions from modifying the table(s). After you acquire an explicit lock on a table in SHARE MODE, any SELECT statements in your transaction can read rows and do not implicitly acquire individual record locks. Any INSERT, UPDATE, and DELETE statements do acquire record locks.

EXCLUSIVE MODE

Allows the current transaction to read and modify the table(s), and prohibits any other transactions from reading or modifying the table(s). After you acquire an explicit lock on a table in EXCLUSIVE MODE, you can SELECT, INSERT, UPDATE, and DELETE rows and your transaction does not implicitly acquire individual record locks for these operations.

EXAMPLES

Unless another transaction holds an EXCLUSIVE lock on the teratab and megatab tables, the SHARE MODE example explicitly locks the tables. The shared lock allows all transactions to read the tables. Only the current transaction can modify the tables:

LOCK TABLE teratab, megatab IN SHARE MODE ; 

Unless another transaction holds a lock on the teratab table, the EXCLUSIVE MODE example locks the teratab table for exclusive use by the current transaction. No other transactions can read or modify the teratab table:

LOCK TABLE teratab IN EXCLUSIVE MODE ; 

Without a table lock, the first SELECT statement in this example could exceed the limits of the record lock table. The LOCK TABLE statement prevents the subsequent SELECT statement from consuming the record lock table:

-- Without a table lock, this SELECT statement creates an 
-- entry in the record lock table for every row in teratab.  
  
SELECT COUNT (*) FROM teratab ; 
  
-- The LOCK TABLE IN SHARE MODE operation preserves the 
-- record lock table resource. 
  
LOCK TABLE teratab IN SHARE MODE ; 
SELECT COUNT (*) FROM teratab ; 

NOTES

AUTHORIZATION

Must have DBA privilege or SELECT privilege on the table.

SQL COMPLIANCE

Progress Extension

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications, JDBC applications

RELATED STATEMENTS

COMMIT Statement, ROLLBACK Statement, SET TRANSACTION ISOLATION LEVEL Statement


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095