Contents Index LOAD TABLE statement LOOP statement

ASA SQL Reference
  SQL Statements

LOCK TABLE statement


Description 

Use this statement to prevent other concurrent transactions from accessing or modifying a table.

Syntax 

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

Parameters 

table-name    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    If this clause is specified, the lock is held until the end of the connection. If the clause is not specified, the lock is release when the current transaction is committed or rolled back.

SHARE mode    Prevent other transactions from modifying the table, but allow them read access. In this mode you can change data in the table as long as no other transaction has locked the row being modified, either indirectly or explicitly using LOCK TABLE.

EXCLUSIVE mode    Prevent other transactions from accessing the table. No other transaction can execute queries, updates of any kind, or any other action against the table. If a table t is locked exclusively with LOCK TABLE t IN EXCLUSIVE MODE, the default server behavior is to not acquire row locks for t. This behavior can be disabled by setting the SUBSUME_ROW_LOCKS option OFF.

Usage 

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.

The locks placed by LOCK TABLE in SHARE mode are phantom and anti-phantom locks, which are displayed by the sa_locks procedure as PT and AT.

Permissions 

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.

Side effects 

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

See also 

SELECT statement

sa_locks system procedure

Standards and compatibility 
Example 

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

LOCK TABLE customer
IN SHARE MODE

Contents Index LOAD TABLE statement LOOP statement