Neoview SQL Reference Manual (R2.4 SP2)

LOCK TABLE Statement
• “Syntax Description of LOCK TABLE”
• “Considerations for LOCK TABLE”
• “Examples of LOCK TABLE”
The LOCK TABLE statement locks all partitions of a table (or the underlying tables of a view)
and its indexes, limiting other access to the table and its indexes while your process executes
DML statements. Locks are held until the end of the transaction. See “Database Integrity and
Locking” (page 27).
LOCK TABLE is a Neoview SQL extension.
LOCK TABLE table IN {SHARE | EXCLUSIVE} MODE
Syntax Description of LOCK TABLE
table
is the name of the table, view, or materialized view to be locked. See “Database Object Names”
(page 230).
IN {SHARE | EXCLUSIVE} MODE
specifies the locking mode:
Other processes can read, but not delete, insert, or
update the table or view.
SHARE
Other processes can read with READ UNCOMMITTED
access, but cannot read with READ COMMITTED or
SERIALIZABLE access, and cannot delete, insert, or
update the table or view.
EXCLUSIVE
If you request a share lock on a table locked with an exclusive lock by another user, your
request waits until the exclusive lock is released. A request for a share lock can also timeout
and result in an error 73 type error when another transaction has an exclusive lock on the
table.
If you request an exclusive lock on a table and any part of the table is locked by another user,
your request waits until the lock is released, or until your lock request times out and an error
message is returned.
Considerations for LOCK TABLE
Authorization Requirements
To lock a table, you must have authority to read the table. To lock a view, you must have authority
to read the view but not necessarily the tables underlying the view.
Modifying Default Locking
A SELECT statement automatically acquires share locks unless you specify EXCLUSIVE MODE
in the IN clause of the SELECT statement. The DELETE, INSERT, and UPDATE statements
automatically acquire exclusive locks.
You can use LOCK TABLE with the EXCLUSIVE option to force the use of exclusive locks for a
subsequent SELECT; however, keep in mind that LOCK TABLE locks the entire table.
Effect of AUTOCOMMIT Option
At the start of a session, the AUTOCOMMIT option is ON by default. When this option is ON,
Neoview SQL automatically commits any changes, or rolls back any changes, made to the database
148 SQL Statements