LOCK TABLE statement

LOCK TABLE table-name
[ WITH HOLD ]
IN { SHARE | EXCLUSIVE } MODE

table-name The name of the table. The table must be a base table, not a view. As temporary table data is local to the current connection, locking global or local temporary tables has no effect.
WITH HOLD clause Specify this clause to lock the table until the end of the connection. If the clause is not specified, the lock is released when the current transaction is committed or rolled back.
IN SHARE MODE clause Specify this clause to obtain a shared table lock on the table, preventing other transactions from modifying the table but allowing them read access. If a transaction puts a shared lock on a table, it can change data in the table provided no other transaction holds a lock of any kind on the row(s) being modified. Read locks on individual rows are not acquired when the IN SHARE MODE clause is selected.
IN EXCLUSIVE MODE clause Specify this clause to obtain an exclusive table lock on the table, preventing other transactions from accessing the table. No other transaction can execute queries, updates, or any other action against the table. If a table is locked exclusively with a statement such as LOCK TABLE...IN EXCLUSIVE MODE, the default behavior is to not acquire row locks for the table. This behavior can be disabled by setting the subsume_row_locks option to Off.

The LOCK TABLE statement allows direct control over concurrency at a table level, independent of the current isolation level.

While the isolation level of a transaction generally governs the kinds of locks that are set when the current transaction executes a request, the LOCK TABLE statement allows more explicit control locking of the rows in a table.

You cannot execute the LOCK TABLE statement on a view. However, if you execute the LOCK TABLE statement on a base table, a shared schema lock is created, which locks dependent views. A shared schema lock persists until the transaction is committed or rolled back.

To lock a table in SHARE mode, SELECT privileges are required.

To lock a table in EXCLUSIVE mode, you must be the table owner or have DBA authority.

Other transactions that require access to the locked table may be delayed or blocked.

SQL/2008 Vendor extension.

The following statement prevents other transactions from modifying the Customers table for the duration of the current transaction:

LOCK TABLE Customers
IN SHARE MODE;

LOCK TABLE statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example