Lesson 42 : LOCK Table

LOCK TABLE

LOCK TABLE statement is to lock one or more tables in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation.

Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table.

A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table.

A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

Syntax :

LOCK TABLE tables IN lock_mode MODE [NOWAIT];

Lock_mode is one of the following,

ROW SHARE  -  permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

ROW EXCLUSIVE -  is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

SHARE UPDATE - same as ROW SHARE.

SHARE - permits concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE -  is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

EXCLUSIVE - permits queries on the locked table but prohibits any other activity on it.

NOWAIT

 Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.

If you omit this clause, then the database waits until the table is available, locks it, and returns control to you.